Re: Union query?

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

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

Leslie Isaacs wrote:
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?
    ... I used that information to build the union query I proposed. ... FROM [qry Normal Lines Compare] as C LEFT JOIN ... Leslie Isaacs wrote: ... Compare] 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)