By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,968 Members | 1,588 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,968 IT Pros & Developers. It's quick & easy.

Cross-Tab Problem

P: 25
** Admin Edit **
This thread has been split off from the original (Query/subquery - percentage).
** /Admin Edit **

Seeing as it appears we are coming to the end of this problem, i would like to present you with another one that perhaps will only take a little thought on your half.

Im creating a cross tab query which sorts via [SteveCode] and sums the [Tonnage] for each type of [RecyType]. I want to be able to add a date criteria, so the user can specify that the query processes data between two dates.

I have looked long and hard on the net for any answers and it seems that this has appeared often but without a proper solution. I offer the following SQL of my query:
Expand|Select|Wrap|Line Numbers
  1. PARAMETERS [Forms]![Recycling Rates]![From Date] DateTime, [Forms]![Recycling Rates]![To Date] DateTime;
  2. TRANSFORM Sum(Tonnage.Tonnage) AS SumOfTonnage
  3. SELECT Tonnage.SteveCode
  4. FROM Tonnage
  5. WHERE (((Tonnage.SteveCode) Is Not Null) AND ((Tonnage.RecyType)="Dry (Recyclable)" Or (Tonnage.RecyType)="Green (Recyclable)" Or (Tonnage.RecyType)="Glass (Recyclable)" Or (Tonnage.RecyType)="Food (Recyclable)" Or (Tonnage.RecyType)="Residual") AND ((Tonnage.Date) Between [Forms]![Recycling Rates]![From Date] And [Forms]![Recycling Rates]![To Date]))
  6. GROUP BY Tonnage.SteveCode
  7. PIVOT Tonnage.RecyType;
If i change the two date parameters, the same results will show so it obviously is not working.

Any ideas?
Sep 12 '08 #1
Share this Question
Share on Google+
10 Replies


NeoPa
Expert Mod 15k+
P: 31,347
Seeing as it appears we are coming to the end of this problem, i would like to present you with another one that perhaps will only take a little thought on your half.

Im creating a cross tab query which sorts via [SteveCode] and sums the [Tonnage] for each type of [RecyType]. I want to be able to add a date criteria, so the user can specify that the query processes data between two dates.

I have looked long and hard on the net for any answers and it seems that this has appeared often but without a proper solution. I offer the following SQL of my query:
Expand|Select|Wrap|Line Numbers
  1. PARAMETERS [Forms]![Recycling Rates]![From Date] DateTime, [Forms]![Recycling Rates]![To Date] DateTime;
  2. TRANSFORM Sum(Tonnage.Tonnage) AS SumOfTonnage
  3. SELECT Tonnage.SteveCode
  4. FROM Tonnage
  5. WHERE (((Tonnage.SteveCode) Is Not Null) AND ((Tonnage.RecyType)="Dry (Recyclable)" Or (Tonnage.RecyType)="Green (Recyclable)" Or (Tonnage.RecyType)="Glass (Recyclable)" Or (Tonnage.RecyType)="Food (Recyclable)" Or (Tonnage.RecyType)="Residual") AND ((Tonnage.Date) Between [Forms]![Recycling Rates]![From Date] And [Forms]![Recycling Rates]![To Date]))
  6. GROUP BY Tonnage.SteveCode
  7. PIVOT Tonnage.RecyType;
If i change the two date parameters, the same results will show so it obviously is not working.

Any ideas?
I will have a quick stab at this, but if that doesn't resolve it, please post this as a separate question. You may post a link to your new thread in here if you will, but threads work a lot better if they stay on track.

Anyway, I think your ANDs and ORs might be the problem here. That is to say, I couldn't find an actual problem, but simplifying may help. Try this SQL anyway :
Expand|Select|Wrap|Line Numbers
  1. PARAMETERS [Forms]![Recycling Rates]![From Date] DateTime,
  2.            [Forms]![Recycling Rates]![To Date] DateTime;
  3.  
  4. TRANSFORM Sum(Tonnage) AS SumOfTonnage
  5.  
  6. SELECT SteveCode
  7.  
  8. FROM Tonnage
  9.  
  10. WHERE (([SteveCode] Is Not Null)
  11.   AND  ([RecyType] In('Dry (Recyclable)',
  12.                       'Green (Recyclable)',
  13.                       'Glass (Recyclable)',
  14.                       'Food (Recyclable)',
  15.                       'Residual'))
  16.   AND  ([Date] Between [Forms]![Recycling Rates]![From Date]
  17.                    And [Forms]![Recycling Rates]![To Date]))
  18.  
  19. GROUP BY SteveCode
  20.  
  21. PIVOT RecyType;
Sep 13 '08 #2

P: 25
Last post then, if this doesnt solve then i will start a new thread.

Ive tried that SQL and there was a missing operator error message. my knowledge of SQL is very limited so i cannot solve this.
Sep 15 '08 #3

NeoPa
Expert Mod 15k+
P: 31,347
My knowledge of Cross-Tab queries (PIVOT) is also limited, but if you post the whole error message then I'll see what I can do.

Posting a new thread might have garnered some new, more appropriate, expert interest.
Sep 15 '08 #4

P: 25
Syntax error (missing operator) is query expression
(([SteveCode] Is Not Null)
AND ([RecyType] In('Dry (Recyclable)',
'Green (Recyclable)',
'Glass (Recyclable)',
'Food (Recyclable)',
'Residual')
AND ([Date]
Sep 15 '08 #5

NeoPa
Expert Mod 15k+
P: 31,347
There should have been two ")"s after 'Residual' (My posted SQL was in error). You appreciate I'm not in a good position to test this, so I just have to be as careful as I can, working it out in my head.

I have now fixed this in post #3 so you can copy / paste it again and it should work.

Error messages can be quite helpful really ;)
Sep 15 '08 #6

P: 25
brilliant. Right thats the last of that for me then. Just had to finish a few things off before i leave Friday for university and a completely different carrer path.

Thanks for your help.
Sep 16 '08 #7

NeoPa
Expert Mod 15k+
P: 31,347
Very pleased that's working for you now.

For interest, which Uni are you off to and what are you studying?
Sep 16 '08 #8

NeoPa
Expert Mod 15k+
P: 31,347
I've just come across a further two threads posted yesterday which both appear to have been asking the same question (as this). This is not acceptable (see the rules) as it wastes lots of time. I have removed them both.
Sep 16 '08 #9

aas4mis
P: 97
There should have been two ")"s after 'Residual' (My posted SQL was in error). You appreciate I'm not in a good position to test this, so I just have to be as careful as I can, working it out in my head.

I have now fixed this in post #3 so you can copy / paste it again and it should work.

Error messages can be quite helpful really ;)
Stewart, just lookin out. Neo, Sorry, I couldn't resist. :P
"You just can't get the staff nowadays :D"
Sep 20 '08 #10

NeoPa
Expert Mod 15k+
P: 31,347
:)

NB. The thread you are referring to is sum field twice in same query using different criteria.
Sep 20 '08 #11

Post your reply

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