One to many OR one to one with same data

[Allison]

Access 2003
Windows XP, SP3

This is still in the design phase. I'm trying to determine the best table
structure to end up with a form where I can input all info for one contact.

This is about addresses. I need to be able to isolate four different types
of addresses for each contact (contact ID and name are in a separate table).
For simplicity sake, I'll just call them AddressA, B, C, D.

The problem is, some contacts will have different addresses for all four
types. Some will have the same address for all four types. And some could
have one or two types of addresses be the same, but the others be different.

I'm trying to avoid having to type the same address multiple times. But,
still be able to query to get one type of address at a time. (For instance,
I may need to know all of AddressB for one project, or all of AddressC for
another one.)

I thought about a table called "tblAddressType", which would allow a
drop-down box on a form. But I'm stuck how to allow multiple types for one
address, but be able to identify them individually in a future query.

I think there needs to be some sort of interim query/table. Something that
creates a unique identifier for each address / type combination.

Any suggestions how to do this? Thank you for your help, and for your
patience in reading all this.

 

[Klatuu]

Here is an "off the top of my head" (or I pulled it out of my ..) idea <g>

So you have four possible address types.
Lets call them AT1, AT2, AT3, AT4
And we have contacts that may have 0 to 4 of these address types.
But one physical address could be used for AT1 and AT3 with a different
address for AT4, but he doesn't have an AT2 (possible?)

So in the address table, have a field that identifies the ContactID and the
Address Type.
Now, for the redirection. Add a TypeConversion Table. It would be

ContactID
Address Type
TypeReDirect

So in the above statement, assuming it is client 305, he would have 3
records in the redirection table

ContactID AddressType TypeReDirect
305 AT1 AT1
305 AT3 AT1
305 AT4 AT4

So you relate the Contact table one to many to the Redirect table and the
Redirect table One to Many to the Adress table.

 

[Allison]

Thank you for that. I'll ponder it for awhile to understand the
innerworkings and then take it for a test drive. Thanks.

Allison

 

[Klatuu]

Let me know how it works out.
It is similar to but not exactly like something I did a couple of years ago
to redirecty multiple accounts from an external source to a single account in
our system. It seemed to work well for me.

 

[Beetle]

Hi Dave,

I'm just curious about your solution because I don't get it. Of course
there's nothing new about that

Don't you need an AddressID or something in the TypeConversion table?

 

[Klatuu]

Okay, You have a client table. It has a ClientID as it's primary key.

You have 4 possible types of addresses. For simplicity, I used AT1, AT2,
AT3, and AT4 (AT = Address Type)

And, you have an address table. Each record will have all the address
demographics and it will also have an AddressType field that will contain one
of the values AT1...AT4. It also has the ClientID as a foreign key to the
Client Table so we know which Client client the Address record is a child of.

Now, the kicker. The OP did not want to have to duplicate an address if
that same address exitsted in one of the other address type records. For
example, let's say the client's Address Type 1 is 1 1st St, Podunk, KY 30303
and that his Address Type 3 is the exact same address. To avoid duplicating
it, we use redirection. To do that, we have a redirection table. It has the
ClientID, the Real Address Type, and the psuedo address type or the type we
will use to represent that address.

So for that record, the Redirection table would have

ClientID AddressType AddressRedirect
303 A3 A1

This record would tell us that where ever we need to show Address Type 3, we
would use the record from the address table that has the address type of A1.

To accomplish this, we can use a query like this:

SELECT tblClient.ClientName, tblAddress.Addr1, tblAddress.City,
tblAddress.State, tblAddress.Zip, tblAddrRedirect.AddressType
FROM tblClient INNER JOIN (tblAddrRedirect INNER JOIN tblAddress ON
(tblAddrRedirect.ClientID = tblAddress.ClientID) AND
(tblAddrRedirect.AddressReDirect = tblAddress.AddressType)) ON
tblClient.ClientId = tblAddrRedirect.ClientID
WHERE (((tblAddrRedirect.AddressType)="A3"))
ORDER BY tblClient.ClientName, tblAddrRedirect.AddressType;

So on this line:
WHERE (((tblAddrRedirect.AddressType)="A3"))

Using A3 and A1 will both return the same record.

 

[Beetle]

OK, that makes sense now. Thanks for the explanation. I appreciate
you taking the time.

 

[Klatuu]

Oh, rats, beetle. I was hoping to really confuse you and keep you up all
night thinking about it <g>

 

[Beetle]

Well, I did have to read it like three times before it penetrated my
thick head, if that makes you feel any better

 

[Klatuu]

<g>
--
Dave Hargis, Microsoft Access MVP

Well, I did have to read it like three times before it penetrated my
thick head, if that makes you feel any better

 

[John W. Vinson]

Oh, rats, beetle. I was hoping to really confuse you and keep you up all
night thinking about it <g>

"There is no problem however complicated which, when looked at in the right
way, does not be come yet MORE complicated."

 

[Klatuu]

<g>

BTW, John, if you would take the time to review my suggestion and make
comments, I would appreciate it.