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

Union Query

P: n/a
G'day ppl.

Can anyone assist me with the correct structure of the following in a Union
Query.

tblBookings.FinYear
tblBookings.DepPrefPay
tblBookings.IntPrefPay
tblBookingsFinPrefPay

I tried structuring it as:
SELECT tblBookings.FinYear, tblBookings.DepPrefPay
FROM tblBookings
UNION SELECT tblBookings.IntPrefPay, tblBookings.IntFinPay; AS
PaymentPreference;

The object is to combine all 3 PrefPay fields to determine which payment
method is the overall preference of customers in a given financial year.

As you may have guessed my attempt didn't work, hence the reason I am here.

Many thx for any assistance

TIA

Mark.
Jan 19 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On Thu, 19 Jan 2006 21:27:38 +1100, "NoodNutt" <no******@iprimus.com.au> wrote:
G'day ppl.

Can anyone assist me with the correct structure of the following in a Union
Query.

tblBookings.FinYear
tblBookings.DepPrefPay
tblBookings.IntPrefPay
tblBookingsFinPrefPay

I tried structuring it as:
SELECT tblBookings.FinYear, tblBookings.DepPrefPay
FROM tblBookings
UNION SELECT tblBookings.IntPrefPay, tblBookings.IntFinPay; AS
PaymentPreference;

The object is to combine all 3 PrefPay fields to determine which payment
method is the overall preference of customers in a given financial year.

As you may have guessed my attempt didn't work, hence the reason I am here.

Many thx for any assistance

TIA

Mark.

You haven't given much detail about the table structure, but something like this should work -

SELECT tblBookings.FinYear, tblBookings.DepPrefPay As PrefPay
FROM tblBookings
WHERE Not (tblBookings.DepPrefPay) Is Null And FinYear = 1
UNION ALL SELECT tblBookings.FinYear, tblBookings.IntPrefPay As PrefPay
FROM tblBookings
WHERE Not (tblBookings.IntPrefPay) Is Null And FinYear = 1
UNION ALL SELECT tblBookings.FinYear, tblBookings.FinPrefPay As PrefPay
FROM tblBookings
WHERE Not (tblBookings.FinPrefPay) Is Null And FinYear = 1;

As the 3 PrefPay fields are to be combined they should all be aliased with the same name.

UNION ALL will return all matching records.
UNION will remove duplicates and return 1 row for each distinct data value.

Wayne Gillespie
Gosford NSW Australia
Jan 19 '06 #2

P: n/a
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.
"NoodNutt" <no******@iprimus.com.au> wrote in message
news:43**********@news.iprimus.com.au...
G'day ppl.

Can anyone assist me with the correct structure of the following in a
Union Query.

tblBookings.FinYear
tblBookings.DepPrefPay
tblBookings.IntPrefPay
tblBookingsFinPrefPay

I tried structuring it as:
SELECT tblBookings.FinYear, tblBookings.DepPrefPay
FROM tblBookings
UNION SELECT tblBookings.IntPrefPay, tblBookings.IntFinPay; AS
PaymentPreference;

The object is to combine all 3 PrefPay fields to determine which payment
method is the overall preference of customers in a given financial year.

As you may have guessed my attempt didn't work, hence the reason I am
here.

Many thx for any assistance

TIA

Mark.

Jan 20 '06 #3

P: n/a
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
Jan 21 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.