Sql problem

Old Pedant · December 28th, 2008, 05:35 PM

Okay...first of all, create a STORED QUERY in Access that looks like this:

Code:

SELECT a, b, c, sum(d) AS total, max(h) AS maxDate
FROM Table1
GROUP BY a, b, c

You'll recognize this as almost the same query you said worked for you, before.

But this time use Access to create the query and then give it a name you will remember. I named it
SumsWithMaxDate

And *NOW* you can use *THIS* query to get the results you are after, I think:

Code:

SELECT SumsWithMaxDate.a, SumsWithMaxDate.b, SumsWithMaxDate.c, SumsWithMaxDate.total, Table1.f, Table1.g
FROM Table1, SumsWithMaxDate
WHERE Table1.h = SumsWithMaxDate.maxDate 
AND Table1.c = SumsWithMaxDate.c
AND Table1.b = SumsWithMaxDate.b
AND Table1.a = SumsWithMaxDate.a

See if that works for you.

Old Pedant · December 28th, 2008, 05:39 PM

I thought this query might be too complex for poor little Access, but it *did* work all in one query, after all. I got the identical results as prior post by doing this all in one:

Code:

SELECT SumsWithMaxDate.a, SumsWithMaxDate.b, SumsWithMaxDate.c, SumsWithMaxDate.total, Table1.f, Table1.g
FROM Table1, ( 
    SELECT a, b, c, sum(d) AS total, max(h) AS maxDate
    FROM Table1
    GROUP BY a, b, c
    ) AS  SumsWithMaxDate
WHERE Table1.h = SumsWithMaxDate.maxDate 
AND Table1.c = SumsWithMaxDate.c
AND Table1.b = SumsWithMaxDate.b
AND Table1.a = SumsWithMaxDate.a

Naturally, you can add a final ORDER BY clause to that (don't need one for the inner SELECT...would be useless).

Mangore · December 31st, 2008, 05:22 AM

I tried this:

Quote:

SELECT SumsWithMaxDate.a, SumsWithMaxDate.b, SumsWithMaxDate.c, SumsWithMaxDate.total, Table1.f, Table1.g
FROM Table1, SumsWithMaxDate
WHERE Table1.h = SumsWithMaxDate.maxDate
AND Table1.c = SumsWithMaxDate.c
AND Table1.b = SumsWithMaxDate.b
AND Table1.a = SumsWithMaxDate.a
The Microsoft Jet database engine cannot find the input table or query 'SumsWithMaxDate'. Make sure it exists and that its name is spelled correctly.

but it showed me this error:
The Microsoft Jet database engine cannot find the input table or query 'SumsWithMaxDate'. Make sure it exists and that its name is spelled correctly.

Then I used this

Quote:

SELECT SumsWithMaxDate.a, SumsWithMaxDate.b, SumsWithMaxDate.c, SumsWithMaxDate.total, Table1.f, Table1.g
FROM Table1, (
SELECT a, b, c, sum(d) AS total, max(h) AS maxDate
FROM Table1
GROUP BY a, b, c
) AS SumsWithMaxDate
WHERE Table1.h = SumsWithMaxDate.maxDate
AND Table1.c = SumsWithMaxDate.c
AND Table1.b = SumsWithMaxDate.b
AND Table1.a = SumsWithMaxDate.a

abd it showed me this error:
No value given for one or more required parameters.

By the way the select or chosing as condition by Max(H) is only for F and G columns.

Old Pedant · December 31st, 2008, 07:44 PM

I really *DID* creaqte that table in an Access DB on my machine and I really *DID* run those queries. So I have no idea why they didn't work for you.

Did you try them *IN ACCESS*??? WITHOUT ASP/ADO/VBS getting in the way???

And for the first case, *did* you create the STORED QUERY as I noted? I have to assume that you didn't or you couldn't have gotten that error.

> By the way the select or chosing as condition by Max(H) is only for F and G columns.

Yes, I know. You want to see my actual table and actual results???

Mangore · January 1st, 2009, 08:52 AM

I'm so sorry I was confusing. I didn't creat TAble called SumsWithMaxDate
do you mean I have to Creat table called SumsWithMaxDate and creat in it these columns a,b,c,d,f,g,h...

Old Pedant · January 1st, 2009, 11:09 PM

Not a TABLE. A QUERY.

You start up Access.
You open up your ".mdb" file.
In the main interior panel/window, click on QUERIES.
In the QUERIES window, click on CREATE QUERY IN DESIGN VIEW.
When the SHOW TABLE popup appears, just close it.
Click on the VIEW menu (top of the main window) and then on the SQL VIEW menu item.

Now type in the SQL query I gave you into that text window.

Now click the close box (the X at the top right) of that SQL view text window.

Say "YES" to "Do you want to save..."

When asked for the name of the query, use the name I gave you (or one of your choice).

Presto. You have created a STORED QUERY in Access.

For the most part, you can treat a STORED QUERY just the same as a table!!!

TRY IT!