Inventory by month with expiration date?

[theKruser]

I have attached a sample database based of my real one. The real one has proprietary information, so I cannot attach it. I am trying to build a crosstab query that will output a count of items per category per month that "takes an item out of stock" when it expires. For example (where the ellipses are a representation of a continuation of every month between):

Jan-21​

Feb-21​

Mar-21​

Apr-21​

May-21​

Jun-21​

Jul-21​

Aug-21​

…

Jul-23​

…

Dec-24​

Inside

Tables

10​

10​

9​

9​

9​

8​

8​

4​

0​

0​

Inside

Chairs

10​

10​

10​

10​

4​

4​

4​

0​

0​

0​

Inside

Rugs

10​

10​

9​

9​

9​

7​

7​

3​

3​

0​

Outside

Tables

10​

10​

10​

9​

9​

5​

5​

2​

0​

0​

Outside

Chairs

20​

20​

18​

18​

15​

15​

11​

6​

6​

0​

Outside

Rugs

20​

20​

20​

16​

13​

13​

8​

4​

2​

0​

The crosstab query in the database is all I have been able to come up with. It only shows me when things expire and is missing the "in between" months.

Any help or ideas would be greatly appreciated. Thank you in advance for your time.

 

[Ranman256]

your table is structured wrong. it should be:
inside , chairs, jan-21, 10
inside , tables, jan-21, 10

you can run N append queries, (1 for each date) to rebuild the table correctly.
then you can query correctly.

 

[theDBguy]

The crosstab query in the database is all I have been able to come up with. It only shows me when things expire and is missing the "in between" months.

A crosstab query cannot produce non-existent data for you. You will have to provide it for the query, so it can then display it for you. So, you could try adding another table with all the months and years you need the query to show and join it with your crosstab query. Just a thought...

 

[Pat Hartman]

You can create a table with one row for each month as far in the future as you want. then use a left join to join this dates table to your inventory table using a left join. This will probably add a null row which you can eliminate by adding criteria to select rows where the first column is not null.