Easy JOIN or UNION Question

[gray]

Hi All

Access 2002/2007
WinXP Pro

Or so I thought.... I've tried alternative combinations of Joins, Unions and group by's ..you name it... but I just can't get the results I need. But I just know this is an easy one...!

I have three tables: one for Organisations, one for Contacts and one for Addresses.

My requirements specify that Contacts can have 'Many' Addreseses and that 'Many' Contacts can have the same Address. Equally, Organisations can have 'Many' Addreseses and that 'Many' Organisations can have the same Address.

My Contacts, Organisations and Addresses all have their own 'Unique_No' (Autonumbered Key) and I use these to store relationships in a fourth table namely 'Addresses_Associations" which also has a Unique_No.

so:-

Addresses_Associations
--------------------------
Unique_No (AutoNum), Addresses_Unique_No,Contacts_Unique_No,Organisations_Unique_No

All good so far!

I populate a Combobox which displays, for any given Address, all the existing associations with Contacts (note: there are separate Comboboxes for Contacts and Organisations so dont worry about them for the moment). I have to join the Addresses_Associations table to the Contacts table as there are a number of status fields in the Association which denote use as a Home,Work,Construction Address etc.

OK, Peice of Cake! e.g. For Address No 15

SELECT TBl1.*, TBl2.Unique_No,Tbl2.Home,Tbl2.Work, Tbl2.Addresses_Unique_No,Tbl2.Record_Status FROM Contacts As Tbl1
INNER JOIN Addresses_Associations As Tbl2 ON TBL1.Unique_No = Tbl2.Contacts_Unique_No WHERE Tbl2.Addresses_Unique_No=15 ORDER BY TBL1.Name

In 'Edit' mode, I want to to extend the Combobox list to include ALL other contacts. The user can then pick one and click an 'add' button which runs off to SQL INSERT a record in the Associations table accordingly.

For guidance, I want to mark entries where they are already in use for any given address thus:-

SELECT (IIF(TBL2.Addresses_Unique_No = 15,'[Sel] ','')) + TBL1.Name As Name , TBl1.*, TBL2.Unique_No,Tbl2.Home,Tbl2.Work, Tbl2.Addresses_Unique_No, Tbl2.Record_Status FROM Contacts ...................

Ahh.. I thought, I'll simply change my INNER JOIN for a LEFT JOIN which I thought would return ALL Contacts but still show me where there was an entry in the Associations table for record 15 But it just returns the same results as an the INNER.

So I tried a UNION SELECT using the same SQL but removed the Tbl2.Addresses_Unique_No=15 from the UNION call... but this showed duplicate contacts where a Contact was associated with other Address records... I thought Union automatically removed duplicates absed on primary keys
...

I've tried with Group-Bys but I keep getting syntax errors complaining about aggregate fuctions?

Can anyone help please?

 

[MarkK]

- What I would do is simplify this data model.
- Address data is both volatile and potentially commonly used to return subsets of contacts or companies by region. You gain something in storage efficiency by pointing mulitple objects at a single address, by I think it's a bit of a maintenance headache, namely the headache you are posting for right now. Hard disk space is cheap, cheap, cheap. These days I think data storage efficiency should play second fiddle to data storage simplicity and ease of use. Your many-to-many link is going to have to be understood and navigated for every future query you might make on this address data.
- Users don't get it. Your user interface will require a tool to select an address and link it to a company, say. You'll understand why it works that way, but the $12/hour data entry person isn't going to get it. Where three companies share the same address someone is going to edit it when one company moves and inadvertantly edit all the others' too.
- What I would do is provide dead simple address Copy and Paste functions. If the billing address and shipping address are the same for Company X, then Copy and Paste it and store the duplication. Then when shipping moves to their new address ...
- And this is my opinion. I've implemented the system you are talking about and it's cool, don't get me wrong, but it's fragile. And address data is a high traffic area more like a train station than a china shop.

 

[dkinley]

These days I think data storage efficiency should play second fiddle to data storage simplicity and ease of use.

Hear! Hear!

 

[gray]

Hi

I have to admit, the theory of only ever storing something once does present a mountain of headaches! And, as you say, especially with volatile address data where, for example, maintaining old delivery records is needed.

How I get around this is by using version control.... so where a company moves address, I create a higher version of their contact record... in this way new invoices/delivery notes etc get the higher version of address, old records keep their existing address..... BUT.... if one needs to update old records en masse it can be done by editing one record alone...

Sooo many ways to skin one cat cheers

 

[gray]

I think I have cracked this so if it's of any use to anyone here's how I did it.... please, please, thoroughly check any results you might get....

I run my first query to extract Contact records which are in use for the given Address record (ie. Address no 15) by INNER joining the Associations table

SELECT
TBL1.Name,
TBl1.Unique_No,
TBL2.Unique_No,
Tbl2.Home,
Tbl2.Work,
Tbl2.Addresses_Unique_No,
Tbl2.Record_Status,
Tbl2.Edit_Status, blah, blah
FROM Contacts As Tbl1
INNER JOIN Addresses_Associations As Tbl2
ON TBL1.Unique_No = Tbl2.Contacts_Unique_No
WHERE Tbl2.Addresses_Unique_No=15
ORDER BY TBL1.Name

Then I run a UNION SELECT which ultimately grabs any remaining Contact records i.e. those NOT in use for Address record 15. UNION SELECT apparently needs the same no and type of columns so even though some of the remaining Contact records are not associated with an address at all, the Associations table still needs Joining...so I use LEFT this time....

UNION
SELECT
TBL1.Name,
TBl1.Unique_No,
TBL2.Unique_No,
Tbl2.Home,
Tbl2.Work,
Tbl2.Addresses_Unique_No,
Tbl2.Record_Status,
Tbl2.Edit_Status , blah, blah
FROM Contacts As Tbl1
LEFT JOIN Addresses_Associations As Tbl2
ON TBL1.Unique_No = Tbl2.Contacts_Unique_No

The WHERE statement for my Union Select runs a subquery to extract the same records as the first Select in order that they can be removed from the Union... and I negate them by use of NOT IN...

so.....

WHERE (Tbl1.Unique_No) NOT IN (
SELECT Tbl1.Unique_No FROM Contacts As Tbl1
INNER JOIN Addresses_Associations As Tbl2
ON TBL1.Unique_No = Tbl2.Contacts_Unique_No
WHERE Tbl2.Addresses_Unique_No=15)

then run the same order by....
ORDER BY TBL1.Name

The Select for the NOT IN should only have one column....

So far, this seems OK, it lists all the extg Contacts for an Address and then offers a list of other Contacts whether they are in Use with another address or not in use at all...

Well I hope to hell it's OK... it's taken me a couple of days to crack!!

Rgds