Record AFTER latest by date an time, grouped by serial number

I have 2 tables: tblEqLoc has the following fields:

Start Date | StartTime | EndDate | EndTime | CustNum | SerialNum |
EqStatus

tblEndDate has : EndDate, CustNum, and SerialNum that can be joined with
tblEqLoc

In the CustNum field is either a customer number or a warehouseID number.
CustNums always start with 3004 – all other numbers start with zeros.

When all the data is sorted ascending chronologically, the start date and
start time for any given record is always equal to the end date / end time
for the previous record.

I need to create a query that will show the first record that ISN’T populated
with a customer number AFTER the last record chronologically (by date, by
time)

If a true customer number (not a warehouse #) is in the CustNum field, it
will have either a D or an H for the EqStatus – the record I want returned in
my query will always have an “R� for the EqStatus, and the previous record
will be a “D.� Always.

I could get the DATE I’m looking for by joining on the EqSerialNum, but I am
concerned not only with the last date, but the last date and time.

So I want my query to return, by serial number, the record AFTER the latest
record (by date AND time) that was populated by a “true� customer number in
the CustNum field, which always starts with 3004, and is going to be a “D� in
the EqStatus in that key record that is the last record with a customer
number, and then I want the next record (Chronologically, by date and time)
after that to be the record returned in my query.

--
Message posted via http://www.accessmonster.com

Your date and time should be a single field using datatype DateTime.
But be that as it may, post sample data and indicate which record from the
sample should be returned by the query.

--
Build a little, test a little.


"ironwood9 via AccessMonster.com" wrote:

I have 2 tables: tblEqLoc has the following fields:

Start Date | StartTime | EndDate | EndTime | CustNum | SerialNum |
EqStatus

tblEndDate has : EndDate, CustNum, and SerialNum that can be joined with
tblEqLoc

In the CustNum field is either a customer number or a warehouseID number.
CustNums always start with 3004 – all other numbers start with zeros.

When all the data is sorted ascending chronologically, the start date and
start time for any given record is always equal to the end date / end time
for the previous record.

I need to create a query that will show the first record that ISN’T populated
with a customer number AFTER the last record chronologically (by date, by
time)

If a true customer number (not a warehouse #) is in the CustNum field, it
will have either a D or an H for the EqStatus – the record I want returned in
my query will always have an “R� for the EqStatus, and the previous record
will be a “D.� Always.

I could get the DATE I’m looking for by joining on the EqSerialNum, but I am
concerned not only with the last date, but the last date and time.

So I want my query to return, by serial number, the record AFTER the latest
record (by date AND time) that was populated by a “true� customer number in
the CustNum field, which always starts with 3004, and is going to be a “D� in
the EqStatus in that key record that is the last record with a customer
number, and then I want the next record (Chronologically, by date and time)
after that to be the record returned in my query.

--
Message posted via http://www.accessmonster.com

.

Karl, thanks - how do you post ?

KARL DEWEY wrote:
Your date and time should be a single field using datatype DateTime.
But be that as it may, post sample data and indicate which record from the
sample should be returned by the query.

I have 2 tables: tblEqLoc has the following fields:

[quoted text clipped - 29 lines]
number, and then I want the next record (Chronologically, by date and time)
after that to be the record returned in my query.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200912/1

Karl, thanks - how do you attach ?

KARL DEWEY wrote:
Your date and time should be a single field using datatype DateTime.
But be that as it may, post sample data and indicate which record from the
sample should be returned by the query.

I have 2 tables: tblEqLoc has the following fields:

[quoted text clipped - 29 lines]
number, and then I want the next record (Chronologically, by date and time)
after that to be the record returned in my query.

--
Message posted via http://www.accessmonster.com

Karl, thanks - how do you attach ?

KARL DEWEY wrote:
Your date and time should be a single field using datatype DateTime.
But be that as it may, post sample data and indicate which record from the
sample should be returned by the query.

I have 2 tables: tblEqLoc has the following fields:

[quoted text clipped - 29 lines]
number, and then I want the next record (Chronologically, by date and time)
after that to be the record returned in my query.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200912/1

Karl, thanks - how do you attach ?

KARL DEWEY wrote:
Your date and time should be a single field using datatype DateTime.
But be that as it may, post sample data and indicate which record from the
sample should be returned by the query.

I have 2 tables: tblEqLoc has the following fields:

[quoted text clipped - 29 lines]
number, and then I want the next record (Chronologically, by date and time)
after that to be the record returned in my query.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200912/1

You have been posting. Copy and paste the data in a response.

--
Build a little, test a little.


"ironwood9 via AccessMonster.com" wrote:

Karl, thanks - how do you attach ?

KARL DEWEY wrote:
Your date and time should be a single field using datatype DateTime.
But be that as it may, post sample data and indicate which record from the
sample should be returned by the query.

I have 2 tables: tblEqLoc has the following fields:

[quoted text clipped - 29 lines]
number, and then I want the next record (Chronologically, by date and time)
after that to be the record returned in my query.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200912/1

.