Unique Records Duplicating

[Simonc64]

Hi All

I'm an OK user of Access (not VBA) and seeking some assistance.

I have a query that is working out the distance between postcodes in the UK. I have for example, 16 unique postcodes in one column of data, and another 16 unique postcodes in another column. I was expecting to get 16 results with 16 pairs of codes however I'm getting 256 (16x16) results so whereas I want..........

AAAA AAA to XXXX XXX is 20 miles or 32.2km
BBBB BBB to ZZZZ ZZZ is 5 miles or 8.0km

what I'm getting is...........

AAAA AAA to XXXX XXX is 20 miles or 32.2km
AAAA AAA to ZZZZ ZZZ is 10 miles or 16.1km
BBBB BBB to XXXX XXX is 5 miles or 8.0km
BBBB BBB to ZZZZ ZZZ is 12 miles or 19.3km

ANY help and guidance welcome

Thanks

Simon

 

[Joe4]

It sounds like you have a Cartesian products going on.
Can you post the SQL code for your query (just change your query to SQL view and copy and paste the SQL code here?

 

[Simonc64]

Hi Joe

Many thanks for your response, heres the SQL

SELECT [1 : Postcode 1].[Postcode 1], [2 : Postcode 2].[Postcode 2], ((Sqr(([1 : Postcode 1]![Easting 1]-[2 : Postcode 2]![Easting 2])^2+([1 : Postcode 1]![Northing 1]-[2 : Postcode 2]![Northing 2])^2))/1000)/1.6093 AS [Distance Miles], ((Sqr(([1 : Postcode 1]![Easting 1]-[2 : Postcode 2]![Easting 2])^2+([1 : Postcode 1]![Northing 1]-[2 : Postcode 2]![Northing 2])^2))/1000) AS [Distance KM's] INTO [Calculated DIstances]
FROM [1 : Postcode 1], [2 : Postcode 2];

 

[Joe4]

Actually, 256 records sounds correct.
As each record in the first table links up with each of the 16 records from the second table. So 16 sets of 16 is 256.
So what exactly were you expecting?

 

[Simonc64]

Hi Jo, no that's exactly what I don't want. I want results as per my example in the original post, 16 results.......so the first code in list 1 matching against the first code in list 2, the second code in list 1 matching against the second code in list 2 etc etc, I don't want everything in list 1 matching against everything in list 2 ie 256 results

 

[Joe4]

Hi Jo, no that's exactly what I don't want. I want results as per my example in the original post, 16 results.......so the first code in list 1 matching against the first code in list 2, the second code in list 1 matching against the second code in list 2 etc etc, I don't want everything in list 1 matching against everything in list 2 ie 256 results

OK, then you need to JOIN those two tables in a relationship. You need to join them on a common field.
If you have a field on each one that numbers the records (1,2,3...) you can join on that.
Without any relationship or JOIN, the query will do a Cartesian product, like you see (where EVERY record from Table 1 is matched up to EVERY record in Table 2).

 

[Simonc64]

But that is the whole point, all 16 are different, there is no commonality between them

 

[Joe4]

But that is the whole point, all 16 are different, there is no commonality between them

So how do you know which record from Table 1 to match up to which record from Table 2?

Many new users of Access fail to understand what Access really is. It is NOT an extension of Excel - it is an entirely different related program. It is a relational database program. This implies that you have one or more tables that are related to each other by some common fields. Tables that aren't related are very rarely joined. In the rare occasion that they are, it is usually a Cartesian products (returns all possibilities).

It is also important to understand that within each Table, the order of the records really has no meaning, since each record is supposed to be independent of all the rest. Somebody once said to think of a bunch of records in an Access table like a bag full of marbles, all mixed up, where order really has no meaning. So, you cannot really say join the first record in Table 1 to the first record in Table 2, since there is no inherent order. You link the Table based on something tangible, specifically a field values (or multiple field values). That is not to say that you cannot sort the records in Queries, Reports, or Forms, it just isn't anything you can connect them on (unless you use VBA and loop through Recordsets, one record at a time).

So, think of it this way: if you choose any record from your first Table, how do you know which record from the second Table it should match up with?
If you are going by the strictly Excel concept of row number, you will need to add a field in your Access table and assign those numbers to them so you have a common field to join on.
Otherwise, you are probably using the wrong tool for the job!