Select next SampleID by Test Age - DMax?

[NAT]

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 - Below samples are in group 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.



I can get the max by using DMax: DMax("SampleTestAge","TblSample","[GroupID]=" & [GroupID]) but not sure how to tell it to look at the result prior so I can work out the growth rate.

Any help would be very much appreciated

[ItsMe]

When I encounter hurdles like these, I turn to the query designer. I will start to build queries that provide me with the desired results. The most difficult part is to distinguish a small section of the problem and resolve small sections, one at a time. The big picture will always cloud things for us.

Try to set aside the working example you have. Imagine the only problem is querying the data relative to 14 days. You should already have the means to do this in the working example you set aside. Extract that '14 days' part and that part only. Using Notepad and a text file, make notes. Note the value 14. Make notes of valid key values, like a valid GroupID and SampleID.

Start building some SQL that retrieves a valid Group and sample that is relative to 14. Use the Query Designer to create a new Query Object and hard card criteria for the GroupID and the SampleID, as well as the magic value, 14. When I use the query designer as a scratch pad, I will sometimes need to save the query. When I do this, I use a special naming convention to avoid confusing these 'scratch pad objects' with production objects. I use the prefix A_A_.

After hardcoding some criteria, you should have something similar to your working example that you set aside earlier. The main difference being it retrieves an isolated entity or Tuple. Now, you can expand upon this by including another Tuple, or group of fields. Use a similar process to isolate 7 or maybe hardcode 28. Choose one of the two, maybe 7, and see if you can get some working queries. Maybe you will need to use a subquery. It is OK to build more than one query object and compare the SQL. Determine the best way to merge or JOIN them.

After you are able to retrieve the desired subsets, revisit the working example you set aside earlier. Now you will need to find something else. You will need to determine an Event. What you will be doing is moving from the data side to the application side. With your SQL in hand, determine which Event will command retrieval of the data.

[Micron]

I might have a suggestion for a subquery but don't understand your date relationships. I don't get why there are sample date/time fields in tblGroup as well as tblSample. If they are directly related, then I believe the design is wrong. I'm thinking there should be one fixed create date for a sample grouping and sample dates for the samples. The sample dates should come from tblSample only, by way of group id. It might be possible to get the Max date of a sample that is less than the one before it within a group regardless.

[InsuranceGuy]

Assuming Strength is stored in "TestedStrength", try this as a field definition:

IIF([SampleTestAge]>7,dlookup("TestedStrength",""TblSample","[GroupID]=" & [GroupID] & " AND [SampleTestAge]=" & [SampleTestAge]/2),null)

This takes advantage of the numeric pattern of 7, 14, 28 being multiples of 2. The previous sample, if it exists, will be the current sample test age divided by 2 with the same groupID. Within that record, you want the TestedStrength field.

Always interested in the concrete business. My father in law is a concrete cutter in California. If I can be of further assistance, please let me know.

Cheers,


Jeff

[InsuranceGuy]

I missed a paren and updated the code above.