Finding non-duplicate values

[ehsguy77]

I want to find values in a field called Invoice# that are not duplicates where field Ctrans# is a duplicate. These fields are both in table tblCtransPickups. Any help?

 

[lespaul]

like this?:

SELECT [INVOICE#] FROM tblCtransPickups HAVING COUNT([Ctrans#]) > 1



Leslie

 

[PHV]

Something like this ?
SELECT A.[Ctrans#], A.[INVOICE#]
FROM tblCtransPickups A INNER JOIN
(SELECT [Ctrans#] FROM tblCtransPickups
GROUP BY [Ctrans#] HAVING Count(*) > 1) B
ON A.[Ctrans#] = B.[Ctrans#]
GROUP BY A.[Ctrans#], A.[INVOICE#]
HAVING Count(*) = 1;


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ehsguy77]

PHV,

I don't understand where I would put this query expression. I'm in the select query design view, and there is only one table to be queried for this information. What does the INNER JOIN do?

ehsguy77

 

[PHV]

Go in the SQL windows and copy/paste the SELECT statement and say us what happens when you go in Data view.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ehsguy77]

It tells me my syntax is screwy.

 

[PHV]

Which version of access ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ehsguy77]

2000. It actually says "The syntax of hte subquery in this expression is incorrect. Check the parenthesis in the subquery.

 

[PHV]

Can you please post all the text in your SQL window ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ehsguy77]

SELECT A.[CtransID], A.[INVOICE#] FROM tblCtransPickups A INNER JOIN (SELECT [CtransID] FROM tblCtransPickups GROUP BY [CtransID] HAVING Count(*) > 1) B ON A.[CtransID] = B.[CtransID] GROUP BY A.[CtransID], A.[INVOICE#] HAVING Count(*) = 1;

 

[PHV]

I've created a tblCtransPickups Table with CtransID and INVOICE# fields as long, created a query with copy'n'paste of your previous post and runned it without any error.
I'm using ac2003 in ac2k compatibility mode.
You may consider create a saved query with the subquery's select statement and use it's name in the join clause.
BTW ac2k with which SP or SR ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ehsguy77]

Oops - forgive me, PHV. Invoice# is a text field. It shouldn't be. Would that still give me the syntax error?

 

[PHV]

Changed Invoice# to Text, recreate the query and still no error (as expected).
Have you tried the saved query work around ?
My question about the ac2k patching version isn't naive as original versions of office 2000 apps are known to be severely bugged.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ehsguy77]

haven't tried the saved workaround - will, though. My 2000 is patched.