Query to Show Latest Date

[Tophan]

Hi.

I have two tables. The first is called Drawing Register and contains the fields Drawing No (primary field) and Drawing Title.

The second table is the Drawing Register Details table which contains the fields Drawing No (joined field - Drawing Register table), Revision, Revision Notes, Date Issued.

I have a query and report which will list all the revisions for each drawing. This is very important and useful. However I want a summary report which will only show the last revision for each drawing.

I copied the original query, turned on the Totals option and under the Date Issued field changed Group by to Max. However it is still returning all results for each drawing instead of only the last issue date for each drawing.

Any suggestions on what I am doing wrong?

 

[CJ_London]

it needs to be within your criteria

so try

SELECT DISTINCT * 
FROM drawingregister inner join drawingregisterdetails ON drawingregister.drawingno=drawingregisterdetails.drawingno
WHERE dateissued =(select max(dateissued) from drawingregisterdetails as tmp where drawingno=drawingregisterdetails.drawingno)

 

[Tophan]

I seem to be doing something wrong. I copied your criteria above and entered the table and fields as I have them named but I am getting a syntax/operand error.




from [Drawing Register new] inner join [Drawing Register - Details New] on [Drawing Register - Details New].[Drawing No]=[Drawing Register - Details New].[Drawing No] where [Date Issued]=select max [Dated Issued] from [Drawing Register - Details New] as tmp where [Drawing No]=[Drawing Register - Details New].[Drawing No]

 

[CJ_London]

You are missing a few bits - highlighted in red

[COLOR=red]SELECT DISTINCT *[/COLOR]
from [Drawing Register new] inner join [Drawing Register - Details New] on [Drawing Register - Details New].[Drawing No]=[Drawing Register - Details New].[Drawing No] 
where [Date Issued]=[COLOR=red]([/COLOR]select max [COLOR=red]([/COLOR][Dated Issued][COLOR=red])[/COLOR] from [Drawing Register - Details New] as tmp where [Drawing No]=[Drawing Register - Details New].[Drawing No][COLOR=red])[/COLOR]

 

[Tophan]

I am very sorry (and a little embarrassed) but I just don't understand.

Do I type the code in the criteria line in the query? And the part "Select Distinct *" - where exactly do i find/do this.

This is the first time I am trying a query like this so I think I need a little more clarification.

Thanks for your patience and assistance

 

[BlueIshDan]

SELECT TOP [date_field] FROM [table_name] ORDER BY [date_field] ASC

 

[BlueIshDan]

I am very sorry (and a little embarrassed) but I just don't understand.

Do I type the code in the criteria line in the query? And the part "Select Distinct *" - where exactly do i find/do this.

This is the first time I am trying a query like this so I think I need a little more clarification.

Thanks for your patience and assistance

Show me what your query is in code brackets and I can help you out

 

[Tophan]

Hi...this is what I typed in the Criteria line in the Date Issued field of the query

where [Date Issued]=(select max ([Dated Issued]) from [Drawing Register - Details New] as tmp where [Drawing No]=[Drawing Register - Details New].[Drawing No])from [Drawing Register new] inner join [Drawing Register - Details New] on [Drawing Register - Details New].[Drawing No]=[Drawing Register - Details New].[Drawing No] )

But I don't understand the Select Distinct *

Thanks for any help you can give.

 

[BlueIshDan]

You're complicating it.
show me your entire query in code brackets please

 

[Tophan]

I have attached the database for your reference. The query is "Copy Of Drawing Register Query - For Report"

I had to remove the code I typed because it wasn't being accepted - syntax error.

 

[axolotl66]

Tophan, you can insert the SELECT DISTINCT * using the SQL view.

 

[Tophan]

Thanks. I guess I need to learn more about sql before attempting this.

 

[CJ_London]

To learn SQL, use the query builder to build your query then use the SQL view to see what it looks like.

To SELECT DISTINCT in the query builder you need to view the query properties and set unique values to yes.

SELECT DISTINCT is the equivalent of using group by where all fields are grouped (no sums, counts etc) but more efficient.

If you copy the code I provided exactly (i.e. use copy and paste) into the SQL view then go back to the query builder you will see how it is constructed.

Do learn to interprete SQL - you will see nearly all posts use it. And as requested by others, learn how to use the code tags when posting code - use the advanced editor, highlight your code and select the # button

 

[Tophan]

Dear CJ London,

I guess you realised I really wasn't understanding SQL language. I just wanted to thank you very much for you efforts and also wanted to let you know I found another way of showing the information I needed.

Using the secondary table (Drawing Register - Details), I slowly built my query using the grouping option and changing the date to Max and other fields to Last. I know there are 227 entries in the database but only 96 drawings. By working backwards I was able to create a summary report showing the lastest update for each of the 96 drawings in the register. Only one field would not show correctly but it is not one of the essential fields (even though it would be nice to see) but for now, until I can learn SQL, I can live with this.

Thanks very much for your patience

 

[CJ_London]

glad you got there in the end!

 

[Tophan]

Hi CJ London,

I just wanted to give you an update. I got it done!!! and all the fields that are in my detailed drawing register are also showing in my summary drawing register and summary is only showing the latest drawing issued.

This is what I did

Step 1 - I created a query using the Drawing Register (DR) table and the Drawing Register Details (DRD) table and selected the following fields
- Drawing No from DRD table
- Drawing Title from DR table
- Date Issued from DRD table
- Discontinued from DR table with criteria set to show only those drawings which are not discontinued

I set the totals to group with Max for Date Issued and then I saved this query - Summary Drawing Register (SDR).

Step 2 - I created a second query and selected my SDR query and added all fields. Then I added tables DR and DRD and joined the fields from SDR to each corresponding field in DR and DRD tables (this step I had not done before which resulted in some of the info being incorrect when I ran the query yesterday)

I then added the descriptive fields I needed from DR and DRD tables and it worked. Only the latest revision for each drawing is showing, the notes I added to describe the latest changes and the drawing type (whether it is a construction, tender, as-built, etc.) are showing correctly.

I copied the SQL code and pasted below for your reference:

SELECT ContractDtlsTBL.[Contract No], [Drawing Register (new)].[Issued By], [Query Setup for "Summary Drawing Register" Query].[Drawing No], [Query Setup for "Summary Drawing Register" Query].[Drawing Title], [Query Setup for "Summary Drawing Register" Query].[MaxOfDate Issued], [Drawing Register - Details (New)].Revision, [Drawing Register - Details (New)].[Revision Notes], [Drawing Register - Details (New)].[Drawing Type]
FROM ContractDtlsTBL INNER JOIN (([Query Setup for "Summary Drawing Register" Query] INNER JOIN [Drawing Register (new)] ON ([Query Setup for "Summary Drawing Register" Query].[Drawing No] = [Drawing Register (new)].[Drawing No]) AND ([Query Setup for "Summary Drawing Register" Query].[Drawing Title] = [Drawing Register (new)].[Drawing Title])) INNER JOIN [Drawing Register - Details (New)] ON ([Drawing Register (new)].[Drawing No] = [Drawing Register - Details (New)].[Drawing No]) AND ([Query Setup for "Summary Drawing Register" Query].[MaxOfDate Issued] = [Drawing Register - Details (New)].[Date Issued])) ON ContractDtlsTBL.[Contract No] = [Drawing Register (new)].[Contract No]
ORDER BY [Drawing Register (new)].[Issued By], [Query Setup for "Summary Drawing Register" Query].[Drawing No];

Also, I found a beginners SQL text and some online tutorials and I am determined to learn it so I can build better databases.

...I am so happy!