Help getting specific data from a list of sales orders

[EDGE99]

I have a transaction database that I am trying to query a very select amount of data.

I have a list of Product Codes. I have many Sales Orders that can contain 1 or many product codes. I have been working on a query that will only spit out Sales Orders that contain only 1 Product Code.

What I have done so far is create a list of Sales Orders that contain my list of Product Codes. Now I need to go through these Sales Orders and pick out which ones have only 1 Product Code.

Any ideas on how to do this?

 

[EDGE99]

I can do this by creating two seperate queries but I would like to do this all in one query if possible. Is there a way to do what I am asking in a single query?

 

[dhookom]

Please provide the SQL views of your two queries so that we have the minimum amount of information regarding your tables and fields.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[EDGE99]

Sorry about that. I should have added this at the start.

Query 1: qry_GetSalesOrders (Get a list of Sales Orders)

SELECT [Transactions].[Sales Order ID], Count([Transactions].[Product Id]) AS [CountOfProduct Id]
FROM [Transactions]
GROUP BY [Transactions].[Sales Order ID];

Query 2: Get only Sales Orders that have one Product ID

SELECT qry_GetSalesOrders.[Sales Order ID], qry_GetSalesOrders.[CountOfProduct Id]
FROM qry_GetSalesOrders
WHERE (((qry_GetSalesOrders.[CountOfProduct Id])=1))
ORDER BY qry_GetSalesOrders.[Sales Order ID];

 

[SkipVought]

Hi,

Something like this???

Select (column, list)
From [Sales Orders]
Where [OrderNbr] In (Select [OrderNbr] from [Sales Orders] Group By [OrderNbr] Having Count(Distinct [ProductCode]=1)

Skip,

_[red][/red]

 

[EDGE99]

I was able to get the SQL to take but I am getting this error when executing it:

undefined function 'DISTINCT' in expression

SELECT [Transactions].[Sales Order ID], [Transactions].[Source Sys Product Id]
FROM [Transactions]
WHERE [Transactions].[Sales Order ID] IN (SELECT  [Transactions].[Sales Order] 
FROM [Transactions] GROUP BY  [Transactions].[Sales Order ID] 
HAVING COUNT(DISTINCT([Transactions].[Source Sys Product Id]=1))) ;

Any ideas why I am getting this error?

 

[EDGE99]

I took the DISTINCT out and now it prompts me to enter a Sales Order ID. Not sure how to get around this one as my SQL knowledge is pretty weak.

 

[SkipVought]

you were not consistent with Sales Order ID

SELECT [Transactions].[[b]Sales Order ID[/b]], [Transactions].[Source Sys Product Id]
FROM [Transactions]
WHERE [Transactions].[[b]Sales Order ID[/b]] IN (SELECT  [Transactions].[[b]Sales Order ID[/b]] 
FROM [Transactions] GROUP BY  [Transactions].[[b]Sales Order ID[/b]] 
HAVING COUNT(([Transactions].[Source Sys Product Id]=1))) ;

Skip,

_[red][/red]

 

[EDGE99]

Skip,

Thanks for pointing out the typo. I ran into another issue right after fixing that. I get this: Data type mismatch in criteria expression.

Both fields are text as they can contain numbers and characters. Does the way we are doing things here require a different data type than text?

Thanks again for your help.

 

[SkipVought]

Is the data Type for [Transactions].[Source Sys Product Id] STRING or NUMERIC? The query implies NUMERIC.

I believe that it is nearly ALWAYS a design mistake to make IDENTIFIERS numeric types. But that's another issue.

Skip,

_[red][/red]

 

[EDGE99]

They are string. Is there anyway to do this with string data types?

 

[SkipVought]

String with a value of ...
[tt]
"1"
or
" 1"
[/tt]
or soemthing else??? MAKES A DIFFERENCE!

You need TICS around the literal.

HAVING COUNT(([Transactions].[Source Sys Product Id]='1'))) ;

Skip,

_[red][/red]

 

[EDGE99]

Skip,

Well the SQL did execute however it is not giving only Sales Orders with one Product Code.

Sales Order ID Product Id
451085 NTAB3
441512 NTDU2
441512 NTE98
[red]451085 NTKC14 [/red]
[red]451085 NTKC50 [/red]
441505 NTL3AA
441512 NTTK1B
441505 NTZCAA

 

[SkipVought]

where did I go wrong....

HAVING COUNT([Transactions].[Source Sys Product Id])=1) ;

having the COUNT of ProductID equal 1

sorry, my last post was incorrect.

Skip,

_[red][/red]

 

[EDGE99]

Skip,

Thank you so much for your help. That did the trick. I appreciate you taking the time to show me how to do this. I now understand how this works and why the previous actions failed.

Your awesome...