Linking tables

[Parish Pete]

I know I'm adding the spokes after I've designed the wheel!

Ihave a table of records for about 150 angling club members each with a
unique permit ID. All the anglers have a numbered key to fishery gate and the
lock is changed annually to one of four sets of keys. The key numbers are
also unique and are a field within each record

Some keys are returned and some are not. To give me some idea of who has
what key I have done a full audit and all the keys that can be traced are now
in an XL sheet.

My Access table shows the old key and whether it has been returned and the
new key issued. Old keys are returned to stock for issue in the future.

Presently the XL sheet shows unique key no and who holds it by name (or if I
have it in stock). There are roughly 600 keys.

What I would like to do is bring the sheet into Access as a table and allow
my main record table to update it as each new angler's annual record is
created and the old key returned (or not) and a new one issued.

I can't quite get my head round the best way to link the two tables.

Any thoughts welcomed

Pete

 

[KARL DEWEY]

Use three tables --
[ClubMembers] with unique [PermitID] as primary key field. Add other fields
like FName,LName, Phone, Addr1, Addr2, City, State, Zip, Active (Yes/No), etc.

[KeyNumbers] with unique [KeyID] as primary key field. Add other fields
like LockNum (for locks other than gate), Active (Yes/No), etc.

[KeyIssue] with unique [IssueID] as primary key field. Add [PermitID] and
[KeyID] as foreign key fields, seting one-to-many relationship from
[ClubMembers] and [KeyNumbers] tables. Add fields [IssueDate], [ReturnDate],
etc.

Use form/subform for [ClubMembers] to [KeyIssue] with combo box to select
[KeyID] from [KeyNumbers].

Another form/subform for [KeyNumbers] to [KeyIssue] with combo box to
select [PermitID] from [ClubMembers].

Combo select queries to pull only Active member or keys. Subform queries to
pull records of keys issued ([IssueDate] but [ReturnDate] null).

 

[Parish Pete]

Thanks Karl, I'd considered the permitID and keyID, I overlooked issueID
and the 'active' parameter.

I think I might sit down and re-invent the wheel!

Many thanks

Use three tables --
[ClubMembers] with unique [PermitID] as primary key field. Add other fields
like FName,LName, Phone, Addr1, Addr2, City, State, Zip, Active (Yes/No), etc.

[KeyNumbers] with unique [KeyID] as primary key field. Add other fields
like LockNum (for locks other than gate), Active (Yes/No), etc.

[KeyIssue] with unique [IssueID] as primary key field. Add [PermitID] and
[KeyID] as foreign key fields, seting one-to-many relationship from
[ClubMembers] and [KeyNumbers] tables. Add fields [IssueDate], [ReturnDate],
etc.

Use form/subform for [ClubMembers] to [KeyIssue] with combo box to select
[KeyID] from [KeyNumbers].

Another form/subform for [KeyNumbers] to [KeyIssue] with combo box to
select [PermitID] from [ClubMembers].

Combo select queries to pull only Active member or keys. Subform queries to
pull records of keys issued ([IssueDate] but [ReturnDate] null).

--
KARL DEWEY
Build a little - Test a little

I know I'm adding the spokes after I've designed the wheel!

Ihave a table of records for about 150 angling club members each with a
unique permit ID. All the anglers have a numbered key to fishery gate and the
lock is changed annually to one of four sets of keys. The key numbers are
also unique and are a field within each record

Some keys are returned and some are not. To give me some idea of who has
what key I have done a full audit and all the keys that can be traced are now
in an XL sheet.

My Access table shows the old key and whether it has been returned and the
new key issued. Old keys are returned to stock for issue in the future.

Presently the XL sheet shows unique key no and who holds it by name (or if I
have it in stock). There are roughly 600 keys.

What I would like to do is bring the sheet into Access as a table and allow
my main record table to update it as each new angler's annual record is
created and the old key returned (or not) and a new one issued.

I can't quite get my head round the best way to link the two tables.

Any thoughts welcomed

Pete