Combining Queries and eliminating duplicates

[Guest]

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.

 

[Gary Walter]

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)

Hi Ed,

It appears to me this is doable *if* you provide a
distinct key for a person, i.e., what if you have
two John Doe's in platoon 1? ...or "John Toewes" in
your query is mispelled as "John Taves" in your
Benefits table?

In one of your 3 tables, you should have a unique
identifier field (say "NameID") which you return
in your 3-table query....
and in your Benefits table you should have a
foreign key (say again "NameID") that matches
the names up uniquely.

One method might be to apply your date criteria
in the 3-table query....
then in your final query you might left join your 3-table
query to the Benefits table on "[Date]" and "[NameID]"
and for a column "WorkStatus" you might use an immediate IF:

WorkStatus: IIF(Benefits.Status Is Null, qry3table.Status, Benefits.Status)

"Date" and "Name" are both reserved words and you would
be well advised, before you go any further, to rename them
to something like "DateWorked" and "EmpName" because
somewhere down the line it will "getcha."

Please respond back if I have misunderstood.

Good luck,

Gary Walter