query from tow tables without relation

[jamaarneen]

Hi

I am wondering if there is a possibilety to create a query that should retrieve data from tow tables which are not related?

I have a table with contacts, wherein i have some fields for addresses, and i also have a table with Location, also with some addresses fields. (in both tables, I'v got 4 fields for address information)

What i want is, to have a query with all addresses from both tables.

1) Is it possible?
2) is this the right way to do it? (i thought of making one table named 'address' and link it to both tables, but i think that putting the address information direct into the 'contact' : 'location' tables might be simpler. Am I right,)

It sounds a normalization issue...

Thanks in advanced
Ja

 

[Darrylles]

Jam,

Yep - this is definitely a normalization issue.

If you read your post, you are saying that if you have 10 contacts and 50 addresses - you want each contact to have 50 addresses each - yes? I don't think so.

What is your FINAL requirement? What do you want to achieve / display / produce?

Usually, the format for contacts is:

Contact has one address (which implies that the address should be saved in the Contact table), however, more than one contact may have the SAME address. This tells you that ADDRESS needs to be stored separately in an ADDRESS table.

Of course, a CONTACT may have more than one business address, which means that this is a MANY-TO-MANY relationship that requires a link-table.

A good 'future-proof' solution would be:
[tt]
tblContact tblContact_Address tblAddress
pk <---->>contact_fk |-->pk
forename address_fk <<---| Add1
surname Add2
Add3
Postcode
[/tt]
This enables a contact to have more than one address, and an address to have more than one contact (although this does not need to happen if not required).

PK = Autonumber primary key.

Add all records to tblContact
Add all records to tblAddress
To link contact to address: create a tblContact_Address record and take the tblContact pk and add it to the record, take the tblAddress pk and add it to the record. Done

Does this help?

ATB

Darrylle





Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[jamaarneen]

Thanks a lot Darrylie

Altought it's not news for me, but you did an excellent explanation-job!!!

[blue]and now to my problem: [/blue]

I have a table tblContacts, with information on the contacts, (wherein: con_AddressNumber / con_StreetName / ...). it is Only about Home Address (So, NO contact with addresses). And yes, more then on contact may have the same address, but this would be rarely (That's why I wouldn't bother to make a separate table for this reason only).

I also have a table tblLocations, which contains mainly 'Delivery information' only. which means: a loc_locationName, and the 'address fields.
I wouldn't store the Location in the Contact table, because, some of the fields in tblContacts are irrelevant for a Location.

[blue]What I want to achieve:[/blue] a list with all the addresses from both - Contacts and Locations. this is to give for the user the option to choose from a list a delivery address, either a home-address or a Location-address.

[blue]So:[/blue] is it possible to create a query that retrieves addresses from both tables? ( and of course, each address only ONE time)

Thanks
Ja

 

[lespaul]

have you looked at a UNION query?

SELECT Name, Address, City, State, Zipcode FROM Table1
UNION
SELECT Name, Address, CIty, STate, Zipcode FROM Table2

Leslie

Come join me at New Mexico Linux Fest!

 

[jamaarneen]

Thanks a lot Leslie

you just teached me something new!
never tried that one

Ja