query is returning duplicate records even with Select Distinct

[9tanstaafl9]

Hi,

I know nothing of queries so bear with me. The following query is working for me, EXCEPT that it is returning occasional duplicate values. I only want each number to appear once. I thought using the Union and the Select Distinct would solve the problem but it didn't. Any ideas?

SELECT DISTINCT actrec.recnum
FROM actrec actrec, jobcst jobcst
WHERE ((actrec.recnum=jobcst.jobnum) AND ((jobcst.status=$1) AND ((jobcst.actprd>=?) AND (jobcst.actprd<=?))))
UNION 
SELECT DISTINCT actrec.recnum
FROM actrec actrec, acrinv acrinv
WHERE ((actrec.recnum=acrinv.jobnum) AND ((acrinv.status<=$4) AND ((acrinv.actper>=?) AND (acrinv.actper<=?))))
UNION
SELECT DISTINCT actrec.recnum
FROM actrec actrec
WHERE (actrec.status=$3)

This has been driving me crazy all day, trying to figure out how to eliminate the duplicates so I can copy the list somewhere else for my macto. It finally occured to me that maybe I could avoid that, and some brilliant person could just tell me what's wrong with the query instead???

Jennifer

 

[haissk]

Cant you have it in one query like below

SELECT DISTINCT actrec.recnum
FROM actrec actrec, jobcst jobcst
WHERE ((actrec.recnum=jobcst.jobnum) AND (((jobcst.status=$1) or (acrinv.status<=$4) or (actrec.status=$3)) AND ((jobcst.actprd>=?) AND (jobcst.actprd<=?)) and (acrinv.actper>=?) AND (acrinv.actper<=?))))

 

[9tanstaafl9]

I can't get that to run, it says there's an unknown term? Are you sure about the parens? Sorry, but since I know nothing I can't check it. I tried randomly removing some but that didn't help!!!

 

[Fazza]

HI, Jennifer.

I don't understand what the $1, $4, $3 etc are in the criteria.

One thought, are you certain that the duplicates are duplicates? So, no trailing spaces? This can be quickly checked with an advanced filter or query table.

One approach that should be OK - so long as parameters are OK in a subquery? - and assuming your existing SQL is OK is to make your existing query a subquery as below, or similar.

HTH, Fazza

SELECT DISTINCT A.recnum
FROM ( all of your existing query within in these new parentheses ) A

 

[9tanstaafl9]

Seemed

 

[9tanstaafl9]

HI, Jennifer.

I don't understand what the $1, $4, $3 etc are in the criteria.

One thought, are you certain that the duplicates are duplicates? So, no trailing spaces? This can be quickly checked with an advanced filter or query table.

One approach that should be OK - so long as parameters are OK in a subquery? - and assuming your existing SQL is OK is to make your existing query a subquery as below, or similar.

HTH, Fazza

SELECT DISTINCT A.recnum
FROM ( all of your existing query within in these new parentheses ) A

I got a Syntax error. I don't know why. I copied and pasted your code and cut mine and put it inside the parens, and eliminating the leading and trailing spaces. I also tried eliminating the carriage returns.

As to the $3 and $4 etc. Those are statuses of jobs that we want to include, so we want all of status 3, but only the other statuses if they match the other criteria.

Anyway, thanks everyone for your help. It was late and I gave up and just went with a filter in my macro to eliminate the issue. Thank goodness for Advanced Filter! It was just the perfectionsist in me wanting to do this the right way, or at the very least to UNDERSTAND why the Union wasn't working the way I thought it should.

I asked the almost as unknowledgable person who setup the query about the leading or trailing spaces and he said that couldn't be it because that they are numeric not text so spaces wouldn't be allowed, and besides he was joining tables based on the Job number anyway.

Oddly, we ran the exact same query (my original not yours) in Crystal Reports and it ran fine and there were no duplicates!!!!!!!!!!!!!!!!!!!!!!!!!!

Thanks again!
Jennifer