Highest Value

[John]

Hi,
How can i view only the highest record ID or most recent date in my query.
There are many entries but I need only to print the latest history of the
item.

Regards

John

 

[Duane Hookom]

SELECT Top 1 *
FROM tblWithNoName
ORDER BY [NoNameDateField] DESC, PrimaryKeyField;

 

[Dave Patrick]

SELECT TOP 1 Table1.*
FROM Table1
ORDER BY Table1.myrecID DESC;

or

SELECT TOP 1 Table1.*
FROM Table1
ORDER BY Table1.mydate DESC;

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Hi,
| How can i view only the highest record ID or most recent date in my query.
| There are many entries but I need only to print the latest history of the
| item.
|
| Regards
|
| John
|
|

 

[John]

Thnaks guys

Much Appreciated

 

[John]

Sorry Guys im just being so unhelpful here.
My results are shown in a query Called qryPolicePurchasesFromCustomers
the field in the query that I would like to retrieve the individual record
on is call [TDate].
There is already SQL written as SELECT and FROM where do I insert the SQL
provided by yourselves.

Regards

John

Thnaks guys

Much Appreciated

SELECT TOP 1 Table1.*
FROM Table1
ORDER BY Table1.myrecID DESC;

or

SELECT TOP 1 Table1.*
FROM Table1
ORDER BY Table1.mydate DESC;

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Hi,
| How can i view only the highest record ID or most recent date in my
query.
| There are many entries but I need only to print the latest history of
the
| item.
|
| Regards
|
| John
|
|

 

[Duane Hookom]

How about some additional information about your fields and maybe some
sample records and expected results?

--
Duane Hookom
MS Access MVP
--

Sorry Guys im just being so unhelpful here.
My results are shown in a query Called qryPolicePurchasesFromCustomers
the field in the query that I would like to retrieve the individual record
on is call [TDate].
There is already SQL written as SELECT and FROM where do I insert the SQL
provided by yourselves.

Regards

John

Thnaks guys

Much Appreciated

SELECT TOP 1 Table1.*
FROM Table1
ORDER BY Table1.myrecID DESC;

or

SELECT TOP 1 Table1.*
FROM Table1
ORDER BY Table1.mydate DESC;

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Hi,
| How can i view only the highest record ID or most recent date in my
query.
| There are many entries but I need only to print the latest history of
the
| item.
|
| Regards
|
| John
|
|

 

[John]

Duane,

Fields in the query are as follows, [TID] [TName] [TReg] [Tdate] etc

on a form I have a lookup box which is is sourced from a select query and
shows all the car reg numbers.
I select a number and click a commandbutton which opens a report showing the
cars current and previous owners,
I only want to see the most current owners details i.e. thew Highest record
number [TID] or the most recent date [TDATE]
Ideally I would just like the query to show the highest [TDATE]

Maybe this is betther solved on the report if possible rather than the
query?

Regards

John

How about some additional information about your fields and maybe some
sample records and expected results?

--
Duane Hookom
MS Access MVP
--

Sorry Guys im just being so unhelpful here.
My results are shown in a query Called qryPolicePurchasesFromCustomers
the field in the query that I would like to retrieve the individual
record on is call [TDate].
There is already SQL written as SELECT and FROM where do I insert the
SQL provided by yourselves.

Regards

John

Thnaks guys

Much Appreciated


SELECT TOP 1 Table1.*
FROM Table1
ORDER BY Table1.myrecID DESC;

or

SELECT TOP 1 Table1.*
FROM Table1
ORDER BY Table1.mydate DESC;

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Hi,
| How can i view only the highest record ID or most recent date in my
query.
| There are many entries but I need only to print the latest history of
the
| item.
|
| Regards
|
| John
|
|

 

[Duane Hookom]

Set the record source of your report to something like:

SELECT TOP 1 [TID], [TName], [TReg], [Tdate]
FROM tblYourTable
WHERE TReg = Forms!frmYourFrom!cboYourC0mbo
ORDER BY TDate DESC;

Or, use code in your form like:

Dim strWhere as String
strWhere = "[TReg] =" & Me.cboYourCombo & _
"TDate = #" & DMax("TDate","tblYourtable","TReg=" & Me.cobYourCombo)
DoCmd.OpenReport "rptYourReport, acPreview, , strWhere

Since you didn't provide records or data types, the above may be wrong. I
assumed the TReg field was numeric.

--
Duane Hookom
MS Access MVP

Duane,

Fields in the query are as follows, [TID] [TName] [TReg] [Tdate] etc

on a form I have a lookup box which is is sourced from a select query and
shows all the car reg numbers.
I select a number and click a commandbutton which opens a report showing
the cars current and previous owners,
I only want to see the most current owners details i.e. thew Highest
record number [TID] or the most recent date [TDATE]
Ideally I would just like the query to show the highest [TDATE]

Maybe this is betther solved on the report if possible rather than the
query?

Regards

John

How about some additional information about your fields and maybe some
sample records and expected results?

--
Duane Hookom
MS Access MVP
--

Sorry Guys im just being so unhelpful here.
My results are shown in a query Called qryPolicePurchasesFromCustomers
the field in the query that I would like to retrieve the individual
record on is call [TDate].
There is already SQL written as SELECT and FROM where do I insert the
SQL provided by yourselves.

Regards

John



Thnaks guys

Much Appreciated


SELECT TOP 1 Table1.*
FROM Table1
ORDER BY Table1.myrecID DESC;

or

SELECT TOP 1 Table1.*
FROM Table1
ORDER BY Table1.mydate DESC;

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Hi,
| How can i view only the highest record ID or most recent date in my
query.
| There are many entries but I need only to print the latest history
of the
| item.
|
| Regards
|
| John
|
|

 

[John]

Thanks Duane,
Did a bit of manipulation but got the result I wanted.

Regards
John

Set the record source of your report to something like:

SELECT TOP 1 [TID], [TName], [TReg], [Tdate]
FROM tblYourTable
WHERE TReg = Forms!frmYourFrom!cboYourC0mbo
ORDER BY TDate DESC;

Or, use code in your form like:

Dim strWhere as String
strWhere = "[TReg] =" & Me.cboYourCombo & _
"TDate = #" & DMax("TDate","tblYourtable","TReg=" & Me.cobYourCombo)
DoCmd.OpenReport "rptYourReport, acPreview, , strWhere

Since you didn't provide records or data types, the above may be wrong. I
assumed the TReg field was numeric.

--
Duane Hookom
MS Access MVP

Duane,

Fields in the query are as follows, [TID] [TName] [TReg] [Tdate] etc

on a form I have a lookup box which is is sourced from a select query and
shows all the car reg numbers.
I select a number and click a commandbutton which opens a report showing
the cars current and previous owners,
I only want to see the most current owners details i.e. thew Highest
record number [TID] or the most recent date [TDATE]
Ideally I would just like the query to show the highest [TDATE]

Maybe this is betther solved on the report if possible rather than the
query?

Regards

John

How about some additional information about your fields and maybe some
sample records and expected results?

--
Duane Hookom
MS Access MVP
--

Sorry Guys im just being so unhelpful here.
My results are shown in a query Called qryPolicePurchasesFromCustomers
the field in the query that I would like to retrieve the individual
record on is call [TDate].
There is already SQL written as SELECT and FROM where do I insert the
SQL provided by yourselves.

Regards

John



Thnaks guys

Much Appreciated


SELECT TOP 1 Table1.*
FROM Table1
ORDER BY Table1.myrecID DESC;

or

SELECT TOP 1 Table1.*
FROM Table1
ORDER BY Table1.mydate DESC;

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Hi,
| How can i view only the highest record ID or most recent date in my
query.
| There are many entries but I need only to print the latest history
of the
| item.
|
| Regards
|
| John
|
|