dlookup using multiple criteria (1 Viewer)

Minty · Oct 3, 2017

Okay - so in order to clear up some of the possible issues can you clarify;

Do the axles ever move carriages ? If not then you can store the axle serial numbers against the carriage numbers and pull that information in simply from the carriage number?

Do different Odd and Even units get paired up differently ? Does this fact actually matter for the data you are looking to process?

Maybe we should work backwards - what information do you actually need to enter / store? Is the whole carriage/unit thing an unnecessary complication?

109bow · Oct 3, 2017

Thanks for your time Minty,
As wheels get too small to machine they are removed and sent away to have a new tyre fitted, much like a car tyre. In its place a another wheel is fitted which has a different serial number. The wheel that was sent away, will in the future be fitted to another train, as it would have a new tyre fitted.
At present trains are made up as 02x01, 04x03, 06x05 etc, as the trains are relatively new. In the future they will be mixed around, so may be 06x02, 02x05.
What I am looking for is to be able to relate serial number and postavewd to a specific wheel position on the train and populate the report.
The table data_tbl is so large as it contains data that is used to run other reports, not relevant to this issue. ie, calculate wheel wear and when trains are next due for machining.
Thanks

Minty · Oct 3, 2017

Okay - so to break this down into a proper set of relationships, starting at the bottom and working up, and I'm bound to have this wrong

2 Wheels belong on Axles (2 per axle and the wheels can change on the axle?)
8 Axles Belong on Carriages - and these again can change over time.
4 Carriages belong to Units (Odd or Even - I don't actually think this matters) Again these can change over time?
2 Units make up a Train. And again these change over time.

If this is correct, this is quite challenging to model, because of the change over time.

109bow · Oct 3, 2017

May have been me confusing the descriptions;
axle and wheel are the same thing. I will refer to them as axles in future.
4 axles fitted to a carriage, 16 axles fitted to an odd or even unit and 32 axles fitted to a train.
4 axles belong to a carriage, the position never changes, only the serial number of the axle.
4 carriages to a odd or even unit, the numbers of which will never change, ie 110**, 120**, 130**, 140**. A specific car of a specific train is identified as carid 110, 120, 130 or 140, followed by the odd or even unit number eg 12034.
Yes 2 units make up a train, and the combination will change.

Out curiosity, query lastturn3 gives the last turn data for all 32 axle positions of both an odd and even unit, as select from front page2. Was I going along the right lines with this query as it appears to give the data I am after.

Minty · Oct 3, 2017

Your Query does work - Change it to this ;

Code:

SELECT lastturn2.MaxOfdate, lastturn1.axleserial, lastturn1.KM, lastturn1.avepostwd, [shortno] Mod 2 AS OddNo, lastturn1.carid, lastturn1.axleposition, lastturn1.shortno
FROM lastturn2 INNER JOIN lastturn1 ON (lastturn2.MaxOfdate = lastturn1.Date) AND (lastturn2.[wheelid] = lastturn1.[wheelid]);

Then query it in another query;

Code:

SELECT lastturn3.MaxOfdate, lastturn3.axleserial, lastturn3.KM, lastturn3.avepostwd, lastturn3.shortno, lastturn3.axleposition, lastturn3.carid, lastturn3.OddNo
FROM lastturn3
WHERE (((lastturn3.axleposition)=1) AND ((lastturn3.carid)='110') AND ((lastturn3.OddNo)=No));

Now you have your look up syntax.
However I suspect given a bit of time you could query this using the correct joins, after all a DLookup is no more than another query.
This would probably make your reports either quicker or easier to build.

109bow · Oct 3, 2017

Thanks Minty,
Forgive me, do I now use DLookup in the reports to look at this second query and I will need another 31 similar queries for all 32 axle positions on a train

Minty · Oct 3, 2017

The answer to this come back to the design model I was referring to earlier.

In theory you should be able to determine which axles are on which carriage, and therefore which carriages are in which train, based on the last entry for that combination.

I which case you shouldn't need the lookups, just an extension / refinement of the queries you have started to make.

109bow · Oct 3, 2017

ok, thanks again,
will work on it, thanks

Minty · Oct 3, 2017

If you get stuck, post back.

I really would look carefully at your data structure to ensure you aren't giving yourself any future headaches.

109bow · Oct 3, 2017

Thanks for help, without I will be back, if not about this then something else!

dlookup using multiple criteria (1 Viewer)

Minty

AWF VIP

109bow

Registered User.

Minty

AWF VIP

109bow

Registered User.

Minty

AWF VIP

109bow

Registered User.

Minty

AWF VIP

109bow

Registered User.

Minty

AWF VIP

109bow

Registered User.

Similar threads

Users who are viewing this thread