Calculation and Autofill Question

[SMC]

Let me paint the picture:

Every item I have in inventory has a unique ID number and base value - lets call it 'number sold' to date.

Every week I sell items and I increase the 'number sold' value.

When I fill out my form I would like this new 'number sold' value to populate a box telling me how many I have sold prior to this week. On paper this would look something like Base value + 'number sold' (for every week) = new 'number sold'

How can I populate this box in my form?
I have the 'number sold' each week filling into a raw data table and my base number is in another table which indexes my constant values such as ID number, item name, etc...

I hope that I made this clear enough but pelase post if I need to verify anything.
Thanks!

[mholland999]

If you want to track the history of how many units of a product you sold each week, You should store the productID, datesold, and Quantity in a separate table. This allows you to do a couple of things:

1 you can calculate the total units sold at any time, or by year or by month using the aggregate sum of the qty field
2 you and query all items sold over the past x days

[SMC]

If I store the productID, datesold, and Quantity in a seperate table rather than my complete raw data Table, I won't have this information in my raw data table to create my reports.

The way I have it set up, which makes the most sense to me; (but I don't know how to go about coding it.)

I have a Product table which contains all of my constant information, i.e. base value.
I have a Raw Data table which communicates with my form that contains all of my changing data such as Date and Weekly Sales.
Each table has a ProductID table.

The only way, with my limited knowledge, to create my calculation would be Dlookup(Equation) (if it's possible) using the table references above. This is how it would look:

("[Base Value]", "Product Table", "[ProductID] =" & Forms!Input!txtProductID) This would just find the base value of the relevant ProductID

Before the base value is entered into the text box I want it to be added to the the summation of all items sold (for all dates).
("[Sum of all weekly sales for the relevant ProductID]", "Raw Data Table", "[ProductID] =" & Forms!Input!txtProductID)

[mholland999]

Can you give me a snapshot of your form so I can see how you are entereing your data? I could probably write a line of code for you.

[SMC]

Mholland, I have a picture already uploaded in my profile album. You will notice that I used arbitrary names for values in this thread to make it as simple as possible not thinking anyone would request this.