misunderstanding Dlookup function

[stuart_roberts]

Hi all, i am new to access, but am very interested in using it.




I have been trying to use Dlookup function but i think i have misunderstood its function, i am trying to use a field in a table as a reference in another field in the same table to pull data from another table so :-

Exam Data Vehicle Data

vehicle_IDFK vehicle_ID
V1 LastExam
V2
V2
LastExamV1
LastExamV2
LastExamV3


what i am trying to do is in "[Exam Data]", "LastExamV1" is use "[Exam Data], "V1", to reference from "[Vehicle Data]", "LastExam", and pull data so it shows the latest data for that vehicle.


i thought Dlookup worked in the same fashion as Vlookup in Excel, but i seem to be not having much joy, any assistance would be very greatful.

[orange]

In plain English, what is the "business" that you are trying to support with such a database?
Simple, plain English --no jargon.

[stuart_roberts]

hi thanks for the reply.


i am trying to monitor exam mileages for trains, these trains are connected together and run around in threes, so if something happens when the train is in service, i need to be able to see when that train was last on exam, ie V1,V2,V3

[orange]

to monitor exam mileages for trains

??? That didn't help me, but hopefully someone else understands???
You want to try again to describe it so an 8 year old would understand?

[stuart_roberts]

urm ok

i am monitoring trains on a railway, when these trains have a problem i have to log an entry of the failure, these trains run around connected together in threes, so there is three trains connects, ie, train 1, train 2 and train 3, these train numbers are captured in Table [Exam Data] in fields "V1, V2, V3", in this Table there are fields called "LastExamV1", LastExamV2, LastExamV3, in these fields i wish to enter data from Table [Train Data], which will be referenced from fields[Exam Data] V1, V2, V3, the data will be pulled from Table [Vehicle Data]

Exam Data
Vehicle_IDFK
V1
V2
V3
LastExamV1
LastExamV2
LastExamV3

Vehicle Data
vehicle_ID
VehicleNumber
LastExam

[stuart_roberts]

ok

i have created a query, accessing multiple tables, thought this might help, but still have the same issue,

[orange]

Is a Train an engine/locomotive? as compared to a freight or passenger car?

3 of these "trains" are linked together as a unit and "run around".
There is a finite list of Failures that may happen to a "Train".
"Trains" are "examined" from time to time.
"Failures" are recorded in a "log"


If you have Trains (train1, Train 2..... Train 99), can they be combined in a unit of 3 in any combination? (1,3, 67, or 63,2,14...). Is this combination important? Why?

[stuart_roberts]

Train = passenger train

3 trains connected together as a unit and "run around"
yes a finite list of failures, that may happen to a train (there is a table for this)
yes trains are examined from time to time, this data is held in table "ExamData"
Yes failures are recorded in a "log

Yes they can be combined in any way, and no this is not important, onyl the vehicles/trains that are in the combination

[aytee111]

Dlookup is used to get a value from a table/query with optional criteria.

e.g. Dlookup("Max(fieldname)","table/query name","SomeField='value'")

It is the same as a query, so...using this example, create a query with those two fields - fieldname and somefield. Use the Totals icon to do grouping, this will enable you to use words like "Max" or however you are going to find the last value. Then use the "Where" to specify the value (you can have as many criteria fields as you want). When you have exactly what you need from this query, showing only the one field, you can translate it into the DLookup.

[Micron]

Not sure I'd say it's the same as a query since DLookup cannot return more than one field value from one row, and there is no capability within it to join tables. For that, you'd have to do DLookup on a query, as mentioned. If aytee111's post doesn't help, post back with information that makes more sense.
"[Exam Data]", "LastExamV1" is use "[Exam Data], "V1", to reference from "[Vehicle Data]", "LastExam",
Your example data and what we might think are field and table names is jumbled and doesn't jive with your relationship picture.
Or look here, specifically at Domain and Aggregate Functions for examples on how DLookup works and how to use it.