Cannot design query to display records correctly

[Jack McAfee]

I have a query I am trying to design for a manpower database I am working
with. I have two tables among others involved in the query. The first table
is called Shifts and displays all of the available shifts and jobs. Each
record in the table has two fields: shift code (ie indicates date and
number of the shift - for example 012 would be second shift on Dec 1st) and
job code- the type of job needed -cashier, sales person, etc. The table is
indexed on shift and job and allows for duplicates - on some shifts there
may be two slots for the same job- ie two sales persons,etc.

The second table is called Shift Assignments and indicates who has signed up
for which shifts and jobs. Hence, there are three fields - name, shift code,
and job code. The table has a primary index based on name, shift, and job
and hence the records are unique with no duplicates.

I am trying to create a query that list all of the shifts and who has signed
up using the two tables above. I join the two tables matching on shift &
job. The join is setup to display all records from Shifts and only those
records from Shift Assignment that match. The problem I am running into is
on a shift where there are two sales persons and only one slot has actually
been assigned, the query lists the same name in both slots.

Am I not setting up the indexes or join correctly. What must I do so that
the query displays as it should in this instance two records where the first
list the name of the sales person assigned but on that same shift, the name
field is blank for the other sales person who has not been assigned.
Jack

 

[kingston via AccessMonster.com]

You need to add a field to your tables indicating slot number since that is
relevant information. So, the first table would have the fields [shift code],
[job code], and [slot number]. This represents the actual number of
positions available. The second table would then have [name], [shift code],
[job code], and [slot number]. This represents the actual number of
positions occupied. HTH