Bloody Microsoft (#328923 in a series)

[tamaranth]

I have a set of records*. I am grouping them by PO Number.

edited to correct record counts, which I misremembered...

If I specify Min on the Due Date, I get 1673 records.
If I specify Max on the Due Date, I get 1566 records.

Therefore, I am specifying 'get a low-IQ second jobber and have it generate this report manually'.

*doomed records, as they live in an Access 97 database. Pray for them.

Comments

[red-cloud.livejournal.com]

NULL values?

[tamaranth.livejournal.com]

there aren't any null values in the table.
Anyway, why would there be a Max and not a Min?

[latexiron.livejournal.com]

Difference between > and >= ?

[tamaranth.livejournal.com]

nothing that technical -- am only using SQL aggregate functions! Admittedly all built up by Access, but it looks fine.

I got the numbers the wrong way round, though:
SELECT tblPOHistory.Cat, tblPOHistory.PONO, Max(tblPOHistory.CreateDate) AS CreateDate
FROM tblPOHistory
GROUP BY tblPOHistory.Cat, tblPOHistory.PONO
HAVING (((Max(tblPOHistory.CreateDate))<[start date]));
gives 1566 recs

SELECT tblPOHistory.Cat, tblPOHistory.PONO, Min(tblPOHistory.CreateDate) AS CreateDate
FROM tblPOHistory
GROUP BY tblPOHistory.Cat, tblPOHistory.PONO
HAVING (((Min(tblPOHistory.CreateDate))<[start date]));
gives 1673 recs.

Yes, I am using the same start date, in case you wondered!

[tamaranth.livejournal.com]

just assume the Max and Min in the Select clause are also marked bold, hmm?

[red-cloud.livejournal.com]

It seems reasonable enough to me that these can potentially return different result sets.

Assuming that there is a spread of CreateDate values for any given Cat/PONO combination, then the set of MAX(CreateDate) values potentially differs from the set of MIN(CreateDate) values, thus potentially different numbers of records will meet the conditions in the HAVING clause. I think.

[red-cloud.livejournal.com]

P.S. I am assuming that the fact that these two pieces of SQL return different row counts is the problem that you are highlighting. I have also assumed that there is more than one CreateDate per Cat/PONO combination.

However, if you are saying that there is only ONE row per Cat/PONO combination, and no NULLs for CreateDate, then I agree that there should be no difference between MIN and MAX in that context (and, really, no reason to be using MIN and MAX unless to demonstrate how crap Microsoft can be :-).

[tamaranth.livejournal.com]

OK, let's get specific, as I'm not sure I understand what you are getting at.

For each cat/po combination there should be a Max Date and a Min Date -- even if they are the same date, because derived from the same record.

Here is the first record in the 'Min' set, sorted by PO Number:
Cat PONO CreateDate
ES 1110691 29/10/2004

So, the PO Number on the first record in the 'Max' set, sorted by PO number, should be ... which of the following?
a) 1110691
b) something else

[tamaranth.livejournal.com]

the answer is (b)
Cat PONO CreateDate
ES 1111891 29/10/2004

Poor ol' 1110691 ain't there no more.

And on that note I must away ...

[red-cloud.livejournal.com]

I must admit that does look odd at first sight. I would expect the same Cat/PONO combination with an equal or greater CreateDate value. Thinks some more...

[tamaranth.livejournal.com]

I think the word you're looking for is not 'odd', but 'wrong'.

Ah well. Stuff Access. Off to use non-Microsoft software for novelly thing.

[lostcarpark.livejournal.com]

It seems logical that taking the min() aggregate function will return more rows than the max(). If the youngest CreateDate for a Cat and PONO is greater or equal to [start date], the row will be excluded for max().

I can't see why the specific data you mention is producing odd results. Where is [start date] coming from? Could there be a time part to CreateDate that's throwing things off?

[tamaranth.livejournal.com]

You're right; the query should not be selecting on the Min or Max of that field, it should be selecting all records with create date before [start date] and then giving Min or Max of that.

I think the issue is being blurred by the existence of a definite problem in the results.

I think Access has some little-known features like this ...

[lostcarpark.livejournal.com]

Ah, I think I see your problem is. I think any SQL engine would give the same results.

Try this:

SELECT tblPOHistory.Cat, tblPOHistory.PONO, Max(tblPOHistory.CreateDate) AS CreateDate
FROM tblPOHistory
WHERE (((tblPOHistory.CreateDate)<[start date]))
GROUP BY tblPOHistory.Cat, tblPOHistory.PONO

The difference is that the WHERE clause is applied before the GROUP BY. So if a condition is in the WHERE, it will select only records that meet that condition, then group those, but if it's in the HAVING, it will select only groups that meet the condition. This is why you had to apply an aggragate function to CreateDate (which I removed).

[tamaranth.livejournal.com]

great minds ...
Yes, I hadn't intended the Min/Max to apply to the selection -- I just wanted the Min/Max of what was selected!

[red-cloud.livejournal.com]

In Oracle SQL (can't speak for Access) NULL values sort high.