Inventory by month with expiration date? (1 Viewer)

theKruser

Registered User.
Local time
Today, 03:28
Joined
Aug 6, 2008
Messages
122
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​
InsideTables
10​
10​
9​
9​
9​
8​
8​
4​
0​
0​
InsideChairs
10​
10​
10​
10​
4​
4​
4​
0​
0​
0​
InsideRugs
10​
10​
9​
9​
9​
7​
7​
3​
3​
0​
OutsideTables
10​
10​
10​
9​
9​
5​
5​
2​
0​
0​
OutsideChairs
20​
20​
18​
18​
15​
15​
11​
6​
6​
0​
OutsideRugs
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.
 

Attachments

  • CategoryByExpiry.accdb
    444 KB · Views: 537

Ranman256

Well-known member
Local time
Today, 03:28
Joined
Apr 9, 2015
Messages
4,339
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

I’m here to help
Staff member
Local time
Today, 00:28
Joined
Oct 29, 2018
Messages
21,358
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

Super Moderator
Staff member
Local time
Today, 03:28
Joined
Feb 19, 2002
Messages
42,981
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.
 

Users who are viewing this thread

Top Bottom