Cross-Tab Problem

[zeusspandex]

** 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:

PARAMETERS [Forms]![Recycling Rates]![From Date] DateTime, [Forms]![Recycling Rates]![To Date] DateTime;
TRANSFORM Sum(Tonnage.Tonnage) AS SumOfTonnage
SELECT Tonnage.SteveCode
FROM Tonnage
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]))
GROUP BY Tonnage.SteveCode
PIVOT Tonnage.RecyType;

If i change the two date parameters, the same results will show so it obviously is not working.

Any ideas?

[NeoPa]

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:

PARAMETERS [Forms]![Recycling Rates]![From Date] DateTime, [Forms]![Recycling Rates]![To Date] DateTime;
TRANSFORM Sum(Tonnage.Tonnage) AS SumOfTonnage
SELECT Tonnage.SteveCode
FROM Tonnage
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]))
GROUP BY Tonnage.SteveCode
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 :

PARAMETERS [Forms]![Recycling Rates]![From Date] DateTime,
           [Forms]![Recycling Rates]![To Date] DateTime;
 
TRANSFORM Sum(Tonnage) AS SumOfTonnage
 
SELECT SteveCode
 
FROM Tonnage
 
WHERE (([SteveCode] Is Not Null)
  AND  ([RecyType] In('Dry (Recyclable)',
                      'Green (Recyclable)',
                      'Glass (Recyclable)',
                      'Food (Recyclable)',
                      'Residual'))
  AND  ([Date] Between [Forms]![Recycling Rates]![From Date]
                   And [Forms]![Recycling Rates]![To Date]))
 
GROUP BY SteveCode
 
PIVOT RecyType;

[zeusspandex]

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.

[NeoPa]

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.

[zeusspandex]

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]

[NeoPa]

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 ;)

[zeusspandex]

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.

[NeoPa]

Very pleased that's working for you now.

For interest, which Uni are you off to and what are you studying?

[NeoPa]

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.

[aas4mis]

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"

[NeoPa]

:)

NB. The thread you are referring to is sum field twice in same query using different criteria.