Max Function

[kbreiss]

I have a table called tbl_Weekly_Update. The following is what is in that table.

PROJ_ID-----UPDATE_DATE-------WKLY_UPDATE
---87------------3/24/2003---------another test
---87------------3/25/2003---------this is a test
---90------------3/25/2003---------one more

I would like a query that grabs the latest WKLY_UPDATE for each PROJ_ID. So for example the only thing I would like returned is
PROJ_ID UPDATE_DATE WKLY_UPDATE
---87------------3/25/2003---------this is a test
---90------------3/25/2003---------one more

Hopefully I have explained this clearly and someone can help me.
Thanks in advance,

Kacy
________
Ferrari 333 Sp

 

[jfgambit]

Create a new query with the three fields, utilizing the Group By function button change the Grouped By of the UPDATE_DATE field to Max and the WEEKLY_UPDATE to Last.

Should get you what you are looking for.

 

[kbreiss]

thank you for responding......
When I run this query it is given me the following back...

PROJ_ID--- UPDATE_DATE--- WEEKLY_UPDATE
---87 ------ 3/25/2003 ------ another test
---90 ------ 3/28/2003 ------ hello
---91 ------ 3/15/2003 ------ 91 test

Notice in the previous email that WEEKLY_UPDATE for 3/25/2003 should be "this is a test" It's getting the Max dates for each PROJ_ID, but it's not keeping that correct WEEKLY_UPDATE for that particular UPDATE_DATE.

Any suggestions,
Thanks,
Kacy
________
Lovely Wendie99

 

[jfgambit]

Kacy:

Can you post the SQL of the query?

 

[KevinM]

You are going to have to decide which 'WEEKLY_UPDATE ' you want to see when you have duplicate dates.

You can group it by First, Last, Min or Max.

 

[Pat Hartman]

You have to do this in two parts. One part to find the Max date for a project and the other part to obtain the related information. You can do this with a sub query or with nested queries. I will include an example of nested queries because they are more efficient and easier for most people to understand and test.

Query1:
Select PROJ_ID, Max(UPDATE_DATE) As MaxUPDATE_DATE
From YourTable;

Query2:
Select q.PROJ_ID, q.MaxUPDATE_DATE, t.WEEKLY_UPDATE
From Query1 as q Inner Join YourTable as t ON q.PROJ_ID = t.PROJ_ID;

Bear in mind that if there is more than one entry for the combination of PROJ_ID and UPDATE_DATE, all of them will be returned.

 

[kbreiss]

Ok, here is the latest of queries that I've tried....

SELECT A.PROJ_ID, Max(A.UPDATE_DATE) AS MaxOfUPDATE_DATE, Last(A.WKLY_UPDATE) AS LastOfWKLY_UPDATE
FROM tbl_Weekly_Update AS A
GROUP BY A.PROJ_ID
ORDER BY Max(A.UPDATE_DATE) DESC;

When I run this query it's not giving me the correct WKLY_UPDATE corresponding to the PROJ_ID AND UPDATE_DATE.

Pat mentioned a nested query.....can you help me with that. I have no idea on how to do that.

Thanks in advance,

Kacy
________
Pissing mouth

 

[jfgambit]

Kacy:

Post a sample of your db...if it is too big send me a message and I'll give you an account to send it too.

 

[kbreiss]

I grabbed just the table and query that I'm testing with...thanks.

Kacy
________
Public Vids

 

[kbreiss]

Here I have tried a nested query, but am having a hard time. I've never done one before.

Select A.PROJ_ID, Max(A.UPDATE_DATE) AS MaxOfUPDATE_DATE
FROM tbl_Weekly_Update AS A
GROUP BY A.PROJ_ID

(Select B.PROJ_ID, B.MaxUPDATE_DATE, A.WEEKLY_UPDATE
From Query1 as B Inner Join tbl_Weekly_Update as A ON B.PROJ_ID = A.PROJ_ID);

Does anyone see what I'm doing wrong?

Thanks in advance,

Kacy
________
Depakote Lawsuit Settlements

 

[jfgambit]

Kacy:
Here is what I did...

Create a query and add the following to the SQL:

SELECT tbl_Weekly_Update.PROJ_ID, tbl_Weekly_Update.UPDATE_DATE, tbl_Weekly_Update.WKLY_UPDATE
FROM tbl_Weekly_Update
ORDER BY tbl_Weekly_Update.PROJ_ID, tbl_Weekly_Update.UPDATE_DATE;

Save the Query as qrySort


Change your Query SQL to be:

SELECT qrySort.PROJ_ID, Last(qrySort.UPDATE_DATE) AS LastOfUPDATE_DATE, Last(qrySort.WKLY_UPDATE) AS LastOfWKLY_UPDATE
FROM qrySort
GROUP BY qrySort.PROJ_ID;


Save the query and run....you should get what you need

HTH

 

[kbreiss]

Thanks, I will try that. I have another question for you. If I make this two queries can I have the second of the two as my recordsource for my form. I guess what I'm asking if I use the second query for my recordsource will it query the first one automatically?

Thanks,

Kacy
________
Extreme q

 

[jfgambit]

Yes, that is correct.

 

[kbreiss]

I have created the two queries. It's still not returning back in the right order. For PROJ_ID 87 it's returning the UPDATE_DATE info for 3/24/2003 instead of the 3/25/2003 info for that PROJ_ID.

Any idea,.....sorry,

Kacy
________
Weed

 

[jfgambit]

Kacy:
I just checked the database I have, which I have attached, and everything looks fine. I even changed the infomation in the Text field to see if it was pulling the right data, if you run the Query1 you will see what I mean.

Let me know

 

[Pat Hartman]

Did you create the two queries that I suggested? If you did, Post your SQL.

 

[kbreiss]

First of all, I apologize for emailing you directly in response to the nested queries. Here's the two queries I have created. They are linked on PROJ_ID so it's including all of them as where I just want the most recent update.

Note:
I should say that PROJ_ID is not unique. I've created a table to store all of the weekly updates for a particular project. I have the PROJ_ID and the UPDATE_DATE together as the primary key

Query 1
SELECT PROJ_ID, Max(UPDATE_DATE) AS MaxUPDATE_DATE
FROM tbl_Weekly_Update
GROUP BY PROJ_ID;

Query 2
SELECT q.PROJ_ID, q.MaxUPDATE_DATE, t.WKLY_UPDATE
FROM PATS1 AS q INNER JOIN tbl_Weekly_Update AS t ON q.PROJ_ID = t.PROJ_ID;

Any assistance would be greatly appreciated!

Kacy
________
LovelyWendie99

 

[jfgambit]

Kacy:

Did you see the attachment on the previous post??

 

[kbreiss]

jfgambit,
I didn't see the attachment on the previous post. I tried it and it works great. Thanks a ton. Before I saw the attachment I was working on the following to the query that I had. Out of curiousity....does this work the same?

Query 1:
SELECT PROJ_ID, Max(UPDATE_DATE) AS MaxUPDATE_DATE
FROM tbl_Weekly_Update
GROUP BY PROJ_ID;


QUERY 2
SELECT q.PROJ_ID, q.MaxUPDATE_DATE, t.WKLY_UPDATE
FROM PATS1 AS q INNER JOIN tbl_Weekly_Update AS t ON (q.MaxUPDATE_DATE=T.UPDATE_DATE) AND (q.PROJ_ID = t.PROJ_ID);

In the second query after it links.....
PATS1 AS q INNER JOIN tbl_Weekly_Update AS t ON q.PROJ_ID = t.PROJ_ID

I added:
AND(q.MaxUPDATE_DATE=T.UPDATE_DATE)

Please let me know.
Thanks for figuring this out for me.

Kacy
________
Ipad Accessories

 

[jfgambit]

Your first query is taking the Max of the Fields, mine is Ordering them you the Project Id then by the Date. So they will not work the same.

To ensure that you always get the correct Date, have you also considered changing the Format of the Date to be a General Date, that way instead of having 3 records with 3/25/03 you would get 3/25/2003 11:25:52, 3/25/2003 12:23:09, etc.etc.

Then change the Default Value to Now()

HTH