Access querie help

[Guest]

I have a table with customer order numbers and item numbers as well as other
information. Each order number can have multiple lines with different item
numbers or even the same item number on multiple lines. I need to create a
querie that will identify all orders that have a single item number on them.
Even an order that has multiple lines could possibly have the same item
repeated and it being the only item number on the order. I do need to
account for these types of orders. I need a way to tell it to look for all
orders that have only 1 part number assigned to it, whether the order had one
line or multiple lines.
Any help would be greatly appreciated.

thanks,
Chris

 

[Guest]

I think this will do what you want. Use two queries.

SELECT Table12.OrderID, Table12.PartNum
FROM Table12
GROUP BY Table12.OrderID, Table12.PartNum;

SELECT FirstQuery.OrderID, Count(FirstQuery.PartNum) AS CountOfPartNum
FROM FirstQuery
GROUP BY FirstQuery.OrderID
HAVING (((Count(FirstQuery.PartNum))=1));

 

[Guest]

Forgive me for my ignorance, but how do I tell access to perform the
functions you described in the second query? I am just learning Access.

thanks,
Chris

 

[Guest]

Click on the query and then on the RED exclamation mark on the tool bar.

Or use the query for your form or report record source.

 

[Guest]

I successfully pulled the data for the first query. My confusion is in the
second step you outlined. I am unsure of how to design the query to pull
this data.

Logically I know this will work for me, however.....how do I use the Count
functions that you refer to? I only found Count in the total row, not Count
As. Basically, I do not know how to word the criteria necessary in the
design view of the query.

thanks,
Chris

 

[Guest]

Create a query in the design view. Click on the menu VIEW - SQL View. Paste
the SQL. Click on the menu VIEW - Design View. Save the query. Use as record
source for your form or report.

 

[Guest]

Well, it did not work for me. The first OrderID listed had 15 different part
numbers on it. I double shecked the script and there was no flaw. It just
didn't do what I need it to do.

Chris