469,331 Members | 5,740 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,331 developers. It's quick & easy.

GROUP BY ALL type query needed

I want to list all groups within my data regardless of whether they meet a certain condition - if they do not meet the condition I want to display a count of 0 for the relevant groups.

Access Query Question:

Using Access 2003 SP2.

I wanted to use the 'GROUP BY ALL' command but apparently this cannot be used with remote tables and Microsoft no longer support the 'ALL' command with future releases.

I want a list of all referral sources for all new customers regardless of whether they have placed an order or not - if a group has no orders placed I want the count to be 0.

e.g. code:

Expand|Select|Wrap|Line Numbers
  1. SELECT Customer.Refer, Count(*) AS Orders
  2. FROM Customer
  3. WHERE (((Customer.Date) Between #1/1/2007# And ((#1/8/2007#)+1)) 
  4. AND ((Customer.Notes) Like '*<Orders>*1*</Orders>*' 
  5. Or (Customer.Notes) Like '*<Orders>*2*</Orders>*' 
  6. Or (Customer.Notes) Like '*<Orders>*3*</Orders>*' 
  7. Or (Customer.Notes) Like '*<Orders>*4*</Orders>*' 
  8. Or (Customer.Notes) Like '*<Orders>*5*</Orders>*' 
  9. Or (Customer.Notes) Like '*<Orders>*6*</Orders>*' 
  10. Or (Customer.Notes) Like '*<Orders>*7*</Orders>*' 
  11. Or (Customer.Notes) Like '*<Orders>*8*</Orders>*' 
  12. Or (Customer.Notes) Like '*<Orders>*9*</Orders>*' 
  13. Or (Customer.Notes) Like '*<Orders>*0*</Orders>*'))
  14. GROUP BY  Customer.Refer
Result:

Expand|Select|Wrap|Line Numbers
  1. REFER | ORDERS
  2. <NULL> | 500
However I want all other 'refer' sources to also be listed e.g.

Expand|Select|Wrap|Line Numbers
  1. REFER | ORDERS
  2. <NULL> | 500
  3. REFA | 0
  4. REFB | 0
  5. REFC | 0
The GROUP BY ALL does not work - I get the following error:

"Syntax Error (missing operator) in query expression 'ALL customer.refer'"

Note: On a separate point my 'Where' clause looks complicated because I only know if an order has been placed by looking at a 'memo' field searching for the following pattern "<Orders>####<Orders/>" (where #### can be any number combination - I have not been able to find an easier way to search for a string pattern (any guidance would be greatly appreciated).
Jun 21 '07 #1
4 1935
NeoPa
32,182 Expert Mod 16PB
I would state confidently, that the SQL you posted does NOT match the error message posted.
Please check and post (using Copy/Paste) the matching pair.
Jun 24 '07 #2
NeoPa
32,182 Expert Mod 16PB
It is also difficult to work without the actual data (this is however necessary most times as data is hard to post sensibly), but would your <Orders> information not be detectable simply with the "Like '*<Orders>*</Orders>'" check. Not if you have all non-numeric characters in any order number, but do you have that? I'll leave that with you.
Jun 24 '07 #3
Thank you for your help. I have resolved the problem by using three separate queries - getting the data into the exact format that I require.

You are right the error message reported did not match the data shown - the message was right it was just the table reference that was different.

Thanks again for replying - much appreciated.
Jul 6 '07 #4
NeoPa
32,182 Expert Mod 16PB
No problem.
I'm glad you managed to resolve your issue :)
Jul 6 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Joshua Moore-Oliva | last post: by
3 posts views Thread by Abhi | last post: by
1 post views Thread by David Horowitz | last post: by
6 posts views Thread by cppnow | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Purva khokhar | last post: by
1 post views Thread by haryvincent176 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.