Croossab Query

[Dan]

Hi:

Here is my query:

TRANSFORM Count(ODSDBA_WATS5MIX.MENU_ITM_NBR) AS Expr1
SELECT ODSDBA_WATS5MIX.NATL_STR_NBR
FROM ODSDBA_WATS5MIX RIGHT JOIN [MI for count] ON
ODSDBA_WATS5MIX.NATL_STR_NBR = [MI for count].[Natl#]
WHERE (((ODSDBA_WATS5MIX.DLY_SLS_DT) Between #9/19/2004#
And #9/21/2004#))
GROUP BY ODSDBA_WATS5MIX.NATL_STR_NBR
PIVOT ODSDBA_WATS5MIX.DLY_SLS_DT;

Can you tell me please how do I show the menu_itm_nbr in
the result; not just the count of them?
So, how many Itemms sold per day and the menu item number

Now the result is:

Date1 Date2
Store 3 5

Thanks,

Dan

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Just put the column in the SELECT & GROUP BY clauses:

TRANSFORM Count(ODSDBA_WATS5MIX.MENU_ITM_NBR) AS Expr1
SELECT ODSDBA_WATS5MIX.NATL_STR_NBR, ODSDBA_WATS5MIX.MENU_ITM_NBR
FROM ODSDBA_WATS5MIX RIGHT JOIN [MI for count] ON
ODSDBA_WATS5MIX.NATL_STR_NBR = [MI for count].[Natl#]
WHERE (((ODSDBA_WATS5MIX.DLY_SLS_DT) Between #9/19/2004#
And #9/21/2004#))
GROUP BY ODSDBA_WATS5MIX.NATL_STR_NBR, ODSDBA_WATS5MIX.MENU_ITM_NBR
PIVOT ODSDBA_WATS5MIX.DLY_SLS_DT;

You'll get something like:

Store MenuItem Date1 Date2
Fred's 6318 3 5
Fred's 9855 1 2
Jane's 6547 5 10
Jane's 8907 1 1


--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQVBxXoechKqOuFEgEQKuiACdHvRkQVJT0OhF8Te2cGTFoqm75iwAn3OT
Q3JJp8JB+JR/+FMdeR2IBs6P
=6qIT
-----END PGP SIGNATURE-----

 

[Dan]

Thank you! I have tried that before, but the result I got
is like not the right one; one MI per day?? and I do not
know why?!
NATL_STR_NBR MENU_ITM_NBR 9/19/2004 9/20/2004
1245 1 1 1
1245 3 1 1
1245 4 1
1245 5 1 1
1245 6 1 1
1245 7 1 1
1245 8 1 1
1647 3 1 1
1647 4 1 1
1647 5 1 1
1647 7 1 1
1647 8 1 1
1647 9 1 1
1647 11 1 1
1647 12 1 1

Thanks again,

Dan

-----Original Message-----
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Just put the column in the SELECT & GROUP BY clauses:

TRANSFORM Count(ODSDBA_WATS5MIX.MENU_ITM_NBR) AS Expr1
SELECT ODSDBA_WATS5MIX.NATL_STR_NBR, ODSDBA_WATS5MIX.MENU_ITM_NBR
FROM ODSDBA_WATS5MIX RIGHT JOIN [MI for count] ON
ODSDBA_WATS5MIX.NATL_STR_NBR = [MI for count].[Natl#]
WHERE (((ODSDBA_WATS5MIX.DLY_SLS_DT) Between #9/19/2004#
And #9/21/2004#))
GROUP BY ODSDBA_WATS5MIX.NATL_STR_NBR, ODSDBA_WATS5MIX.MENU_ITM_NBR
PIVOT ODSDBA_WATS5MIX.DLY_SLS_DT;

You'll get something like:

Store MenuItem Date1 Date2
Fred's 6318 3 5
Fred's 9855 1 2
Jane's 6547 5 10
Jane's 8907 1 1


--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQVBxXoechKqOuFEgEQKuiACdHvRkQVJT0OhF8Te2cGTFoqm75i wAn3OT
Q3JJp8JB+JR/+FMdeR2IBs6P
=6qIT
-----END PGP SIGNATURE-----

Hi:

Here is my query:

TRANSFORM Count(ODSDBA_WATS5MIX.MENU_ITM_NBR) AS Expr1
SELECT ODSDBA_WATS5MIX.NATL_STR_NBR
FROM ODSDBA_WATS5MIX RIGHT JOIN [MI for count] ON
ODSDBA_WATS5MIX.NATL_STR_NBR = [MI for count].[Natl#]
WHERE (((ODSDBA_WATS5MIX.DLY_SLS_DT) Between #9/19/2004#
And #9/21/2004#))
GROUP BY ODSDBA_WATS5MIX.NATL_STR_NBR
PIVOT ODSDBA_WATS5MIX.DLY_SLS_DT;

Can you tell me please how do I show the menu_itm_nbr in
the result; not just the count of them?
So, how many Itemms sold per day and the menu item number

Now the result is:

Date1 Date2
Store 3 5

Thanks,

Dan

.

 

[Duane Hookom]

Which MENU_ITM_NBR of the 3 or 5 values do you expect to see in the result?

 

[Dan]

Hi Duane:

All of them thanks!; but now is OK; I am not showing the
MI number, just the count of them: please see below...
NATL_STR_NBR 9/1/2004 9/2/2004 9/3/2004
1245 194 182 193
1647 205 221 214
1765 198 194 198

Dan

-----Original Message-----
Which MENU_ITM_NBR of the 3 or 5 values do you expect to see in the result?

--
Duane Hookom
MS Access MVP

Hi:

Here is my query:

TRANSFORM Count(ODSDBA_WATS5MIX.MENU_ITM_NBR) AS Expr1
SELECT ODSDBA_WATS5MIX.NATL_STR_NBR
FROM ODSDBA_WATS5MIX RIGHT JOIN [MI for count] ON
ODSDBA_WATS5MIX.NATL_STR_NBR = [MI for count].[Natl#]
WHERE (((ODSDBA_WATS5MIX.DLY_SLS_DT) Between #9/19/2004#
And #9/21/2004#))
GROUP BY ODSDBA_WATS5MIX.NATL_STR_NBR
PIVOT ODSDBA_WATS5MIX.DLY_SLS_DT;

Can you tell me please how do I show the menu_itm_nbr in
the result; not just the count of them?
So, how many Itemms sold per day and the menu item number

Now the result is:

Date1 Date2
Store 3 5

Thanks,

Dan

.

 

[Duane Hookom]

I don't see a list of multiple MENU_ITM_NBR values in anything you have
posted to the NG. In your most recent posting what does 194 represent? Could
you post what data you currently have and how you want it to appear in the
crosstab?

--
Duane Hookom
MS Access MVP
--

Hi Duane:

All of them thanks!; but now is OK; I am not showing the
MI number, just the count of them: please see below...
NATL_STR_NBR 9/1/2004 9/2/2004 9/3/2004
1245 194 182 193
1647 205 221 214
1765 198 194 198

Dan

-----Original Message-----
Which MENU_ITM_NBR of the 3 or 5 values do you expect to see in the result?

--
Duane Hookom
MS Access MVP

Hi:

Here is my query:

TRANSFORM Count(ODSDBA_WATS5MIX.MENU_ITM_NBR) AS Expr1
SELECT ODSDBA_WATS5MIX.NATL_STR_NBR
FROM ODSDBA_WATS5MIX RIGHT JOIN [MI for count] ON
ODSDBA_WATS5MIX.NATL_STR_NBR = [MI for count].[Natl#]
WHERE (((ODSDBA_WATS5MIX.DLY_SLS_DT) Between #9/19/2004#
And #9/21/2004#))
GROUP BY ODSDBA_WATS5MIX.NATL_STR_NBR
PIVOT ODSDBA_WATS5MIX.DLY_SLS_DT;

Can you tell me please how do I show the menu_itm_nbr in
the result; not just the count of them?
So, how many Itemms sold per day and the menu item number

Now the result is:

Date1 Date2
Store 3 5

Thanks,

Dan

.