On Fri, 20 Jan 2006 23:27:49 +1100, "NoodNutt" <no******@iprim us.com.au> wrote:
G'day Wayne
My apologies, you're right, I haven't provided enough detail.
the finYear field is text "2005/2006" etc.....
All 3 PrefPay fields are numeric (long)
and is recorded as this:
1 = Cash
2 = Cheq
3 = Card
The end result I would like to count each matching criteria then sum the
total count returned and hope the ensuing report would reflect the
following:
FinYear Cash Cheq Card
2004/2005 500 50 1,000
2005/2006 300 10 2,500
I am hoping a union query can achieve the same result as opposed to 3
individual crosstabs.
Thx again Wayne.
Mark.
Melbourne, VIC. Australia.
Others more conversant with SQL than me may have a better solution, but this seems to work.
Save the following SQL as a stored query (qryMyQuery)
SELECT FinYear, Count(DepPrefPa y) AS Csh, 0 As Chq, 0 As Crd
FROM tblBookings
WHERE (((DepPrefPay)= 1))
GROUP BY FinYear
UNION ALL SELECT FinYear, Count(IntPrefPa y) AS Csh, 0 As Chq, 0 As Crd
FROM tblBookings
WHERE (((IntPrefPay)= 1))
GROUP BY FinYear
UNION ALL SELECT FinYear, Count(FinPrefPa y) AS Csh, 0 As Chq, 0 As Crd
FROM tblBookings
WHERE (((FinPrefPay)= 1))
GROUP BY FinYear
UNION ALL SELECT FinYear, 0 AS Csh, Count(DepPrefPa y) As Chq, 0 As Crd
FROM tblBookings
WHERE (((DepPrefPay)= 2))
GROUP BY FinYear
UNION ALL SELECT FinYear, 0 AS Csh, Count(IntPrefPa y) As Chq, 0 As Crd
FROM tblBookings
WHERE (((IntPrefPay)= 2))
GROUP BY FinYear
UNION ALL SELECT FinYear, 0 AS Csh, Count(FinPrefPa y) As Chq, 0 As Crd
FROM tblBookings
WHERE (((FinPrefPay)= 2))
GROUP BY FinYear
UNION ALL SELECT FinYear, 0 AS Csh, 0 As Chq, Count(DepPrefPa y) As Crd
FROM tblBookings
WHERE (((DepPrefPay)= 3))
GROUP BY FinYear
UNION ALL SELECT FinYear, 0 AS Csh, 0 As Chq, Count(IntPrefPa y) As Crd
FROM tblBookings
WHERE (((IntPrefPay)= 3))
GROUP BY FinYear
UNION ALL SELECT FinYear, 0 AS Csh, 0 As Chq, Count(FinPrefPa y) As Crd
FROM tblBookings
WHERE (((FinPrefPay)= 3))
GROUP BY FinYear;
Then create a 2nd query based on this saved query -
SELECT FinYear, Sum(Csh) AS Cash, Sum(Chq) AS Cheq, Sum(Crd) AS Card
FROM qryMyQuery
GROUP BY FinYear;
Wayne Gillespie
Gosford NSW Australia
