Return only record with most recent sample date in report

[Guest]

I've spent hours trying to figure this out through reading similar problems
and trying coding... I'm code challenged. I'm trying to generate a report on
children with developmental disabilities who have periodic tests for delays.
I want the report to print records from multiple clients, but with only the
results of the most recent testing date for each client. Testing dates can be
different for clients, so I can't just ask Access to match a specific date.

I have a table with client records - primary key is [Client ID]. This
primary key becomes the client ID in all linked tables.

I have a second table that lists scores. [Client ID] is manually entered to
match the primary key on the client record table. The same client may have
many testing dates [Sample Date].

What string of commands do I use to tell the report that when there are many
test results for one client, to only report the most recent [Sample Date]?

Thanks in advance for your help - I'm going bald pulling my hair out here.

 

[Duane Hookom]

Could you share your table and field names? We don't know if there is only a
single record per date in the "second table" or multiple :-(

 

[Guest]

Could you share your table and field names? We don't know if there is only a
single record per date in the "second table" or multiple :-(

First table: "tblECIPclients" ... On this table field [Client ID] is primary
key.

Second table: "tlbAEPS SCORES" ... On this table a [Record ID] is primary
key. I have a field in this table for [Client ID] that is linked to [Client
ID] field in "tblECIPclients".

In the AEPS SCORES table there are, for each [Sample Date], scores in 8
fields that measure developmental delays as a + or - (FM, RL, EL, GM, AAM,
VAM, COG, SH, SOC).

There are many client score results recorded in the SCORES table. Each
client has multiple records (depending on how frequently their skills were
tested) and can have very different [Sample Date] fields, but also identical
date fields. For instance:

Client 22 has scores from January 1, 2005, January 1, 2006, June 1, 2006.

Client 23, meanwhile, has scores from January 1, 2005, April 12, 2005, June
6, 2006. In table view, if I were able to show you the table sorted by
client ID, it lays out like this:

[22] [01-Jan-05] [Fm] [GM] [AAM] [VAM] [COG] [SH] [SOC]
[22] [01-Jan-06] [FM] [GM] [AAM] [VAM] [COG] [SH] [SOC]
[22] [01-Jun-06] [FM] [GM] [AAM] [VAM] [COG] [SH] [SOC]
[23] [01-Jan-05] [FM] [GM] [AAM] [VAM] [COG] [SH] [SOC]
[23] [12-Apr-05] [FM]... etc.
etc...

I want to ask ACCESS to return a report that lists all the clients and
returns the data on only their most recent [Sample Date].

The results I imagine on report would look something like:

Client 22: Sample Date: January 1, 2006: FM+, RL+, EL-.... (etc.)
Client 23: Sample Date: June 6, 2006: FM-, RL-, EL+... (etc).

Does this explain what I'm looking for of have I increased the confusion? <

 

[Duane Hookom]

Your explanation is better than your table structure. You have basically
used field names to store data in an un-normalized manner.

That said, you can create a report that sorts and groups by Client ID with a
group header. Sort next by Sample Date descending. Place all your fields in
the client header and set the detail section to invisible.
--
Duane Hookom
MS Access MVP

Could you share your table and field names? We don't know if there is
only a
single record per date in the "second table" or multiple :-(

First table: "tblECIPclients" ... On this table field [Client ID] is
primary
key.

Second table: "tlbAEPS SCORES" ... On this table a [Record ID] is primary
key. I have a field in this table for [Client ID] that is linked to
[Client
ID] field in "tblECIPclients".

In the AEPS SCORES table there are, for each [Sample Date], scores in 8
fields that measure developmental delays as a + or - (FM, RL, EL, GM, AAM,
VAM, COG, SH, SOC).

There are many client score results recorded in the SCORES table. Each
client has multiple records (depending on how frequently their skills were
tested) and can have very different [Sample Date] fields, but also
identical
date fields. For instance:

Client 22 has scores from January 1, 2005, January 1, 2006, June 1, 2006.

Client 23, meanwhile, has scores from January 1, 2005, April 12, 2005,
June
6, 2006. In table view, if I were able to show you the table sorted by
client ID, it lays out like this:

[22] [01-Jan-05] [Fm] [GM] [AAM] [VAM] [COG] [SH] [SOC]
[22] [01-Jan-06] [FM] [GM] [AAM] [VAM] [COG] [SH] [SOC]
[22] [01-Jun-06] [FM] [GM] [AAM] [VAM] [COG] [SH] [SOC]
[23] [01-Jan-05] [FM] [GM] [AAM] [VAM] [COG] [SH] [SOC]
[23] [12-Apr-05] [FM]... etc.
etc...

I want to ask ACCESS to return a report that lists all the clients and
returns the data on only their most recent [Sample Date].

The results I imagine on report would look something like:

Client 22: Sample Date: January 1, 2006: FM+, RL+, EL-.... (etc.)
Client 23: Sample Date: June 6, 2006: FM-, RL-, EL+... (etc).

Does this explain what I'm looking for of have I increased the confusion?
<

 

[Guest]

Your explanation is better than your table structure. You have basically
used field names to store data in an un-normalized manner.

That said, you can create a report that sorts and groups by Client ID with a
group header. Sort next by Sample Date descending. Place all your fields in
the client header and set the detail section to invisible.

Un-normalized. You mean I’ve made a right royal mess of it <

I based the tables on a book store model in a Microsoft manual. My [Client
ID] is their [Customer ID]. In this book store, there are only nine books
available to order. Every time the customer orders, they order the same nine
titles, but different amounts of each title. The “book titles†are the
equivalent of the FM, GM, AAM, etc. score names.

Is this my error: I’m trying to make the table of book titles do double-duty
as a record of order details. Is that where I’ve messed up? I need to create
a table that just lists the test names of the books (tests) – then another
table for order details that would list the order (test) date, Customer ID,
books ordered (test names), and number of each (test scores).

[Sample Date] [Client ID] [Test Name] [Test Score]

This seems more awkward than what I had, though… since you’d have to enter
or choose the test names each time and they are always the same … I have to
find a better model, maybe. It’s a *limited* order form – the client always
has to “buy†the same nine items, their only choice is how many of each.

Thank you for your help and patience – please forgive my novice-ness. I’ve
taken one basic course in Access and dog-eared the Microsoft manual but I’m a
PR person and admin assistant, not a programmer. I’m just doing the best I
can to come up with a way for this non-profit to generate the reports it has
to generate without manually having to go through every physical file (the
current method).

Gina

 

[Duane Hookom]

Regardless of you structure, did you try my suggestion? If so, did it work
for you?

I'm not sure which MS manual would create a table structure with books as
fields but it is wrong. I can't imagine having to add more fields, controls,
etc if you want to sell more than 9 books. The same would hold true of
tests.

I would have created tables like:

tblClients
================
cliCliID primary key
cli.... other fields

tblTests
================
tstTstID primary key
tstTitle values like FM, RL, EL, GM, AAM

tblClientSampling
================
clsClSID primary key
clsCliID link to tblClients.cliCliID
clsDate date to sampling

tblSampleTests
=================
smtSmTID primary key
smtClSID link to tblClientSampling.cslClSID
smtTstID link to tblTests.tstTstID
smtResult finally the place to enter the results

This is very close to the same structure I used at work for environmental
sampling in our factory. Someone goes to the plant floor and collects
multiple samples that are tested for a variety of "bugs" or whatever. We can
add more tests at any time.

--
Duane Hookom
MS Access MVP

Your explanation is better than your table structure. You have basically
used field names to store data in an un-normalized manner.

That said, you can create a report that sorts and groups by Client ID
with a
group header. Sort next by Sample Date descending. Place all your fields
in
the client header and set the detail section to invisible.

Un-normalized. You mean I've made a right royal mess of it <

I based the tables on a book store model in a Microsoft manual. My [Client
ID] is their [Customer ID]. In this book store, there are only nine books
available to order. Every time the customer orders, they order the same
nine
titles, but different amounts of each title. The "book titles" are the
equivalent of the FM, GM, AAM, etc. score names.

Is this my error: I'm trying to make the table of book titles do
double-duty
as a record of order details. Is that where I've messed up? I need to
create
a table that just lists the test names of the books (tests) - then another
table for order details that would list the order (test) date, Customer
ID,
books ordered (test names), and number of each (test scores).

[Sample Date] [Client ID] [Test Name] [Test Score]

This seems more awkward than what I had, though. since you'd have to enter
or choose the test names each time and they are always the same . I have
to
find a better model, maybe. It's a *limited* order form - the client
always
has to "buy" the same nine items, their only choice is how many of each.

Thank you for your help and patience - please forgive my novice-ness. I've
taken one basic course in Access and dog-eared the Microsoft manual but I'm
a
PR person and admin assistant, not a programmer. I'm just doing the best I
can to come up with a way for this non-profit to generate the reports it
has
to generate without manually having to go through every physical file (the
current method).

Gina

 

[Guest]

Regardless of you structure, did you try my suggestion? If so, did it work
for you?

I'd already tried something similar but, yes, thank you - your suggestion
returned a better-organized list of results. Still shows all the test dates
and scores (rather than just the most recent) but at least the most recent is
on top for each client.

Thank you also for the detailed list of tables you would have created. I do
understand my method is, as you said, "un-normalized." Like I said, I'm not a
programmer, my system is better than what they were doing before but I know
my understanding of how the table structures work and relate in Access is
limited.

I see I'd have to entirely rewrite the database to correct this issue.

I'm still not sure I've explained the testing accurately enough... the GM,
FM, AAM, etc. are really sub-tests of a test, so if I set up the tblTests
with each as a different test and then the clsDate in the Client Sampling
table, would that not mean I'd have to re-enter the date each time for each
sub-test?

I can't just format it to use "today's date" because there is often a lag
between when the tests are performed and when the data is entered.

The way I have it set up now, I only enter the date once for each "set" of
sub-tests.

Gina

 

[Duane Hookom]

I believe my solution should work if you place all controls in the group
header for the client. Then, make the detail section invisible.

Normalization is up to you. There are simple methods for entering the date
once in the main form and showing the subtests in a related subform. This is
done with the Link Master/Child like you would see in the Northwind
Orders<->Order Details. You don't find the OrderDate in the Order Details
table because it is stored in the related Orders table.

Appending all the subtests for a particular sampling is one line of code to
run an append query. Again, this is up to you if you want to re-do your
existing system or just tuck this away for the future.

 

[Guest]

Hi Duane,

Sorry it's taken me a week to respond on this one - been swamped. Yes,
placing the controls in the group header and making the detail section
invisible returns only the most recent score.

Thanks again for your help and patience.

Gina