Queries using same data produce different results

[kojak43]

I make two queries at the end of each fiscal quarter. One is a detail query and one is a summary query. Both underlying queries are exactly the same except the detail query uses two additional fields that show photo name and photo description.(from Photo table) The criteria for the quarter's date filter is exactly the same in both queries. The cost (from License table) is Summed in the summary query, but not the detail query.
When I run the queries, one of the line items appears in the summary query but not in the detail query.
When I check the underlying tables that holds the data, that one item appears in all the tables. i.e. Job table, Photo table and License table. I have checked that there are no extra spaces or other gremlins in the data.

So, the question is, why does it appear in one query and not the other?

When something has worked for two years, then suddenly stops, it makes me nuts!

TIA
kojak

 

[mdmilner]

Show us the queries (SQL)

Have a guess but can't substantiate until we see them.

 

[kojak43]

Sorry, I'm not sure how to send you the Queries (SQL).

I checked the FAQ but did not see any direction. I recall reading that you all do not want any attachments sent because of virus fears.

You may have to guide me through that.

k

 

[kojak43]

Hey wait. I figured it out!!!

This is the summary query:

SELECT Jobs.Prefix, Jobs.[Job#], Jobs.JobDescription, Sum(License.Cost) AS SumOfCost
FROM Vendor INNER JOIN (Jobs INNER JOIN (Photos INNER JOIN License ON Photos.[Photo#] = License.[Photo#]) ON Jobs.[Job#] = License.[Job#]) ON Vendor.[Vendor#] = Photos.[Vendor#]
GROUP BY Jobs.Prefix, Jobs.[Job#], Jobs.JobDescription, Jobs.Client, License.[Purchase Not Approved], License.PhotoStartDate
HAVING (((Sum(License.Cost))>0) AND ((Jobs.Client)="carnival") AND ((License.[Purchase Not Approved])=No) AND ((License.PhotoStartDate)>=#3/1/2004# And (License.PhotoStartDate)<=#5/31/2004#));

This is the Detail Query:

SELECT Jobs.Prefix, License.PhotoStartDate, Jobs.[Job#], Jobs.JobDescription, Photos.[Photo#], Photos.PhotoDescription, License.Cost, Vendor.VenName
FROM Vendor INNER JOIN (Jobs INNER JOIN (Photos INNER JOIN License ON Photos.[Photo#] = License.[Photo#]) ON Jobs.[Job#] = License.[Job#]) ON Vendor.[Vendor#] = Photos.[Vendor#]
WHERE (((License.PhotoStartDate)>=#3/1/2004# And (License.PhotoStartDate)<=#5/31/2004#) AND ((License.Cost)>0) AND ((License.[Purchase Not Approved])=No) AND ((Jobs.Client)="carnival") AND ((Jobs.Category)<>"Advertisement"));

Hope this is what you wanted.
k

 

[kojak43]

That last reply wasn't clear. I figured out how to send you the SQL. I have not figured out what is causing the problem.

k

 

[kojak43]

I figured it out. I really should be put to sleep. It was just carelessness on my part. I entered an incorrect criteria and it filtered for that criteria.

I am very embarrased.

But I feel good that I did figure it out

k

 

[mdmilner]

Great - Excellent. I wasn't betting on anything specifically but that's precisely why I wanted to see the SQL. Something had to be different about the two queries.

Mike