Hi,
I'm in the conceptual phase of this database, and I'm working with the attached Excel sheet for my customer. I'm trying to take what they manually enter into a spreadsheet, and automate through a database design. In short, it is a sales usage report that shows the quantity of accumulated shipped units per item.
Column A are my items I sell to my customer. Column B, Order Amount, is always going to be a fixed value for the starting quantity of goods in inventory. I figured I would create a table in my db representing columns A&B at this point. Column C, Shipped, is going to be the accumulated total of shipped goods for a particular starting point to present. To get this value, my plan was to use Monarch and dump/append sales report into usage table(s), for whatever normalization called upon. To reconstruct what I show on the attached sheet, I figured I would use a query and build a relationship between the item #'s on the usage table and the item #'s on the first table I mentioned at the beginning of this paragraph that contain Column A and B. For easier purposes I have left out the item #'s and used descriptions. From there I would display the description, fixed amount, summarized usage, and calculate the remaining to ship by subtracting fixed minus sum. of usage.
This leads me to why I am here... Coulmns E and F are relative to the change in report to report. I have to turn this report in twice a week, Tuesday's and Thursday's. E&F show the change in shipments from one report to the next. We may not have ship goods on everything between reports, or ship anything at all. So my question is how do we calculate, by item, the difference in total usage this report from the last report. I know somehow I need to store a snapshot of the previous report, and then each new report will take the spot of that previous report. Also, my customer likes to have an X appear in column E when column F denotes a change. If anyone is able to help on that as well then that would be great.
Mucho gracias to anyone that can help!!
Rob
I'm in the conceptual phase of this database, and I'm working with the attached Excel sheet for my customer. I'm trying to take what they manually enter into a spreadsheet, and automate through a database design. In short, it is a sales usage report that shows the quantity of accumulated shipped units per item.
Column A are my items I sell to my customer. Column B, Order Amount, is always going to be a fixed value for the starting quantity of goods in inventory. I figured I would create a table in my db representing columns A&B at this point. Column C, Shipped, is going to be the accumulated total of shipped goods for a particular starting point to present. To get this value, my plan was to use Monarch and dump/append sales report into usage table(s), for whatever normalization called upon. To reconstruct what I show on the attached sheet, I figured I would use a query and build a relationship between the item #'s on the usage table and the item #'s on the first table I mentioned at the beginning of this paragraph that contain Column A and B. For easier purposes I have left out the item #'s and used descriptions. From there I would display the description, fixed amount, summarized usage, and calculate the remaining to ship by subtracting fixed minus sum. of usage.
This leads me to why I am here... Coulmns E and F are relative to the change in report to report. I have to turn this report in twice a week, Tuesday's and Thursday's. E&F show the change in shipments from one report to the next. We may not have ship goods on everything between reports, or ship anything at all. So my question is how do we calculate, by item, the difference in total usage this report from the last report. I know somehow I need to store a snapshot of the previous report, and then each new report will take the spot of that previous report. Also, my customer likes to have an X appear in column E when column F denotes a change. If anyone is able to help on that as well then that would be great.
Mucho gracias to anyone that can help!!
Rob
