Include records even if date is null

[CW]

I have a query that shows customer details including their latest order date.
I get this by using Group By Max in the Order Date field.
The query doesn't give me customers who have never placed an order, i.e.
their order date field is null.
I know I could do a separate query to pull just those records, but ideally I
would like them to be included on the one report, possibly at the top of the
listed records, before those that do have order dates.
Can do, or not?
Many thanks
CW

 

[KARL DEWEY]

I assume you have two tables - customers & orders. Use a left join customers
to orders.
If you can not get that to work post the SQL of your query.

 

[John Spencer]

Can be done assuming you have a table of customers. You need to add that
table into your query with an outer join (LEFT Join or RIGHT Join in place of
INNER JOIN).

Would you care to share your current query? You can post it by opening it in
design view and selecting View: SQL from the menu. Copy and paste the text
into your message.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[CW]

Karl -
Thanks, I have posted the SQL as suggested. Note that whereas I called them
"Customers" and "orders" in my post, in our mdb the terminology is actually
"Corporates", "actions" and "activities"...

SELECT [Corporates].[AccountManager], [CorpActivities].[CorpName],
Max([CorpActivities].[ActionDate]) AS MaxOfActionDate,
Last([CorpActivities].[Subject]) AS LastOfSubject,
[Corporates].[NextActionDate], [Corporates].[NextActivity]
FROM Corporates LEFT JOIN CorpActivities ON
[Corporates].[CorpName]=[CorpActivities].[CorpName]
GROUP BY [Corporates].[AccountManager], [CorpActivities].[CorpName],
[Corporates].[NextActionDate], [Corporates].[NextActivity]
HAVING (((CorpActivities.CorpName) Is Not Null));

Will look forward to your comments
Many thanks
CW

 

[CW]

Same as with Karl's earlier response...
Thanks, I have posted the SQL as suggested. Note that whereas I called them
"Customers" and "orders" in my post, in our mdb the terminology is actually
"Corporates", "actions" and "activities"...

SELECT [Corporates].[AccountManager], [CorpActivities].[CorpName],
Max([CorpActivities].[ActionDate]) AS MaxOfActionDate,
Last([CorpActivities].[Subject]) AS LastOfSubject,
[Corporates].[NextActionDate], [Corporates].[NextActivity]
FROM Corporates LEFT JOIN CorpActivities ON
[Corporates].[CorpName]=[CorpActivities].[CorpName]
GROUP BY [Corporates].[AccountManager], [CorpActivities].[CorpName],
[Corporates].[NextActionDate], [Corporates].[NextActivity]
HAVING (((CorpActivities.CorpName) Is Not Null));

Will look forward to your comments
Many thanks
CW