duplicate

  • Thread starter Thread starter Craig McLaughlin
  • Start date Start date
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
 
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
 
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
Click to expand...
Click to expand...
 
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
Click to expand...
Click to expand...
 
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
 
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top