SQL Question

[luceze]

When i am running a union query on three tables it treats the select statement as if the distinct property was there is there any way to pull all the records. I included the code if you want to look at it.

SELECT DATE_ENTER, FUND, FUNCTION, OBJECT, SUB_OBJT, ORG, FND_YEAR, PROGRAM, NUMBER, REASON, AMOUNT, FIELD_IND, [fund] & "-" & [function] & "-" & [object] & "-" & [sub_objt] & "-" & [org] & "-" & [fnd_year] & "-" & [program] AS CODE
FROM FINYCASH
WHERE (((FUND) Between 100 And 200 And Not (FUND)=102) AND ((OBJECT) Between 5000 And 6000) AND ((FND_YEAR)="1&quot)
ORDER BY ORG;

UNION SELECT [DATE_ENTER], [FUND], [FUNCTION], [OBJECT], [SUB_OBJT], [ORG], [FND_YEAR], [PROGRAM], [NUMBER], [REASON], [AMOUNT], [FIELD_IND], [fund] & "-" & [function] & "-" & [object] & "-" & [sub_objt] & "-" & [org] & "-" & [fnd_year] & "-" & [program] AS CODE
FROM FINYGENJ WHERE ((Not (FUND)=102) AND ((OBJECT) Between 5000 And 6000) AND ((FND_YEAR)="1&quot AND ((FIELD_IND)="3&quot)
ORDER BY ORG

UNION SELECT DATE_ENTER, FUND, FUNCTION, OBJECT, SUB_OBJT, ORG, FND_YEAR, PROGRAM, NUMBER, REASON, AMOUNT, FIELD_IND, [fund] & "-" & [function] & "-" & [object] & "-" & [sub_objt] & "-" & [org] & "-" & [fnd_year] & "-" & [program] AS CODE
FROM REVFINYCHK
WHERE (((FUND) Between 100 And 200 And Not (FUND)=102) AND ((OBJECT) Between 5000 And 6000))
ORDER BY ORG;

 

[Smitty020]

use UNION ALL instead of UNION

hth,
Smitty

 

[JoeMiller]

In your code after the first block of the select statement you have a ";" which would cause the engine to stop as soon as it reads that because it thinks the select statement is done. Not sure if that's your problem but was the only thing I noticed.

HTH Joe Miller
joe.miller@flotech.net

 

[luceze]

That worked, Thanks so much.