8 Records in Table - Query returns 2 records. Formula to subtract each Qty and add to a new table

[ctpaulm]

Hi, I'm in waaay over my head. I know some basics, but I need a little help to move forward! This is a "Cash Register" (Point of Service/POS), "Inventory", "Contacts", "Supplies", "Expenses", etc...

In POS Table, Style Code became a Lookup Column (did I do that?)
In Inventory Table, 8 records listed. Query of Inventory returns Record 1 and 7. ???
Last Name, Style Code and Description are not correct. I have a relationship under the Style Code & Desc, and I'm not allowed to change these controls (can do it thru relationships?) I've been hurting my brain over this, for hourssssss! Thank you!!
In regards to Inventory, I would like the Primary Key, Style Code be decreased by number (Qty) field in the Inventory Table, and have it create new Field "SOLD". In other words, as the QTY on the POS Table decreases, it should reflect on the Inventory Table.... BUT no relationship the other way around (can add into Inventory Form, but do not have it add to any POS data.



ANY guidance is greatly appreciated!!!

[June7]

Should not save aggregate data to table. Inventory balance should be calculated when needed.

Enter raw data records of inventory received and inventory used, sum the data and calculate difference.

[ctpaulm]

Thank you June7!! So I will let the POS Form drive the whole system, and manually enter inventory, each with unique StyleCode. Won't it still need to be defined in the inventory table (first)? I know what we're selling, so I can list out the items, and when it comes to a sale, I would select the StyleCode (primary) that matches up with Description from a dropdown. INSTEAD of having Access work it, go into Inventory and manually delete/change the Qty('s)? That sounds fine if I understand correctly, I just thought I could automate that.

I still have the issue of the StyleCode, which became Style Code Lookup Column. Same with Description. I obviously did something, but I don't know. Trial and error! These fields can not be changed because they belong to relationships. I don't see any, but maybe I'm looking in the wrong area...

I'm so sorry for my questions as they may not make sense.. I only used Access before for my Xmas Card Labels!! lol

Thanks, Paul

[June7]

Review http://allenbrowne.com/AppInventory.html

[orange]

ctpaulm,

You might want to review and work through this tutorial from RogersAccessLibrary.
It will help with database design, tables and relationships. You can use what you learn with your own database.
Getting the proper design to support your business is critical to database.

You should also read and understand Normalization (tutorial starts here).

Good luck.