only display one date record in a query

[orshims]

hi

can someone please help me.
i am trying to create a query where only the last date record is displayed and i can choose multiple criteria to display
eg i have a table with a field name of quote number and quoted date what i want to do is select several quote numbers and only display the last date entry for that quote number. i have been pulling my hair out

thanks

simon

 

[rainman89]

look at the max function for your query... this should give you the latest date

 

[orshims]

hi
i have tried this i have the query as a totals query then put the group by to max however i dont get the answer i want all the records are still displayed

 

[rainman89]

can you post the sql of your query?

 

[orshims]

hi
this is the sql view

SELECT [Quote max and min value and order value].[Quote Number], Max([Quote max and min value and order value].[Quoted Date]) AS [MaxOfQuoted Date], [Quote max and min value and order value].[Date Ordered], [Quote max and min value and order value].[Drawing No/Part No], [Quote max and min value and order value].Issue, [Quote max and min value and order value].[Cust Del Date]
FROM [Quote max and min value and order value]
GROUP BY [Quote max and min value and order value].[Quote Number], [Quote max and min value and order value].[Date Ordered], [Quote max and min value and order value].[Drawing No/Part No], [Quote max and min value and order value].Issue, [Quote max and min value and order value].[Cust Del Date]
HAVING ((([Quote max and min value and order value].[Quote Number]) Like [enter quote no]));

i also have a parameter in the quote number field because i also want to choose multiple quote numbers but havent worked out how to do that yet!!!!!!

 

[orshims]

hi this is the sql view
i also have a parameter in the quote number field to choose a quote number however i really want to choose multiple quote numbers and only show the last record in that quote
please help!!!!!!!!!!!!!!!!!


SELECT [Quote max and min value and order value].[Quote Number], Max([Quote max and min value and order value].[Quoted Date]) AS [MaxOfQuoted Date], [Quote max and min value and order value].[Date Ordered], [Quote max and min value and order value].[Drawing No/Part No], [Quote max and min value and order value].Issue, [Quote max and min value and order value].[Cust Del Date]
FROM [Quote max and min value and order value]
GROUP BY [Quote max and min value and order value].[Quote Number], [Quote max and min value and order value].[Date Ordered], [Quote max and min value and order value].[Drawing No/Part No], [Quote max and min value and order value].Issue, [Quote max and min value and order value].[Cust Del Date]
HAVING ((([Quote max and min value and order value].[Quote Number]) Like [enter quote no]));

 

[orshims]

hi this is the sql view
i also have a parameter value in the quote number field this is to choose a specific quote however i want to choose multiple quote numbers and only display the last quoted date in that quote number if this makes sense
please help!!!!!!!!!!!!!!!1

SELECT [Quote max and min value and order value].[Quote Number], Max([Quote max and min value and order value].[Quoted Date]) AS [MaxOfQuoted Date], [Quote max and min value and order value].[Date Ordered], [Quote max and min value and order value].[Drawing No/Part No], [Quote max and min value and order value].Issue, [Quote max and min value and order value].[Cust Del Date]
FROM [Quote max and min value and order value]
GROUP BY [Quote max and min value and order value].[Quote Number], [Quote max and min value and order value].[Date Ordered], [Quote max and min value and order value].[Drawing No/Part No], [Quote max and min value and order value].Issue, [Quote max and min value and order value].[Cust Del Date]
HAVING ((([Quote max and min value and order value].[Quote Number]) Like [enter quote no]));

 

[rainman89]

I'm assuming that you have diff data in the table for the date ordered, etc.... because of this, you will not be able to select a single record because they have different values in the other fields.. I think you will have to create 2 queries and go from there.

This code will allow you to select the order # with the latest date ONLY

SELECT [Quote max and min value and order value].[Quote Number], Max([Quote max and min value and order value].[Quoted Date]) AS [MaxOfQuoted Date]
FROM [Quote max and min value and order value]
GROUP BY [Quote max and min value and order value].[Quote Number]
HAVING ((([Quote max and min value and order value].[Quote Number]) Like [enter quote no]));

 

[orshims]

hi
yes this works for just the quoted date hovever how do i then do it to inclued the information i need??? the extra information i need is
ordered date
drawing number
issue number
customer del date

appologies in advance if this is something simple!!!!!!!!

 

[rainman89]

You would need a 2nd query that would pull the information based on the ID of that Quote, which i dont believe is included in the query that i gave you

 

[orshims]

thanks
i got this to work now however there is a part two to this query. in the quote number field i want the user to be able to select multiple quote numbers to eventually create a report to display the selected quote numbers as a customer order because the customer will not always order 1 quoted item
hope i make sense