Pick last entered records from table

G

Guest

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?????
 
J

Joseph Meehan

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...

Maybe adding an additional field might be the best bet. Consecutive
numbers and just pick the four largest.

I am uncertain of the form of your data, but it appears that the last
two numbers of the second filed will always be 01, 02, 03 or 04 and that you
want the last one of each. I guess my question is what is that first
number? Will it always be unique and will it always be increasing?
 
G

Guest

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
 
G

Guest

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...
 
G

Guest

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...
 
G

Guest

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...
 
G

Guest

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...
 
G

Guest

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

Receiving valid Multicast address failed on mini port driver 0
Find then highlight in yellow 6
convert range of years to series 10
How to sum 2
possible with cell formula in access? 4
Somekind of crosstab query 6
Date query 4
Calculating number of days between multiple dat2 1

Top