Query to calculate current inventory.

[shanep4db]

I have developed a simple inventory database for work (my first project with Access) and I pretty much have everything so it's usable. However, I have one problem.
Let me first describe how my database is setup. I have a main table, BacT, which contains the following information: ID(PK, autonumber), Lot number, Item description, location, and starting inventory. I have a second table, BacT-WithdrawalHistory, which contains the same information except starting inventory is changed to InventoryWithdrawn and ID, also an autonumber, is different. The database contains a form, which is linked to BacT, that contains a subform linked to BacT-WithdrawalHistory. The form also contains a simple calculation to display every item's current inventory. So far, so good. I have set up a query, CurrentInventory, to determine the current inventory of a specific lot number of material that is based on InventoryWithdrawn from BacT-WithdrawalHistory subtracted from StartingInventory of BacT. The query works fine for everything that has any sort of withdrawal history. But, the items that have not had an inventory count change do not show up in the BacT-WithdrawalHistory table and therefore do not appear in the query, CurrentInventory. CurrentInventory will be used to generate a report every few months.
What is the best way to solve this dilemma? Actually, I'll take any solution that works for now. I've made it this far and I could make an entry on the form where the amount withdrawn is zero for every item in BacT in order to get it to show up on BacT-
Withdrawal History to get by for now but this database will eventually be passed on to other people who have absolutely no Access experience and I need to make it very idiot-proof.
Any help will be greatly appreciated!
Thanks,
Shane

 

[AncientOne]

I would think that you don't have the query correctly set up. You should edit the join in the query to show all the records from the main table and all the records from the withdrawal table that are matched in the main table. This is a Left Join. You edit the join by clicking on it in design view. But I would question the way you have set up your tables anyway. You have the same data twice?

 

[shanep4db]

Thanks for the response, Ancient One. After perusing message posts in this forum I have come to the conclusion that my database has some inherent flaws. I do have the same data twice: Lot number , Item description, and location are examples of the same data being in two different tables.
This is my first Access project and I want to learn how to set databases up correctly because, if this one is a success, I'm sure I will be asked by my superiors to construct other databases.
Based on the limited information I have listed in this thread, can anyone help me with the correct manner in which to set up my tables? If I have proper table structure I think I can follow up with queries and forms on my own. Please remember that this is my first database (except for Excel, which I have learned from this forum, has corrupted my vision of what a database should be) so I will need fairly specific instruction.

Again, I appreciate any help which may be given!

Shane

 

[AncientOne]

As setting up tables is a complex process, it's not something that can easily be condensed into a few sentences. A basic book on database design is your best bet.

If you post your database with some sample data in zip form, I or someone else will probably look it over and point out any glaring errors. Having the same data repeated in two tables definitely flies in the face of good database design, that's for sure. It's just not necessary.