How to select SampleID by Test Age?

[NTDIR82]

Hi,

I have created an access system which allows me to input, test and report test samples and cannot for the life of me figure out how to code a part which is crucial.

I enter the samples on my main test entry form. There is a sub form on the page which I enter the samples onto which usually contains between 1 and 6 samples. The main form is the group table and the sub form is the sample table so I have for example a group ID of 108 and say 3 samples within this group with their own sample ID's. Each sample has a test age assigned to it for example:

GROUP ID: 108
SAMPLE ID 100 - Test Age = 7 days (When tested I enter the strength against the sample)
SAMPLE ID 101 - Test Age = 14 days (When tested I enter the strength against the sample)
SAMPLE ID 102 - Test Age = 28 days (When tested I enter the strength against the sample)

I would like to be able to tell the sample at 28 days to look at the sample from 14 days or whatever test age is next down and see the strength so I can do some math to work our growth since the last test. I can enter sample test dates from 1 to 100 so would always need it to look at the previous test and test age...?

They are in a group so I figure I could say check SampleID x in GroupID where TestAge = < current SampleID or along them lines? Am I totally off key here? Would a loop be required? I'm kinda lost at this point...

If I need to provide ANY details for someone to help me no problem.

Any help would be very much appreciated

 

[plog]

You didn't provide table nor field names so I can't do this specifically for you, but you are on the right track. You would do this via a subquery, which determines the number of days that is the highest of all those that are lower than the current days. You would do this via a DMax:

LastRecord: DMAX("TestAge", "YourTestTable", "[TestAge]<" & [TestAge])

Then you can link that query back to YourTestTAble to find the prior record for each record.

 

[sneuberg]

For subqueries I found http://allenbrowne.com/subquery-01.html helpful

 

[NTDIR82]

Hi Plog & Sneuberg,

Thank you so much for the time you have taken to help me, very much appreciated. I am going to read the suggested on DMAX and sub queries as I would love to be able to code Access more fluently than now.

I have attached some screenshots showing the page in which the results are pulled and the table setup. I hope I'm not being too cheeky by seeing if you would be able to take another look for me with suggestions to DMax etc?

Sometimes the record may not be the last or most recent record entered. Sometimes I new record can be added a week later. This is why I thought it would have to look into the group and assess the next lowest that way perhaps?

Thanks again in anticipation guys...

 

[plog]

It seems you've simply restated your issue in your last post. My advice still stands from my initial post. Dmax and then use the criteria to determine the 'last' record you want.

 

[NTDIR82]

Hi Plog,

Thanks for the advice but I am confused. My sincere apologise for being stupid on this one: It's frustrating not being able to understand.

My screenshots using DMAX - At this time it's selecting the SampleTestAge - 1

Are you able to offer any further advice to me please?

 

[plog]

First, clear out all other tables except TblSample. Next, you need to use GroupID in your DMax criteria (you only want samples of the same group). Last, I'd rename it from LastRecord to LastAge. Because its not really the last record yet, its only returning the last age of the record.

 

[NTDIR82]

Thank you - I shall take a look at what you have suggested.