get summary of open orders

[buratti]

I need to complie a list of customers with open orders. This one should be pretty simple but I am stummped on how to get it. I have the following query, but it returns a list of each open order for each customer, essentially listing the same customer multiple times (if they have multiple open orders). I just need to list each customer once regardless if they have 1 or 100 open orders (Open orders are defined of having a status id <>3)

SELECT [Customers Extended].*, Orders.*
FROM [Customers Extended] LEFT JOIN Orders ON [Customers Extended].ID = Orders.[Customer ID]
WHERE ((Orders.[Status ID])=3);

In my research I have found that using a totals query will return the summary that I need. Although this techically works, it wont let me calculate totals on an asterisk (*), and I have to enter each field manually. This is not such a problem except both my customers extended table and orders table hold combined about 40 different fields and I need all of them in the results.

Is there an easier or different way to just get a non duplicated list of customers with open open orders using the asterisk(*) and not having to enter each field manually?

 

[MarkK]

Your requirement is in conflict with itself. You say you need to A) create a list of distinct customers with open orders, and that you need B) all the results from the Orders table, but you can't have it both ways. If one customer has two orders open and you want to have all the order information, then you must have that customer listed twice.

If this is for a report look into 'grouping', since in that case you can create a report header for a distinct customer and list one or more orders under that single customer header.

 

[buratti]

I kind of caught that after I posted it. Actually I do NOT need any information from the orders table just the Customers table. I just need a list of customers that have open orders

 

[plog]

I just need to list each customer once regardless if they have 1 or 100 open orders (Open orders are defined of having a status id <>3)

The below code will procuce a list of Customer ID's that have at least one open order:

 SELECT [Customers Extended].[ID] 
FROM [Customers Extended] 
WHERE [Customers Extended].[ID] = [Orders].[Customer ID] AND [Orders].[Status ID]<>3
GROUP BY [Customers Extended].[ID];

 

[buratti]

The below code will procuce a list of Customer ID's that have at least one open order:

 SELECT [Customers Extended].[ID] 
FROM [Customers Extended] 
WHERE [Customers Extended].[ID] = [Orders].[Customer ID] AND [Orders].[Status ID]<>3
GROUP BY [Customers Extended].[ID];

When I try to run that query it asks me for a value for [Orders].[Customer ID]. I added the Orders table without adding any inner/left/right joins and a few other modifications, and I got it to work, but that still doesn't solve my problem about having to add those 40 or so fields manually. Its not that big of a deal. Actually in the time it took to respond back and forth here I already added those fields and saved it as a query and will refer to that query in my vba instead of writing it out there. But just for future reference is there a way to summarize using the asterisk(*) in the query?

 

[plog]

is there a way to summarize using the asterisk(*) in the query?

No. An aggregate query in Microsoft Access requires you to specify the fields you want included.

 

[MarkK]

If all you need is the CustomerID, those are present in the Orders table, so you don't even need to look in Customers ...

SELECT DISTINCT CustomerID 
FROM Orders 
WHERE StatusID = 3

Also, consider never using spaces in identifier names, which will save you tons of keystrokes and make your work look much tidier.
If you need details from the Customer table, you could do a SubQuery ...

SELECT CustomerID, CustomerName 
FROM Customer 
WHERE CustomerID IN (
   SELECT DISTINCT CustomerID 
   FROM Orders 
   WHERE StatusID = 3 )

Cheers,

 

[buratti]

Thanks for all the suggestions. As stated in my third post in the time it took to write this all out and get responses I just did the dirty work I was originally trying to avoid. I entered all the fields I needed in the query and used totals to not display the duplicates, then saved it and reference this in VBA whenever needed. Thanks for your time anyway. I will keep these suggestions on file for future reference.