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.