Using two different records from same table on Form

[Guest]

I have just spent the last hour or so looking through the topics and have
found a few very helpful ones - but I haven't managed to sort my problem out.

I am creating a database for my shipping company - we need to print out
forms with two different contact addresses on - e.g. Consignee and Sender
(amoung other things)

My problem is that I would like these "people" to be housed in one table
because they could at any time be either a Consignee or a Sender so to have
two address tables means having to search/enter/amend data twice.

I have tried to insert the fields onto a form that looks up the names and
inserts the relevant addresses which works - but when I look up the second
address it changes the first one too- therefore they always end up the same!
They seem to be linked somehow, but I can't find a way to unlink them. I
tried using a subform (same result). I tried creating a linked table called
Contact1 which is a mirror image of Contact and it still changed both
sections - am about ready to bin the whole idea.

I am a beginner, and have no VB skills, but am a quick learner!

Am I going about this the right way? Any suggestions would be greatfully
received.

 

[Allen Browne]

It makes perfect sense to have the different kinds of clients--consignee,
sender, and so on--in the same table.

However, you will need another table to handle the actual
loads/consignments/containers or whatever you send and receive. This table
should have fields such as these:
ConsigneeID foreign key to Client.ClientID
SenderID foreign key to Client.ClientID
DateShipped date
Contents description of what was shipped.

You can then use the Relationships window (Tools menu) to create two
relationships between the clients table and this one. To create the 2nd
relationship, add the Clients table to the Relationships window a second
time. Access will alias it as Client_1, and you can then create the 2nd
relationship.

You will then have one form where you enter the clients, and another where
you enter the consignments. In the consignments form, you will be able to
the sender and the consignee for the current record. Unless you have many
thousands of clients, you could do that with a pair of combo boxes.

 

[Guest]

Thank you Allen, I will try that. I appreciate your help.

T-ray

 

[Guest]

Hi there again

I have been trying to implement your suggestion but am still encountering
problems.

Firstly I amalgamated the consignees and senders into one "Contact" table.
My key field is therefore ContactID. In the Consigment Table I have fields
ConsigneeID and SenderID. I added the Contacts table twice on the
Relationship screen and linked the fields from Consigment to Contact via
SenderID to ConactID and ConsignmentID to ContactID inthe Contact_1 table.
(do I need to linke the two contact tables?)

When I try and fix my Consignment Note form there doesn't seem to be any way
of selecting fields from the Contact_1 table so I am still getting the
addresses as the same.

I tried creating a new form with the Wizard to see if it picked it up
automatically but only the ConsigneeID field was there - this worked in the
sense that it let me choose a different Contact name, but there was no way of
inserting all the other address fields on the form.

I'm sure my problem is linked to the Foreign key - but I can't make out what
that is/means.

Any thoughts would be appreciated.

Regards

 

[Allen Browne]

Okay you have the people in one table, and the consignments in another.

Joining Consignment.ConsignmentID to Contact.ContactID sounds wrong. I would
suggest the following:

Contact table:
ContactID AutoNumber Primary key
...

Consignment table:
ConsignmentID AutoNumber Primary key
SenderID Number links to Contact.ContactID
ConsigneeID Number links to Contact_1.ContactID
...

A "foreign key" just means a field in one table (like SenderID in
Consignment) that links to the primary key of another one. You are using
them here, even if the jargon is not familiar.