Union query?

[Leslie Isaacs]

Hello All

I have two queries, [qry changed basics] and [qry normal lines compare].
Each query includes the field [person] (which is a key field in table
[persons].) I need a query that will return all records from [qry changed
basics] and [qry normal lines compare]. At the moment my 'select' query only
returns records from one of the source queries where there is a related
field in the other (depending on the direction of the join). I need the
query to return all records from each of the source queries, and seem to
remember that this is what a union query does!

Hope someone can help.
Many thanks
Les

 

[Leslie Isaacs]

Hello Chis

Thanks for your reply.

I don't seem able to get this to work!
I now have:
SELECT staffname, PrevHourlyRate,NewHourlyRate
FROM [qry changed basics]
UNION ALL SELECT name,[ch amt],[this amt]
FROM [qry normal lines compare]
.... but this seems to 'stack' the values from two separate (supposedly
corresponding?) fields into a single field.

If [qry changed basics] outputs the following fields:
[personname], [PrevHourlyRate], [ThisHourlyRate],[RateChange]

and [qry normal lines compare] outputs the following fields:
[person], [this tax], [prev tax], [tax change]

I need a query that returns the following fields:
[personname] (or [person] - these are matched fields), [PrevHourlyRate],
[ThisHourlyRate],[RateChange], [person], [this tax], [prev tax], [tax
change]

Surely this can be done?

Thanks for your continued help.
Leslie Isaacs

A union query requires each select clause to use the same number of fields
and each field must be of a compatible data type. If you want all records in
both queries, including duplicates, here's an example:

SELECT person
FROM [qry changed basics]
UNION ALL SELECT person
FROM [qry normal lines compare]


Chris
Microsoft MVP

Hello All

I have two queries, [qry changed basics] and [qry normal lines compare].
Each query includes the field [person] (which is a key field in table
[persons].) I need a query that will return all records from [qry changed
basics] and [qry normal lines compare]. At the moment my 'select' query only
returns records from one of the source queries where there is a related
field in the other (depending on the direction of the join). I need the
query to return all records from each of the source queries, and seem to
remember that this is what a union query does!

Hope someone can help.
Many thanks
Les

 

[Leslie Isaacs]

Hello Chris

I definitely want all fields in all records in both queries.
i.e. if:
[qry normal lines compare] returns
[personname], [PrevHourlyRate], [ThisHourlyRate],[RateChange]
John Smith, £8.65, £9.25, £0.50
Peter Brown, £7.41, £7.99, £0.57
Kevin Fox, £14.30, £15.68, £1.38
etc.

and
[qry normal lines compare] returns
[person], [this tax], [prev tax], [tax change]

John Smith, 11000, 11470, 470
Peter Brown, 12500, 12658, 158
Alan Dee, 74200, 75100, 900
etc.

I want a query that will return:
[personname], [PrevHourlyRate], [ThisHourlyRate],[RateChange], [this tax],
[prev tax], [tax change]

John Smith, £8.65, £9.25, £0.50, 11000, 11470, 470
Peter Brown, £7.41, £7.99, £0.57, 12500, 12658, 158
Kevin Fox, £14.30, £15.68, £1.38, null, null, null
Alan Dee, null, null, null, 74200, 75100, 900

I hope that explains it better!

Thanks for your continued help.
Leslie Isaacs

If a personname and person means the same thing, but PrevHourlyRate and
[this
tax] don't mean the same thing, and ThisHourlyRate and [prev tax[ don't
mean
the same thing, etc, then you don't want a union query. You'd be mixing
apples with oranges, and only getting 4 fields for each record.

Do you really want a query with 8 fields? Person, Personname,
PrevHourlyRate,
ThisHourlyRate, RateChange, [this tax], [prev tax], and [tax change]? If
yes,
then you need an outer join, not the default inner join.

For the query layout, do you want all 8 fields in all records in [qry
normal
lines compare] and only the corresponding records in [qry changed basics]
where each personname has a matching person? Or do you want all 8 fields
in
all records in [qry changed basics and only the corresponding records in
[qry
normal lines compare] where each person has a matching personname? Or do
you
want all 8 fields in all records in [qry normal lines compare] and all 8
fields in all records in [qry changed basics]? (From your first post, it
sounds like this might be the one you want.)

I ask because each of the three choices uses a slightly different outer
join
syntax.

Chris
Microsoft MVP

Hello Chis

Thanks for your reply.

I don't seem able to get this to work!
I now have:
SELECT staffname, PrevHourlyRate,NewHourlyRate
FROM [qry changed basics]
UNION ALL SELECT name,[ch amt],[this amt]
FROM [qry normal lines compare]
... but this seems to 'stack' the values from two separate (supposedly
corresponding?) fields into a single field.

If [qry changed basics] outputs the following fields:
[personname], [PrevHourlyRate], [ThisHourlyRate],[RateChange]

and [qry normal lines compare] outputs the following fields:
[person], [this tax], [prev tax], [tax change]

I need a query that returns the following fields:
[personname] (or [person] - these are matched fields), [PrevHourlyRate],
[ThisHourlyRate],[RateChange], [person], [this tax], [prev tax], [tax
change]

Surely this can be done?

Thanks for your continued help.
Leslie Isaacs

A union query requires each select clause to use the same number of
fields
and each field must be of a compatible data type. If you want all
records in

[quoted text clipped - 22 lines]

Many thanks
Les

 

[John Spencer]

Then you will need to union more complex queries. Something like the following.

SELECT name,[ch amt],[this amt], PrevHourlyRate, NewHourlyRate
FROM [qry Normal Lines Compare] as C LEFT JOIN
[qry Changed basics] as B on C.[Name]= B.staffname
UNION ALL

SELECT StaffName ,[ch amt],[this amt], PrevHourlyRate, NewHourlyRate
FROM [qry Normal Lines Compare] as C RIGHT JOIN
[qry Changed basics] as B on C.[Name]= B.staffname
WHERE C.[Name] is null

The first section query returns all records from [qry Normal Lines Compare]
with any matching record data from [qry changed basics]. The second section
returns all records from [qry changed basics] that did not have a match in
[qry normal lines compare].

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Hello Chris

I definitely want all fields in all records in both queries.
i.e. if:
[qry normal lines compare] returns
[personname], [PrevHourlyRate], [ThisHourlyRate],[RateChange]
John Smith, £8.65, £9.25, £0.50
Peter Brown, £7.41, £7.99, £0.57
Kevin Fox, £14.30, £15.68, £1.38
etc.

and
[qry normal lines compare] returns
[person], [this tax], [prev tax], [tax change]

John Smith, 11000, 11470, 470
Peter Brown, 12500, 12658, 158
Alan Dee, 74200, 75100, 900
etc.

I want a query that will return:
[personname], [PrevHourlyRate], [ThisHourlyRate],[RateChange], [this tax],
[prev tax], [tax change]

John Smith, £8.65, £9.25, £0.50, 11000, 11470, 470
Peter Brown, £7.41, £7.99, £0.57, 12500, 12658, 158
Kevin Fox, £14.30, £15.68, £1.38, null, null, null
Alan Dee, null, null, null, 74200, 75100, 900

I hope that explains it better!

Thanks for your continued help.
Leslie Isaacs

If a personname and person means the same thing, but PrevHourlyRate and
[this
tax] don't mean the same thing, and ThisHourlyRate and [prev tax[ don't
mean
the same thing, etc, then you don't want a union query. You'd be mixing
apples with oranges, and only getting 4 fields for each record.

Do you really want a query with 8 fields? Person, Personname,
PrevHourlyRate,
ThisHourlyRate, RateChange, [this tax], [prev tax], and [tax change]? If
yes,
then you need an outer join, not the default inner join.

For the query layout, do you want all 8 fields in all records in [qry
normal
lines compare] and only the corresponding records in [qry changed basics]
where each personname has a matching person? Or do you want all 8 fields
in
all records in [qry changed basics and only the corresponding records in
[qry
normal lines compare] where each person has a matching personname? Or do
you
want all 8 fields in all records in [qry normal lines compare] and all 8
fields in all records in [qry changed basics]? (From your first post, it
sounds like this might be the one you want.)

I ask because each of the three choices uses a slightly different outer
join
syntax.

Chris
Microsoft MVP

Hello Chis

Thanks for your reply.

I don't seem able to get this to work!
I now have:
SELECT staffname, PrevHourlyRate,NewHourlyRate
FROM [qry changed basics]
UNION ALL SELECT name,[ch amt],[this amt]
FROM [qry normal lines compare]
... but this seems to 'stack' the values from two separate (supposedly
corresponding?) fields into a single field.

If [qry changed basics] outputs the following fields:
[personname], [PrevHourlyRate], [ThisHourlyRate],[RateChange]

and [qry normal lines compare] outputs the following fields:
[person], [this tax], [prev tax], [tax change]

I need a query that returns the following fields:
[personname] (or [person] - these are matched fields), [PrevHourlyRate],
[ThisHourlyRate],[RateChange], [person], [this tax], [prev tax], [tax
change]

Surely this can be done?

Thanks for your continued help.
Leslie Isaacs

A union query requires each select clause to use the same number of
fields
and each field must be of a compatible data type. If you want all
records in
[quoted text clipped - 22 lines]
Many thanks
Les

 

[Leslie Isaacs]

John

Many thanks for your reply.

I am not now goung to be able to test this on my actual data until Tuesday,
as I'm not in that office today and then it's the weekend and Bank Holiday
Monday!

I am a little confused though: what are the fields [ch amt] and [this amt]
referred to in the first and second parts of the query you suggested? Should
I in fact have
SELECT personname, PrevHourlyRate, ThisHourlyRate, RateChange, [this tax],
[prev tax], [tax change]
as the first line in eash part?

Thanks again for your help.
Leslie Isaacs

Then you will need to union more complex queries. Something like the
following.

SELECT name,[ch amt],[this amt], PrevHourlyRate, NewHourlyRate
FROM [qry Normal Lines Compare] as C LEFT JOIN
[qry Changed basics] as B on C.[Name]= B.staffname
UNION ALL

SELECT StaffName ,[ch amt],[this amt], PrevHourlyRate, NewHourlyRate
FROM [qry Normal Lines Compare] as C RIGHT JOIN
[qry Changed basics] as B on C.[Name]= B.staffname
WHERE C.[Name] is null

The first section query returns all records from [qry Normal Lines
Compare] with any matching record data from [qry changed basics]. The
second section returns all records from [qry changed basics] that did not
have a match in
[qry normal lines compare].

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Hello Chris

I definitely want all fields in all records in both queries.
i.e. if:
[qry normal lines compare] returns
[personname], [PrevHourlyRate], [ThisHourlyRate],[RateChange]
John Smith, £8.65, £9.25, £0.50
Peter Brown, £7.41, £7.99, £0.57
Kevin Fox, £14.30, £15.68, £1.38
etc.

and
[qry normal lines compare] returns
[person], [this tax], [prev tax], [tax change]

John Smith, 11000, 11470, 470
Peter Brown, 12500, 12658, 158
Alan Dee, 74200, 75100, 900
etc.

I want a query that will return:
[personname], [PrevHourlyRate], [ThisHourlyRate],[RateChange], [this
tax], [prev tax], [tax change]

John Smith, £8.65, £9.25, £0.50, 11000, 11470, 470
Peter Brown, £7.41, £7.99, £0.57, 12500, 12658, 158
Kevin Fox, £14.30, £15.68, £1.38, null, null, null
Alan Dee, null, null, null, 74200, 75100, 900

I hope that explains it better!

Thanks for your continued help.
Leslie Isaacs

If a personname and person means the same thing, but PrevHourlyRate and
[this
tax] don't mean the same thing, and ThisHourlyRate and [prev tax[ don't
mean
the same thing, etc, then you don't want a union query. You'd be mixing
apples with oranges, and only getting 4 fields for each record.

Do you really want a query with 8 fields? Person, Personname,
PrevHourlyRate,
ThisHourlyRate, RateChange, [this tax], [prev tax], and [tax change]?
If yes,
then you need an outer join, not the default inner join.

For the query layout, do you want all 8 fields in all records in [qry
normal
lines compare] and only the corresponding records in [qry changed
basics]
where each personname has a matching person? Or do you want all 8
fields in
all records in [qry changed basics and only the corresponding records in
[qry
normal lines compare] where each person has a matching personname? Or
do you
want all 8 fields in all records in [qry normal lines compare] and all 8
fields in all records in [qry changed basics]? (From your first post,
it
sounds like this might be the one you want.)

I ask because each of the three choices uses a slightly different outer
join
syntax.

Chris
Microsoft MVP


Leslie Isaacs wrote:
Hello Chis

Thanks for your reply.

I don't seem able to get this to work!
I now have:
SELECT staffname, PrevHourlyRate,NewHourlyRate
FROM [qry changed basics]
UNION ALL SELECT name,[ch amt],[this amt]
FROM [qry normal lines compare]
... but this seems to 'stack' the values from two separate (supposedly
corresponding?) fields into a single field.

If [qry changed basics] outputs the following fields:
[personname], [PrevHourlyRate], [ThisHourlyRate],[RateChange]

and [qry normal lines compare] outputs the following fields:
[person], [this tax], [prev tax], [tax change]

I need a query that returns the following fields:
[personname] (or [person] - these are matched fields),
[PrevHourlyRate],
[ThisHourlyRate],[RateChange], [person], [this tax], [prev tax], [tax
change]

Surely this can be done?

Thanks for your continued help.
Leslie Isaacs

A union query requires each select clause to use the same number of
fields
and each field must be of a compatible data type. If you want all
records in
[quoted text clipped - 22 lines]
Many thanks
Les

 

[John Spencer]

I misread your earlier posting where you said you had a query
SELECT staffname, PrevHourlyRate,NewHourlyRate
FROM [qry changed basics]
UNION ALL SELECT name,[ch amt],[this amt]
FROM [qry normal lines compare]

I used that information to build the union query I proposed. After rereading,
I see you posted additional information lower in the message.

SO you are correct that you need to use the actual field names in the SELECT
clause.

[person], [PrevHourlyRate], [ThisHourlyRate],[RateChange], [this tax], [prev
tax], [tax change]
and
[personname], [PrevHourlyRate], [ThisHourlyRate],[RateChange], [this tax],
[prev tax], [tax change]



Note the change in the two for the first field

SELECT [person], [PrevHourlyRate], [ThisHourlyRate],[RateChange], [this tax],
[prev tax], [tax change]
FROM [qry Normal Lines Compare] as C LEFT JOIN
[qry Changed basics] as B on C.[Name]= B.staffname
UNION ALL

SELECT [personname], [PrevHourlyRate], [ThisHourlyRate],[RateChange], [this
tax], [prev tax], [tax change]
FROM [qry Normal Lines Compare] as C RIGHT JOIN
[qry Changed basics] as B on C.[Name]= B.staffname
WHERE C.[Name] is null
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

John

Many thanks for your reply.

I am not now goung to be able to test this on my actual data until Tuesday,
as I'm not in that office today and then it's the weekend and Bank Holiday
Monday!

I am a little confused though: what are the fields [ch amt] and [this amt]
referred to in the first and second parts of the query you suggested? Should
I in fact have
SELECT personname, PrevHourlyRate, ThisHourlyRate, RateChange, [this tax],
[prev tax], [tax change]
as the first line in eash part?

Thanks again for your help.
Leslie Isaacs

Then you will need to union more complex queries. Something like the
following.

SELECT name,[ch amt],[this amt], PrevHourlyRate, NewHourlyRate
FROM [qry Normal Lines Compare] as C LEFT JOIN
[qry Changed basics] as B on C.[Name]= B.staffname
UNION ALL

SELECT StaffName ,[ch amt],[this amt], PrevHourlyRate, NewHourlyRate
FROM [qry Normal Lines Compare] as C RIGHT JOIN
[qry Changed basics] as B on C.[Name]= B.staffname
WHERE C.[Name] is null

The first section query returns all records from [qry Normal Lines
Compare] with any matching record data from [qry changed basics]. The
second section returns all records from [qry changed basics] that did not
have a match in
[qry normal lines compare].

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Hello Chris

I definitely want all fields in all records in both queries.
i.e. if:
[qry normal lines compare] returns
[personname], [PrevHourlyRate], [ThisHourlyRate],[RateChange]
John Smith, £8.65, £9.25, £0.50
Peter Brown, £7.41, £7.99, £0.57
Kevin Fox, £14.30, £15.68, £1.38
etc.

and
[qry normal lines compare] returns
[person], [this tax], [prev tax], [tax change]

John Smith, 11000, 11470, 470
Peter Brown, 12500, 12658, 158
Alan Dee, 74200, 75100, 900
etc.

I want a query that will return:
[personname], [PrevHourlyRate], [ThisHourlyRate],[RateChange], [this
tax], [prev tax], [tax change]

John Smith, £8.65, £9.25, £0.50, 11000, 11470, 470
Peter Brown, £7.41, £7.99, £0.57, 12500, 12658, 158
Kevin Fox, £14.30, £15.68, £1.38, null, null, null
Alan Dee, null, null, null, 74200, 75100, 900

I hope that explains it better!

Thanks for your continued help.
Leslie Isaacs



If a personname and person means the same thing, but PrevHourlyRate and
[this
tax] don't mean the same thing, and ThisHourlyRate and [prev tax[ don't
mean
the same thing, etc, then you don't want a union query. You'd be mixing
apples with oranges, and only getting 4 fields for each record.

Do you really want a query with 8 fields? Person, Personname,
PrevHourlyRate,
ThisHourlyRate, RateChange, [this tax], [prev tax], and [tax change]?
If yes,
then you need an outer join, not the default inner join.

For the query layout, do you want all 8 fields in all records in [qry
normal
lines compare] and only the corresponding records in [qry changed
basics]
where each personname has a matching person? Or do you want all 8
fields in
all records in [qry changed basics and only the corresponding records in
[qry
normal lines compare] where each person has a matching personname? Or
do you
want all 8 fields in all records in [qry normal lines compare] and all 8
fields in all records in [qry changed basics]? (From your first post,
it
sounds like this might be the one you want.)

I ask because each of the three choices uses a slightly different outer
join
syntax.

Chris
Microsoft MVP


Leslie Isaacs wrote:
Hello Chis

Thanks for your reply.

I don't seem able to get this to work!
I now have:
SELECT staffname, PrevHourlyRate,NewHourlyRate
FROM [qry changed basics]
UNION ALL SELECT name,[ch amt],[this amt]
FROM [qry normal lines compare]
... but this seems to 'stack' the values from two separate (supposedly
corresponding?) fields into a single field.

If [qry changed basics] outputs the following fields:
[personname], [PrevHourlyRate], [ThisHourlyRate],[RateChange]

and [qry normal lines compare] outputs the following fields:
[person], [this tax], [prev tax], [tax change]

I need a query that returns the following fields:
[personname] (or [person] - these are matched fields),
[PrevHourlyRate],
[ThisHourlyRate],[RateChange], [person], [this tax], [prev tax], [tax
change]

Surely this can be done?

Thanks for your continued help.
Leslie Isaacs

A union query requires each select clause to use the same number of
fields
and each field must be of a compatible data type. If you want all
records in
[quoted text clipped - 22 lines]
Many thanks
Les

 

[Leslie Isaacs]

John

OK, thanks for that - and sorry to have misled you with the earlier posting.
I will try your proposed query when I'm back in the office, and will post
back here to let you know.

I am surprised it's so complex!
I wouldn't have thought it was so uncommon to want to return all the result
from 2 separate queries that have one field in common.

Oh well!

Thanks again
Leslie Isaacs

I misread your earlier posting where you said you had a query
SELECT staffname, PrevHourlyRate,NewHourlyRate
FROM [qry changed basics]
UNION ALL SELECT name,[ch amt],[this amt]
FROM [qry normal lines compare]

I used that information to build the union query I proposed. After
rereading, I see you posted additional information lower in the message.

SO you are correct that you need to use the actual field names in the
SELECT clause.

[person], [PrevHourlyRate], [ThisHourlyRate],[RateChange], [this tax],
[prev tax], [tax change]
and
[personname], [PrevHourlyRate], [ThisHourlyRate],[RateChange], [this tax],
[prev tax], [tax change]



Note the change in the two for the first field

SELECT [person], [PrevHourlyRate], [ThisHourlyRate],[RateChange], [this
tax], [prev tax], [tax change]
FROM [qry Normal Lines Compare] as C LEFT JOIN
[qry Changed basics] as B on C.[Name]= B.staffname
UNION ALL

SELECT [personname], [PrevHourlyRate], [ThisHourlyRate],[RateChange],
[this tax], [prev tax], [tax change]
FROM [qry Normal Lines Compare] as C RIGHT JOIN
[qry Changed basics] as B on C.[Name]= B.staffname
WHERE C.[Name] is null
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

John

Many thanks for your reply.

I am not now goung to be able to test this on my actual data until
Tuesday, as I'm not in that office today and then it's the weekend and
Bank Holiday Monday!

I am a little confused though: what are the fields [ch amt] and [this
amt] referred to in the first and second parts of the query you
suggested? Should I in fact have
SELECT personname, PrevHourlyRate, ThisHourlyRate, RateChange, [this
tax], [prev tax], [tax change]
as the first line in eash part?

Thanks again for your help.
Leslie Isaacs

Then you will need to union more complex queries. Something like the
following.

SELECT name,[ch amt],[this amt], PrevHourlyRate, NewHourlyRate
FROM [qry Normal Lines Compare] as C LEFT JOIN
[qry Changed basics] as B on C.[Name]= B.staffname
UNION ALL

SELECT StaffName ,[ch amt],[this amt], PrevHourlyRate, NewHourlyRate
FROM [qry Normal Lines Compare] as C RIGHT JOIN
[qry Changed basics] as B on C.[Name]= B.staffname
WHERE C.[Name] is null

The first section query returns all records from [qry Normal Lines
Compare] with any matching record data from [qry changed basics]. The
second section returns all records from [qry changed basics] that did
not have a match in
[qry normal lines compare].

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Leslie Isaacs wrote:
Hello Chris

I definitely want all fields in all records in both queries.
i.e. if:
[qry normal lines compare] returns
[personname], [PrevHourlyRate], [ThisHourlyRate],[RateChange]
John Smith, £8.65, £9.25, £0.50
Peter Brown, £7.41, £7.99, £0.57
Kevin Fox, £14.30, £15.68, £1.38
etc.

and
[qry normal lines compare] returns
[person], [this tax], [prev tax], [tax change]

John Smith, 11000, 11470, 470
Peter Brown, 12500, 12658, 158
Alan Dee, 74200, 75100, 900
etc.

I want a query that will return:
[personname], [PrevHourlyRate], [ThisHourlyRate],[RateChange], [this
tax], [prev tax], [tax change]

John Smith, £8.65, £9.25, £0.50, 11000, 11470, 470
Peter Brown, £7.41, £7.99, £0.57, 12500, 12658, 158
Kevin Fox, £14.30, £15.68, £1.38, null, null, null
Alan Dee, null, null, null, 74200, 75100, 900

I hope that explains it better!

Thanks for your continued help.
Leslie Isaacs



If a personname and person means the same thing, but PrevHourlyRate
and [this
tax] don't mean the same thing, and ThisHourlyRate and [prev tax[
don't mean
the same thing, etc, then you don't want a union query. You'd be
mixing
apples with oranges, and only getting 4 fields for each record.

Do you really want a query with 8 fields? Person, Personname,
PrevHourlyRate,
ThisHourlyRate, RateChange, [this tax], [prev tax], and [tax change]?
If yes,
then you need an outer join, not the default inner join.

For the query layout, do you want all 8 fields in all records in [qry
normal
lines compare] and only the corresponding records in [qry changed
basics]
where each personname has a matching person? Or do you want all 8
fields in
all records in [qry changed basics and only the corresponding records
in [qry
normal lines compare] where each person has a matching personname? Or
do you
want all 8 fields in all records in [qry normal lines compare] and all
8
fields in all records in [qry changed basics]? (From your first post,
it
sounds like this might be the one you want.)

I ask because each of the three choices uses a slightly different
outer join
syntax.

Chris
Microsoft MVP


Leslie Isaacs wrote:
Hello Chis

Thanks for your reply.

I don't seem able to get this to work!
I now have:
SELECT staffname, PrevHourlyRate,NewHourlyRate
FROM [qry changed basics]
UNION ALL SELECT name,[ch amt],[this amt]
FROM [qry normal lines compare]
... but this seems to 'stack' the values from two separate
(supposedly
corresponding?) fields into a single field.

If [qry changed basics] outputs the following fields:
[personname], [PrevHourlyRate], [ThisHourlyRate],[RateChange]

and [qry normal lines compare] outputs the following fields:
[person], [this tax], [prev tax], [tax change]

I need a query that returns the following fields:
[personname] (or [person] - these are matched fields),
[PrevHourlyRate],
[ThisHourlyRate],[RateChange], [person], [this tax], [prev tax], [tax
change]

Surely this can be done?

Thanks for your continued help.
Leslie Isaacs

A union query requires each select clause to use the same number of
fields
and each field must be of a compatible data type. If you want all
records in
[quoted text clipped - 22 lines]
Many thanks
Les

 

[John Spencer]

My understanding was that you wanted to return in the same row all the data
for all the rows in both tables.

So if a row exists in table one (based on the name field) you wanted a rwo
returned with all the data in table one and the associated data from table
two. In addition, if there were any rows (based on name field) in table two
that did not exist in table one the you wanted those rows also returned.

That is why you need the union query - you are asking for two separate sets of
data.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

John

OK, thanks for that - and sorry to have misled you with the earlier posting.
I will try your proposed query when I'm back in the office, and will post
back here to let you know.

I am surprised it's so complex!
I wouldn't have thought it was so uncommon to want to return all the result
from 2 separate queries that have one field in common.

Oh well!

Thanks again
Leslie Isaacs

I misread your earlier posting where you said you had a query
SELECT staffname, PrevHourlyRate,NewHourlyRate
FROM [qry changed basics]
UNION ALL SELECT name,[ch amt],[this amt]
FROM [qry normal lines compare]

I used that information to build the union query I proposed. After
rereading, I see you posted additional information lower in the message.

SO you are correct that you need to use the actual field names in the
SELECT clause.

[person], [PrevHourlyRate], [ThisHourlyRate],[RateChange], [this tax],
[prev tax], [tax change]
and
[personname], [PrevHourlyRate], [ThisHourlyRate],[RateChange], [this tax],
[prev tax], [tax change]



Note the change in the two for the first field

SELECT [person], [PrevHourlyRate], [ThisHourlyRate],[RateChange], [this
tax], [prev tax], [tax change]
FROM [qry Normal Lines Compare] as C LEFT JOIN
[qry Changed basics] as B on C.[Name]= B.staffname
UNION ALL

SELECT [personname], [PrevHourlyRate], [ThisHourlyRate],[RateChange],
[this tax], [prev tax], [tax change]
FROM [qry Normal Lines Compare] as C RIGHT JOIN
[qry Changed basics] as B on C.[Name]= B.staffname
WHERE C.[Name] is null
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

John

Many thanks for your reply.

I am not now goung to be able to test this on my actual data until
Tuesday, as I'm not in that office today and then it's the weekend and
Bank Holiday Monday!

I am a little confused though: what are the fields [ch amt] and [this
amt] referred to in the first and second parts of the query you
suggested? Should I in fact have
SELECT personname, PrevHourlyRate, ThisHourlyRate, RateChange, [this
tax], [prev tax], [tax change]
as the first line in eash part?

Thanks again for your help.
Leslie Isaacs



Then you will need to union more complex queries. Something like the
following.

SELECT name,[ch amt],[this amt], PrevHourlyRate, NewHourlyRate
FROM [qry Normal Lines Compare] as C LEFT JOIN
[qry Changed basics] as B on C.[Name]= B.staffname
UNION ALL

SELECT StaffName ,[ch amt],[this amt], PrevHourlyRate, NewHourlyRate
FROM [qry Normal Lines Compare] as C RIGHT JOIN
[qry Changed basics] as B on C.[Name]= B.staffname
WHERE C.[Name] is null

The first section query returns all records from [qry Normal Lines
Compare] with any matching record data from [qry changed basics]. The
second section returns all records from [qry changed basics] that did
not have a match in
[qry normal lines compare].

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Leslie Isaacs wrote:
Hello Chris

I definitely want all fields in all records in both queries.
i.e. if:
[qry normal lines compare] returns
[personname], [PrevHourlyRate], [ThisHourlyRate],[RateChange]
John Smith, £8.65, £9.25, £0.50
Peter Brown, £7.41, £7.99, £0.57
Kevin Fox, £14.30, £15.68, £1.38
etc.

and
[qry normal lines compare] returns
[person], [this tax], [prev tax], [tax change]

John Smith, 11000, 11470, 470
Peter Brown, 12500, 12658, 158
Alan Dee, 74200, 75100, 900
etc.

I want a query that will return:
[personname], [PrevHourlyRate], [ThisHourlyRate],[RateChange], [this
tax], [prev tax], [tax change]

John Smith, £8.65, £9.25, £0.50, 11000, 11470, 470
Peter Brown, £7.41, £7.99, £0.57, 12500, 12658, 158
Kevin Fox, £14.30, £15.68, £1.38, null, null, null
Alan Dee, null, null, null, 74200, 75100, 900

I hope that explains it better!

Thanks for your continued help.
Leslie Isaacs



If a personname and person means the same thing, but PrevHourlyRate
and [this
tax] don't mean the same thing, and ThisHourlyRate and [prev tax[
don't mean
the same thing, etc, then you don't want a union query. You'd be
mixing
apples with oranges, and only getting 4 fields for each record.

Do you really want a query with 8 fields? Person, Personname,
PrevHourlyRate,
ThisHourlyRate, RateChange, [this tax], [prev tax], and [tax change]?
If yes,
then you need an outer join, not the default inner join.

For the query layout, do you want all 8 fields in all records in [qry
normal
lines compare] and only the corresponding records in [qry changed
basics]
where each personname has a matching person? Or do you want all 8
fields in
all records in [qry changed basics and only the corresponding records
in [qry
normal lines compare] where each person has a matching personname? Or
do you
want all 8 fields in all records in [qry normal lines compare] and all
8
fields in all records in [qry changed basics]? (From your first post,
it
sounds like this might be the one you want.)

I ask because each of the three choices uses a slightly different
outer join
syntax.

Chris
Microsoft MVP


Leslie Isaacs wrote:
Hello Chis

Thanks for your reply.

I don't seem able to get this to work!
I now have:
SELECT staffname, PrevHourlyRate,NewHourlyRate
FROM [qry changed basics]
UNION ALL SELECT name,[ch amt],[this amt]
FROM [qry normal lines compare]
... but this seems to 'stack' the values from two separate
(supposedly
corresponding?) fields into a single field.

If [qry changed basics] outputs the following fields:
[personname], [PrevHourlyRate], [ThisHourlyRate],[RateChange]

and [qry normal lines compare] outputs the following fields:
[person], [this tax], [prev tax], [tax change]

I need a query that returns the following fields:
[personname] (or [person] - these are matched fields),
[PrevHourlyRate],
[ThisHourlyRate],[RateChange], [person], [this tax], [prev tax], [tax
change]

Surely this can be done?

Thanks for your continued help.
Leslie Isaacs

A union query requires each select clause to use the same number of
fields
and each field must be of a compatible data type. If you want all
records in
[quoted text clipped - 22 lines]
Many thanks
Les