Thursday, May 12, 2022

10:00 AM

Hello!

I am creating a database to manage multiple households.

The problem I am running into is measuring Average life of an item.

I am currently trying to use 3 tables to fix this. I have table {Supplies} where I have my item records, {Log} which keeps track of weekly inventory checks, and {Orders} which records orders placed for this item.

In the {Log} table I have a different record for checking each of the items in my household, I group them by Check Date. In {Orders} I group the items that are ordered together. And am manually entering Date Ordered and Date Received.

My problem is that I can’t seem to find a way to calculate days elapsed since the previous order for each entry in the {Log} without manually entering the date of the previously received order for this item.

I think I need to find a way for the Previous Order Received Date the records in the {Log} table to stop updating so that I can measure Days Elapsed on that record. (DateTime_Diff (Previous Order Received Date-Date Ordered))

I’d really appreciate some help on this.

Thanks!