Simple way to combine unmatched and matched records in one query?

  • Thread starter Thread starter Ruth
  • Start date Start date
R

Ruth

I have a number of tables:-
All Customer Name & Address Details
Account codes for Customers who purchased Product X
Annual Sales for all customers
Annual Credits for all customers

I want to produce a report which lists Customer Name and Address Details for
Customers who purchased Product X with their total turnover - which I want to
calculate by Annual Sales less Annual Credits.

The problem I have is that when I run a query it will only pull through
Customer Details for customers who purchased Product X who have an Annual
Sales value AND an Annual Credits value. Obviously not all customers will
have an annual credit value, so where they don't, they don't appear. I want
the report to list all Customers who bought Product X and where there were no
credits - to show zero.

Is there a simple way to achieve this???!
 
Can you post the SQL of the query you are using now?
To do this, open the query in design view and switch to SQL view. Copy the
code and paste it into a message.
 
SELECT [Dairy Cake Customers].kcusno, [All Customer Details].name, [All
Customer Details].address__1, [All Customer Details].address__2, [All
Customer Details].city, [All Customer Details].county, [All Customer
Details].pcode, [All Customer Details].salman, [All Sales Invoices].[Net
Sales], [All Sales Credits].[Net Credit], [Net Sales]-[Net Credit] AS Turnover
FROM (([All Customer Details] INNER JOIN [Dairy Cake Customers] ON [All
Customer Details].[acode] = [Dairy Cake Customers].[kcusno]) INNER JOIN [All
Sales Invoices] ON [Dairy Cake Customers].[kcusno] = [All Sales
Invoices].[kcusno]) INNER JOIN [All Sales Credits] ON [Dairy Cake
Customers].[kcusno] = [All Sales Credits].[kcusno];
 
I belive the problem is in your last JOIN. It is an INNER JOIN which will
include only customer who have both sales and credits. If you change it to a
LEFT JOIN, I think it will get what you want. Also, notice I have changed
your calculated field to include the Nz function. This will avoid issues
where either of the two is NULL.

SELECT [Dairy Cake Customers].kcusno, [All Customer Details].name, [All
Customer Details].address__1, [All Customer Details].address__2, [All
Customer Details].city, [All Customer Details].county, [All Customer
Details].pcode, [All Customer Details].salman, [All Sales Invoices].[Net
Sales], [All Sales Credits].[Net Credit], Nz([Net Sales],0)-Nz([Net
Credit],0 AS Turnover
FROM (([All Customer Details] INNER JOIN [Dairy Cake Customers] ON [All
Customer Details].[acode] = [Dairy Cake Customers].[kcusno]) INNER JOIN [All
Sales Invoices] ON [Dairy Cake Customers].[kcusno] = [All Sales
Invoices].[kcusno]) LEFT JOIN [All Sales Credits] ON [Dairy Cake
Customers].[kcusno] = [All Sales Credits].[kcusno];
--
Dave Hargis, Microsoft Access MVP


Ruth said:
SELECT [Dairy Cake Customers].kcusno, [All Customer Details].name, [All
Customer Details].address__1, [All Customer Details].address__2, [All
Customer Details].city, [All Customer Details].county, [All Customer
Details].pcode, [All Customer Details].salman, [All Sales Invoices].[Net
Sales], [All Sales Credits].[Net Credit], [Net Sales]-[Net Credit] AS Turnover
FROM (([All Customer Details] INNER JOIN [Dairy Cake Customers] ON [All
Customer Details].[acode] = [Dairy Cake Customers].[kcusno]) INNER JOIN [All
Sales Invoices] ON [Dairy Cake Customers].[kcusno] = [All Sales
Invoices].[kcusno]) INNER JOIN [All Sales Credits] ON [Dairy Cake
Customers].[kcusno] = [All Sales Credits].[kcusno];

Klatuu said:
Can you post the SQL of the query you are using now?
To do this, open the query in design view and switch to SQL view. Copy the
code and paste it into a message.
Click to expand...
Click to expand...
 
How do I effect the change using your SQL statement - is it just a case of
cutting and pasting it into the SQL screen in design view - I've just tried
this and it says there is a syntax error (missing operator) in the nz query
expression......

Klatuu said:
I belive the problem is in your last JOIN. It is an INNER JOIN which will
include only customer who have both sales and credits. If you change it to a
LEFT JOIN, I think it will get what you want. Also, notice I have changed
your calculated field to include the Nz function. This will avoid issues
where either of the two is NULL.

SELECT [Dairy Cake Customers].kcusno, [All Customer Details].name, [All
Customer Details].address__1, [All Customer Details].address__2, [All
Customer Details].city, [All Customer Details].county, [All Customer
Details].pcode, [All Customer Details].salman, [All Sales Invoices].[Net
Sales], [All Sales Credits].[Net Credit], Nz([Net Sales],0)-Nz([Net
Credit],0 AS Turnover
FROM (([All Customer Details] INNER JOIN [Dairy Cake Customers] ON [All
Customer Details].[acode] = [Dairy Cake Customers].[kcusno]) INNER JOIN [All
Sales Invoices] ON [Dairy Cake Customers].[kcusno] = [All Sales
Invoices].[kcusno]) LEFT JOIN [All Sales Credits] ON [Dairy Cake
Customers].[kcusno] = [All Sales Credits].[kcusno];
--
Dave Hargis, Microsoft Access MVP


Ruth said:
SELECT [Dairy Cake Customers].kcusno, [All Customer Details].name, [All
Customer Details].address__1, [All Customer Details].address__2, [All
Customer Details].city, [All Customer Details].county, [All Customer
Details].pcode, [All Customer Details].salman, [All Sales Invoices].[Net
Sales], [All Sales Credits].[Net Credit], [Net Sales]-[Net Credit] AS Turnover
FROM (([All Customer Details] INNER JOIN [Dairy Cake Customers] ON [All
Customer Details].[acode] = [Dairy Cake Customers].[kcusno]) INNER JOIN [All
Sales Invoices] ON [Dairy Cake Customers].[kcusno] = [All Sales
Invoices].[kcusno]) INNER JOIN [All Sales Credits] ON [Dairy Cake
Customers].[kcusno] = [All Sales Credits].[kcusno];

Klatuu said:
Can you post the SQL of the query you are using now?
To do this, open the query in design view and switch to SQL view. Copy the
code and paste it into a message.
--
Dave Hargis, Microsoft Access MVP


:

I have a number of tables:-
All Customer Name & Address Details
Account codes for Customers who purchased Product X
Annual Sales for all customers
Annual Credits for all customers

I want to produce a report which lists Customer Name and Address Details for
Customers who purchased Product X with their total turnover - which I want to
calculate by Annual Sales less Annual Credits.

The problem I have is that when I run a query it will only pull through
Customer Details for customers who purchased Product X who have an Annual
Sales value AND an Annual Credits value. Obviously not all customers will
have an annual credit value, so where they don't, they don't appear. I want
the report to list all Customers who bought Product X and where there were no
credits - to show zero.

Is there a simple way to achieve this???!
Click to expand...
Click to expand...
Click to expand...
 
My syntax error, Should be a closing paren:

Nz([Net Sales],0)-Nz([Net Credit],0)

Yes, you can paste the code into SQL view, then switch back to design view.
I would suggest you keep a copy of the original in case my code is not
correct. Without seeing how the query is constructed in design view, it may
be you need an outer join instead of a left join.
--
Dave Hargis, Microsoft Access MVP


Ruth said:
How do I effect the change using your SQL statement - is it just a case of
cutting and pasting it into the SQL screen in design view - I've just tried
this and it says there is a syntax error (missing operator) in the nz query
expression......

Klatuu said:
I belive the problem is in your last JOIN. It is an INNER JOIN which will
include only customer who have both sales and credits. If you change it to a
LEFT JOIN, I think it will get what you want. Also, notice I have changed
your calculated field to include the Nz function. This will avoid issues
where either of the two is NULL.

SELECT [Dairy Cake Customers].kcusno, [All Customer Details].name, [All
Customer Details].address__1, [All Customer Details].address__2, [All
Customer Details].city, [All Customer Details].county, [All Customer
Details].pcode, [All Customer Details].salman, [All Sales Invoices].[Net
Sales], [All Sales Credits].[Net Credit], Nz([Net Sales],0)-Nz([Net
Credit],0 AS Turnover
FROM (([All Customer Details] INNER JOIN [Dairy Cake Customers] ON [All
Customer Details].[acode] = [Dairy Cake Customers].[kcusno]) INNER JOIN [All
Sales Invoices] ON [Dairy Cake Customers].[kcusno] = [All Sales
Invoices].[kcusno]) LEFT JOIN [All Sales Credits] ON [Dairy Cake
Customers].[kcusno] = [All Sales Credits].[kcusno];
--
Dave Hargis, Microsoft Access MVP


Ruth said:
SELECT [Dairy Cake Customers].kcusno, [All Customer Details].name, [All
Customer Details].address__1, [All Customer Details].address__2, [All
Customer Details].city, [All Customer Details].county, [All Customer
Details].pcode, [All Customer Details].salman, [All Sales Invoices].[Net
Sales], [All Sales Credits].[Net Credit], [Net Sales]-[Net Credit] AS Turnover
FROM (([All Customer Details] INNER JOIN [Dairy Cake Customers] ON [All
Customer Details].[acode] = [Dairy Cake Customers].[kcusno]) INNER JOIN [All
Sales Invoices] ON [Dairy Cake Customers].[kcusno] = [All Sales
Invoices].[kcusno]) INNER JOIN [All Sales Credits] ON [Dairy Cake
Customers].[kcusno] = [All Sales Credits].[kcusno];

:

Can you post the SQL of the query you are using now?
To do this, open the query in design view and switch to SQL view. Copy the
code and paste it into a message.
--
Dave Hargis, Microsoft Access MVP


:

I have a number of tables:-
All Customer Name & Address Details
Account codes for Customers who purchased Product X
Annual Sales for all customers
Annual Credits for all customers

I want to produce a report which lists Customer Name and Address Details for
Customers who purchased Product X with their total turnover - which I want to
calculate by Annual Sales less Annual Credits.

The problem I have is that when I run a query it will only pull through
Customer Details for customers who purchased Product X who have an Annual
Sales value AND an Annual Credits value. Obviously not all customers will
have an annual credit value, so where they don't, they don't appear. I want
the report to list all Customers who bought Product X and where there were no
credits - to show zero.

Is there a simple way to achieve this???!
Click to expand...
Click to expand...
Click to expand...
 
Oh my report works a treat now - that Nz function is worth remembering!
Thank you so much for your help.

Klatuu said:
My syntax error, Should be a closing paren:

Nz([Net Sales],0)-Nz([Net Credit],0)

Yes, you can paste the code into SQL view, then switch back to design view.
I would suggest you keep a copy of the original in case my code is not
correct. Without seeing how the query is constructed in design view, it may
be you need an outer join instead of a left join.
--
Dave Hargis, Microsoft Access MVP


Ruth said:
How do I effect the change using your SQL statement - is it just a case of
cutting and pasting it into the SQL screen in design view - I've just tried
this and it says there is a syntax error (missing operator) in the nz query
expression......

Klatuu said:
I belive the problem is in your last JOIN. It is an INNER JOIN which will
include only customer who have both sales and credits. If you change it to a
LEFT JOIN, I think it will get what you want. Also, notice I have changed
your calculated field to include the Nz function. This will avoid issues
where either of the two is NULL.

SELECT [Dairy Cake Customers].kcusno, [All Customer Details].name, [All
Customer Details].address__1, [All Customer Details].address__2, [All
Customer Details].city, [All Customer Details].county, [All Customer
Details].pcode, [All Customer Details].salman, [All Sales Invoices].[Net
Sales], [All Sales Credits].[Net Credit], Nz([Net Sales],0)-Nz([Net
Credit],0 AS Turnover
FROM (([All Customer Details] INNER JOIN [Dairy Cake Customers] ON [All
Customer Details].[acode] = [Dairy Cake Customers].[kcusno]) INNER JOIN [All
Sales Invoices] ON [Dairy Cake Customers].[kcusno] = [All Sales
Invoices].[kcusno]) LEFT JOIN [All Sales Credits] ON [Dairy Cake
Customers].[kcusno] = [All Sales Credits].[kcusno];
--
Dave Hargis, Microsoft Access MVP


:


SELECT [Dairy Cake Customers].kcusno, [All Customer Details].name, [All
Customer Details].address__1, [All Customer Details].address__2, [All
Customer Details].city, [All Customer Details].county, [All Customer
Details].pcode, [All Customer Details].salman, [All Sales Invoices].[Net
Sales], [All Sales Credits].[Net Credit], [Net Sales]-[Net Credit] AS Turnover
FROM (([All Customer Details] INNER JOIN [Dairy Cake Customers] ON [All
Customer Details].[acode] = [Dairy Cake Customers].[kcusno]) INNER JOIN [All
Sales Invoices] ON [Dairy Cake Customers].[kcusno] = [All Sales
Invoices].[kcusno]) INNER JOIN [All Sales Credits] ON [Dairy Cake
Customers].[kcusno] = [All Sales Credits].[kcusno];

:

Can you post the SQL of the query you are using now?
To do this, open the query in design view and switch to SQL view. Copy the
code and paste it into a message.
--
Dave Hargis, Microsoft Access MVP


:

I have a number of tables:-
All Customer Name & Address Details
Account codes for Customers who purchased Product X
Annual Sales for all customers
Annual Credits for all customers

I want to produce a report which lists Customer Name and Address Details for
Customers who purchased Product X with their total turnover - which I want to
calculate by Annual Sales less Annual Credits.

The problem I have is that when I run a query it will only pull through
Customer Details for customers who purchased Product X who have an Annual
Sales value AND an Annual Credits value. Obviously not all customers will
have an annual credit value, so where they don't, they don't appear. I want
the report to list all Customers who bought Product X and where there were no
credits - to show zero.

Is there a simple way to achieve this???!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top