Re: Union query?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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



"John Spencer" <spencer@xxxxxxxxx> wrote in message
news:eH9WFVOvIHA.1936@xxxxxxxxxxxxxxxxxxxxxxx
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

Leslie Isaacs wrote:
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



"John Spencer" <spencer@xxxxxxxxx> wrote in message
news:u$kpLfMvIHA.5832@xxxxxxxxxxxxxxxxxxxxxxx
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



"Chris O'C via AccessMonster.com" <u29189@uwe> wrote in message
news:848aae25070d2@xxxxxx
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
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200805/1




.

Relevant Pages

  • Re: Union query?
    ... Leslie Isaacs wrote: ... I will try your proposed query when I'm back in the office, and will post back here to let you know. ... FROM [qry Normal Lines Compare] as C LEFT JOIN ... The first section query returns all records from with any matching record data from [qry changed basics]. ...
    (microsoft.public.access.queries)
  • Re: Union query?
    ... I used that information to build the union query I proposed. ... FROM [qry Normal Lines Compare] as C LEFT JOIN ... Leslie Isaacs wrote: ... The first section query returns all records from with any matching record data from [qry changed basics]. ...
    (microsoft.public.access.queries)
  • Re: Union query?
    ... FROM [qry Normal Lines Compare] as C RIGHT JOIN ... The first section query returns all records from [qry Normal Lines ... Compare] with any matching record data from [qry changed basics]. ... Leslie Isaacs wrote: ...
    (microsoft.public.access.queries)
  • Re: Union query?
    ... Then you will need to union more complex queries. ... FROM [qry Normal Lines Compare] as C RIGHT JOIN ... The first section query returns all records from with any matching record data from [qry changed basics]. ...
    (microsoft.public.access.queries)
  • Re: I should give up programming!
    ... I have a lot of code where I'm basically using code to compare two tables in ... " ORDER BY CompanyName" ... into a query for debugging. ... "FROM tblContacts;" ...
    (microsoft.public.access.modulesdaovba)