MaxOf / Last Grouping Problem

[Tophan]

Good morning,

I have a database for registering drawings for a construction company. The drawings are registered according to date received and revision letter/number. The query for the register report uses the MaxOf date and Last Rev letter and produces a summary report.

This database has been in use for approx. 3 years without a problem until this week when one of the architects issued revision B and C of the same drawing on the same day. The report is therefore showing both revisions when I need it to only show the last revision - Rev C.

I've included the SQL of the query. Hoping someone can help me with this.

Thanks in advance for your help

SELECT QRYContractsReg.ContractNo, 
TBLDwgRegister.ContractName, TBLDwgRegister.IssuedBy, 
QRYSmryDwgReg.DrawingNo, QRYSmryDwgReg.DwgNo, 
QRYSmryDwgReg.DrawingTitle, QRYSmryDwgReg.MaxOfDateIssued, 
TBLDwgRegisterDtls.Rev, TBLDwgRegisterDtls.RevNotes, 
TBLDwgTypes.DrawingID
FROM TBLDwgTypes INNER JOIN (QRYContractsReg INNER JOIN 
((TBLDwgRegister INNER JOIN QRYSmryDwgReg ON 
(TBLDwgRegister.DrawingNo = QRYSmryDwgReg.DrawingNo) AND 
(TBLDwgRegister.DrawingTitle = QRYSmryDwgReg.DrawingTitle)) INNER 
JOIN TBLDwgRegisterDtls ON (TBLDwgRegister.DrawingNo = 
TBLDwgRegisterDtls.DrawingNo) AND (QRYSmryDwgReg.MaxOfDateIssued =
 TBLDwgRegisterDtls.DateIssued)) ON QRYContractsReg.ContractName =
TBLDwgRegister.ContractName) ON TBLDwgTypes.DrawingID = 
TBLDwgRegisterDtls.DwgType
WHERE (((TBLDwgRegister.Discontinued)=False))
ORDER BY QRYContractsReg.ContractNo, TBLDwgRegister.ContractName, 
TBLDwgRegister.IssuedBy, QRYSmryDwgReg.DrawingNo, 
QRYSmryDwgReg.DrawingNo, TBLDwgRegisterDtls.Rev DESC;

 

[plog]

2 methods:

1. Change your date received field to incorporate a time as well. That way the latest time will be the most current version and your query should continue to work.

2. Use another subquery. I'm assuming one of those queries determines the latest time. You will need another subquery to determine the highest revision for the latest time.

 

[Tophan]

Thanks. I'll give it a shot and let you know

 

[Tophan]

Thank you. Adding the date/time stamp column seems to be working. Took a few more steps as the report was not showing anything without the date/time stamp so no historical data was showing.

This is what I did:

1) Added a calculated field named DateTimeEntered and entered =[DateIssued] in the expression. This allowed the report to show historical info

2) Exported the table to Excel and then reimported, overwriting the existing table and in so doing also cleared the calculated field now making it a Date/Time field

3) Entered a default value of Now() and chose Never for show Date Picker

4) Amended the query to add the new field DateTimeEntered and now the report is showing the correct information.

Thank you so much for your help, your answer came not a moment too soon as I again received two revisions for one drawing while I was fixing the database