I hope to make myself clear but ask any question if I dont.

I have an inventory DB, with all sorts of information as you can imagine. I have a model table that has the model number and device type as well as other items. I have a table that has the install date of a device at a location.

I can produce a report that shows the total number of devices by device type.

Now comes the design issue.

End user runs the report on the last day of January and gets 150 routers in production. They put that in a spreadsheet. Then on the last day of February they run the report and get 148. they do this every month and manually update the excel spreadsheet. I want to be able to generate it from access. Not sure where to begin. If the install date is 12/15/2011, that is the only date on the record. I can agree that seeing as it is still in production on 3/30/2012 that it should be counted in January, February and March. Would I be better server to create a table to hold the results or device some elaborate query that can put the counts in the proper months.

Hi Bob,

Sorry, I don't understand the question. You said: "End user runs the report..." So, it sounds like to me that you are already doing this from Access.

What I don't get is what do the users do with the Excel spreadsheet? Is it a cummulative list of production numbers for each month? If that's what you're trying to duplicate in Access, then I think you can just store the results of each report run into a table.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

Bob G,

Not to make any assumptions. Could you please explain how you're using the term "production" -


AvgJoe (IMG:style_emoticons/default/hat_tip.gif)

How, in the table, do you know it's still in production?

Yes, DBG, you have it.

The user takes the monthly numbers from access and keeps a YTD spreadsheet. So, you think making a table would be the right idea here ? I am not sure they have ALL the information in the spreadsheet, there might be device types that they dont have that we would want to show going forward. Getting those numbers into the table might be tricky

Merle,

There is a status field. There is also a status changed date field if the device moves from production to spare or vice versa.

Joe,

Yep. That is the way I am looking at it also

OK. I came up with a way to do it because I can't use the spreadsheet as a starting point.

I have a query for every month for 2012 and one query for all years less than 2012.

the difference is the name of the field and the number of the month. I guess now I need to put them in a cross tab query ??

The end result would be the total number of devices at the end of 2011 and then a column for each month with the additions.