duplicate

C

Craig McLaughlin

I have something that I thought was simple but I was wrong.

I have a table which is a staff rota that covers more than one premises.

Day, Day Shift (which is a staff name), Back shift (which is also a staff
name), unit name

I need a query that searches to make sure that the same staff member has not
been selected to work on the same day in different premises.

So say staff 1 has been scheduled to do the day shift in Unit 1 on Monday,
and the back shift on Unit 3 on the Monday.

I need a query that finds such duplicates, but I just cant work it out.

Anyone ideas - because the data can be spread across different fields it is
difficult to work out.

Cheers

Craig
 
J

John Spencer MVP

You can use a union query to get the data structured so this can be done more
simply.

SELECT Day, [Day Shift] as Staff, "Day" as Shift, [Unit Name] as Unit
FROM [Your Table]
UNION ALL
SELECT Day, [Back Shift] as Staff, "Back" as Shift, [Unit Name]
FROM [Your Table]

Now using that Saved query you can do the following
SELECT *
FROM qSavedUnionQuery
WHERE Day In(
SELECT Day
FROM qSavedUnionQuery as Temp
GROUP BY Day, Staff
HAVING Count(*)>1) And Temp.Staff=qSavedUnionQuery.Staff )


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
C

Craig McLaughlin

Thanks for the reply john.

I am not used to SQL and union queries and I copied your text into the sql
of the query.

I get an error on the temp.staff line which asks for the field. I am not
sure how to correct it.

this is the text I have entered












John Spencer MVP said:
You can use a union query to get the data structured so this can be done
more simply.

SELECT Day, [Day Shift] as Staff, "Day" as Shift, [Unit Name] as Unit
FROM [Your Table]
UNION ALL
SELECT Day, [Back Shift] as Staff, "Back" as Shift, [Unit Name]
FROM [Your Table]

Now using that Saved query you can do the following
SELECT *
FROM qSavedUnionQuery
WHERE Day In(
SELECT Day
FROM qSavedUnionQuery as Temp
GROUP BY Day, Staff
HAVING Count(*)>1) And Temp.Staff=qSavedUnionQuery.Staff )


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Craig said:
I have something that I thought was simple but I was wrong.

I have a table which is a staff rota that covers more than one premises.

Day, Day Shift (which is a staff name), Back shift (which is also a staff
name), unit name

I need a query that searches to make sure that the same staff member has
not been selected to work on the same day in different premises.

So say staff 1 has been scheduled to do the day shift in Unit 1 on
Monday, and the back shift on Unit 3 on the Monday.

I need a query that finds such duplicates, but I just cant work it out.

Anyone ideas - because the data can be spread across different fields it
is difficult to work out.

Cheers

Craig
 
C

Craig McLaughlin

Sorry last post got cut short!!!

the SQL for the first query is

SELECT Day, [Staff Day] as Staff, "Day" as Shift, [Unit] as Unit2
FROM [Tbl_Template_Shifts]
UNION ALL SELECT Day, [Staff Back] as Staff, "Back" as Shift, [Unit]
FROM [Tbl_Template_Shifts];

Craig




John Spencer MVP said:
You can use a union query to get the data structured so this can be done
more simply.

SELECT Day, [Day Shift] as Staff, "Day" as Shift, [Unit Name] as Unit
FROM [Your Table]
UNION ALL
SELECT Day, [Back Shift] as Staff, "Back" as Shift, [Unit Name]
FROM [Your Table]

Now using that Saved query you can do the following
SELECT *
FROM qSavedUnionQuery
WHERE Day In(
SELECT Day
FROM qSavedUnionQuery as Temp
GROUP BY Day, Staff
HAVING Count(*)>1) And Temp.Staff=qSavedUnionQuery.Staff )


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Craig said:
I have something that I thought was simple but I was wrong.

I have a table which is a staff rota that covers more than one premises.

Day, Day Shift (which is a staff name), Back shift (which is also a staff
name), unit name

I need a query that searches to make sure that the same staff member has
not been selected to work on the same day in different premises.

So say staff 1 has been scheduled to do the day shift in Unit 1 on
Monday, and the back shift on Unit 3 on the Monday.

I need a query that finds such duplicates, but I just cant work it out.

Anyone ideas - because the data can be spread across different fields it
is difficult to work out.

Cheers

Craig
 
J

John Spencer MVP

My error. I had one too many parentheses in the second query. I removed the )
after Having Count(*)>1. The query now reads:

SELECT *
FROM qSavedUnionQuery
WHERE Day In(
SELECT Day
FROM qSavedUnionQuery as Temp
GROUP BY Day, Staff
HAVING Count(*)>1 And Temp.Staff=qSavedUnionQuery.Staff )

Hopefully this will work and give you the results you need.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
C

Craig McLaughlin

Your solution worked

Thanks

John


Craig McLaughlin said:
Sorry last post got cut short!!!

the SQL for the first query is

SELECT Day, [Staff Day] as Staff, "Day" as Shift, [Unit] as Unit2
FROM [Tbl_Template_Shifts]
UNION ALL SELECT Day, [Staff Back] as Staff, "Back" as Shift, [Unit]
FROM [Tbl_Template_Shifts];

Craig




John Spencer MVP said:
You can use a union query to get the data structured so this can be done
more simply.

SELECT Day, [Day Shift] as Staff, "Day" as Shift, [Unit Name] as Unit
FROM [Your Table]
UNION ALL
SELECT Day, [Back Shift] as Staff, "Back" as Shift, [Unit Name]
FROM [Your Table]

Now using that Saved query you can do the following
SELECT *
FROM qSavedUnionQuery
WHERE Day In(
SELECT Day
FROM qSavedUnionQuery as Temp
GROUP BY Day, Staff
HAVING Count(*)>1) And Temp.Staff=qSavedUnionQuery.Staff )


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Craig said:
I have something that I thought was simple but I was wrong.

I have a table which is a staff rota that covers more than one premises.

Day, Day Shift (which is a staff name), Back shift (which is also a
staff name), unit name

I need a query that searches to make sure that the same staff member has
not been selected to work on the same day in different premises.

So say staff 1 has been scheduled to do the day shift in Unit 1 on
Monday, and the back shift on Unit 3 on the Monday.

I need a query that finds such duplicates, but I just cant work it out.

Anyone ideas - because the data can be spread across different fields it
is difficult to work out.

Cheers

Craig
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top