Listing all Fields

[David S. Zuza]

OK here is the problem.

I have a table that has

Key Table:
KeyID
KeyDescription
Key Location

Personnel Table:
KeyID
LastName
FirstName


I am trying to create a report that lists all keys and under that any
personnel that have the key. Now I tried to create a simple report but every
time I try to list "all" the keys, it only list them if there is are
personnel related to that key. I want all keys listed even if there are no
personnel connected to that key. HELP! BTW, I am a newbie and using the
wizards are my main way of creating things. I can drag and drop controls on
to forms and get them to work. So please make it idiot proof.

Thanx in advance

 

[tina]

sounds like one key may be held by many people (copies of a master key, i
presume!), and one person may hold many keys. that's a many-to-many
relationship. suggest the following basic setup, as

tblKeys
KeyID (primary key)
KeyDescription
Key Location
(note: location should describe the key, not anything about who has the
key - or copies of it.)

tblPersons
PersonID (pk)
FirstName
LastName

tblKeyHolders
HolderID (pk)
KeyID (foreign key from tlbKeys)
PersonID (fk from tblPersons)

this is very basic, of course, but gets you moving in the right direction.
once the tables are built and correctly related in the Relationships
window - and populated - you can build a query using all three tables. LEFT
JOIN tblKeys to tblKeyHolders, and LEFT JOIN tblKeyHolders to tblPersons, on
the tables' primary/foreign key field pairs. post back if you need more
detail re the query.

also, recommend you read up on relational design, so you can ensure that
your tables/relationships are correctly set up in this db and future ones.
for more information, see
http://home.att.net/~california.db/tips.html#aTip1.

hth

 

[David S. Zuza]

Tina,

Thanx so much for the help. That did the trick.

David Zuza

 

[tina]

you're welcome

Tina,

Thanx so much for the help. That did the trick.

David Zuza