Inventory System by date, with times

[kmh83]

Hello,

I've used access in the past, and apparently forgotten everything I've learned. I'm afraid that I won't be able to communicate this well, so bear with me.

I work for a company that sells apples, oranges, tomatoes, and bananas. For each day, I must record the inventory at 7a, 11a, and 3p. My long term goal is to be able to run reports over a long period of time to look for trends such as, highest day at 7a over the last six months. Previously I had a poorly designed Excel sheet that didn't allow me any flexibility.

I think I need:

Table 1: Date, Item Name, 7a, 11a, 3p
Table 2: List of Items, Custom Sort Order for items, Type of Item

I need to be able to have the date be static for today's date (set in the morning). I would like to have a form prepopulated with the Date, a Row of Item names, and columns for the times. I want only one day per sheet.



Does this make sense? I've tried a lot of methods, queries, forms/subforms and I just can't seem to connect the dots. I've watched hours of youtube videos. I'm pretty sure that I am missing something basic. Like the relationships between the tables maybe?

Thanks in advance for any advice whether it be steering me to resources or offering your experience.

Best,

KMH83

[Micron]

Can't devote much time to this at this hour but to quote you

I think I need:
Table 1: Date, Item Name, 7a, 11a, 3p
Table 2: List of Items, Custom Sort Order for items, Type of Item

definitely NOT. That is an Excel influenced design. You need to review and understand relational database design. Perhaps
Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.com...ng-part-i.html

I suspect you should review other db design concepts but I don't want to presume too much at the moment.

[madpiet]

Why not something simple like (DateTime, Product, Quantity On Hand) ?

Product--(1,M)--Inventory--(M,1)---Calendar.

and then I'd do any analysis in Excel or PowerBI.