Query based on dates ish

[BigMac4]

First off thanks to Rod on my last impossible question how he sorted it i will never understand but he did.

Well he goes with problem number 2 not sure how i can explain it, i have added a couple of images for you to look at that might help with what i am after.

I have a table (sampletaken) in this table i have
Unique ID


Code
Date
A
B
C
D
E

I have a second Table (Codechange) in this table i have
Unique ID
Code
Date
A
B
C
D
E

Right what we have is people taking samples and inputing data into Sampletaken just Date and Code, The ABCDE are ingredient totals for each Code.

We also have someone coming along every so often and changing the ingredients in Codechange they will input all fields Date,Code,ABCDE.

What i need is a form so that if someone wanted to take a sample they would enter in the code of the sample and the date and it would automatically return the current ingredient list, for example the last change in the list but before the date the sample was taken.

I have made a database where it auto returns the ingredient lists but the problem is if i was to change the ingredients on monday then sample on wednesday all the previous samples of that code would be the same ingredients. This has really got me stumped and i dont know enough about SQL/Queries to design what apears to be a massive undertaking. Now that being said im sure if i have explained this well enough (probably not) there will be someone whom will come up with the answer in about ten mins.

Cheers in advance

Mac

[pbaldy]

You can open a recordset on the codes table using an SQL statement that finds the most recent record for the given code (or the most recent previous to the date in the samples table if appropriate). Then copy values from the recordset to the form.

[June7]

Don't save the code value in SampleTaken, save the ID value. Change the Code field in SampleTaken to number type, store the CodeChange ID value in that field.

The tables will be related by the ID/Code primary and foreign key fields of the two tables. Whenever you want to see the ABCDE values associated with the SampleTaken record, build a query that joins the two tables on the ID/Code fields. No need for the ABCDE fields in SampleTaken.

If you don't use table relationship then will have to keep the ABCDE fields in SampleTaken and use VBA code to save the values to record. This does not take advantage of relational database concept.

Restricting the combobox RowSource to only the most recent record prior to sample date for each code will require subquery. Review http://allenbrowne.com/subquery-01.html
Look at the example for: Top n records per group.

I recommend more descriptive names for the date fields, such as SampleDate and CodeDate.

[BigMac4]

Any chance of an example database being made then I can look at how it works and convert it into my database please

Cheers Mac

[June7]

Not much chance. Here is a reference on database design http://forums.aspfree.com/microsoft-...es-208217.html

There are many examples. Open Access and an assortment is presented.

Attempt a design and post it for analysis.