Pick last entered records from table

B

Barry

I have a query as shown below in which i need to select the last records
entered by the lift number. At the moment it shows all records entered for
each lift but i only need it to show the last records entered

Installation Lift No Scheduled Week No
99 LC05-0359-04 07-06
98 LC05-0359-03 13-06
97 LC05-0359-02 08-06
96 LC05-0359-01 08-06
63 LC05-0359-04 09-06
62 LC05-0359-03 09-06
61 LC05-0359-02 05-06
60 LC05-0359-01 05-06

What i actually need to see is:
99 LC05-0359-04 07-06
98 LC05-0359-03 13-06
97 LC05-0359-02 08-06
96 LC05-0359-01 08-06

I tried grouping the lift number by the max and last function in the query
but this still shows all the records.
Can anybody help me with this please?????
 
O

Ofer

Try using SQL with Top and the amount of records you want for each lift

SELECT [M1].[Installation], [M1].[Lift No], [M1].[Scheduled Week No]
FROM [Table Name] AS M1
WHERE M1.[DateFieldName] In (SELECT Top 2 M2.[Installation]
FROM [Table Name] as M2
WHERE M2.[Lift No] =M1.[Lift No]
ORDER BY M2.[Installation] Desc)
================================
In this SQL it returns 2 last events for each lift, I assume that the
Installation field indicate which are the last records to be inserted to the
table
 
B

Barry

This is probably a stupid question but i have not used alot of sql statements
in the past, where would this fit into my sql statement.

SELECT tblSales.chrJobname, tblLiftspec.chrLiftNoID,
tblLifttype.chrLiftType, tblLiftspec.chrLiftref,
Pre_Production_SiteSurvey.PreProductionID,
Pre_Production_SiteSurvey.[Scheduled Week No],
Pre_Production_SiteSurvey.[Completed Week], Pre_Production_SiteSurvey.[Survey
By], Pre_Production_SiteSurvey.Paperwork, tblLiftspec.SiteDeliveryDate,
tblLiftspec.chrManLiftNo, tblSales.chrJobNoID,
Pre_Production_SiteSurvey.DateLogged, Pre_Production_SiteSurvey.UserLogged,
tblLiftspec.chrLiftNoID, tblSales.blnHandoverComplete,
tblInstallLog.dtmActualStartDate
FROM (((Pre_Production_SiteSurvey RIGHT JOIN tblLiftspec ON
Pre_Production_SiteSurvey.chrLiftNoID = tblLiftspec.chrLiftNoID) RIGHT JOIN
tblSales ON tblLiftspec.chrJobNoID = tblSales.chrJobNoID) LEFT JOIN
tblInstallLog ON tblLiftspec.chrLiftNoID = tblInstallLog.chrLiftNoID) LEFT
JOIN tblLifttype ON tblLiftspec.lngLifttypeID = tblLifttype.lngLifttypeID
WHERE (((tblSales.chrJobname) Like "*" & [Forms]![Site Survey - Pre
Production All Jobs]![txtJobName] & "*") AND ((tblLiftspec.chrLiftNoID)<>"")
AND ((tblLifttype.chrLiftType)<>"Stairlift") AND
((Pre_Production_SiteSurvey.[Scheduled Week No]) Is Null Or
(Pre_Production_SiteSurvey.[Scheduled Week No]) Like "*" & [Forms]![Site
Survey - Pre Production All Jobs]![txtScheduledWeekNo] & "*") AND
((Pre_Production_SiteSurvey.[Completed Week]) Is Null Or
(Pre_Production_SiteSurvey.[Completed Week]) Like "*" & [Forms]![Site Survey
- Pre Production All Jobs]![txtCompletedWeekNo] & "*") AND
((Pre_Production_SiteSurvey.[Survey By]) Is Null Or
(Pre_Production_SiteSurvey.[Survey By]) Like "*" & [Forms]![Site Survey - Pre
Production All Jobs]![txtSurveyBy] & "*" Or
(Pre_Production_SiteSurvey.[Survey By]) Is Null) AND
((tblLiftspec.SiteDeliveryDate) Is Null Or (tblLiftspec.SiteDeliveryDate)
Like "*" & [Forms]![Site Survey - Pre Production All Jobs]![txtDeliveryDate]
& "*") AND ((tblSales.blnHandoverComplete)=0) AND
((tblInstallLog.dtmActualStartDate) Is Null))
ORDER BY tblSales.chrJobname, tblLiftspec.chrLiftNoID;



Ofer said:
Try using SQL with Top and the amount of records you want for each lift

SELECT [M1].[Installation], [M1].[Lift No], [M1].[Scheduled Week No]
FROM [Table Name] AS M1
WHERE M1.[DateFieldName] In (SELECT Top 2 M2.[Installation]
FROM [Table Name] as M2
WHERE M2.[Lift No] =M1.[Lift No]
ORDER BY M2.[Installation] Desc)
================================
In this SQL it returns 2 last events for each lift, I assume that the
Installation field indicate which are the last records to be inserted to the
table

--
\\// Live Long and Prosper \\//
BS"D


Barry said:
I have a query as shown below in which i need to select the last records
entered by the lift number. At the moment it shows all records entered for
each lift but i only need it to show the last records entered

Installation Lift No Scheduled Week No
99 LC05-0359-04 07-06
98 LC05-0359-03 13-06
97 LC05-0359-02 08-06
96 LC05-0359-01 08-06
63 LC05-0359-04 09-06
62 LC05-0359-03 09-06
61 LC05-0359-02 05-06
60 LC05-0359-01 05-06

What i actually need to see is:
99 LC05-0359-04 07-06
98 LC05-0359-03 13-06
97 LC05-0359-02 08-06
96 LC05-0359-01 08-06

I tried grouping the lift number by the max and last function in the query
but this still shows all the records.
Can anybody help me with this please?????
Click to expand...
Click to expand...
 
O

Ofer

Where are the fields:
Installation Lift No Scheduled Week No

and what are there names, in the table

--
\\// Live Long and Prosper \\//
BS"D


Barry said:
This is probably a stupid question but i have not used alot of sql statements
in the past, where would this fit into my sql statement.

SELECT tblSales.chrJobname, tblLiftspec.chrLiftNoID,
tblLifttype.chrLiftType, tblLiftspec.chrLiftref,
Pre_Production_SiteSurvey.PreProductionID,
Pre_Production_SiteSurvey.[Scheduled Week No],
Pre_Production_SiteSurvey.[Completed Week], Pre_Production_SiteSurvey.[Survey
By], Pre_Production_SiteSurvey.Paperwork, tblLiftspec.SiteDeliveryDate,
tblLiftspec.chrManLiftNo, tblSales.chrJobNoID,
Pre_Production_SiteSurvey.DateLogged, Pre_Production_SiteSurvey.UserLogged,
tblLiftspec.chrLiftNoID, tblSales.blnHandoverComplete,
tblInstallLog.dtmActualStartDate
FROM (((Pre_Production_SiteSurvey RIGHT JOIN tblLiftspec ON
Pre_Production_SiteSurvey.chrLiftNoID = tblLiftspec.chrLiftNoID) RIGHT JOIN
tblSales ON tblLiftspec.chrJobNoID = tblSales.chrJobNoID) LEFT JOIN
tblInstallLog ON tblLiftspec.chrLiftNoID = tblInstallLog.chrLiftNoID) LEFT
JOIN tblLifttype ON tblLiftspec.lngLifttypeID = tblLifttype.lngLifttypeID
WHERE (((tblSales.chrJobname) Like "*" & [Forms]![Site Survey - Pre
Production All Jobs]![txtJobName] & "*") AND ((tblLiftspec.chrLiftNoID)<>"")
AND ((tblLifttype.chrLiftType)<>"Stairlift") AND
((Pre_Production_SiteSurvey.[Scheduled Week No]) Is Null Or
(Pre_Production_SiteSurvey.[Scheduled Week No]) Like "*" & [Forms]![Site
Survey - Pre Production All Jobs]![txtScheduledWeekNo] & "*") AND
((Pre_Production_SiteSurvey.[Completed Week]) Is Null Or
(Pre_Production_SiteSurvey.[Completed Week]) Like "*" & [Forms]![Site Survey
- Pre Production All Jobs]![txtCompletedWeekNo] & "*") AND
((Pre_Production_SiteSurvey.[Survey By]) Is Null Or
(Pre_Production_SiteSurvey.[Survey By]) Like "*" & [Forms]![Site Survey - Pre
Production All Jobs]![txtSurveyBy] & "*" Or
(Pre_Production_SiteSurvey.[Survey By]) Is Null) AND
((tblLiftspec.SiteDeliveryDate) Is Null Or (tblLiftspec.SiteDeliveryDate)
Like "*" & [Forms]![Site Survey - Pre Production All Jobs]![txtDeliveryDate]
& "*") AND ((tblSales.blnHandoverComplete)=0) AND
((tblInstallLog.dtmActualStartDate) Is Null))
ORDER BY tblSales.chrJobname, tblLiftspec.chrLiftNoID;



Ofer said:
Try using SQL with Top and the amount of records you want for each lift

SELECT [M1].[Installation], [M1].[Lift No], [M1].[Scheduled Week No]
FROM [Table Name] AS M1
WHERE M1.[DateFieldName] In (SELECT Top 2 M2.[Installation]
FROM [Table Name] as M2
WHERE M2.[Lift No] =M1.[Lift No]
ORDER BY M2.[Installation] Desc)
================================
In this SQL it returns 2 last events for each lift, I assume that the
Installation field indicate which are the last records to be inserted to the
table

--
\\// Live Long and Prosper \\//
BS"D


Barry said:
I have a query as shown below in which i need to select the last records
entered by the lift number. At the moment it shows all records entered for
each lift but i only need it to show the last records entered

Installation Lift No Scheduled Week No
99 LC05-0359-04 07-06
98 LC05-0359-03 13-06
97 LC05-0359-02 08-06
96 LC05-0359-01 08-06
63 LC05-0359-04 09-06
62 LC05-0359-03 09-06
61 LC05-0359-02 05-06
60 LC05-0359-01 05-06

What i actually need to see is:
99 LC05-0359-04 07-06
98 LC05-0359-03 13-06
97 LC05-0359-02 08-06
96 LC05-0359-01 08-06

I tried grouping the lift number by the max and last function in the query
but this still shows all the records.
Can anybody help me with this please?????
Click to expand...
Click to expand...
Click to expand...
 
B

Barry

My appoligies i was not actually correct with the first post, i just tried to
make it easier to interpret. I have 2 tables 1 called tblLiftSpec and the
other named Pre_Production_SiteSurvey. The idea behind this is that a lift in
the tblLiftspec table can have many site surveys but on the form showing all
the lifts we only need to see the most current site survey.

The actual fields would have been

Installation: PreProductionID
Lift No: chrLiftNoID
Scheduled Week No: [Scheduled Week No]

Ofer said:
Where are the fields:
Installation Lift No Scheduled Week No

and what are there names, in the table

--
\\// Live Long and Prosper \\//
BS"D


Barry said:
This is probably a stupid question but i have not used alot of sql statements
in the past, where would this fit into my sql statement.

SELECT tblSales.chrJobname, tblLiftspec.chrLiftNoID,
tblLifttype.chrLiftType, tblLiftspec.chrLiftref,
Pre_Production_SiteSurvey.PreProductionID,
Pre_Production_SiteSurvey.[Scheduled Week No],
Pre_Production_SiteSurvey.[Completed Week], Pre_Production_SiteSurvey.[Survey
By], Pre_Production_SiteSurvey.Paperwork, tblLiftspec.SiteDeliveryDate,
tblLiftspec.chrManLiftNo, tblSales.chrJobNoID,
Pre_Production_SiteSurvey.DateLogged, Pre_Production_SiteSurvey.UserLogged,
tblLiftspec.chrLiftNoID, tblSales.blnHandoverComplete,
tblInstallLog.dtmActualStartDate
FROM (((Pre_Production_SiteSurvey RIGHT JOIN tblLiftspec ON
Pre_Production_SiteSurvey.chrLiftNoID = tblLiftspec.chrLiftNoID) RIGHT JOIN
tblSales ON tblLiftspec.chrJobNoID = tblSales.chrJobNoID) LEFT JOIN
tblInstallLog ON tblLiftspec.chrLiftNoID = tblInstallLog.chrLiftNoID) LEFT
JOIN tblLifttype ON tblLiftspec.lngLifttypeID = tblLifttype.lngLifttypeID
WHERE (((tblSales.chrJobname) Like "*" & [Forms]![Site Survey - Pre
Production All Jobs]![txtJobName] & "*") AND ((tblLiftspec.chrLiftNoID)<>"")
AND ((tblLifttype.chrLiftType)<>"Stairlift") AND
((Pre_Production_SiteSurvey.[Scheduled Week No]) Is Null Or
(Pre_Production_SiteSurvey.[Scheduled Week No]) Like "*" & [Forms]![Site
Survey - Pre Production All Jobs]![txtScheduledWeekNo] & "*") AND
((Pre_Production_SiteSurvey.[Completed Week]) Is Null Or
(Pre_Production_SiteSurvey.[Completed Week]) Like "*" & [Forms]![Site Survey
- Pre Production All Jobs]![txtCompletedWeekNo] & "*") AND
((Pre_Production_SiteSurvey.[Survey By]) Is Null Or
(Pre_Production_SiteSurvey.[Survey By]) Like "*" & [Forms]![Site Survey - Pre
Production All Jobs]![txtSurveyBy] & "*" Or
(Pre_Production_SiteSurvey.[Survey By]) Is Null) AND
((tblLiftspec.SiteDeliveryDate) Is Null Or (tblLiftspec.SiteDeliveryDate)
Like "*" & [Forms]![Site Survey - Pre Production All Jobs]![txtDeliveryDate]
& "*") AND ((tblSales.blnHandoverComplete)=0) AND
((tblInstallLog.dtmActualStartDate) Is Null))
ORDER BY tblSales.chrJobname, tblLiftspec.chrLiftNoID;



Ofer said:
Try using SQL with Top and the amount of records you want for each lift

SELECT [M1].[Installation], [M1].[Lift No], [M1].[Scheduled Week No]
FROM [Table Name] AS M1
WHERE M1.[DateFieldName] In (SELECT Top 2 M2.[Installation]
FROM [Table Name] as M2
WHERE M2.[Lift No] =M1.[Lift No]
ORDER BY M2.[Installation] Desc)
================================
In this SQL it returns 2 last events for each lift, I assume that the
Installation field indicate which are the last records to be inserted to the
table

--
\\// Live Long and Prosper \\//
BS"D


:

I have a query as shown below in which i need to select the last records
entered by the lift number. At the moment it shows all records entered for
each lift but i only need it to show the last records entered

Installation Lift No Scheduled Week No
99 LC05-0359-04 07-06
98 LC05-0359-03 13-06
97 LC05-0359-02 08-06
96 LC05-0359-01 08-06
63 LC05-0359-04 09-06
62 LC05-0359-03 09-06
61 LC05-0359-02 05-06
60 LC05-0359-01 05-06

What i actually need to see is:
99 LC05-0359-04 07-06
98 LC05-0359-03 13-06
97 LC05-0359-02 08-06
96 LC05-0359-01 08-06

I tried grouping the lift number by the max and last function in the query
but this still shows all the records.
Can anybody help me with this please?????
Click to expand...
Click to expand...
Click to expand...
 
O

Ofer

Save your SQL As a query, and then create another query that is based on the
first one, and try this

SELECT [M1].[PreProductionID], [M1].[chrLiftNoID], [M1].[Scheduled Week No]
FROM [Query Name] AS M1
WHERE M1.[PreProductionID] In (SELECT Top 2 M2.[PreProductionID]
FROM [Query Name] as M2
WHERE M2.[chrLiftNoID] =M1.[chrLiftNoID]
ORDER BY M2.[PreProductionID] Desc)

--
\\// Live Long and Prosper \\//
BS"D


Barry said:
My appoligies i was not actually correct with the first post, i just tried to
make it easier to interpret. I have 2 tables 1 called tblLiftSpec and the
other named Pre_Production_SiteSurvey. The idea behind this is that a lift in
the tblLiftspec table can have many site surveys but on the form showing all
the lifts we only need to see the most current site survey.

The actual fields would have been

Installation: PreProductionID
Lift No: chrLiftNoID
Scheduled Week No: [Scheduled Week No]

Ofer said:
Where are the fields:
Installation Lift No Scheduled Week No

and what are there names, in the table

--
\\// Live Long and Prosper \\//
BS"D


Barry said:
This is probably a stupid question but i have not used alot of sql statements
in the past, where would this fit into my sql statement.

SELECT tblSales.chrJobname, tblLiftspec.chrLiftNoID,
tblLifttype.chrLiftType, tblLiftspec.chrLiftref,
Pre_Production_SiteSurvey.PreProductionID,
Pre_Production_SiteSurvey.[Scheduled Week No],
Pre_Production_SiteSurvey.[Completed Week], Pre_Production_SiteSurvey.[Survey
By], Pre_Production_SiteSurvey.Paperwork, tblLiftspec.SiteDeliveryDate,
tblLiftspec.chrManLiftNo, tblSales.chrJobNoID,
Pre_Production_SiteSurvey.DateLogged, Pre_Production_SiteSurvey.UserLogged,
tblLiftspec.chrLiftNoID, tblSales.blnHandoverComplete,
tblInstallLog.dtmActualStartDate
FROM (((Pre_Production_SiteSurvey RIGHT JOIN tblLiftspec ON
Pre_Production_SiteSurvey.chrLiftNoID = tblLiftspec.chrLiftNoID) RIGHT JOIN
tblSales ON tblLiftspec.chrJobNoID = tblSales.chrJobNoID) LEFT JOIN
tblInstallLog ON tblLiftspec.chrLiftNoID = tblInstallLog.chrLiftNoID) LEFT
JOIN tblLifttype ON tblLiftspec.lngLifttypeID = tblLifttype.lngLifttypeID
WHERE (((tblSales.chrJobname) Like "*" & [Forms]![Site Survey - Pre
Production All Jobs]![txtJobName] & "*") AND ((tblLiftspec.chrLiftNoID)<>"")
AND ((tblLifttype.chrLiftType)<>"Stairlift") AND
((Pre_Production_SiteSurvey.[Scheduled Week No]) Is Null Or
(Pre_Production_SiteSurvey.[Scheduled Week No]) Like "*" & [Forms]![Site
Survey - Pre Production All Jobs]![txtScheduledWeekNo] & "*") AND
((Pre_Production_SiteSurvey.[Completed Week]) Is Null Or
(Pre_Production_SiteSurvey.[Completed Week]) Like "*" & [Forms]![Site Survey
- Pre Production All Jobs]![txtCompletedWeekNo] & "*") AND
((Pre_Production_SiteSurvey.[Survey By]) Is Null Or
(Pre_Production_SiteSurvey.[Survey By]) Like "*" & [Forms]![Site Survey - Pre
Production All Jobs]![txtSurveyBy] & "*" Or
(Pre_Production_SiteSurvey.[Survey By]) Is Null) AND
((tblLiftspec.SiteDeliveryDate) Is Null Or (tblLiftspec.SiteDeliveryDate)
Like "*" & [Forms]![Site Survey - Pre Production All Jobs]![txtDeliveryDate]
& "*") AND ((tblSales.blnHandoverComplete)=0) AND
((tblInstallLog.dtmActualStartDate) Is Null))
ORDER BY tblSales.chrJobname, tblLiftspec.chrLiftNoID;



:

Try using SQL with Top and the amount of records you want for each lift

SELECT [M1].[Installation], [M1].[Lift No], [M1].[Scheduled Week No]
FROM [Table Name] AS M1
WHERE M1.[DateFieldName] In (SELECT Top 2 M2.[Installation]
FROM [Table Name] as M2
WHERE M2.[Lift No] =M1.[Lift No]
ORDER BY M2.[Installation] Desc)
================================
In this SQL it returns 2 last events for each lift, I assume that the
Installation field indicate which are the last records to be inserted to the
table

--
\\// Live Long and Prosper \\//
BS"D


:

I have a query as shown below in which i need to select the last records
entered by the lift number. At the moment it shows all records entered for
each lift but i only need it to show the last records entered

Installation Lift No Scheduled Week No
99 LC05-0359-04 07-06
98 LC05-0359-03 13-06
97 LC05-0359-02 08-06
96 LC05-0359-01 08-06
63 LC05-0359-04 09-06
62 LC05-0359-03 09-06
61 LC05-0359-02 05-06
60 LC05-0359-01 05-06

What i actually need to see is:
99 LC05-0359-04 07-06
98 LC05-0359-03 13-06
97 LC05-0359-02 08-06
96 LC05-0359-01 08-06

I tried grouping the lift number by the max and last function in the query
but this still shows all the records.
Can anybody help me with this please?????
Click to expand...
Click to expand...
Click to expand...
 
B

Barry

Thanks alot Ofer this really helped me

Ofer said:
Save your SQL As a query, and then create another query that is based on the
first one, and try this

SELECT [M1].[PreProductionID], [M1].[chrLiftNoID], [M1].[Scheduled Week No]
FROM [Query Name] AS M1
WHERE M1.[PreProductionID] In (SELECT Top 2 M2.[PreProductionID]
FROM [Query Name] as M2
WHERE M2.[chrLiftNoID] =M1.[chrLiftNoID]
ORDER BY M2.[PreProductionID] Desc)

--
\\// Live Long and Prosper \\//
BS"D


Barry said:
My appoligies i was not actually correct with the first post, i just tried to
make it easier to interpret. I have 2 tables 1 called tblLiftSpec and the
other named Pre_Production_SiteSurvey. The idea behind this is that a lift in
the tblLiftspec table can have many site surveys but on the form showing all
the lifts we only need to see the most current site survey.

The actual fields would have been

Installation: PreProductionID
Lift No: chrLiftNoID
Scheduled Week No: [Scheduled Week No]

Ofer said:
Where are the fields:
Installation Lift No Scheduled Week No

and what are there names, in the table

--
\\// Live Long and Prosper \\//
BS"D


:

This is probably a stupid question but i have not used alot of sql statements
in the past, where would this fit into my sql statement.

SELECT tblSales.chrJobname, tblLiftspec.chrLiftNoID,
tblLifttype.chrLiftType, tblLiftspec.chrLiftref,
Pre_Production_SiteSurvey.PreProductionID,
Pre_Production_SiteSurvey.[Scheduled Week No],
Pre_Production_SiteSurvey.[Completed Week], Pre_Production_SiteSurvey.[Survey
By], Pre_Production_SiteSurvey.Paperwork, tblLiftspec.SiteDeliveryDate,
tblLiftspec.chrManLiftNo, tblSales.chrJobNoID,
Pre_Production_SiteSurvey.DateLogged, Pre_Production_SiteSurvey.UserLogged,
tblLiftspec.chrLiftNoID, tblSales.blnHandoverComplete,
tblInstallLog.dtmActualStartDate
FROM (((Pre_Production_SiteSurvey RIGHT JOIN tblLiftspec ON
Pre_Production_SiteSurvey.chrLiftNoID = tblLiftspec.chrLiftNoID) RIGHT JOIN
tblSales ON tblLiftspec.chrJobNoID = tblSales.chrJobNoID) LEFT JOIN
tblInstallLog ON tblLiftspec.chrLiftNoID = tblInstallLog.chrLiftNoID) LEFT
JOIN tblLifttype ON tblLiftspec.lngLifttypeID = tblLifttype.lngLifttypeID
WHERE (((tblSales.chrJobname) Like "*" & [Forms]![Site Survey - Pre
Production All Jobs]![txtJobName] & "*") AND ((tblLiftspec.chrLiftNoID)<>"")
AND ((tblLifttype.chrLiftType)<>"Stairlift") AND
((Pre_Production_SiteSurvey.[Scheduled Week No]) Is Null Or
(Pre_Production_SiteSurvey.[Scheduled Week No]) Like "*" & [Forms]![Site
Survey - Pre Production All Jobs]![txtScheduledWeekNo] & "*") AND
((Pre_Production_SiteSurvey.[Completed Week]) Is Null Or
(Pre_Production_SiteSurvey.[Completed Week]) Like "*" & [Forms]![Site Survey
- Pre Production All Jobs]![txtCompletedWeekNo] & "*") AND
((Pre_Production_SiteSurvey.[Survey By]) Is Null Or
(Pre_Production_SiteSurvey.[Survey By]) Like "*" & [Forms]![Site Survey - Pre
Production All Jobs]![txtSurveyBy] & "*" Or
(Pre_Production_SiteSurvey.[Survey By]) Is Null) AND
((tblLiftspec.SiteDeliveryDate) Is Null Or (tblLiftspec.SiteDeliveryDate)
Like "*" & [Forms]![Site Survey - Pre Production All Jobs]![txtDeliveryDate]
& "*") AND ((tblSales.blnHandoverComplete)=0) AND
((tblInstallLog.dtmActualStartDate) Is Null))
ORDER BY tblSales.chrJobname, tblLiftspec.chrLiftNoID;



:

Try using SQL with Top and the amount of records you want for each lift

SELECT [M1].[Installation], [M1].[Lift No], [M1].[Scheduled Week No]
FROM [Table Name] AS M1
WHERE M1.[DateFieldName] In (SELECT Top 2 M2.[Installation]
FROM [Table Name] as M2
WHERE M2.[Lift No] =M1.[Lift No]
ORDER BY M2.[Installation] Desc)
================================
In this SQL it returns 2 last events for each lift, I assume that the
Installation field indicate which are the last records to be inserted to the
table

--
\\// Live Long and Prosper \\//
BS"D


:

I have a query as shown below in which i need to select the last records
entered by the lift number. At the moment it shows all records entered for
each lift but i only need it to show the last records entered

Installation Lift No Scheduled Week No
99 LC05-0359-04 07-06
98 LC05-0359-03 13-06
97 LC05-0359-02 08-06
96 LC05-0359-01 08-06
63 LC05-0359-04 09-06
62 LC05-0359-03 09-06
61 LC05-0359-02 05-06
60 LC05-0359-01 05-06

What i actually need to see is:
99 LC05-0359-04 07-06
98 LC05-0359-03 13-06
97 LC05-0359-02 08-06
96 LC05-0359-01 08-06

I tried grouping the lift number by the max and last function in the query
but this still shows all the records.
Can anybody help me with this please?????
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

count not-overlapping days in a "Table" with Beginning and EndingsDates Specified 0
Find then highlight in yellow 6
Calculating Work Time 7
Reverse Index Match 2
convert range of years to series 14
How to sum 2
Sum value between two dates and copy to new cell 7
Date query 4

Top