On Fri, 20 Jan 2006 23:27:49 +1100, "NoodNutt" <no******@iprimus.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(DepPrefPay) AS Csh, 0 As Chq, 0 As Crd
FROM tblBookings
WHERE (((DepPrefPay)=1))
GROUP BY FinYear
UNION ALL SELECT FinYear, Count(IntPrefPay) AS Csh, 0 As Chq, 0 As Crd
FROM tblBookings
WHERE (((IntPrefPay)=1))
GROUP BY FinYear
UNION ALL SELECT FinYear, Count(FinPrefPay) AS Csh, 0 As Chq, 0 As Crd
FROM tblBookings
WHERE (((FinPrefPay)=1))
GROUP BY FinYear
UNION ALL SELECT FinYear, 0 AS Csh, Count(DepPrefPay) As Chq, 0 As Crd
FROM tblBookings
WHERE (((DepPrefPay)=2))
GROUP BY FinYear
UNION ALL SELECT FinYear, 0 AS Csh, Count(IntPrefPay) As Chq, 0 As Crd
FROM tblBookings
WHERE (((IntPrefPay)=2))
GROUP BY FinYear
UNION ALL SELECT FinYear, 0 AS Csh, Count(FinPrefPay) As Chq, 0 As Crd
FROM tblBookings
WHERE (((FinPrefPay)=2))
GROUP BY FinYear
UNION ALL SELECT FinYear, 0 AS Csh, 0 As Chq, Count(DepPrefPay) As Crd
FROM tblBookings
WHERE (((DepPrefPay)=3))
GROUP BY FinYear
UNION ALL SELECT FinYear, 0 AS Csh, 0 As Chq, Count(IntPrefPay) As Crd
FROM tblBookings
WHERE (((IntPrefPay)=3))
GROUP BY FinYear
UNION ALL SELECT FinYear, 0 AS Csh, 0 As Chq, Count(FinPrefPay) 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
