Duplicate Record in Query then Report

[mguffey5]

I have a database that has multiple relationships such as the following

Main database is called cadets2 and it has relationships to the following:
PFA Data
PDT Data
FTU Data
AFOQT Data

Now the main data entry is pulled from cadets2 and I enter most of my data
on that form and its stored in that table and queries are pulled and reports
generated correctly.

The problem I'm running into is this. The above 4 listed tables that have
relationships to the cadets 2 table also store data that I need on some
reports, and the data stored in these tables is entered through sub-forms on
the main data entry form for the database.

My problem is as follows, all 4 of those tables will have multiple or
duplicate records for each cadet but I only need the most current one to show
in the query that is linked to the report for example in AFOQT Data a Cadet
takes this test and we get their scores and enter that information on the
sub-form so it is stored in the AFOQT Data table, but that cadet failed the
1st time so they have to retake the test 6 months down the road, after the
cadet takes the test a 2nd time we enter that data and they pass. How do I
get the query and report to only show the most current data be a fail or pass.

The table for AFOQT has the following cells:

Last Name
SSAN
AFOQT Pilot
AFOQT CSO
AFOQT AA
AFOQT Verbal
AFOQT Quantative
Pilot Qualified
CSO Qualified
2nd Failure
AFOQT Date
PIlot CSO Totals
Status

I forgot to mention that the relationship link to all the tables above are
Last Name and SSAN.

For the PFA Data I have the query to the report only looking for PFA's given
between specific dates, but the problem with the AFOQT Data table is we have
cadets for 4 - 5 years and they may have taken the test 4-5 years ago and
maybe twice and I only need the most current test to show up, and not
duplicate all of the cadets information in the query which then causes a
duplicate record to appear in the report of everything on the cadet.

Hopefully this helps.

v/r
Matt

 

[Allen Browne]

Use a Totals query to get the most recent date per cadet:

1. Create a new query using the AFOQT table.

2. Depress the Total button on the toolbar in query design.
Access adds a Total row the the query design grid.

3. In the total row under [Last name] and [SSAN], accept:
Group By

4. In the total row under the date field, choose:
Max

5. Test: it returns only the most recent date per person. Save as (say)
qryAFOQT_Last and close the query.

6. Create a query using your Cadets2 table, and qryAFOQT_Last as input
'tables.' Join them on [Last name] and [SSAN]. Then add table AFOQT. Join
the table to qryAFOQT_Last on [Last name] and [SSAN] and MaxOfAFOQTDate.
This query returns full details of the most recent AFOQT record for each
cadet.

7. Repeat for your other tables.

To get this working correctly, you need to ensure that the cadet does not
have 2 entries in the AFOQT table with the same date. If they do, the
concept of 'the most recent one' becomes undefined. You may therefore want
to design your table so it has:
- An AutoNumber primary key. This makes it easy to refer to the record.

- A CadetID foreign key, that relates to the primary key of your Cadet2
table. This will simplify your joins. Set the field's Required property to
Yes, so you can't have an AFOQT entry with no cadet specified.

- The date/time field (I prefer without the space, so you don't have to keep
adding the square brackets around the [AFOQT Date] name), also marked as
Required.

- A unique index on the combination of CadetID+AFOQTDate, so you can always
tell which is the most recent entry. (If you are not sure how to make such
an index, post back.)

Similarly for your other tables.

 

[mguffey5]

I get instructions 1, 2, 4 and 5, but #3 has me confused as I can't put
anything in the Total row as it automatically as a drop down menu with 2
options (none or count), I can't put accept: Group By in either box for Last
Name or SSAN.

Use a Totals query to get the most recent date per cadet:

1. Create a new query using the AFOQT table.

2. Depress the Total button on the toolbar in query design.
Access adds a Total row the the query design grid.

3. In the total row under [Last name] and [SSAN], accept:
Group By

4. In the total row under the date field, choose:
Max

5. Test: it returns only the most recent date per person. Save as (say)
qryAFOQT_Last and close the query.

6. Create a query using your Cadets2 table, and qryAFOQT_Last as input
'tables.' Join them on [Last name] and [SSAN]. Then add table AFOQT. Join
the table to qryAFOQT_Last on [Last name] and [SSAN] and MaxOfAFOQTDate.
This query returns full details of the most recent AFOQT record for each
cadet.

7. Repeat for your other tables.

To get this working correctly, you need to ensure that the cadet does not
have 2 entries in the AFOQT table with the same date. If they do, the
concept of 'the most recent one' becomes undefined. You may therefore want
to design your table so it has:
- An AutoNumber primary key. This makes it easy to refer to the record.

- A CadetID foreign key, that relates to the primary key of your Cadet2
table. This will simplify your joins. Set the field's Required property to
Yes, so you can't have an AFOQT entry with no cadet specified.

- The date/time field (I prefer without the space, so you don't have to keep
adding the square brackets around the [AFOQT Date] name), also marked as
Required.

- A unique index on the combination of CadetID+AFOQTDate, so you can always
tell which is the most recent entry. (If you are not sure how to make such
an index, post back.)

Similarly for your other tables.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a database that has multiple relationships such as the following

Main database is called cadets2 and it has relationships to the following:
PFA Data
PDT Data
FTU Data
AFOQT Data

Now the main data entry is pulled from cadets2 and I enter most of my data
on that form and its stored in that table and queries are pulled and
reports
generated correctly.

The problem I'm running into is this. The above 4 listed tables that have
relationships to the cadets 2 table also store data that I need on some
reports, and the data stored in these tables is entered through sub-forms
on
the main data entry form for the database.

My problem is as follows, all 4 of those tables will have multiple or
duplicate records for each cadet but I only need the most current one to
show
in the query that is linked to the report for example in AFOQT Data a
Cadet
takes this test and we get their scores and enter that information on the
sub-form so it is stored in the AFOQT Data table, but that cadet failed
the
1st time so they have to retake the test 6 months down the road, after the
cadet takes the test a 2nd time we enter that data and they pass. How do
I
get the query and report to only show the most current data be a fail or
pass.

The table for AFOQT has the following cells:

Last Name
SSAN
AFOQT Pilot
AFOQT CSO
AFOQT AA
AFOQT Verbal
AFOQT Quantative
Pilot Qualified
CSO Qualified
2nd Failure
AFOQT Date
PIlot CSO Totals
Status

I forgot to mention that the relationship link to all the tables above are
Last Name and SSAN.

For the PFA Data I have the query to the report only looking for PFA's
given
between specific dates, but the problem with the AFOQT Data table is we
have
cadets for 4 - 5 years and they may have taken the test 4-5 years ago and
maybe twice and I only need the most current test to show up, and not
duplicate all of the cadets information in the query which then causes a
duplicate record to appear in the report of everything on the cadet.

Hopefully this helps.

v/r
Matt

 

[mguffey5]

Actually I figured it out, its working perfectly...Thank-you!!!

I get instructions 1, 2, 4 and 5, but #3 has me confused as I can't put
anything in the Total row as it automatically as a drop down menu with 2
options (none or count), I can't put accept: Group By in either box for Last
Name or SSAN.

Use a Totals query to get the most recent date per cadet:

1. Create a new query using the AFOQT table.

2. Depress the Total button on the toolbar in query design.
Access adds a Total row the the query design grid.

3. In the total row under [Last name] and [SSAN], accept:
Group By

4. In the total row under the date field, choose:
Max

5. Test: it returns only the most recent date per person. Save as (say)
qryAFOQT_Last and close the query.

6. Create a query using your Cadets2 table, and qryAFOQT_Last as input
'tables.' Join them on [Last name] and [SSAN]. Then add table AFOQT. Join
the table to qryAFOQT_Last on [Last name] and [SSAN] and MaxOfAFOQTDate.
This query returns full details of the most recent AFOQT record for each
cadet.

7. Repeat for your other tables.

To get this working correctly, you need to ensure that the cadet does not
have 2 entries in the AFOQT table with the same date. If they do, the
concept of 'the most recent one' becomes undefined. You may therefore want
to design your table so it has:
- An AutoNumber primary key. This makes it easy to refer to the record.

- A CadetID foreign key, that relates to the primary key of your Cadet2
table. This will simplify your joins. Set the field's Required property to
Yes, so you can't have an AFOQT entry with no cadet specified.

- The date/time field (I prefer without the space, so you don't have to keep
adding the square brackets around the [AFOQT Date] name), also marked as
Required.

- A unique index on the combination of CadetID+AFOQTDate, so you can always
tell which is the most recent entry. (If you are not sure how to make such
an index, post back.)

Similarly for your other tables.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a database that has multiple relationships such as the following

Main database is called cadets2 and it has relationships to the following:
PFA Data
PDT Data
FTU Data
AFOQT Data

Now the main data entry is pulled from cadets2 and I enter most of my data
on that form and its stored in that table and queries are pulled and
reports
generated correctly.

The problem I'm running into is this. The above 4 listed tables that have
relationships to the cadets 2 table also store data that I need on some
reports, and the data stored in these tables is entered through sub-forms
on
the main data entry form for the database.

My problem is as follows, all 4 of those tables will have multiple or
duplicate records for each cadet but I only need the most current one to
show
in the query that is linked to the report for example in AFOQT Data a
Cadet
takes this test and we get their scores and enter that information on the
sub-form so it is stored in the AFOQT Data table, but that cadet failed
the
1st time so they have to retake the test 6 months down the road, after the
cadet takes the test a 2nd time we enter that data and they pass. How do
I
get the query and report to only show the most current data be a fail or
pass.

The table for AFOQT has the following cells:

Last Name
SSAN
AFOQT Pilot
AFOQT CSO
AFOQT AA
AFOQT Verbal
AFOQT Quantative
Pilot Qualified
CSO Qualified
2nd Failure
AFOQT Date
PIlot CSO Totals
Status

I forgot to mention that the relationship link to all the tables above are
Last Name and SSAN.

For the PFA Data I have the query to the report only looking for PFA's
given
between specific dates, but the problem with the AFOQT Data table is we
have
cadets for 4 - 5 years and they may have taken the test 4-5 years ago and
maybe twice and I only need the most current test to show up, and not
duplicate all of the cadets information in the query which then causes a
duplicate record to appear in the report of everything on the cadet.

Hopefully this helps.

v/r
Matt