Multiple relationships?

[sspayne]

Hi all, apologies for probably dumb question - haven't used Access for a couple of years and out of practice!

I have a table containing addresses. Two of the fields are [street] and [postcode]. Values in either can be duplicated, but when they are combined they create a unique value. I have a second table containing customer addresses, with the same fields, and wish to create a link between the two tables that only returns exact matches.

What's the best way of creating a relationship between the two tables that draws on two fields to create a unique value?

Thanks.

 

[fneily]

You might want to read, and find other readings on,
Fundamentals of Relational Database Design

http://r937.com/relational.html

In a relational database, such as Access, your tables should, I say must, be normalized. A protocol of normalization is that you don't have duplicate values, except for a primary key being a foreign key, in two different tables. So in your case, address and postcode is only in one table.
Why would you need them in two tables??

You basically answered your own question. In a query, connect address to address and postalcode to postalcode. Just remember, your database design is not correct.

 

[sspayne]

Thanks, I think I understand the point. The situation I have is that the first table contains all addresses in the county plus some attributes for each address. The second table is a set of customer addresses and the services they use. I wanted to, in effect, join the attribute data in the first table to the service used data in the second, using address as the link. The problem was that the address in both tables is split over five fields - I couldn't figure out how to create a relationship based on those five fields.

I know the design is inadequate, but it's more a case of what has been provided rather than what was designed.

 

[fneily]

The database needs to be redesigned. That's up to you. These patches will not help for future problems.

You didn't state what the primary keys are for the tables.

To make things simple: Create two queries, one for each table. In the query design, create a new field, let's say call it FullAddress and concatenate the five fields. Example:
FullAddress:[Street]& [City]& [State] & [Postalcode] & [Whatever]
For the one table, include attributes in the query.
For the second table, include service.
Then create a third query with the above two as sources. Connect the FullAddress fields. This'll create an inner join, matches only. You'll have matching address with their attributes and service.

 

[sspayne]

Thanks fneily, that was the sort of thing I had in mind when I posed the question. Part of this whole exercise will be illustrate to the guys commissioning this work that their data collection and storage techniques need huge improvement. At least I can try and give them a short term solution.

Thanks for your time.