URGENT HELP REQ for union query (pt2)

[Guest]

I messed up the message below! apologies for that.

Is it possible to take out duplicates in an SQL for a union query. In my
database I have information on working hours and I need to know whether a
member of staff worked that week (but a member of staff can work for more
than one department during one week).

I guess I want to take duplicates for "WeekID" so the same week date and
name doesn't appear more than once.

 

[John Vinson]

I messed up the message below! apologies for that.

Is it possible to take out duplicates in an SQL for a union query. In my
database I have information on working hours and I need to know whether a
member of staff worked that week (but a member of staff can work for more
than one department during one week).

I guess I want to take duplicates for "WeekID" so the same week date and
name doesn't appear more than once.

Since I know nothing about the structure of your tables or your query,
all I can say is "yes, you can take out duplicates by properly
constructing the query". Care to post the existing SQL, and indicate
what constitutes a "duplicate"? If you have other fields than the
WeekID and the name, do you want to arbitrarily discard one record,
see a total, or what?

John W. Vinson[MVP]

 

[Guest]

SQL:

SELECT [Name], [workstatus], [WeekID]
FROM [Temp Prod]

UNION ALL SELECT [Name], [workstatus], [WeekID]
FROM [Perm Prod];

but then I realised the answer is staring me in the face ! doh!

 

[Lynn Trapp]

SQL:

SELECT [Name], [workstatus], [WeekID]
FROM [Temp Prod]

UNION ALL SELECT [Name], [workstatus], [WeekID]
FROM [Perm Prod];

but then I realised the answer is staring me in the face ! doh!

Yeah, it is definitely staring you in the face... For those who might not
have caught on the word ALL in his Union query statement is causing the
duplicates to be returned. A normal Union query eliminates duplicates.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[John Vinson]

SQL:

SELECT [Name], [workstatus], [WeekID]
FROM [Temp Prod]

UNION ALL SELECT [Name], [workstatus], [WeekID]
FROM [Perm Prod];

but then I realised the answer is staring me in the face ! doh!

It happens to ALL of us! <g>

John W. Vinson[MVP]