Inventory Count By Month

[Guest]

Access 2000

As in all businesses, inventory counts continually move. What I am trying to do is get a historical picture of my inventory so I can compare it to other historical data.

My data is about cars and in my table I have a "Unit #", a "Date In Service" and a "Sold Date". We keep cars (units) for about 36 months and when we are done with the car we sell it (and put the Sold Date in the table). What I am trying to do is produce a query that shows the historical inventory by month. For example, I would like to see how many units i had in inventory for January 2002.

My Data has the intial date in service and if it hasbeen sold (or out of service) there is a date. ) What I cant figure out is how to get a count of all the units for a specific month that are greater than the Date in Service but is less than the sales date or where the sales date is blank?

Can anyone help. Here is what the end results should look like:

Month/Year Unit Count
1/02 742
2/02 735
3/02 754
4/02 767
Etc.........

 

[Allen Browne]

The report needs to get a list of months from somewhere.

1. Create a table containing just one date/time field, marked as primary
key, and enter for the first day of each month, e.g.:
2/1/02
3/1/02
4/1/02
Save the table.

2. Create a query containing this table, and your other table. The must be
no line joining the 2 tables in query design view. The lack of any join
creates a record for every combination (i.e. each vehicle will show up under
every month).

3. Drag the date field from the new table into the output grid. In the
Criteria row under this field, enter:
Between [Date In Service] And Nz([Sold Date], #1/1/9999#)
This limits the query so each vehicle only shows up under the dates it was
in service.

4. Depress the Totals button on the toolbar.
Access adds a Total row to the grid.
In the Total row under your date field (above), choose Where.

5. Drag the date field into the grid again. In the Total row, accept Group
By.

6. Drag the Unit# field into the grid. In the Total row, choose Count.

The query creates a record for each date in the date table, and counts the
number of vehicles in service for each date.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Access 2000

As in all businesses, inventory counts continually move. What I am trying

to do is get a historical picture of my inventory so I can compare it to
other historical data.

My data is about cars and in my table I have a "Unit #", a "Date In

Service" and a "Sold Date". We keep cars (units) for about 36 months and
when we are done with the car we sell it (and put the Sold Date in the
table). What I am trying to do is produce a query that shows the historical
inventory by month. For example, I would like to see how many units i had
in inventory for January 2002.

My Data has the intial date in service and if it hasbeen sold (or out of

service) there is a date. ) What I cant figure out is how to get a count of
all the units for a specific month that are greater than the Date in Service
but is less than the sales date or where the sales date is blank?