MaxOfDate Not Returning the Expected Results (1 Viewer)

[Tophan]

Hi,

I am creating a drawing register database for a construction project and would like to see the latest revisions for each drawing.

Right now I have a drawing with two revisions - revision C1 was issued March 3rd and revision C2 issued March 28th. In my query I changed the group by to Max for the revision number and date issued fields. When I run the report I want to only see revision C2 dated March 28th; however both C1 and C2 are still shown.

Is there a step I am missing?

 

[isladogs]

Using max for both fields will give both records as each revision only has one date issued.
Just use the max date or the max revision (if that is guaranteed to be sequential)

 

[Tophan]

Hi. That didn't work either. Both drawing revisions still showing.

 

[isladogs]

Which of my suggestions did you try?
Suggest you provide some sample data together with the SQL used for your report

 

[Tophan]

I tried both suggestions but no luck. Below is the SQL

SELECT qryDrwgRegister.tblDrwgRegister.DwgID, qryDrwgRegister.ContractName, qryDrwgRegister.IssuedBy, qryDrwgRegister.DwgNo, qryDrwgRegister.DwgTitle, qryDrwgRegister.tblDrwgRegisterDtls.DwgID, Max(qryDrwgRegister.Rev) AS MaxOfRev, qryDrwgRegister.RevNotes, qryDrwgRegister.DateIssued, qryDrwgRegister.DrawingID, qryDrwgRegister.Discontinued
FROM qryDrwgRegister
GROUP BY qryDrwgRegister.tblDrwgRegister.DwgID, qryDrwgRegister.ContractName, qryDrwgRegister.IssuedBy, qryDrwgRegister.DwgNo, qryDrwgRegister.DwgTitle, qryDrwgRegister.tblDrwgRegisterDtls.DwgID, qryDrwgRegister.RevNotes, qryDrwgRegister.DateIssued, qryDrwgRegister.DrawingID, qryDrwgRegister.Discontinued;

 

[Tophan]

At present there is no sensitive data in my DB - just entering test info to test the queries. Should I upload the database?

 

[CarlettoFed]

Maybe it's better.

 

[Tophan]

The query I'm having trouble with is qryDrwgRegisterSummary

 

[isladogs]

Actually you have 3 revisions C1, C2 & C6
You are returning all the records as each has different values for other fields such as DwgTitle, RevNotes & DrawingID
There is no C2 record issued on 28/03/2023

Please clarify: How many records do you want to be returned? Which records (based on the PK fields)?

 

[Tophan]

Actually you have 3 revisions C1, C2 & C6
You are returning all the records as each has different values for other fields such as DwgTitle, RevNotes & DrawingID
How many records do you want to be returned? Which records (based on the PK fields)?

Revision C6 is for a different drawing. Right now Drawing Number C330 is the one with a revision C1 & C2. I need to see all drawings but only the last revision for each. So for drawing C330 i want to only see revision C2 listed

 

[Tophan]

The primary key is just an autonumber. I didn't want to set the drawing number as the primary key as different design consultants tend to use similar numbering systems

 

[GPGeorge]

PMFJI:

Here is the Group BY clause in your query:
GROUP BY qrydrwgregister.tbldrwgregister.dwgid,
qrydrwgregister.contractname,
qrydrwgregister.issuedby,
qrydrwgregister.dwgno,
qrydrwgregister.dwgtitle,
qrydrwgregister.tbldrwgregisterdtls.dwgid,
qrydrwgregister.revnotes,
qrydrwgregister.dateissued,
qrydrwgregister.drawingid,
qrydrwgregister.discontinued;

That means each and every one of those fields, from dwgid through discontinued, has an impact on the records returned. As Colin pointed out, if any of those fields is unique, for example if it's a primary key (I'm looking a drawingID as the culprit), it will cause one record to be returned for each of those unique values. That's not what you want.

Eliminate all of the fields except the two you need to group on. Use date issued for one of them, since you want the max of the dates. Use the drawing number for the other; that's the one you want to group on, I think.

With that as a subquery, you can join it back to the main table to get the remaining fields. The join will be on the date and the drawing number.

 

[XPS35]

I do no understand your problem / data. There are 16 unique drawings and each drawing has ONE stored revision. So using MAX will have no effect.

 

[Tophan]

I do no understand your problem / data. There are 16 unique drawings and each drawing has ONE stored revision. So using MAX will have no effect.

Actually DwgNo C330 has 2 revisions - C1 & C2

 

[isladogs]

I agree with all the previous comments. You need to think through exactly what you are trying to do. With the information provided so far, its impossible to provide a generic solution

 

[XPS35]

Actually DwgNo C330 has 2 revisions - C1 & C2

Sorry, I did not see that.

The problem is you are also grouping by RevNotes. The values are different for the reviews, so you get the max for "Construction issue" and the max for "Section D revised".

 

[CarlettoFed]

You need to create two queries:

1. qryMaxRev with the following predicate:

   SELECT tblDrwgRegisterDtls.DwgID, Max(tblDrwgRegisterDtls.DateIssued) AS MaxDiDateIssuedFROM tblDrwgRegisterDtls
   GROUP BY tblDrwgRegisterDtls.DwgID;

2. qryMaxRevData with the following predicate:

   SELECT tblDrwgRegister.DwgID, tblDrwgRegister.ContractName, tblDrwgRegister.IssuedBy, tblDrwgRegister.DwgNo, tblDrwgRegister.DwgTitle, tblDrwgRegister.Discontinued, tblDrwgRegisterDtls.Rev, tblDrwgRegisterDtls.RevNotes, tblDrwgRegisterDtls.DateIssued, TBLDwgTypes.DrawingIDFROM TBLDwgTypes INNER JOIN (tblDrwgRegister INNER JOIN (qryMaxRev INNER JOIN tblDrwgRegisterDtls ON (qryMaxRev.DwgID = tblDrwgRegisterDtls.DwgID) AND (qryMaxRev.MaxDiDateIssued = tblDrwgRegisterDtls.DateIssued)) ON tblDrwgRegister.DwgID = tblDrwgRegisterDtls.DwgID) ON TBLDwgTypes.DwgTypeID = tblDrwgRegisterDtls.DwgType;

 

[Tophan]

Ok. I'll try your suggestions and let you know how I get through

 

[Pat Hartman]

1. Every table needs a primary key. tblDrwgRegisterDtls does not have one. Use an autonumber. This will help you to get the right Rev.
2. When you make the Rev a text field, you are going to have a hell of a time sorting the field so you can identify which Rev is the newest rev since C2 will sort AFTER C10.
3. Get rid of all the multi-value fields. This will not help you and I'm not sure what they are used for. Do you really have a contract that applies to multiple clients?
4. You need to describe the purpose of this application. Are you just trying to track revision history or do you need to know where a rev is in the approval process. ie. who has the drawing at this point and why?

I have a very sophisticated drawing log database that I cannot give to you but I would be willing to demo it so you can see one in operation.

 

[Tophan]

Thank you all for your help.

@CarlettoFed - i followed your query suggestions and the summary now works Thank you
@Pat Hartman - I will definitely add an autonumber to the tblDrwgRegisterDetails. I forgot that sorting will result in C2 coming after C10. Unfortunately I cannot change the revisions to a number only as the letter prefix usually defines the type of document. Also, I have had instances where a project had more than one client - two corporations with 50/50 interests but with different management rolls so I need to keep the multi-value field.

Thank you all so much for your assistance; I really do appreciate everyone's help. Your forums and contributors always set me right.