Access - find most recent date in a record containing 9 dates

[Johanna]

I have a table that has 9 immunization dates in each record. I need to
capture the most recent date for a report. All the information I have found
refers to comparing dates between records, not within a single record.

 

[Wayne-I-M]

Hi Johanna

Can I suggest you split your table. It's quite simple.

1st Make a copy of your database and work on the copy (just in case)

Open the copy of your database
Copy the table and paste with a new names
tblA - tblB

Open table A and delete all the immunization dates

Open table B and delete everuthing "except" the immunization dates and the
ID field (or other primary field)
If you have used an autonumber for the primary field change the format to
Number. and remove the primary field status.
Insert a new field into table B and make it an autonumber

Close both tables and open the relationship window
Click the primary field in table A and drag it over the new field you have
just made in table B. Select - enforce referential integrity

You now have a properly contstructed table structure for both these tables.
AND you can (if you want) add as many immunization dates for each record as
you want.

You can create a main form from Table A and a sub frm from table B, etc, etc.

Back to your post - you can also select the most recent immunization date
from any record very simply by making a query with both tables in and using
=DMax("[TheDateFiedInTableB]", "[TableB]") as a criteria

Hope this helps

 

[Tony Toews [MVP]]

Can I suggest you split your table. It's quite simple.

I suspect you mean normalize your table rather than splitting.

You now have a properly contstructed table structure for both these tables.

Not really. Now you have a one to one table. I suspect you mean you
should create a table which has as a foreign key the first tables
primary key. Another field would be a foreign key to an Immunization
type table. A third field would be the date field. Then you'd need
to use nine append queries to populate this table.

Thus you'd have potentially nine times as many records in this table
as the original table.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Wayne-I-M]

Hi Tony

I suspect you mean normalize your table rather than splitting.

Like I used to say - Scusate,ma il mio Inglese fa schiffo Percio se non
ci siamo capiti, mi mandate un messagio e provero di spiegarmi meglio.
LoL

Not really. Now you have a one to one table.

I assumed that Johanna had only one table to start with - now she has 2 ?

I suspect you mean you should create a table
which has as a foreign key the first tables
primary key.

I thought I said that ?
I was giveing a step by step method rather then just a technical
explanation. I find it's better - I know prefer it when something new is
explaned that way.

Thus you'd have potentially nine times as many records in this table
as the original table.

Yes but apart from the added primary field of the new table Johanna should
have the same amount of data only now it's (your word) normalize.
I just looked this word up in my Italian / English diconary and it's not
there. I think it means the process of createing the correct relationships
between tables, etc.


So Johanna does have more records and a new table but before there was not
the possiblity to add more than 9, so i think my suggestion was correct. If
not it's still the way I would do it.

--
Wayne
Manchester, England.

 

[Johanna]

I should have mentioned that the initial table comes from another database.
I cannot modify its structure. Each time I run the Access database, it goes
out to the primary database and captures this table (along with quite a few
other tables).

I was thinking I might be able to use a maketable query that would create a
little table for one record at a time. It would get overwritten as the
report goes from one student to the next. Is that possible?

 

[Wayne-I-M]

Sorry missed this bit

Then you'd need
to use nine append queries to populate this table.

No you wouldn't. Sorry but the method I gave to Johanna was the very safe
method of creating the table "without" the need to populate the table. As it
was a "copy" of the original with most fields deleted it was already
populated with the corect field data and foreign field "providing that" the
method was follwed as I put.



--
Wayne
Manchester, England.



:
Then you'd need

 

[Douglas J. Steele]

The point is, to be properly normalized, she needs 9 rows in the second
table, not one row with 9 fields.

Your approach wouldn't correct that. As Tony says, you'd need 9 append
queries to fix it (or just 8 if you leave the first date field intact)

 

[Johanna]

As Tony says, you'd need 9 append
queries to fix it (or just 8 if you leave the first date field intact)

OK, but append queries??? I only want 9 rows in the table so that they get
overwritten for each new immunization record.

The structure is: each kid has 8-10 different immunizations, each kind of
immunization is a separate record with places for up to 9 dates. So each
time I read a new immunization record I need to re-populate that table. This
includes clearing any lines for which there are no dates in the new record.
Hope this is clear!

 

[Wayne-I-M]

Oh - I see what you mean now

I have a table that has 9 immunization dates in each record.

Should have read the properly. I went along on the assumption that johanna
had 9 records - each one with a date in.

Well spotted Tony

Johanna - ignor my post.

 

[Wayne-I-M]

Hi Johanna

Sorry about the mess-up. Here is your answer - you need to create a sub
query - so create a new query based on the table you are importing

You need at least 2 columns, Primary field, Immunization Dates Field. Use
this as the criteria for the immunization date field

In (Select Top 1 [ImmunizationDatesField] From TableName Where
[PrimaryField] =[TableName].[PrimaryField] Order By [ImmunizationDatesField]
Desc)

Change
Immunization Dates Field
Primary Field
Table Name

This will give you the top date for each record. I "Think" this is what you
are looking for

 

[Douglas J. Steele]

OK, but append queries??? I only want 9 rows in the table so that they get
overwritten for each new immunization record.

The structure is: each kid has 8-10 different immunizations, each kind of
immunization is a separate record with places for up to 9 dates. So each
time I read a new immunization record I need to re-populate that table.
This
includes clearing any lines for which there are no dates in the new
record.
Hope this is clear!

Our point is that having 9 fields that can hold dates in a single row is
wrong.

Rather than having 1 record

ChildID ImmunizationID ImmunDate1 ImmunDate2 ImmunDate3 ImmunDate4
....
123 XYZ 2002-03-22 2003-02-11
2004-03-02 2005-03-12 ...

you should have 4 records

ChildID ImmunizationID ImmunDate
123 XYZ 2002-03-22
123 XYZ 2003-02-11
123 XYZ 2004-03-02
123 XYZ 2005-03-12

That means you need to transform the existing data. You can use a UNION
query to do that:

SELECT ChildID, ImmunizationID, ImmunDate1 As ImmunDate
FROM MyTable
UNION
SELECT ChildID, ImmunizationID, ImmunDate2
FROM MyTable
UNION
SELECT ChildID, ImmunizationID, ImmunDate3
FROM MyTable
UNION
....
UNION
SELECT ChildID, ImmunizationID, ImmunDate9
FROM MyTable

In fact, that UNION query could form the basis of the only Append query
required to get the data into the properly designed table.

 

[Arvin Meyer [MVP]]

I have a table that has 9 immunization dates in each record. I need to
capture the most recent date for a report. All the information I have
found
refers to comparing dates between records, not within a single record.

Good design requires a separate record for each immunization, but since
you've already designed it, you can use something like this code from Doug
Steele:

Function MyMax(ParamArray Values()) As Variant
Dim intLoop As Integer
Dim varMax As Variant

If IsMissing(Values) Then
MyMax = Null
Else
For intLoop = LBound(Values) To UBound(Values)
If varMax < Values(intLoop) Then
varMax = Values(intLoop)
End If
Next intLoop
MyMax = varMax
End If

End Function

Then use it in a query like:

Select MyMax(Date1, Date2, ... Date9) As LastDate From TableName;

 

[David W. Fenton]

Function MyMax(ParamArray Values()) As Variant

Just a naming convention comment:

Since IIf() is Immediate If, for use in standalone expressions,
instead of code, I name my function that does the above iMax(), for
Immediate Max as opposed to the domain aggregate function DMax() or
the summary function Max().

 

[Michelle Meyer]

Johanna:

Have you tried the Max() function?

In your query do the following:

1. Bring in the name fields
2. Bring in your immunization date field
3. Go to your VIEW menu, choose TOTALS (You will see a new row appear in
your query grid called TOTAL. It appears right beneath your TABLE row).
4. In your TOTAL row do the following:
A. Make sure each of your name fields have GROUP BY.
B. Make sure your immunization field has MAX
5. Now run your query - you should get the LAST date of immunization for
each individual listed.

Hope this helps

Michelle

 

[Douglas J. Steele]

That would only work if all of her dates were in a single field (as would be
the case with the UNION query I posted earlier). Since she's got up to 9
different dates on each row, MAX won't help in this situation.

 

[Arvin Meyer [MVP]]

It's Doug's function and his name. I might have named it something like
RowMax which better describes what it does. By the use of that name, I think
that Doug probably wrote it as a generic for later user modification, but
I'm not sure.