Find Duplicates Many Fields (1 Viewer)

[gMAC]

I created a simple database to track employee’s vacations. One table with EmpName, Vacation1, Vacation2, Vacation3, ect. Employees can have up to six week of vacation depending on there time with the company. With over a 100 employees our company will only allow so many employees off per week. I thought I could use the find duplicate query to find the employees name whose vacation date matches another. But this only works with one date field, not six-date field. How can I find the employees that have chosen the same date searching six fields?
Thanks
gMAC

 

[Pat Hartman]

Restructure the db. You need two tables to properly represent a 1-to-many relationship. 1 employee-many vacations. Once you have done this, the queries will be much easier. The tables will look something like:

tblEmployee
EmployeeID
LastName
FirstName
HireDate
etc.

tblVacation
EmployeeID
StartDt
EndDt

 

[Jon K]

"One table with EmpName, Vacation1, Vacation2, Vacation3, ect."
"Employees can have up to six week of vacation"
"But this only works with one date field, not six-date field"


It seems there are six date fields in your table (Vacation1,2,..6) and each of them represents one week of vacation. I wonder how you can use a date field to represent a week of vacation!

Pat is right. You should re-structure your table.


But, if you need an urgent solution, the following may help.

Assuming Vacation1,2,...6 are the six date fields you referred to, the following two queries can help you show the number of matching dates (paste each query to the SQL View of a new query, replacing with the correct table name in the first query):


qry1_VacationUnion:-
SELECT EmpName, Vacation1 as Vacation FROM yourTable WHERE not isnull(Vacation1)
union
SELECT EmpName, Vacation2 FROM yourTable WHERE not isnull(Vacation2)
union
SELECT EmpName, Vacation3 FROM yourTable WHERE not isnull(Vacation3)
union
SELECT EmpName, Vacation4 FROM yourTable WHERE not isnull(Vacation4)
union
SELECT EmpName, Vacation5 FROM yourTable WHERE not isnull(Vacation5)
UNION
SELECT EmpName, Vacation6 FROM yourTable WHERE not isnull(Vacation6);


qry2_VacationCount:-
SELECT Vacation, dcount("Vacation", "qry1_VacationUnion", "Vacation =#" & [Vacation] & "#") AS NumOfEmployees, EmpName
FROM qry1_VacationUnion
ORDER BY Vacation, EmpName;


When you run the second query, the column NumOfEmployees will show the total number for each vacation date along with employee names.

DCount is an inefficient function. It may take time to display the numbers. But it should work.


Or you can run this query, which is much faster but does not show the employee names:

SELECT Vacation, count(*) AS NumOfEmployees
FROM qry1_VacationUnion
GROUP BY Vacation;

Hope this helps.

 

[gMAC]

The six vacation fields represent the week of the vacation the employee has chosen for the coming year. Example: Bill Smith has three week vacation per year and has chosen 4/16/ 02 for his first week 6/12/02 for his second and 10/12 /02 for his third. He selects these weeks at the first of the year. Our company will allow 15 employees off per week, so out of 100 employees I need to search all chosen vacation dates to determine if more than 15 employees have chosen the same week.

Thanks for the help
gMAC

 

[Pat Hartman]

Don't vacations ever start on days other than a monday and can't they ever be less than a full week?

 

[Jon K]

gMAC,

I would like to remind you that my queries can help you find matching dates, not matching weeks.

For instance, if two employees have chosen the same two weeks, one starting on Mondays while another starting on Tuesdays:

8/5/02(Mon) and 8/19/02(Mon)
8/6/02(Tue) and 8/20/02(Tue)

the queries will not tell you they have chosen the same two weeks.

So you should examine the query result to determine the weeks. I still think you should re-structure your table.

Jon

 

[gMAC]

I’m sure at other company’s vacation could start on any day. But the company I work for vacations always start on Mondays, and there are always a full week. There are other holidays to consider but if I get this problem worked out I’m sure I can figure out the holidays. I will try to restructure the database, I’ve recently started on it, and so it shouldn’t be a problem.
Thanks again
gMAC