'Group By' problem

T

Tcs

I have a local table of twelve (12) records. These are utility rates, which include the date they
became effective. Two (2) of these rates (flagged) have been superceded by newer rates. I'm trying
to 'drop' the old rate record so I only have the newer (current) record and thus the correct charge.
Here's the code that produced the output below:

==========
SELECT
tblSanLoc_SCRATCH.Serv,
tblSanLoc_SCRATCH.Class,
tblSanLoc_SCRATCH.[Rate Code],
tblSanLoc_SCRATCH.[Rate Desc],
tblSanLoc_SCRATCH.[Rate Chg],
Max(tblSanLoc_SCRATCH.[Eff Date]) AS [MaxOfEff Date]

FROM
tblSanLoc_SCRATCH

GROUP BY
tblSanLoc_SCRATCH.Serv,
tblSanLoc_SCRATCH.Class,
tblSanLoc_SCRATCH.[Rate Code],
tblSanLoc_SCRATCH.[Rate Desc],
tblSanLoc_SCRATCH.[Rate Chg];
==========
Serv Class Rate Rate Rate MaxOfEff
Code Desc Chg Date
---- ----- ---- -------------------- ---- ----------
GA CH RGA GARBAGE 48 19990101
GA CM C1XW CANS 1 PICKUP PER WK 24 19990101
GA CM C2XW CANS 2 PICKUP PER WK 48 19990101
GA CM C3XW CANS 3 PICKUP PER WK 72 19990101
GA CM C4XW CANS 4 PICKUP PER WK 96 19990101
GA CM C5XW CANS 5 PICKUP PER WK 120 19990101
GA CM CGA GARBAGE 12 19990101
GA CM DUMP DUMPSTER PICKUP CHGS 10 19990101 <-- old
GA CM DUMP DUMPSTER PICKUP CHGS 19 20020801
GA CM RGA GARBAGE 18 20021231
GA RE RGA GARBAGE 15 19990101 <-- old
GA RE RGA GARBAGE 18 20020801
==========

Initially I tried to retrieve my backend data and write directly to my "good" local table (withOUT
the records I don't want). Then I changed to write an intermediate "scratch" table, thinking that I
could easily drop the unwanted records when reading my scratch table and writing my good table.

It seems the ONLY way I can get Access (2k3) to drop the rows that I *don't* want, is to leave off
the "Rate Chg" column. I've tried Max & Last. I've tried moving the "Rate Chg" column to the end,
*after* the date. What am I doing wrong, not doing....WHATEVER? It seems to me that this should be
so SIMPLE, yet it's turning out to be anything BUT. Your assistance wuld be most welcome.

Thanks in advance,

Tom
 
D

dysgraphia

I have a table with fields:
MDate PatientName TestA TestB

I would like to run a query that returns, say, the
5 most recent TestA and TestB results for all the PatientNames.
I think I need to do a sub query and hence write the SQL
rather than use the design grid. I have checked back in the newsgroup
and googled and found plenty of refs to sub queries but cannot find
a solution. Any suggestions appreciated!
....dysgraphia
 
M

Marshall Barton

Tcs said:
I have a local table of twelve (12) records. These are utility rates, which include the date they
became effective. Two (2) of these rates (flagged) have been superceded by newer rates. I'm trying
to 'drop' the old rate record so I only have the newer (current) record and thus the correct charge.
Here's the code that produced the output below:

==========
SELECT
tblSanLoc_SCRATCH.Serv,
tblSanLoc_SCRATCH.Class,
tblSanLoc_SCRATCH.[Rate Code],
tblSanLoc_SCRATCH.[Rate Desc],
tblSanLoc_SCRATCH.[Rate Chg],
Max(tblSanLoc_SCRATCH.[Eff Date]) AS [MaxOfEff Date]

FROM
tblSanLoc_SCRATCH

GROUP BY
tblSanLoc_SCRATCH.Serv,
tblSanLoc_SCRATCH.Class,
tblSanLoc_SCRATCH.[Rate Code],
tblSanLoc_SCRATCH.[Rate Desc],
tblSanLoc_SCRATCH.[Rate Chg];
==========
Serv Class Rate Rate Rate MaxOfEff
Code Desc Chg Date
---- ----- ---- -------------------- ---- ----------
GA CH RGA GARBAGE 48 19990101
GA CM C1XW CANS 1 PICKUP PER WK 24 19990101
GA CM C2XW CANS 2 PICKUP PER WK 48 19990101
GA CM C3XW CANS 3 PICKUP PER WK 72 19990101
GA CM C4XW CANS 4 PICKUP PER WK 96 19990101
GA CM C5XW CANS 5 PICKUP PER WK 120 19990101
GA CM CGA GARBAGE 12 19990101
GA CM DUMP DUMPSTER PICKUP CHGS 10 19990101 <-- old
GA CM DUMP DUMPSTER PICKUP CHGS 19 20020801
GA CM RGA GARBAGE 18 20021231
GA RE RGA GARBAGE 15 19990101 <-- old
GA RE RGA GARBAGE 18 20020801
==========

Initially I tried to retrieve my backend data and write directly to my "good" local table (withOUT
the records I don't want). Then I changed to write an intermediate "scratch" table, thinking that I
could easily drop the unwanted records when reading my scratch table and writing my good table.

It seems the ONLY way I can get Access (2k3) to drop the rows that I *don't* want, is to leave off
the "Rate Chg" column. I've tried Max & Last. I've tried moving the "Rate Chg" column to the end,
*after* the date. What am I doing wrong, not doing....WHATEVER? It seems to me that this should be
so SIMPLE, yet it's turning out to be anything BUT. Your assistance wuld be most welcome.
Click to expand...


No need for a scratch table.

SELECT
T.Serv,
T.Class,
T.[Rate Code],
T.[Rate Desc],
T.[Rate Chg],
T.[Eff Date]

FROM
tblSanLoc As T

WHERE [Eff Date] = (
SELECT Max(X.[Eff Date])
FROM tblSanLoc As X
WHERE X.[Rate Code] = T.[Rate Code])

I'm not sure about the field in
WHERE X.[Rate Code] = T.[Rate Code]
but you should be able to fix that if needed.
 
T

Tcs

No need for a scratch table.
Click to expand...
Thanks, but...it's not working. It's picking up all 12 records. Here's my code:

----------
SELECT
T.Serv,
T.Class,
T.[Rate Code],
T.[Rate Desc],
T.[Rate Chg],
T.[Eff Date]

INTO
tblSanLoc_UT710AP IN 'D:\Projects\AS400\UtilityBilling\As400_Finance_data.mdb'

FROM
tblSanLoc_UT710AP_SCRATCH AS T

WHERE (((T.[Eff Date])=(
SELECT Max(T.[Eff Date])
FROM tblSanLoc_UT710AP_SCRATCH As X
WHERE ((X.[Class] = T.[Class]) and (X.[Rate Code] = T.[Rate Code])))));
----------

(Access doesn't like formatted code with regards to the nested select, does it? It won't "fix" it,
like it does otherwise. Annoying, but workable.)

I'm trying to test the nested select, but I'm not having any success.

Also, as it turns out, I *need* the scratch table. I need it because I have to build the date I'm
using to look at. In this particular instance, the date is in three (3) separate fields. (In some
places it's stored in four (4) fields.) So first I have to build it. I'm doing so like this:

IIf(([UTREFY]<50),(((2000+[UTREFY])*10000)+([UTREFM]*100)+[UTREFD]),(((1900+[UTREFY])*10000)+([UTREFM]*100)+[UTREFD]))
AS [Eff Date]

Thanks,

Tom
 
M

Marshall Barton

Tcs said:
No need for a scratch table.
Click to expand...
Thanks, but...it's not working. It's picking up all 12 records. Here's my code:

----------
SELECT
T.Serv,
T.Class,
T.[Rate Code],
T.[Rate Desc],
T.[Rate Chg],
T.[Eff Date]

INTO
tblSanLoc_UT710AP IN 'D:\Projects\AS400\UtilityBilling\As400_Finance_data.mdb'

FROM
tblSanLoc_UT710AP_SCRATCH AS T

WHERE (((T.[Eff Date])=(
SELECT Max(T.[Eff Date])
FROM tblSanLoc_UT710AP_SCRATCH As X
WHERE ((X.[Class] = T.[Class]) and (X.[Rate Code] = T.[Rate Code])))));
----------
I'm trying to test the nested select, but I'm not having any success.

Also, as it turns out, I *need* the scratch table. I need it because I have to build the date I'm
using to look at. In this particular instance, the date is in three (3) separate fields. (In some
places it's stored in four (4) fields.) So first I have to build it. I'm doing so like this:

IIf(([UTREFY]<50),(((2000+[UTREFY])*10000)+([UTREFM]*100)+[UTREFD]),(((1900+[UTREFY])*10000)+([UTREFM]*100)+[UTREFD]))
AS [Eff Date]
Click to expand...


Well that's a different reason for using a scratch table. I
would still try to find a way to avoid that, but this is not
the question at hand.

You have a T where you want an X in the subquery:
SELECT Max(X.[Eff Date])
 
T

Tcs

Well that's a different reason for using a scratch table. I
would still try to find a way to avoid that, but this is not
the question at hand.
Click to expand...

I'd *like* to avoid it, but I don't know how. Access won't go for my trying to build my date at the
same time I'm looking for the max value. I tried.
You have a T where you want an X in the subquery:
SELECT Max(X.[Eff Date])
Click to expand...

Excellent. That did it! This is...GREAT.

Thanks a lot,

Tom
 
M

Marshall Barton

Tcs said:
Well that's a different reason for using a scratch table. I
would still try to find a way to avoid that, but this is not
the question at hand.
Click to expand...

I'd *like* to avoid it, but I don't know how. Access won't go for my trying to build my date at the
same time I'm looking for the max value. I tried.
You have a T where you want an X in the subquery:
SELECT Max(X.[Eff Date])
Click to expand...

Excellent. That did it! This is...GREAT.
Click to expand...


Progress! This is good ;-)

The Max should work OK with your expression, even if it
makes the SQL a lot longer. Just replace [Eff Date] with
the entire expression everywhere in a copy of our working
query and change the scratch table name to the name of the
original table.

At least you can use a query to calculate the [Eff Date]
value with out saving it to a temp table. Just name the
query tblSanLoc_UT710AP_SCRATCH and the query we've been
working on should work just as well as with the scratch
table (depending on the size of the table, it may be slower
though).
 
Top