Selecting Min and Max in query - Access 2010

[Krazy Kasper]

I have a query that selects the Min of BillingStartDate and the Max of BillingEndDate.
In the same query, I determine if the BillingEndDate is Greater than the BillingStartDate and denote it as OK, else Error.


I need to modify the query to determine if the Min BillingEndDate is Greater than the Max BillingStartDate and denote it as OK, else Error.
Following is the query. Thanks for any help.

SELECT dbo_SI_Main.MachineId, Min(dbo_SI_Main.BillingStartDate) AS MinOfBillingStartDate, Max(dbo_SI_Main.BillingEndDate) AS MaxOfBillingEndDate, IIf([BillingEndDate]>[BillingStartDate],"OK","Error") AS ErrorOrOK
FROM dbo_SI_Main
GROUP BY dbo_SI_Main.ConfigurationId, IIf([BillingEndDate]>[BillingStartDate],"OK","Error")
HAVING (((dbo_SI_Main.ConfigurationId)="123456"))
ORDER BY Min(dbo_SI_Main.BillingStartDate) DESC;

[oemar00]

This query does not look right.

The MIN and MAX is used to find the minimum and maximum value in a table so it is useless within the same row.

Looks like the data in this table need cleaning so that there is NO End Of Billing date with value less than Start Of Billing Date.

Why not find out the bad record with this SQL:

SELECT dbo_SI_Main.MachineId
, dbo_SI_Main.BillingStartDate
, dbo_SI_Main.BillingEndDate
WHERE dbo_SI_Main.BillingStartDate > dbo_SI_Main.BillingEndDate;

After you see the listing then fix those bad records.

[Krazy Kasper]

Thanks for your help. I figured it out.
There are multiple BillingStartDates and multiple BillingEndDates so I couldn't use your suggestion, however, by creating a table from the query, and then running a second query, I was able to get the desired results.
Thanks again.
This can now be closed.