Retrieving a value with less than/equal to date

[Markb384]

Hi guys.



I have a table of data in which I do some calculations using VBA and Dlookup and it all works very smoothly. Some of the calculations involve comparisons to baseline measurements. These are within the same table as my results but with a checkbox to indicate that they are baseline. Multiple pieces of equipment are in the same table. For instance, when simplified, my data table will look kind of like below. Currently I use DLookup with the criteria of matching equipment ID and date for the normal measurements to retrieve the measurement prior to using it within VBA.

Equipment ID	Date	Baseline	Measurement
A	5/1/2014	No	10
A	4/1/2014	Yes	10
B	3/1/2014	Yes	15
A	2/1/2014	No	9
A	1/1/2014	Yes	10

Now, if we look at the case of equipment A, if users choose the date 5/1, then I want to compare the measurement to the 4/1 baseline. However, if the user chooses to look at date 2/1, I want to compare against the 1/1. I also want to flag up if the user chooses a baseline date (e.g. 4/1) as I want to do other things in that case.

My initial thoughts are to ensure that the database is sorted chronologically by date and use DLookup with an additional clause where the date is equal or less than the date chosen by the user. I am under the belief that DLookup returns the first entry it finds that matches its criteria, so I think this should work. However, I am struggling at finding out how to sort the table and as I can't guarantee that the data will be always be entered chronologically by the user, this is quite an important part of this method. I'm also not entirely sure how efficient this actually is.

Does anyone have any ideas how to do this, or of a possibly better method I can achieve this task.

Thank you

[rpeare]

What happens if you choose the 4/1 baseline, do you want to see that 4/1 is a baseline by itself or do you want to see the 1/1 baseline measurement?

I'd handle this like this:

First, create a query that correctly pulls the record you're interested in (assuming you have one record per date as your data indicates)

SELECT tblTest.Equipment_ID, tblTest.WorkDate, tblTest.Baseline, tblTest.Measurement
FROM tblTest
WHERE (((tblTest.Equipment_ID)="A") AND ((tblTest.WorkDate)=[Enter the Date]));

This selects the Equipment item A with a specific work date (NOTE: I changed 'date' to 'workdate' to avoid the use of reserved words and I also changed EQUIPMENT ID to Equipment_ID to avoid having spaces in field names)
Name this query 'qryDataPiece'

Second, create this query:

SELECT tblTest.Equipment_ID, Max(tblTest.WorkDate) AS MRBaselineDate
FROM tblTest
GROUP BY tblTest.Equipment_ID, tblTest.Baseline
HAVING (((Max(tblTest.WorkDate))<=[Enter the Date]) AND ((tblTest.Baseline)=Yes));

Name this query qryMRBaseline, it will list the most recent baseline measurement based on the date entered by the user

Third create this query:

SELECT qryDataPiece.Equipment_ID, qryDataPiece.WorkDate, qryDataPiece.Baseline, qryDataPiece.Measurement, qryMRBaseline.MRBaselineDate, tblTest.Measurement AS BaselineMeasure
FROM (qryDataPiece LEFT JOIN qryMRBaseline ON qryDataPiece.Equipment_ID = qryMRBaseline.Equipment_ID) LEFT JOIN tblTest ON (qryMRBaseline.MRBaselineDate = tblTest.WorkDate) AND (qryMRBaseline.Equipment_ID = tblTest.Equipment_ID);

This final query will show you the data related to a specific record (again assuming records are individual by date) and it's related most recent baseline information.

[Markb384]

I have solved it (I think) using

DoCmd.OpenTable ("tableName")
DoCmd.SetOrderBy "[fieldName]"
DoCmd.Close acTable, ("tableName")

and the appropriate DLookup criteria.

Edit: As I typed my reply, your post wasn't there yet Rpeare. Thank you very much for your reply though. Once I get my head around what it is doing, I might implement it as it looks slightly more robust than my method of opening and closing the table.

[amrut]

Look for DateAdd function http://www.techonthenet.com/access/f...te/dateadd.php

[ItsMe]

I don't think I have ever used DoCmd.OpenTable

[rpeare]

I don't think you're going to get consistent results doing it the way you are particularly as your database gets larger but that's your choice of course

[Markb384]

Well, you are correct, my ingenious method fell at the first hurdle. Based on my observations, regardless of the visible order of the data within the table, it appears that DLookup is still goings through the data in order of primary key, bypassing all the subsequent baselines as it is stopping at the initial one.

[trevor40]

It's a big bowl of Alphbet soup. records are just added to the table, a Pk is used to keep them in order, but this may also be inacurate. if you delete a record it's Pk is gone and the next asigned one is last largets plus 1.

[June7]

Criteria where each record must consider value of other records in same table is never easy. Requires series of queries or nested subqueries or domain aggregate functions (which you tried with DLookup). The DLookup could probably be made to work with the correct WHERE CONDITION phrasing, but it is tricky.

Maybe a solution is somewhere in http://allenbrowne.com/subquery-01.html

[rpeare]

I wonder if he even tried my solution...

[Markb384]

I wonder if he even tried my solution...

Not yet, I've been busy with other tasks at work so this project has taken a brief back seat. So I haven't tried any solution yet and probably won't be able to until next week now.