Showing specific Record in a form (1 Viewer)

[Mark Richardson]

Right my problem is this. I have set up a form where the user inputs a information regarding a train journey. At a certain point they need to enter a start mileage for the journey. Once they have updated the field a macro is run which queries another table identifying what journey they are travelling in. This works however the user is seeing the entire Journey information, rather than the initial start mile and final end mile.

I must at this point explain the table that i have just queried. The table i have queried holds a list of Railway ELR, Track ID and Start and End Mile information. Now for each ELR there can be several Track ID's for example 1100, 2100 etc. Now these Track id's are separated down into smaller segments identified by the start and end mile information. I have already indexed the track ID's and grouped them to identify routes.
The query extracts the ELR, Track ID and milage information for specific routes depending on what the user inputs. It creates a datasheet look at the individual sections of the Journey. However all i want to do is extract the beginning start mileage and the final End Mileage, thus creating a overall journey view, rather than a broken down view.

Thanks in advance for any help, thats if you've not fallen asleep from reading this explanation.

 

[mderby]

A bit tricky

Hi,

It seems like the table structure needs some refinement. If you really want to display such high-level data, you might want to consider creating a master table Journey Header which consist of ELR, Track ID and Overall Start and Overall End Mile. The Overall Start field will be the starting point and Overall End Mile will be the ending point of the track. This file should have a 1-to-many relationship with the Railway table.

Another way is add in two textbox A, B into the form.
In textbox A's Row Source; put this sql statement
Select Min[Start Mile].[tablename] from [tablename] where [ERL].[tablename] = 'value' and [TrackID].[tablename] = 'value'.

In textbox A's Row Source; put this sql statement
Select Max[End Mile].[tablename] from [tablename] where [ERL].[tablename] = 'value' and [TrackID].[tablename] = 'value'.

this way from the breakdown of the query result, the min (Start) value = Start Mile and max(End) value = End Mile.

Hope this help.

Regards,
Mderby

 

[Mark Richardson]

Cheers thats perfect.