Count?

  • Thread starter Thread starter Dan
  • Start date Start date
D

Dan

Hi:

I would need a query from this table that counts the
number of stores and the number of menu items sold per
month.

EG:So, January were 254 stores and they sold 345 menu
items.

(the table is huge!!!)
Thank you,

Dan

storeno menuid date
12 1 1/1/2004
14 2 1/2/2003
15 1 2/1/2004
16 1 3/1/2003
16 1 1/4/2004
 
Use a Totals query:

1. Create a query into this table.

2. Depress the Totals icon on the toolbar (upper sigma icon).

3. In the Field Row, enter:
TheYear: Year([DateField])
Accept Group By in the Total row.

4. In the next column of the Field row, enter:
TheMonth: Month([DateField])
Accept Group By.

5. Drag StoreNo into the grid.
Accept Group By.

6. Drag MenuID into the grid.
Accept Group By.

7. Drag the primary key into the grid.
In the Total row under this field, choose Count.

This will group by the year, month, store, and item, and count the number of
entries for each combination.

BTW, if you really do have a field called "date", consider renaming it.
That's a reservered word in VBA (for the system date). It will probably work
in this context, but sooner or later it will bite you.
 
Hi Allen:

Thanks I did that; please see below the sql, but the
result is not right:(ignore the other fields)

SELECT Count([Use 9000 MI].MSITE_NBR) AS CountOfMSITE_NBR,
Count([Use 9000 MI].MENU_ITM_NBR) AS CountOfMENU_ITM_NBR,
Month([dly_sls_dt]) AS [Month], Year([dly_sls_dt]) AS
[Year]
FROM [Use 9000 MI]
GROUP BY [Use 9000 MI].DLY_SLS_DT, [Use 9000 MI].DC_NBR
ORDER BY Month([dly_sls_dt]), Year([dly_sls_dt]);

and the result is something like:

CountOfMSITE_NBR CountOfMENU_ITM_NBR Month
Year
29 29 1 2004
335 335 1 2004
11 11 1 2004
43 43 1 2004
26 26 1 2004
307 307 1 2004
18 18 1 2004
32 32 1 2004
50 50 1 2004
12 12 1 2004


Any idea why?

Thanks,

Dan
-----Original Message-----
Use a Totals query:

1. Create a query into this table.

2. Depress the Totals icon on the toolbar (upper sigma icon).

3. In the Field Row, enter:
TheYear: Year([DateField])
Accept Group By in the Total row.

4. In the next column of the Field row, enter:
TheMonth: Month([DateField])
Accept Group By.

5. Drag StoreNo into the grid.
Accept Group By.

6. Drag MenuID into the grid.
Accept Group By.

7. Drag the primary key into the grid.
In the Total row under this field, choose Count.

This will group by the year, month, store, and item, and count the number of
entries for each combination.

BTW, if you really do have a field called "date", consider renaming it.
That's a reservered word in VBA (for the system date). It will probably work
in this context, but sooner or later it will bite you.

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

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

Hi:

I would need a query from this table that counts the
number of stores and the number of menu items sold per
month.

EG:So, January were 254 stores and they sold 345 menu
items.

(the table is huge!!!)
Thank you,

Dan

storeno menuid date
12 1 1/1/2004
14 2 1/2/2003
15 1 2/1/2004
16 1 3/1/2003
16 1 1/4/2004
Click to expand...


.
Click to expand...
 
Dear Dan:

I take it you just want to count how many different storeno values you
have, and similarly how many different menuid values you have. For
the sample that would be 4 and 2, right?

SELECT COUNT(*) AS StoreCount
FROM (SELECT DISTINCT storeno FROM YourTable)

SELECT COUNT(*) AS MenuidCount
FROM (SELECT DISTINCT menuid FROM YourTable)

To do this as one query, put the two queries in as subqueries to
another query. This query would have no FROM clause, and I think some
versions of Jet didn't like that, but it may work.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
The SQL is grouping to DLY_SLS_DT, which I assume is the daily date. That
won't give the monthly figures.

See if Tom Ellison has understood what you are aiming to do, and I'll check
back tomorrow.

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

Reply to group, rather than allenbrowne at mvps dot org.
Dan said:
Hi Allen:

Thanks I did that; please see below the sql, but the
result is not right:(ignore the other fields)

SELECT Count([Use 9000 MI].MSITE_NBR) AS CountOfMSITE_NBR,
Count([Use 9000 MI].MENU_ITM_NBR) AS CountOfMENU_ITM_NBR,
Month([dly_sls_dt]) AS [Month], Year([dly_sls_dt]) AS
[Year]
FROM [Use 9000 MI]
GROUP BY [Use 9000 MI].DLY_SLS_DT, [Use 9000 MI].DC_NBR
ORDER BY Month([dly_sls_dt]), Year([dly_sls_dt]);

and the result is something like:

CountOfMSITE_NBR CountOfMENU_ITM_NBR Month
Year
29 29 1 2004
335 335 1 2004
11 11 1 2004
43 43 1 2004
26 26 1 2004
307 307 1 2004
18 18 1 2004
32 32 1 2004
50 50 1 2004
12 12 1 2004


Any idea why?

Thanks,

Dan
-----Original Message-----
Use a Totals query:

1. Create a query into this table.

2. Depress the Totals icon on the toolbar (upper sigma icon).

3. In the Field Row, enter:
TheYear: Year([DateField])
Accept Group By in the Total row.

4. In the next column of the Field row, enter:
TheMonth: Month([DateField])
Accept Group By.

5. Drag StoreNo into the grid.
Accept Group By.

6. Drag MenuID into the grid.
Accept Group By.

7. Drag the primary key into the grid.
In the Total row under this field, choose Count.

This will group by the year, month, store, and item, and count the number of
entries for each combination.

BTW, if you really do have a field called "date", consider renaming it.
That's a reservered word in VBA (for the system date). It will probably work
in this context, but sooner or later it will bite you.

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

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

Hi:

I would need a query from this table that counts the
number of stores and the number of menu items sold per
month.

EG:So, January were 254 stores and they sold 345 menu
items.

(the table is huge!!!)
Thank you,

Dan

storeno menuid date
12 1 1/1/2004
14 2 1/2/2003
15 1 2/1/2004
16 1 3/1/2003
16 1 1/4/2004
Click to expand...


.
Click to expand...
Click to expand...
 
Hi Tom:

Correct, but per month: how many different sotrs numbers
sold count of different menus per month...

Thanks,

Dan
 
Yes, is the date when the Menu was sold...

Thanks,

Dan
-----Original Message-----
The SQL is grouping to DLY_SLS_DT, which I assume is the daily date. That
won't give the monthly figures.

See if Tom Ellison has understood what you are aiming to do, and I'll check
back tomorrow.

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

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

Thanks I did that; please see below the sql, but the
result is not right:(ignore the other fields)

SELECT Count([Use 9000 MI].MSITE_NBR) AS CountOfMSITE_NBR,
Count([Use 9000 MI].MENU_ITM_NBR) AS CountOfMENU_ITM_NBR,
Month([dly_sls_dt]) AS [Month], Year([dly_sls_dt]) AS
[Year]
FROM [Use 9000 MI]
GROUP BY [Use 9000 MI].DLY_SLS_DT, [Use 9000 MI].DC_NBR
ORDER BY Month([dly_sls_dt]), Year([dly_sls_dt]);

and the result is something like:

CountOfMSITE_NBR CountOfMENU_ITM_NBR Month
Year
29 29 1 2004
335 335 1 2004
11 11 1 2004
43 43 1 2004
26 26 1 2004
307 307 1 2004
18 18 1 2004
32 32 1 2004
50 50 1 2004
12 12 1 2004


Any idea why?

Thanks,

Dan
-----Original Message-----
Use a Totals query:

1. Create a query into this table.

2. Depress the Totals icon on the toolbar (upper sigma icon).

3. In the Field Row, enter:
TheYear: Year([DateField])
Accept Group By in the Total row.

4. In the next column of the Field row, enter:
TheMonth: Month([DateField])
Accept Group By.

5. Drag StoreNo into the grid.
Accept Group By.

6. Drag MenuID into the grid.
Accept Group By.

7. Drag the primary key into the grid.
In the Total row under this field, choose Count.

This will group by the year, month, store, and item, and count the number of
entries for each combination.

BTW, if you really do have a field called "date", consider renaming it.
That's a reservered word in VBA (for the system date).
Click to expand...
It
will probably work
in this context, but sooner or later it will bite you.

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

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

Hi:

I would need a query from this table that counts the
number of stores and the number of menu items sold per
month.

EG:So, January were 254 stores and they sold 345 menu
items.

(the table is huge!!!)
Thank you,

Dan

storeno menuid date
12 1 1/1/2004
14 2 1/2/2003
15 1 2/1/2004
16 1 3/1/2003
16 1 1/4/2004


.
Click to expand...
Click to expand...


.
Click to expand...
 
Hi,


From a couple of observations due to Steve Dassin:



TRANSFORM COUNT(*) As aa

SELECT Month( [date] ),
SUM(menuid) As [SumOfItems?] ,
COUNT(aa) As CountOfDistinctStoreNo

FROM yourTable

GROUP BY Month( [date] )

PIVOT storeNo IN( NULL )




COUNT(*) would return NULL under a "cell" where there is no data, so,
COUNTING those cells, for the given group, wlll then count the number of
PIVOT-values that supply a value (not a NULL), so, if you think two seconds
about it, is the same as a DISTINCT COUNT. Sure, you are not interested in
the new "generated fields", the PIVOT values, themselves, are not relevant
(and you don't want be limited to 255, anyhow), so IN( NULL ) closes the
trick, nicely.



Hoping it may help,
Vanderghast, Access MVP
 
Thank you! I think we get there but still the number of
menus is too HIGH! can I use count and not sum? and I will
add the year?

Thanks again,

Dan
-----Original Message-----
Hi,


From a couple of observations due to Steve Dassin:



TRANSFORM COUNT(*) As aa

SELECT Month( [date] ),
SUM(menuid) As [SumOfItems?] ,
COUNT(aa) As CountOfDistinctStoreNo

FROM yourTable

GROUP BY Month( [date] )

PIVOT storeNo IN( NULL )




COUNT(*) would return NULL under a "cell" where there is no data, so,
COUNTING those cells, for the given group, wlll then count the number of
PIVOT-values that supply a value (not a NULL), so, if you think two seconds
about it, is the same as a DISTINCT COUNT. Sure, you are not interested in
the new "generated fields", the PIVOT values, themselves, are not relevant
(and you don't want be limited to 255, anyhow), so IN( NULL ) closes the
trick, nicely.



Hoping it may help,
Vanderghast, Access MVP



Hi:

I would need a query from this table that counts the
number of stores and the number of menu items sold per
month.

EG:So, January were 254 stores and they sold 345 menu
items.

(the table is huge!!!)
Thank you,

Dan

storeno menuid date
12 1 1/1/2004
14 2 1/2/2003
15 1 2/1/2004
16 1 3/1/2003
16 1 1/4/2004
Click to expand...


.
Click to expand...
 
Hi,


We can get it done in one query, but that adds useless complexity. If
you need two "count distinct", run two crosstab queries, then JOIN their
results in a third one (you join on the GROUP BY fields).


TRANSFORM COUNT(*) As aa

SELECT Month( [date] ) As TheMonth,
COUNT(aa) As CountOfDistinctStoreNo

FROM yourTable

GROUP BY Month( [date] )

PIVOT storeNo IN( NULL )



and


TRANSFORM COUNT(*) As aa

SELECT Month( [date] ) As TheMonth,
COUNT(aa) As CountOfDistinctMenuId

FROM yourTable

GROUP BY Month( [date] )

PIVOT menuId IN( NULL )



Your third query join on the common field TheMonth.



Hoping it may help,
Vanderghast, Access MVP



Dan said:
Thank you! I think we get there but still the number of
menus is too HIGH! can I use count and not sum? and I will
add the year?

Thanks again,

Dan
-----Original Message-----
Hi,


From a couple of observations due to Steve Dassin:



TRANSFORM COUNT(*) As aa

SELECT Month( [date] ),
SUM(menuid) As [SumOfItems?] ,
COUNT(aa) As CountOfDistinctStoreNo

FROM yourTable

GROUP BY Month( [date] )

PIVOT storeNo IN( NULL )




COUNT(*) would return NULL under a "cell" where there is no data, so,
COUNTING those cells, for the given group, wlll then count the number of
PIVOT-values that supply a value (not a NULL), so, if you think two seconds
about it, is the same as a DISTINCT COUNT. Sure, you are not interested in
the new "generated fields", the PIVOT values, themselves, are not relevant
(and you don't want be limited to 255, anyhow), so IN( NULL ) closes the
trick, nicely.



Hoping it may help,
Vanderghast, Access MVP



Hi:

I would need a query from this table that counts the
number of stores and the number of menu items sold per
month.

EG:So, January were 254 stores and they sold 345 menu
items.

(the table is huge!!!)
Thank you,

Dan

storeno menuid date
12 1 1/1/2004
14 2 1/2/2003
15 1 2/1/2004
16 1 3/1/2003
16 1 1/4/2004
Click to expand...


.
Click to expand...
Click to expand...
 
Dear Dan:

Sure, just add a filter by month and year, such as:

WHERE Month([date]) = 1 AND Year([Date]) = 2004

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Thanks all of you! I will see which one is the best one!

DAn
-----Original Message-----
Hi,


We can get it done in one query, but that adds useless complexity. If
you need two "count distinct", run two crosstab queries, then JOIN their
results in a third one (you join on the GROUP BY fields).


TRANSFORM COUNT(*) As aa

SELECT Month( [date] ) As TheMonth,
COUNT(aa) As CountOfDistinctStoreNo

FROM yourTable

GROUP BY Month( [date] )

PIVOT storeNo IN( NULL )



and


TRANSFORM COUNT(*) As aa

SELECT Month( [date] ) As TheMonth,
COUNT(aa) As CountOfDistinctMenuId

FROM yourTable

GROUP BY Month( [date] )

PIVOT menuId IN( NULL )



Your third query join on the common field TheMonth.



Hoping it may help,
Vanderghast, Access MVP



Thank you! I think we get there but still the number of
menus is too HIGH! can I use count and not sum? and I will
add the year?

Thanks again,

Dan
-----Original Message-----
Hi,


From a couple of observations due to Steve Dassin:



TRANSFORM COUNT(*) As aa

SELECT Month( [date] ),
SUM(menuid) As [SumOfItems?] ,
COUNT(aa) As CountOfDistinctStoreNo

FROM yourTable

GROUP BY Month( [date] )

PIVOT storeNo IN( NULL )




COUNT(*) would return NULL under a "cell" where there
Click to expand...
is
no data, so,
COUNTING those cells, for the given group, wlll then count the number of
PIVOT-values that supply a value (not a NULL), so, if
Click to expand...
you
think two seconds
about it, is the same as a DISTINCT COUNT. Sure, you are not interested in
the new "generated fields", the PIVOT values,
Click to expand...
themselves,
are not relevant
(and you don't want be limited to 255, anyhow), so IN( NULL ) closes the
trick, nicely.



Hoping it may help,
Vanderghast, Access MVP



Hi:

I would need a query from this table that counts the
number of stores and the number of menu items sold per
month.

EG:So, January were 254 stores and they sold 345 menu
items.

(the table is huge!!!)
Thank you,

Dan

storeno menuid date
12 1 1/1/2004
14 2 1/2/2003
15 1 2/1/2004
16 1 3/1/2003
16 1 1/4/2004



.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top