Query results in Excel 2003 workbook changing for no reason.

[9tanstaafl9]

Hi all. This is very strange. There is a sheet in my workbook containing a table generated by the query listed below. It seems to be picking up two overlapping but different sets of data - though I know for a fact the data the query is looking at has not changed.

I notice that the number of rows remains the same at 181, but sometimes, five costcodes appear TWICE, and the other times I run the report, 5 additional costcodes apear, and none appear twice.

Any suggestions? Ideas? If you can't trust your queries, how are they supposed to be useful? I admit I don't know what I'm doing with regards to queries, but I thought it would either work or not work, not just work sometimes...

ODBC;DSN=Visual FoxPro Tables;UID=;;SourceDB=F:\MB7\BDBS;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;

SELECT DISTINCT jobcst.cstcde, cstcde.cdenme
FROM jobcst jobcst, cstcde cstcde
WHERE jobcst.cstcde = cstcde.recnum AND (jobcst.jobnum=?) AND (jobcst.status=$1) 
UNION 
SELECT DISTINCT bdglin.cstcde, cstcde.cdenme
FROM bdglin bdglin, cstcde cstcde
WHERE bdglin.cstcde = cstcde.recnum AND (bdglin.recnum=?) 
UNION
SELECT DISTINCT sbcgln.cstcde, cstcde.cdenme
FROM cstcde cstcde, prmchg prmchg, sbcgln sbcgln
WHERE prmchg.recnum = sbcgln.recnum AND sbcgln.cstcde = cstcde.recnum AND (prmchg.jobnum = ?)
UNION
SELECT DISTINCT pmcgln.cstcde, cstcde.cdenme
FROM cstcde cstcde, prmchg prmchg, pmcgln pmcgln
WHERE prmchg.recnum = pmcgln.recnum AND pmcgln.cstcde = cstcde.recnum AND (prmchg.jobnum = ?)
UNION
SELECT DISTINCT sbcnln.cstcde, cstcde.cdenme
FROM cstcde cstcde, sbcnln sbcnln, subcon subcon
WHERE subcon.recnum = sbcnln.recnum AND sbcnln.cstcde = cstcde.recnum AND (subcon.jobnum = ?)
UNION
SELECT DISTINCT pcorln.cstcde, cstcde.cdenme
FROM cstcde cstcde, pcorln pcorln, pchord pchord
WHERE pchord.recnum = pcorln.recnum AND pcorln.cstcde = cstcde.recnum AND (pchord.jobnum = ?)

I've been pressing the refresh button over and over, and it seems that one set of data (the one with the duplicates) is showing up about 10 times more often than the other.

I'm getting ready to just quit and tell them they can't have what they want... They can't kill me and eat me...

All help appreciated.
Jennifer

 

[RoryA]

Which version of FoxPro are you using? You shouldn't really need the DISTINCT clauses as the UNION adds an implicit DISTINCT operation to the combined table.

 

[9tanstaafl9]

Which version of FoxPro are you using? You shouldn't really need the DISTINCT clauses as the UNION adds an implicit DISTINCT operation to the combined table.

It's Visual FoxPro 9.0.

That's the first time I've used the Distinct. We have hundreds of cost codes, types and phases -- the combination of which would exceed the row limitations in Excel 2003 (no choice here). I only want to bring in cost codes that have costs in any one (or more) of the other tables.

Jennifer