Find MAX Date for each Part ID record 1

[rwn]

I have the following code that displays all the part numbers recieved. What I need to do is only see that last transaction date for each Part Number, for I'm trying to see what the last POCost was. Thank you for any advice.

SELECT TransactionDetail.POCost,
TransactionHeader.TransactionDate,
TransactionHeader.TransactionType,
PartMaster.PartNumber,
PartMaster.ISC
FROM (TransactionHeader
INNER JOIN TransactionDetail ON TransactionHeader.TransactionGroup = TransactionDetail.TransactionGroup)
INNER JOIN PartMaster ON TransactionDetail.PartNumber = PartMaster.PartNumber
WHERE (((TransactionHeader.TransactionType)="POR"));

 

[dhookom]

Create a group by query with SQL of:

SELECT Max(TransactionHeader.TransactionDate) As MaxDate,
    PartMaster.PartNumber
FROM (TransactionHeader
    INNER JOIN TransactionDetail ON TransactionHeader.TransactionGroup = TransactionDetail.TransactionGroup)
    INNER JOIN PartMaster ON TransactionDetail.PartNumber = PartMaster.PartNumber
WHERE (((TransactionHeader.TransactionType)="POR"))
GROUP BY PartMaster.PartNumber;

Then add this query to your above query and join both the PartNumber and MaxDate to TransactionDate.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[rwn]

Thank you for the kind reply.

Being a newbee in this area, I'm truly not sure how to add this and how the JOIN query should be written.

 

[dhookom]

Did you create the new totals query and save it with a name like qgrpPartNumLastDate? Then open your initial query and add the qgrpPartNumLastDate query. Create joins between the PartNumber fields and TransactionDate to MaxDate. This should result in displaying records from only the last transaction date.

Do you ever have a partnumber with two transactions on the same date?

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[rwn]

Yes, I was able to Join both queries. But the TransactionDetail.Cost field doesn't show the correct values. I need to see the last TransactionDetail.TransactionDate and the last TransactionDetail.Cost associated to that date for the PartMaster.Partnumber.

Yes, I do have part numbers with transactions on the same date.

 

[rwn]

Got it to work. Thank You!

Is there a way to get the same results with out having to make another query and JOINing the two queries? Thus, just having one query to do it all.

 

[r937]

yes, there is a way

please show your two queries

r937.com | rudy.ca

 

[rwn]

Here they areThank YOU!!!)
Query 1)
SELECT TransactionDetail.POCost,
TransactionHeader.TransactionDate,
TransactionHeader.TransactionType,
PartMaster.PartNumber,
PartMaster.ISC
FROM (TransactionHeader
INNER JOIN TransactionDetail ON TransactionHeader.TransactionGroup = TransactionDetail.TransactionGroup)
INNER JOIN PartMaster ON TransactionDetail.PartNumber = PartMaster.PartNumber
WHERE (((TransactionHeader.TransactionType)="POR"));

Query 2)

SELECT Max(TransactionHeader.TransactionDate) As MaxDate,
PartMaster.PartNumber
FROM (TransactionHeader
INNER JOIN TransactionDetail ON TransactionHeader.TransactionGroup = TransactionDetail.TransactionGroup)
INNER JOIN PartMaster ON TransactionDetail.PartNumber = PartMaster.PartNumber
WHERE (((TransactionHeader.TransactionType)="POR"))
GROUP BY PartMaster.PartNumber;

 

[r937]

SELECT TransactionDetail.POCost,
TransactionHeader.TransactionDate,
TransactionHeader.TransactionType,
PartMaster.PartNumber,
PartMaster.ISC
FROM [red]([/red](TransactionHeader
INNER JOIN TransactionDetail ON TransactionHeader.TransactionGroup = TransactionDetail.TransactionGroup)
INNER JOIN PartMaster ON TransactionDetail.PartNumber = PartMaster.PartNumber[red])
INNER JOIN (
SELECT Max(TransactionHeader.TransactionDate) As MaxDate,
PartMaster.PartNumber
FROM (TransactionHeader
INNER JOIN TransactionDetail ON TransactionHeader.TransactionGroup = TransactionDetail.TransactionGroup)
INNER JOIN PartMaster ON TransactionDetail.PartNumber = PartMaster.PartNumber
WHERE (((TransactionHeader.TransactionType)="POR"))
GROUP BY PartMaster.PartNumber
) AS fred
ON fred.PartNumber = PartMaster.PartNumber
AND fred.MaxDate = TransactionHeader.TransactionDate[/red]
WHERE (((TransactionHeader.TransactionType)="POR"));




r937.com | rudy.ca

 

[rwn]

Thank you very much. Is the 'fred' table a temp table so when the query runs in an actual SQL query it doesn't look for it? or am I in left field how that works?

 

[lespaul]

it's an alias..in this particular case it's a required object in the statement, but you can use it for non-required objects in order to make typing/reading the query easier.

For example,

SELECT VeryLongTableName1.Field1, VeryLongTableName2.Field2, VeryLongTableName1.Field3
FROM VeryLongTableName1
INNER JOIN VeryLongTableName2 ON VeryLongTableName1.Field1 = VeryLongTableName2.Field1

or by using an alias:

SELECT A.Field1, B.Field2, A.Field3
FROM VeryLongTableName1 As [b]A[/b]
INNER JOIN VeryLongTableName2 As [b]B[/b] ON A.Field1 = B.Field1

However in the context that Rudy used it above as an alias for a SELECT clause as the JOIN condition you HAVE to have an alias to reference the SELECT clause in the JOIN.

HTH


Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[rwn]

This query works wonderfull!

Is there a way to have the last TransactionDetail.POCost amount value update the PartMaster.defaultPOCost field to the same value?