Combining 3 queries into one table of 3 columns

[mjb-is]

Hi all, I hope this is simple for someone as I feel like I am banging my head against a brick wall!



Forgive me as there is some underlying data I won't be able to share, due to it's nature.

I have 3 queries that are based on the same underlying data:-

'partial_postcode' is the first part of a postcode, plus the first number of the second part, e.g NN1 7, SK18 3, etc

They all return 'partial_postcode' and 'number_of_customers'

Query1 - ALL customers for each 'partial_postcode', with grouping on 'partial_postcode' and a count of the number of customers grouped into that postcode area
Query2 - Subset A of customers in each 'partial_postcode', same grouping and counting as above
Query3 - Subset B of customers in each 'partial_postcode', same grouping and counting as above

The nature of this is that:-

• all 'partial_postcodes' in Query3 are present in Query2 and Query1
• all 'partial_postcodes' in Query2 are present in Query1

What I want is to joint the outputs into 4 columns

partial_postcode, query1!count_of_customers, query2!count_of_customers, query3!count_of_customers

So I have Query > Query2 > Query3, with LEFT JOINS on partial_postcode



Now my logic says I should get:-

• ALL records from Query1 (2100), PLUS
• any corresponding records from Query2 (1268), with non-existent Query2 records (832) showing blank, PLUS
• any corresponding records from Query3 (901) , with non existent Query3 records (1199) showing blank

Therefore my output i expected to be 2100 records.

However, when I do this I get 3154 records, and I cannot understand why.

Some postcodes are split back out, i.e. not grouped to a single occurrences, and some totals don't even make sense. i.e. I have Query 3 with a value of '2', but then the corresponsing out put for that column can be '1'

Here is an example of the data

Query 1

CA3 0, 11 - One Unique record

Query 2

CA3 0, 2 - One Unique record

Query 3


CA3 0, 1 - One unique record

Expected Output

CA3 0, 11, 2, 1 - One record

Actual Output


CA3 0, 11, 2, 1
CA3 0, 11, 1, 1
CA3 0, 11, 1, 1
CA3 0, 11, 2, 1
CA3 0, 11, 1, 1
CA3 0, 11, 1, 1


I get six records when I expect one. And how can the 3rd field be '1' in four of those records when the corresponding data from Query2 is '2'?

I am also considering resorting to the following to get this dataset I need:-

• Convert these to 'Make Table' queries and create 3 tables
• Export those tables to CSV
• Import the CSVs into Excel
• Copy/Paste the Query2 and Query3 output into tabs in the Query1 file
• Use VLookup to populate column 3 and 4 from lookups of the corresponding tabs

This just seems like a cop-out, having to revert to Excel to manage data! This has to be something simple I am missing.

[Minty]

You have written a very good description but without some actual data to play with this is very difficult to visualise.
We also can't see how the underlying queries are working before the joins.

Can you not just anonymise the sensitive data from your tables, remove unrequired columns and leave behind the postcode data, which isn't sensitive, I already have a list of all the postcode districts.
Then we can also see the queries you already have and see where the issue is.

There are some tools available to help randomising data if you have a search.

[Welshgasman]

Here is one of them.
https://www.accessforums.net/showthr...t=randomize#19

[Gicu]

Can you please show us the design view of the final query where you join the three queries. If I read your explanation right you should ONLY have 2,100 records from Q1 with the second column populated for each record, the third column for some and the fourth for even less. To do that you should have left joins between the partial codes going from Q1 to both Q2 and Q3.

Cheers,

[mjb-is]

Can you please show us the design view of the final query where you join the three queries. If I read your explanation right you should ONLY have 2,100 records from Q1 with the second column populated for each record, the third column for some and the fourth for even less. To do that you should have left joins between the partial codes going from Q1 to both Q2 and Q3.

Cheers,

This is the final query design view, showing how many records are returned by each of the joined queries.



Q1 Output (2100 records)



Q2 Output (1268 Records)



Q3 Output (910 Records)



Final Query Output - 3154 Records when 2100 expected. You can see the highlighted parts where Postcodes split back out, which I can't understand.




In these, 'RLD' is just an alpha character (A, B, C etc) that groups postcodes into larger areas.

The postcodes in Q2 are a subset of Q1, and the postcodes in Q3 are a subset of Q2 and Q1. The only difference is the underlying queries that produce different totals, based on differing criteria to select the resultant postcodes output.

I expected only 2100 records out too, and don't understand how I can get 3154 out.

[mjb-is]

Sorry, ignore the highlighting on 'CV3 3' and 'CV8 3'. The ones that are split out are the 'CA3 0' ones in this partial result.

[Gicu]

You need to left join query 3 directly to query 1 not 2.

Cheers,

[CJ_London]

Can’t see the full data but a left join will not include postcodes in queries 2 and 3 that are not in query 1.

[June7]

That output indicates to me that CA3 0 is not unique in the 3 queries, resulting in a Cartesian relation of records. If each query has 2 CA3 0 records then the output would have 6. Considering the large difference in expected and actual, there must be more codes that are not unique. Perhaps a compound join with RLD and Partial pairs is needed.

[mjb-is]

Can’t see the full data but a left join will not include postcodes in queries 2 and 3 that are not in query 1.

Correct. All Q2 postcodes appear in Q1. All Q3 postcodes appear in Q2 and Q1.

[mjb-is]

That output indicates to me that CA3 0 is not unique in the 3 queries, resulting in a Cartesian relation of records. If each query has 2 CA3 0 records then the output would have 6. Considering the large difference in expected and actual, there must be more codes that are not unique. Perhaps a compound join with RLD and Partial pairs is needed.

There is only one record of CA3 0 in each query, as shown in the screenshot of each. I understand what you mean and agree it would explain multiple rows in the final query if this was the case.

[mjb-is]

** deleted as should have been a reply to a poster **

[mjb-is]

You need to left join query 3 directly to query 1 not 2.

Cheers,

I am sure I tried this, as I had the same thought, but it made no difference, as the join should work either way as Q3 is a subset of both Q2 and Q1, so should work in the same way when joined to Q1 directly, or via Q2.

[June7]

I cannot replicate this issue from the data shown. You need to provide your db for analysis.