Calculating Query

[faye]

<p>Hi all, i am facing a problem that i don't any idea to go about doing in. Basically, i have a table listing a list of figures which looks like this.</p>

<p>Part No........ Product Builded......... Calender Period
Ver1000.................1000..................... 11 2005
Ver10001 ...............2000.................... 12 2005
Ver10001 ...............2500...................... 1 2006</p>

<p>i have another table which looked like this.</p>

Part No.................. Inventory............. Calender Period
Ver10001................... 300....................... 10 2005

<p>How can i use a query in Access to sum up the inventory figures very month? example: for calender period 11 2005, the inventory will be 1300. While for calendar period 12 2005, the inventory will be (1300+2000) which is 3300, so on and so fore.
Thanks!!!</p>

[XLGibbs]

This is a little tricky to explain, but can be done. Can you post a sanitized version of your database..it would be easier to set up a sample than explain the process..

You would have to have your two tables linked in PartNo, and have another Calendar period table visible in the query and joined as well.

The calculation would be built using a Sum([Table1]!Inventory +[Table2]!Built + [Table3]!Built)

And the criteria would be

Where [Table3]!CalendarPeriod <= [Table2]!CalendarPeriod

It is tricky, as I said...

[faye]

2843

Thanks for the reply!
I've attached the file..
Thanks again for the help!

[XLGibbs]

Faye, are the calendar periods in your sample the same as the "real" data? Are they text formatted 10 2005 and 1 2006 format? or will there be actual dates in there?

[XLGibbs]

Faye, it is a little trickier since the Calender Period is not an actual date, but the attached shows a method of doing what you ask. First a query is made to provide the running total of the "Products Builded" field from that table.

A second query then joins up the Inventory table and This new query on Part Number and the aggregate of the RunningBuilt total and the Starting inventory is calculated....

Hope that helps.

In the attached, the running total of the products builded field isin the query RunningBuilt and the Aggregates are in the AggregateTotals query.

[faye]

Thanks. It works great. However, is it better to use the real date format for the tables? As i feel that it is "unsafe" to use the ID assign by Access to identify the fields...

[XLGibbs]

Yes, it is better to use a real date, as it is more useable for sorting and formatting and such....

In my example I think I used the ID field - 1 to specify "last month". If the date were a real date field (preferably identified by month/1/year)

you could use a date formula....in the critiria instead...

the date field would be "last month" by doing:

dateadd(m,-1,[PeriodDate])


My sample was just to show the idea of behind my thought process and how I generally get this kind of issue done. It is consistent with this article by MSN

http://support.microsoft.com/?kbid=208953

which more efficient, but my example was meant to be a little more specific when looking at it as to what I did.

Hope it helps.

[faye]

I see... I think i will use a real date for all my tables. But i am still quite confuse about using the method u are talking about and how to go about building the Query...

[XLGibbs]

I am not sure I can explain it terms that would be more understandable than the example. I will take a look as soon as I can at the latest db attachment. It may be a couple of days before i can get a chance though....