Using forms to add or subtract quantites from a table

[borntobeoutdoors]

Complete newbie here. Programed Alpha 4 and early Access, but at a complete loss here.

What i want to do is the following.

I have a table with four fields. Product, In Stock, Price and category. I want to create a form that will allow me to use a list box to select the product and then enter the amount I want to ADD to the current quantity. In other words if there are 3 of the number in stock and I enter 2 in the form, it will change the quantity field in the table to 5. Then I want to create an invoice form that will allow me select items from the product field, enter the number sold and have it then subtract that total from the quantity field notifying me if there is not enough in stock. And then printing the invoice once it is complete.

I have a few other ways i would like to manipulate the data as well but I need to know if the above an be done with base as well as the best place to find the instructions or manual to do so. Most of what i have found is pretty basic (which is helpful in getting started) but doesn't approach what i need to learn.

Is there a book or documentation with complete instructions for working with all the features in base?

I have no difficulty for paying a reasonable price for such documentation.

Thanks in advice for any suggestions.

[Villeroy]

Database with 2 tables and a relation:
[Articles.ID] 1--n [Quantities.Article.ID and Counter plus auto-ID and/or time-stamp]

Articles-form with 2 subforms linked through Article-ID.
Editable Subform1 allows insertion of new values only. It collects plus and minus integers.
Calculated Subform2 shows the Sum(Quantities.Counter) for the related article-ID, refreshable through a refresh-button.

Another thing I can think of:
2 quantity tables for articles coming and going, both collecting positive integers and a balance-query to get the total.

[borntobeoutdoors]

Database with 2 tables and a relation:
[Articles.ID] 1--n [Quantities.Article.ID and Counter plus auto-ID and/or time-stamp]

Articles-form with 2 subforms linked through Article-ID.
Editable Subform1 allows insertion of new values only. It collects plus and minus integers.
Calculated Subform2 shows the Sum(Quantities.Counter) for the related article-ID, refreshable through a refresh-button.

Another thing I can think of:
2 quantity tables for articles coming and going, both collecting positive integers and a balance-query to get the total.

Thanks but that's still all Greek to me. It's been a long, long time since I've done anything with databases or spreadsheets and when i did, i had pretty extensive books that were complete, yet written in such a way that a person without a programmers background could understand them. There were some step by step, hands on examples along with a floppy disk (remember those??!!) with sample applications on them that one could look at to see how they worked.

That's sort of what i'm looking for.

I have plenty of time (retired except for my small business), but not a lot of expertise.

Thanks again

[Villeroy]

I was talking about the same type of relational database that was established as "relational database" somewhere in the 80ies. This office suite and all of it's tools are extremely old-fashioned. It's just a huge program to open office files and an additional tool set to import plain, old-fashioned, vanilla SQL-databases with 2-dimensional tables and one-to-many relations (1:n) between tables. There is nothing web-based, object-oriented, multimedia or anything that did not exist back in the early 90ies. The only revolutionary aspect of this office suite is the ODF file format.
http://wiki.services.openoffice.org/wik ... n/Database

[Villeroy]

The attached DB contains
- a table of articles with name and auto-incrementing ID-Number(primary key)
- a table of input entries with article-ID (foreign key), time-stamp, quantity and auto-incrementing ID-Number(primary key)
- a table of output entries with article-ID (foreign key), time-stamp, quantity and auto-incrementing ID-Number(primary key)

Setting the default value of the stamps can't be done in the graphical interface:
Call the command line menu:Tools>SQL...

Code: Select all

ALTER TABLE "Input" ALTER COLUMN "Stamp" TIMESTAMP DEFAULT CURRENT_DATE NOT NULL;
ALTER TABLE "Output" ALTER COLUMN "Stamp" TIMESTAMP DEFAULT CURRENT_DATE NOT NULL;

main-menu:Edit>Database>AdvancedSettings... "Form input checks required fields" = OFF

menu:Tools>Relations... shows the relations between the 3 tables (referential integrity to enforce input and output of listed articles only)

A query to sum input, output and inventory for each article. It could be the source of a report.

Two manually designed forms to insert new input and output. Picking an article by name sets the foreign key number "Article_ID", "Quantity" allows for integer numbers >0. Pick an article, tab, set number, Enter.

Edit: Thanks to this forum's most valued member eremel and after a thousand downloads I got a note that my In_Out_Database.odb has a major flaw in its balance query. It calculates wrong differences between input and output. Hereby I attach a better version In_Out_Database2.odb which queries the cumulated inputs and outputs for all item IDs (including existing items that did not go in or out yet) and a third query which combines these two queries with each other and with the item name

[borntobeoutdoors]

The attached DB contains
- a table of articles with name and auto-incrementing ID-Number(primary key)
- a table of input entries with article-ID (foreign key), time-stamp, quantity and auto-incrementing ID-Number(primary key)
- a table of output entries with article-ID (foreign key), time-stamp, quantity and auto-incrementing ID-Number(primary key)

Setting the default value of the stamps can't be done in the graphical interface:
Call the command line menu:Tools>SQL...

Code: Select all

ALTER TABLE "Input" ALTER COLUMN "Stamp" TIMESTAMP DEFAULT CURRENT_DATE NOT NULL;
ALTER TABLE "Output" ALTER COLUMN "Stamp" TIMESTAMP DEFAULT CURRENT_DATE NOT NULL;

main-menu:Edit>Database>AdvancedSettings... "Form input checks required fields" = OFF

menu:Tools>Relations... shows the relations between the 3 tables (referential integrity to enforce input and output of listed articles only)

A query to sum input, output and inventory for each article. It could be the source of a report.

Two manually designed forms to insert new input and output. Picking an article by name sets the foreign key number "Article_ID", "Quantity" allows for integer numbers >0. Pick an article, tab, set number, Enter.

Thanks, I'll look these over and see if they penetrate my thick skull. It'll give me a bit if a start.

Much appreciated.

Hawk

[Villeroy]

Thanks, I'll look these over and see if they penetrate my thick skull. It'll give me a bit if a start.

A bit of a start indeed. However, don't take the bate developing a whole enterprise resource planning system in Base. That is just a theoretical option.

Base is a fine tool to connect office documents with existing databases from various sources. It's fine to dump addresses and names into your serial letters and you can easily use database data as input for your spreadsheet calculations without caring about file formats and other limitations (in fact this is even better than using the spreadsheet's data).

In a second step you may consider input forms to write manually entered data back into your existing database.

The built-in database Base creates newly from scratch, contained in a single file, accessible for a single user at a time, is nothing more than a nasty plastic toy. The issue with the automatic time-stamp may give a hint related to usability and feature-completeness, but there are more serious ones. You do apply a well proven backup strategy for your business data, don't you?
This type of integrated database is a nice thing for training purposes. The underlying HSQL-database is fully documented at http://hsqldb.org/doc/guide/ch09.html It complies with valid standards, so you can easily transfer that knowlege (and literal SQL-statements) to most other databases on the market.