Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

Hello,

You could possibly do something similar in Base but it would be as faulty as the Access statement provided. When working with database records, in most cases this 'ID' field is an auto increment field. Now is a record is deleted then number is not necessarily used again. So now there is no 'ID - 1' to be retrieved.

In you particular situation, the common factors are the 'TripLogID' and the accomodations. So if you select a record where the 'TripLogID' fields are equal AND 'PrevAccomID' of the current record is equal to a record containing that in the 'ArriveAccomID' you have the previous record.

Hello,

You could possibly do something similar in Base but it would be as faulty as the Access statement provided. When working with database records, in most cases this 'ID' field is an auto increment field. Now is a record is deleted then number is not necessarily used again. So now there is no 'ID - 1' to be retrieved.

In you particular situation, the common factors are the 'TripLogID' and the accomodations. So if you select a record where the 'TripLogID' fields are equal AND 'PrevAccomID' of the current record is equal to a record containing that in the 'ArriveAccomID' you have the previous record.

Edit 2018-12-28:

With your last comment it seems there is still a bit of a connection problem with the situation. Hopefully this will clear up a few items. Attached is a sample I've been using to help answer your questions. It is certainly not your tables or data within but it should do. Please look at form TripToAccom. This is simply my record of your tblTrips table. The main table control just lists all records for that trip (only one trip in my file - see top of form). When a record is selected the table control in the bottom sub sub form displays the previous record.

If the top control has the first record selected the bottom control is empty because there is no previous record. Any other record does show the previous record. This is what you have been asking for, was answered above, and is done through the form linkage (typical form & sub form).

But I have been trying to state this is NOT what you want and you have been eluding to that. It appears you want additional information - calculations. What these are may determine the processing scheme. Doing this with queries may be tough but not impossible. Doing this with macros is another story. Macros require absolute detail - all internal field & form names and possibly more. Macros without the original Base file may not be easily possible and in either case requires time. Macros also requires your understanding of them in order to modify in the future.

You may also want to look at my Query section - in particular Mileage. This simply appends an odometer reading from one record onto another. Whether it is previous or next it is just SQL modifications. The point is that SQL may be part of your answer but the question is certainly not clear.

Sample ----- TowVehicles.odb

Hello,

You could possibly do something similar in Base but it would be as faulty as the Access statement provided. When working with database records, in most cases this 'ID' field is an auto increment field. Now is a record is deleted then number is not necessarily used again. So now there is no 'ID - 1' to be retrieved.

In you particular situation, the common factors are the 'TripLogID' and the accomodations. So if you select a record where the 'TripLogID' fields are equal AND 'PrevAccomID' of the current record is equal to a record containing that in the 'ArriveAccomID' you have the previous record.

Edit 2018-12-28:

With your last comment it seems there is still a bit of a connection problem with the situation. Hopefully this will clear up a few items. Attached is a sample I've been using to help answer your questions. It is certainly not your tables or data within but it should do. Please look at form TripToAccom. This is simply my record of your tblTrips table. The main table control just lists all records for that trip (only one trip in my file - see top of form). When a record is selected the table control in the bottom sub sub form displays the previous record.

If the top control has the first record selected the bottom control is empty because there is no previous record. Any other record does show the previous record. This is what you have been asking for, was answered above, and is done through the form linkage (typical form & sub form).

But I have been trying to state this is NOT what you want and you have been eluding alluding to that. It appears you want additional information - calculations. What these are may determine the processing scheme. Doing this with queries may be tough but not impossible. Doing this with macros is another story. Macros require absolute detail - all internal field & form names and possibly more. Macros without the original Base file may not be easily possible and in either case requires time. Macros also requires your understanding of them in order to modify in the future.

You may also want to look at my Query section - in particular Mileage. This simply appends an odometer reading from one record onto another. Whether it is previous or next it is just SQL modifications. The point is that SQL may be part of your answer but the question is certainly not clear.

Sample ----- TowVehicles.odb

Edit 2018-12-28 #2:

Notwithstanding the aforementioned, you can take you current query and using a 'left join` (see -> SQL Joins) similar to the one used in the 'Mileage' query example, append the information wanted from the previous segment. Then use the results to create your form. Using SQL directly instead of the graphical interface you have previously displayed will require you to understand SQL and its constructs. But this will enable you to gather all information you want and create totals (calculations) wanted.

Hello,

You could possibly do something similar in Base but it would be as faulty as the Access statement provided. When working with database records, in most cases this 'ID' field is an auto increment field. Now is a record is deleted then number is not necessarily used again. So now there is no 'ID - 1' to be retrieved.

In you particular situation, the common factors are the 'TripLogID' and the accomodations. So if you select a record where the 'TripLogID' fields are equal AND 'PrevAccomID' of the current record is equal to a record containing that in the 'ArriveAccomID' you have the previous record.

Edit 2018-12-28:

With your last comment it seems there is still a bit of a connection problem with the situation. Hopefully this will clear up a few items. Attached is a sample I've been using to help answer your questions. It is certainly not your tables or data within but it should do. Please look at form TripToAccom. This is simply my record of your tblTrips table. The main table control just lists all records for that trip (only one trip in my file - see top of form). When a record is selected the table control in the bottom sub sub form displays the previous record.

If the top control has the first record selected the bottom control is empty because there is no previous record. Any other record does show the previous record. This is what you have been asking for, was answered above, and is done through the form linkage (typical form & sub form).

But I have been trying to state this is NOT what you want and you have been alluding to that. It appears you want additional information - calculations. What these are may determine the processing scheme. Doing this with queries may be tough but not impossible. Doing this with macros is another story. Macros require absolute detail - all internal field & form names and possibly more. Macros without the original Base file may not be easily possible and in either case requires time. Macros also requires your understanding of them in order to modify in the future.

You may also want to look at my Query section - in particular Mileage. This simply appends an odometer reading from one record onto another. Whether it is previous or next it is just SQL modifications. The point is that SQL may be part of your answer but the question is certainly not clear.

Sample ----- TowVehicles.odb

Edit 2018-12-28 #2:

Notwithstanding the aforementioned, you can take you current query and using a 'left join` (see -> SQL Joins) similar to the one used in the 'Mileage' query example, append the information wanted from the previous segment. Then use the results to create your form. Using SQL directly instead of the graphical interface you have previously displayed will require you to understand SQL and its constructs. But this will enable you to gather all information you want and create totals (calculations) wanted.

Edit 2018-12-28 #3:

First, there was no formed named frmTripandSubForm. Second, you really need to learn more about Base, database and especially SQL. SQL (and not the graphical interface) will provide more answers to problems than anything else. Without it you'll forever be asking questions. Finally, the attached sample is nothing more than the application of all that has been mentioned in the answer already.

frmTripsModified uses the same linked form sub form method had given in the previous sample. It uses individual fields vs the frowned upon table control (told you there is no difference - they are just controls).

frmTripLog_sfmTripsModified uses the SQL approach noted in edit #2 above (and a previous answer).

Sample ----- EditedFromMycle.odb