Drill down query problem 1

[cbase]

Hi guys,
I am hoping someone can point me in the right direction with this problem.

Query:
SELECT AudMainOrders.Date, AudMainOrders.EnquiryNumber, [OrderNumber] & " " & [OrderNumLetter] AS OrderNo, AudMainOrders.Consultant, AudMainOrders!Hours*AudMainOrders!Rate+AudMainOrders!Days*AudMainOrders!Rate+AudMainOrders!PVB*AudMainOrders!Rate+AudMainOrders!EWS+AudMainOrders!EwsExp AS CommitedTotal
FROM AudMainOrders
GROUP BY AudMainOrders.Date, AudMainOrders.EnquiryNumber, [OrderNumber] & " " & [OrderNumLetter], AudMainOrders.Consultant, AudMainOrders!Hours*AudMainOrders!Rate+AudMainOrders!Days*AudMainOrders!Rate+AudMainOrders!PVB*AudMainOrders!Rate+AudMainOrders!EWS+AudMainOrders!EwsExp
HAVING (((AudMainOrders.Date) Between [Start Date] And [End Date]))
ORDER BY AudMainOrders.EnquiryNumber;

I have listed the sql as far as I can get, now I need to limit the results to those where a change is detected between the committed total field.
That is, most of the results will have only one entry, these are to be disregarded the unique identifier (semi) will be the EnquiryNumber.

If an Enquiry Number appears more than once then if the difference is in the Committed Total Then list.

Appreciate all of your help

Diverdon

 

[PaulBricker]

I tested it out quickly and think you need to get rid of the Having statement. That left out some of the records I should have returned. Other than that, it ran well. I will look it over some more.

Paul

 

[cbase]

Cheers Paul, I removed the Having statement and returned the same 49 but my data, if it was perfect wouldn't produce any at all.
The end result of all our work but be an exception query that only shows entries where possible fraud or collusion between consultants and the administrator were involved.

In my posted SQL I changed the committed Fees to Fees to avoid having to put in a calculated field all the time. In the final code it will be [CommittedFees].

Its just before midnight in England so I better stop now or my wife will be more than a little upset that on Christmas Eve I would rather be on the computer than with her! These problems get under the skin until there sorted don't they?

Don

 

[PaulBricker]

Merry Christmas. I assumed Fees was probably the CommittedTotal we had been working with but I wasn't sure what your final evaluation would be used for. Depending on the complexity of all this, you could also send the data out to a Function that would return discrepancies. You would run the Function from a Form and just insert records into a Table that fell into certain categories like changed Fees or large numbers of transactions. Things like that. The function would be more powerful in terms of analyzing the data. Something for you to think about.
It's 5 a.m. here and everyone will sleep in this morning except me. Never could sleep on Christmas. Have a good day.

Paul