Count Query

[athomas8251]

My booking table has an [InventoryID] plus twelve [MonthYear] fields (Jan2012, Feb2012, Mar2012, etc....).

The data in MonthYear fields are [Open], [Booked], [Hold], or [Other]

I need to query the table to count distinct [Open], [Booked], [Hold], and [Other] by month AND multiple months. Is that possible?



Your help would be most appreciated.

[Robeen]

Are you totally committed to that table design?

I think it would be better if your Table was designed something like this:

InventoryID
InventoryDate
Status

It would be possible to get the Month and Year from the InventoryDate field - using the query that would group the records by month & year and count how many of each Status are in each month.

Or do you have to stay with your current Table design?

[athomas8251]

I don't **have** to stay with this table design, but the datasheet functions as a subform where the user manages bookings. Is there a way to use the table suggested and keep the (attached) datasheet layout?

[Robeen]

No. Once you change the Table to what I suggested - you would lose what is in your screenshot.

So - sticking to what you have . . .
You want to write a query that shows how many Open, Booked, Hold and Other records there are for each Month?
Is that correct?

[athomas8251]

Yes. It would be great to be able to total ALL months as well.

[athomas8251]

BTW - I've been able to write the query to do the counts for each month. I guess the issue is reporting multiple months or year.

SELECT ([tblMonth2012].[Jan2012]) AS Jan2012, Count(([tblMonth2012].[Jan2012])) AS [Count]
FROM tblMonth2012
GROUP BY ([tblMonth2012].[Jan2012]);

[Robeen]

I have to tell you . . .
Your Table design is throwing me for a loop - because it is the opposite of what the usual Table design is supposed to be and will make it very convoluted grouping all your Opens, Holds & Others.
I'm still trying to figure it out - but I think the problem is that the design is not ideal for what you're trying to do.

[Robeen]

Yes - that's the problem . . . you have to write one query for each column of the table.
Then you have to write another query that pulls in the data from all these little queries you have . . .

That tells me that the design of the table is inefficient.

It is possible to do the same thing you are attempting with ONE query - if the table is designed correctly.

Seriously, re-design your Table.

Google 'Table Normalization' - you will get a better picture of what I am trying to say.

If you decide you cannot make the change I am recommending, please post your question again.
Perhaps someone who knows more than I do will have a solution for you.

All the best!

[athomas8251]

I know! It makes for a great booking form, but it's c*@p otherwise.

What about union queries? Is there a way to make table with these data?

[Robeen]

Once you have a Count query for each value for each column
Eg:
JAN - Open
JAN - Hold
JAN - Other
. . .then you'll have to create a query that uses all the Open Queries
and another that uses all the Hold queries . . .

I strongly recommend you go to your boss and point out that a re-design is the best option.

It will not be a difficult re-design.

And once it is done - everything else that follows will become really easy.

I hate to be the bearer of bad tidings.

Again . . . please re-post if you don't like my advice.

If you don't re-post - there is a chance that other programmers will not look at this thread.

All the best.

[Robeen]

Let me know how you resolve your problem.
If I was wrong in what I said - I'd like to know that as well!!

[athomas8251]

Thanks Robeen - I will let him know.

It's going to be heck to report on.

A