Too many records returned

[Dan]

This one may be a big favour to ask...

I want to query two tables which have a parent-child
relationship (and possibly a query, depending on what
advice you have for me). I am not giving a complete list
of the fields in each table, only the ones I think are
pertinent.

GeneralExperimentInformation
(k) ExperimentName (text)
CallusMainenanceFinished (y/n)

TransferInformation
(k) ExperimentName (text)
(k) DateTransferred (date)
NumberCallus (number)
NumberPlates (number)
ExplantsPerPlate (number)

---from the two fields immediately above I actually only
want the product of the two. I have a query that performs
this:

TransferTotals
TotalExplants (number)

I want the query to return only the record for the most
recent date for each experiment. It is returning one
record for each different number in the NumberCallus field.
For each of these records it is listing the last date for
that experiment (ie many records, one date) If I run the
query with all the fields I listed under the
TransferInformation table I get many more records.

I can't seem to make it list the full record for ONLY the
records corresponding to maxOF DateTransferred for each
ExperimentName. A few people have said this is better
suited to VB. I was hoping the SQL editor could handle it.

Once this is all done, there are two other tables (a child
of GeneralExperimentInformation, and a child of that one
that I need records from. I think I can figure that out if
you can get me past my latest problem.

If you need any more information I'm glad to give it.

Thanks in advance for any help,
Dan

 

[John Vinson]

I can't seem to make it list the full record for ONLY the
records corresponding to maxOF DateTransferred for each
ExperimentName. A few people have said this is better
suited to VB. I was hoping the SQL editor could handle it.

It should be able to, a couple of ways. The most efficient would be to
create a Totals query based on TransferInformation; Group By
ExperimentName and select Max of DateTransferred, and NO other fields.

Now create a second query joining the Experiment table to
TransferInformation by ExperimentName, and then joining this query to
TransferInformation by both ExperimentName and DateTransferred. This
will exclude any records for older dates.

 

[Guest]

Thank you very much.
That was as simple as I assumed it should have been. It
just wasn't clear to me

I'm grateful,
Dan