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