Using LAST in a query

[Guest]

Hello,
I'm trying to get the last record by using last in the sum/total row in a
query.

I'm using 'Last' on all four of the four fields listed below. I sometimes
get the correct result until I close MS Access and return in hours the result
is completely different.

ContrDate
Activity
HoursSpent
AmtPaid

 

[Guest]

Instead of using Last for each record, use the Top 1, with sort on the field
that indicate which field was the last one to be insert, like a date or a
number

Select Top 1 TableName Order By ContrDate Desc

You can change the Top number to recieve more then one record

 

[John Spencer]

SELECT ContrDate, Activity, HoursSpent, AmtPaid
FROM YourTable
WHERE YourTable.ContrDate =
(SELECT Max(T.ContrDate)
FROM YourTable as T)

If you need to limit the results to a specific activity (for instance) you
would add that in the where clause of the subquery. Something like
SELECT ContrDate, Activity, HoursSpent, AmtPaid
FROM YourTable
WHERE YourTable.ContrDate =
(SELECT Max(T.ContrDate)
FROM YourTable as T
WHERE t.Activity = YourTable.Activity)

This would give you the latest record for each activity.

 

[Guest]

I'm not sure what you mean by Select Top 1 TableName Order By ContrDate Desc

Sorry

 

[Guest]

I'm following you all the but, I' not sure about the (FROM YourTable as T)
letter 'T'. What is the 'T' for?
--
Thanks
D''''Angelia

SELECT ContrDate, Activity, HoursSpent, AmtPaid
FROM YourTable
WHERE YourTable.ContrDate =
(SELECT Max(T.ContrDate)
FROM YourTable as T)

If you need to limit the results to a specific activity (for instance) you
would add that in the where clause of the subquery. Something like
SELECT ContrDate, Activity, HoursSpent, AmtPaid
FROM YourTable
WHERE YourTable.ContrDate =
(SELECT Max(T.ContrDate)
FROM YourTable as T
WHERE t.Activity = YourTable.Activity)

This would give you the latest record for each activity.

 

[Guest]

Sorry, but I answered your next post again, I didn't notice it was you.

When you run a query, and you sort it by DateField Desc, it will display
that last enetered record on the Top of the query, and under it the rest of
the records by order
When you add the Top 1, the query will return only one record the the top one

Select Top 1 TableName.* From TableName Order By dateField Desc
==============================================
So just create a query that return all the records, with the recent one on
top, and then add to the sql Top 1
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck

 

[Guest]

I'm so sorry but, I just can't seem to get an understanding of what you are
stating.
I put the Top 1 in the sql view and I got a order by error.

I didn't think this post went threw so I tried rephrasing the question.
This is what I tried that seems to work however, it does not give me the
last day of the month it gives me the next to the last day of the month.

I used this for your FIELD in the query grid --
X: DatePart("yyyy",[YourDateField])&DatePart("m",[YourDateField])

I used this as the Criteria in the query grid --
DatePart("yyyy",Date()-Day(Date()))&DatePart("m",Date()-Day(Date()))

 

[Guest]

Can you post the full SQL please?
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck

I'm so sorry but, I just can't seem to get an understanding of what you are
stating.
I put the Top 1 in the sql view and I got a order by error.

I didn't think this post went threw so I tried rephrasing the question.
This is what I tried that seems to work however, it does not give me the
last day of the month it gives me the next to the last day of the month.

I used this for your FIELD in the query grid --
X: DatePart("yyyy",[YourDateField])&DatePart("m",[YourDateField])

I used this as the Criteria in the query grid --
DatePart("yyyy",Date()-Day(Date()))&DatePart("m",Date()-Day(Date()))

--
Thanks
D''''Angelia

Sorry, but I answered your next post again, I didn't notice it was you.

When you run a query, and you sort it by DateField Desc, it will display
that last enetered record on the Top of the query, and under it the rest of
the records by order
When you add the Top 1, the query will return only one record the the top one

Select Top 1 TableName.* From TableName Order By dateField Desc
==============================================
So just create a query that return all the records, with the recent one on
top, and then add to the sql Top 1
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck

 

[Guest]

Here's the full sql:
SELECT [tbl Payee Info].PayeeNumber, [tbl Payee Info].PayeeName, [tbl Payee
Detail].ContrDate, [tbl Payee Detail].Activity, [tbl Payee
Detail].HoursSpent, [tbl Payee Detail].AmountPaid
FROM [tbl Payee Info] INNER JOIN [tbl Payee Detail] ON [tbl Payee
Info].PayeeNumber = [tbl Payee Detail].PayeeNumber
ORDER BY [tbl Payee Detail].ContrDate DESC
Select Top 1 tbl Payee Detail.* From tbl Payee Detail Order By ContrDate
Desc;
--
Thanks
D''''Angelia

Can you post the full SQL please?
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck

I'm so sorry but, I just can't seem to get an understanding of what you are
stating.
I put the Top 1 in the sql view and I got a order by error.

I didn't think this post went threw so I tried rephrasing the question.
This is what I tried that seems to work however, it does not give me the
last day of the month it gives me the next to the last day of the month.

I used this for your FIELD in the query grid --
X: DatePart("yyyy",[YourDateField])&DatePart("m",[YourDateField])

I used this as the Criteria in the query grid --
DatePart("yyyy",Date()-Day(Date()))&DatePart("m",Date()-Day(Date()))

--
Thanks
D''''Angelia

Sorry, but I answered your next post again, I didn't notice it was you.

When you run a query, and you sort it by DateField Desc, it will display
that last enetered record on the Top of the query, and under it the rest of
the records by order
When you add the Top 1, the query will return only one record the the top one

Select Top 1 TableName.* From TableName Order By dateField Desc
==============================================
So just create a query that return all the records, with the recent one on
top, and then add to the sql Top 1
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck



:

I'm not sure what you mean by Select Top 1 TableName Order By ContrDate Desc

Sorry
--
Thanks
D''''Angelia


:

Instead of using Last for each record, use the Top 1, with sort on the field
that indicate which field was the last one to be insert, like a date or a
number

Select Top 1 TableName Order By ContrDate Desc

You can change the Top number to recieve more then one record
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck



:

Hello,
I'm trying to get the last record by using last in the sum/total row in a
query.

I'm using 'Last' on all four of the four fields listed below. I sometimes
get the correct result until I close MS Access and return in hours the result
is completely different.

ContrDate
Activity
HoursSpent
AmtPaid

 

[Guest]

Try this

SELECT Top 1 [tbl Payee Info].PayeeNumber, [tbl Payee Info].PayeeName, [tbl
Payee Detail].ContrDate, [tbl Payee Detail].Activity, [tbl Payee
Detail].HoursSpent, [tbl Payee Detail].AmountPaid
FROM [tbl Payee Info] INNER JOIN [tbl Payee Detail] ON [tbl Payee
Info].PayeeNumber = [tbl Payee Detail].PayeeNumber
ORDER BY [tbl Payee Detail].ContrDate DESC

--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck

Here's the full sql:
SELECT [tbl Payee Info].PayeeNumber, [tbl Payee Info].PayeeName, [tbl Payee
Detail].ContrDate, [tbl Payee Detail].Activity, [tbl Payee
Detail].HoursSpent, [tbl Payee Detail].AmountPaid
FROM [tbl Payee Info] INNER JOIN [tbl Payee Detail] ON [tbl Payee
Info].PayeeNumber = [tbl Payee Detail].PayeeNumber
ORDER BY [tbl Payee Detail].ContrDate DESC
Select Top 1 tbl Payee Detail.* From tbl Payee Detail Order By ContrDate
Desc;
--
Thanks
D''''Angelia

Can you post the full SQL please?
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck

I'm so sorry but, I just can't seem to get an understanding of what you are
stating.
I put the Top 1 in the sql view and I got a order by error.

I didn't think this post went threw so I tried rephrasing the question.
This is what I tried that seems to work however, it does not give me the
last day of the month it gives me the next to the last day of the month.

I used this for your FIELD in the query grid --
X: DatePart("yyyy",[YourDateField])&DatePart("m",[YourDateField])

I used this as the Criteria in the query grid --
DatePart("yyyy",Date()-Day(Date()))&DatePart("m",Date()-Day(Date()))

--
Thanks
D''''Angelia


:

Sorry, but I answered your next post again, I didn't notice it was you.

When you run a query, and you sort it by DateField Desc, it will display
that last enetered record on the Top of the query, and under it the rest of
the records by order
When you add the Top 1, the query will return only one record the the top one

Select Top 1 TableName.* From TableName Order By dateField Desc
==============================================
So just create a query that return all the records, with the recent one on
top, and then add to the sql Top 1
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck



:

I'm not sure what you mean by Select Top 1 TableName Order By ContrDate Desc

Sorry
--
Thanks
D''''Angelia


:

Instead of using Last for each record, use the Top 1, with sort on the field
that indicate which field was the last one to be insert, like a date or a
number

Select Top 1 TableName Order By ContrDate Desc

You can change the Top number to recieve more then one record
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck



:

Hello,
I'm trying to get the last record by using last in the sum/total row in a
query.

I'm using 'Last' on all four of the four fields listed below. I sometimes
get the correct result until I close MS Access and return in hours the result
is completely different.

ContrDate
Activity
HoursSpent
AmtPaid

 

[Michel Walsh]

Hi,


It is an alias, a name for a reference. You could use any name you want.
Note that YourTable appears in two different contexts (FROM clause), so, to
differentiate which one is which, we supply an alias, T, to one of them.
Again, you could have use any other name.


Hoping it may help,
Vanderghast, Access MVP

I'm following you all the but, I' not sure about the (FROM YourTable as T)
letter 'T'. What is the 'T' for?

 

[John Spencer]

T is an name for the second instance (a copy) of the table. It could be
anything you wanted - for instance it could be TemporaryCopyOfMyTable.

It allows you to have copies of the table and refer to them by a specific
name. You can also use it to "rename" any table in your query for the
duration of the query. That allows you to type less - see silly example
below

SELECT X.FieldA, B.FieldA
FROM MyGoodnessThisTableHasALongName as X
INNER JOIN [I Hate Typing Spaces in Table Names] as B
ON X.FieldAA = B.FieldBB

I'm following you all the but, I' not sure about the (FROM YourTable as T)
letter 'T'. What is the 'T' for?