Okay...I think this is part of the answer to the question I posted yesterday. My table relationships are not set up correctly.

Here is the deal...I have two tables. A CUSTOMER table, and an APPLICATION table. Lets say I have two customers that are applying through this application. The user will create both customers separately. Then they will open the application form where I have setup two fields called ID1 and ID2. The problem is I need to have a relationship between the CUSTOMERID setup in the CUSTOMER table and ID1 and ID2 in the APPLICATION table. How can I create this relationship without throwing ACCESS through a loop?

Any help would be greatly appreciated!

Thanks from Texas http://www.access-programmers.co.uk/ubb/smile.gif
jonp

What are you really trying to do?

Are you trying to establish a relationship between two customers? Or are you trying to say that multiple customers use a single application? The answer is different, and I would guess that it probably involves redesigning your tables. Usually, if you need a number after a field name to make it unique (ID1 and ID2), you have a problem with table design...

Chris

I am trying to establish an application that multiple customers use. Each customer is created separately. To uniquely identify each customer we use their SSN. So when all the customers are entered into the system with their personal info, then the user will begin creating the application.

Ultimately I need to be able to associate an application record with one to two customers.
What kind of field(s) can I setup on the APPLICATION form so that the user can select a customer from the CUSTOMER database.

JONP

In the terms of relational database design you can't have 2 of something. You can have 0, 1, or many. You need a separate table to store the relationship since it is many-to-many. The table should contain only two columns - customerID and AppliationID. This will allow you to relate many customers to many applications.

Hmmm...if I were your customer, I would NOT give you my SSN (social security number, for you international readers...). How will you assign an ID in that case?

Pat is correct about the table structure.

Ditto on the SSN comment. There are (maybe it's were - I see so many people trying to use it) federal prohibitions on its being used a primary key field for privacy reasons. As far as I know the law is still that you are only required to supply your SSN to someone who is going to pay you money and needs to report the transaction to the IRS like your employer or your bank (interest). We have not yet gotten to the point in the US where SSN is a national personID although we're getting closer. Big Brother is watching you.