If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Record AFTER latest by date an time, grouped by serial number
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 . |
#3
|
|||
|
|||
Record AFTER latest by date an time, grouped by serial number
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 |
#4
|
|||
|
|||
Record AFTER latest by date an time, grouped by serial number
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 |
#5
|
|||
|
|||
Record AFTER latest by date an time, grouped by serial number
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 |
#6
|
|||
|
|||
Record AFTER latest by date an time, grouped by serial number
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 |
#7
|
|||
|
|||
Record AFTER latest by date an time, grouped by serial number
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 . |
Thread Tools | |
Display Modes | |
|
|