Combining Queries and eliminating duplicates

[ewd97]

I am trying to create a query where the resultant is a combination of information from a table and a query and eliminate duplicates.

I have created a database where I run a query to display a schedule based on the information in three different tables. To display this information it will prompt the user for the date that they would like to see and it then displays the schedule. The schedule query displays, Date, Name, Platoon, Shift, and Status (worked or off).

I have also created a Benefits Table to enter when a person is scheduled off for vacation. It consists of Date, Name, Status (vacation, personal), start time, end time.

What I am trying to accomplish is running a query, based on a specific date, that will display the information from the Schedule Query and the Benefits Table. What needs to happen is, if there is an entry for a specific person on the date in question, the information from the Benefits Table will display for that person and not the information from the Schedule Query and still display all of the schedule query information for all other employees.

For Example:

Date Name Platoon Shift Status
02/04/04 Smith, John 1 05:00-15:30 Worked (from Schedule Query)
02/04/04 Doe, John 1 06:00-16:30 Vacation (from Benefits Table)

Thank you for the help.

 

[PaulBricker]

Use a Union query.
SELECT tblBenefits.Date, tblBenefits.Name,tblBenefits.Shift, tblBenefits.Status
FROM tblBenefits
Union Select tblSchedule.Date,tblSchedule.Name,tblSchedule.[start time] & "-" & tblSchedule.[end time] As Shift, tblSchedule.Status
From tblSchedule;

Then put your criteria on the date line in this query.

Paul

 

[JonFer]

Would it work for you to have a field in the tblSchedule table where you could mark the date as Worked or Vacation (or Sick or PTO)? If someone was scheduled and then took vacation, you'd update the Work status to Vacation and save yourself having to enter a new record in tblBenefits.

Your desired results would then be a simple select query from tblSchedule.

If that won't work, use Paul's union query but you should have a criteria on tblSchedule that the person is:

Not In (Select person from tblBenefits where DateField=RequestedDate)

 

[PaulBricker]

I thought about that but couldn't figure out how staff could be in two places at the same time. Either they are Scheduled or Benefits but didn't figure they could be both. You may be right that you have to add the criteria. The SQL would look like this

[blue]SELECT tblBenefits.Date, tblBenefits.Name,tblBenefits.Shift, tblBenefits.Status
FROM tblBenefits
Union Select tblSchedule.Date,tblSchedule.Name,tblSchedule.[start time] & "-" & tblSchedule.[end time] As Shift, tblSchedule.Status
From tblSchedule;
Where tblSchedule.Name Not In(Select tblBenefits.Name From tblBenefits);[/blue]

Paul

 

[ewd97]

JonFer, I understand what you are saying, and that was the original direction that I was heading with this database. The problem is, with 365 days per year and 71 employees, the schedule table then has 25,000 entries. This would work fine, except, if the schedule for any person changes throughout the year, I then have to recreate the table with the changes.

This is why setting the criteria and then running a query to give the results works, because if you change a schedule, you re-run the query and print it out.

I will try these suggestions tomorrow and let you know how I make out. If you have any other ideas, all would be appreciated.

Thank you.

 

[JonFer]

Paul - I assumed that Schedule was the rule (e.g worked M-F 8am-5 pm) and Benefits had the exceptions (e.g. vacation Tues 8am-5pm). A query on Schedule generates the individual dates or prompts for a specific date and then finds which schedules apply for that date.

ewd - Does tblSchedule just tell you what the current schedule is? It does not track time actually worked, just the schedule for a certain time period?

This makes sense based on what you were asking. If so, Paul's new union SQL is a good example of what to do. Don't try to merge the two tables like I first suggested. That was only a good idea if your schedule table had records for each day already.

 

[ewd97]

JonFer, you are correct that the tblSchedule just tells what the current schedule is. It does not track time actually worked. I am handling that through a different table since the schedule and time worked never seem to match.

I still have not had time to try the suggestion, but plan on working on it today.

Thank you.