Duplicate Record in Query then Report

[mguffey5]

I've asked this question before and got an answer from Mr. Allen Brown but
that answer doesn't seem to work, even after multiple tries and attempts to
do so.

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
Security Clearance

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 5 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.

I can't just look for a specific date period as we have cadets who have been
with us for 4 - 5 years and we need their AFOQT data (most current) to show
up on the report/query only.

Now for the PFA Data cadets are required to take the PFA once each semester
and twice during the spring semester of the sophomore year and twice during
their last semester of the graduating year. Again I only need the most
current data to show up and this is not happening in either case with Mr.
Browns recommendations as it still duplicates the record on the report
showing multiple entries on the same person with all data on them the same
except the PFA data.

Here is the previous post I had with Mr. Brown's reply:

http://www.microsoft.com/communitie...cess&mid=e7cfbd4c-d054-4da9-a6d7-4ddac6e61ba2

The database is to large to upload as its over 200MB with all the data in it
and its protected under the Privacy Act.

Hopefully this helps.

v/r
Matt

 

[Ken Sheridan]

You'll need to use subqueries in the query's WHERE clause to restrict each of
the referenced tables to the row with the latest date. So the query's WHERE
clause would be something like this:

WHERE [PFA Data].[PFA Date] =
(SELECT MAX([PFA Date])
FROM [PFA Data]
WHERE [PFA Data].[SSAN] = [Cadets 2].[SSAN])
AND [PDT Data].[PDT Date] =
(SELECT MAX([PDT Date])
FROM [PDT Data]
WHERE [PDT Data].[SSAN] = [Cadets 2].[SSAN])
AND [FTU Data].[FTU Date] =
(SELECT MAX([FTU Date])
FROM [FTU Data]
WHERE [FTU Data].[SSAN] = [Cadets 2].[SSAN])
AND [AFOQT Data].[AFOQT Date] =
(SELECT MAX([AFOQT Date])
FROM [AFOQT Data]
WHERE [AFOQT Data].[SSAN] = [Cadets 2].[SSAN])
AND [Security Clearance].[Security Clearance Date] =
(SELECT MAX([Security Clearance Date])
FROM [Security Clearance]
WHERE [Security Clearance].[SSAN] = [Cadets 2].[SSAN])

I've assumed in the above that the SSAN columns are the keys, even though
you do mention the Last Name columns in this context. If the keys are the
solely the SSAN columns then the Last Name columns in the referencing tables
are of course redundant and should be deleted as they leave the door open to
inconsistent data being entered. If the SSAN values are not unique to each
cadet, however, and the keys are composite ones each of the subqueries would
need to be correlated with the outer query on both columns, e.g. for PFA Data:

WHERE [PFA Data].[SSAN] = [Cadets 2].[SSAN]
AND [PFA Data].[Last name] = [Cadets 2].[Last Name])

Names are unreliable as parts of a key, however as they can be duplicated.
I've mentioned here before my experience at a clinic where two patients
turned up on the same day, both female, both with the same first and last
names and both with the same date of birth. As the hospital uses the
combination of these values as a supposedly unique patient identifier a
certain amount of confusion ensued. A key of a unique number is the only
reliable approach where people are concerned. I'm assuming that SSAN (Social
Security Account Number?) is such a number, akin to our National Insurance
Number, which is in fact an alphanumeric string.

Ken Sheridan
Stafford, England

 

[mguffey5]

I tried the below and I either get a blank query with nothing in it or I get
errors saying the following:

You tried to execute a query that does not include the specified expression
'SSAN' as part of an aggregate function.

And now I'm completely lost.

Matt

You'll need to use subqueries in the query's WHERE clause to restrict each of
the referenced tables to the row with the latest date. So the query's WHERE
clause would be something like this:

WHERE [PFA Data].[PFA Date] =
(SELECT MAX([PFA Date])
FROM [PFA Data]
WHERE [PFA Data].[SSAN] = [Cadets 2].[SSAN])
AND [PDT Data].[PDT Date] =
(SELECT MAX([PDT Date])
FROM [PDT Data]
WHERE [PDT Data].[SSAN] = [Cadets 2].[SSAN])
AND [FTU Data].[FTU Date] =
(SELECT MAX([FTU Date])
FROM [FTU Data]
WHERE [FTU Data].[SSAN] = [Cadets 2].[SSAN])
AND [AFOQT Data].[AFOQT Date] =
(SELECT MAX([AFOQT Date])
FROM [AFOQT Data]
WHERE [AFOQT Data].[SSAN] = [Cadets 2].[SSAN])
AND [Security Clearance].[Security Clearance Date] =
(SELECT MAX([Security Clearance Date])
FROM [Security Clearance]
WHERE [Security Clearance].[SSAN] = [Cadets 2].[SSAN])

I've assumed in the above that the SSAN columns are the keys, even though
you do mention the Last Name columns in this context. If the keys are the
solely the SSAN columns then the Last Name columns in the referencing tables
are of course redundant and should be deleted as they leave the door open to
inconsistent data being entered. If the SSAN values are not unique to each
cadet, however, and the keys are composite ones each of the subqueries would
need to be correlated with the outer query on both columns, e.g. for PFA Data:

WHERE [PFA Data].[SSAN] = [Cadets 2].[SSAN]
AND [PFA Data].[Last name] = [Cadets 2].[Last Name])

Names are unreliable as parts of a key, however as they can be duplicated.
I've mentioned here before my experience at a clinic where two patients
turned up on the same day, both female, both with the same first and last
names and both with the same date of birth. As the hospital uses the
combination of these values as a supposedly unique patient identifier a
certain amount of confusion ensued. A key of a unique number is the only
reliable approach where people are concerned. I'm assuming that SSAN (Social
Security Account Number?) is such a number, akin to our National Insurance
Number, which is in fact an alphanumeric string.

Ken Sheridan
Stafford, England

I've asked this question before and got an answer from Mr. Allen Brown but
that answer doesn't seem to work, even after multiple tries and attempts to
do so.

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
Security Clearance

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 5 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.

I can't just look for a specific date period as we have cadets who have been
with us for 4 - 5 years and we need their AFOQT data (most current) to show
up on the report/query only.

Now for the PFA Data cadets are required to take the PFA once each semester
and twice during the spring semester of the sophomore year and twice during
their last semester of the graduating year. Again I only need the most
current data to show up and this is not happening in either case with Mr.
Browns recommendations as it still duplicates the record on the report
showing multiple entries on the same person with all data on them the same
except the PFA data.

Here is the previous post I had with Mr. Brown's reply:

http://www.microsoft.com/communitie...cess&mid=e7cfbd4c-d054-4da9-a6d7-4ddac6e61ba2

The database is to large to upload as its over 200MB with all the data in it
and its protected under the Privacy Act.

Hopefully this helps.

v/r
Matt

 

[Ken Sheridan]

Post back with the complete SQL for the query. It sounds as if you might be
using aggregation operations in the outer query, probably the MAX operator.
I suspect you may now be mixing the approach suggested by Allen Brown with
that suggested by me involving the use of subqueries to restrict the outer
query to the latest dates. Using the subqueries you should not need to use
any aggregation operators at all in the outer query only in the subqueries.

Essentially you just need to join all the tables in the query and return
whatever columns you want from them. Unrestricted this will give you
multiple rows per cadet of course, but by restricting each referencing table
(i.e. all but Cadets 2) by a subquery which returns the latest date per
cadet, the query should then return only one row per cadet with the data from
each referencing table being that from the row where the date matches that
returned by the relevant subquery.

This does assume that the joins are all INNER JOINs, i.e. each referencing
table includes at least one row per cadet. If LEFT OUTER JOINs are used,
which would be necessary if any of the referencing tables does not include at
least one matching row for each cadet, then the query won't work as you can
only restrict a query in such cases on columns on the left side of the join,
i.e. columns in the Cadets 2 table. In that scenario you would have to
firstly create 5 separate queries to each return the row from each
referencing table with the latest date. Again you'd have to use a subquery
for this, e.g. for the PFA Data

SELECT *
FROM [PFA Data] AS PFA1
WHERE PFA1.[PFA Date] =
(SELECT MAX([PFA Date])
FROM [PFA Data] AS PFA2
WHERE PFA2.SSAN = PFA1.SSAN);

You'd then LEFT OUTER JOIN the Cadets 2 table to each of these 5 queries on
the SSAN columns to return the final result set for the report. You could of
course adopt this multi-query approach even if INNER JOINs are involved
rather than using subqueries to restrict a single query.

Ken Sheridan
Stafford, England

I tried the below and I either get a blank query with nothing in it or I get
errors saying the following:

You tried to execute a query that does not include the specified expression
'SSAN' as part of an aggregate function.

And now I'm completely lost.

Matt

You'll need to use subqueries in the query's WHERE clause to restrict each of
the referenced tables to the row with the latest date. So the query's WHERE
clause would be something like this:

WHERE [PFA Data].[PFA Date] =
(SELECT MAX([PFA Date])
FROM [PFA Data]
WHERE [PFA Data].[SSAN] = [Cadets 2].[SSAN])
AND [PDT Data].[PDT Date] =
(SELECT MAX([PDT Date])
FROM [PDT Data]
WHERE [PDT Data].[SSAN] = [Cadets 2].[SSAN])
AND [FTU Data].[FTU Date] =
(SELECT MAX([FTU Date])
FROM [FTU Data]
WHERE [FTU Data].[SSAN] = [Cadets 2].[SSAN])
AND [AFOQT Data].[AFOQT Date] =
(SELECT MAX([AFOQT Date])
FROM [AFOQT Data]
WHERE [AFOQT Data].[SSAN] = [Cadets 2].[SSAN])
AND [Security Clearance].[Security Clearance Date] =
(SELECT MAX([Security Clearance Date])
FROM [Security Clearance]
WHERE [Security Clearance].[SSAN] = [Cadets 2].[SSAN])

I've assumed in the above that the SSAN columns are the keys, even though
you do mention the Last Name columns in this context. If the keys are the
solely the SSAN columns then the Last Name columns in the referencing tables
are of course redundant and should be deleted as they leave the door open to
inconsistent data being entered. If the SSAN values are not unique to each
cadet, however, and the keys are composite ones each of the subqueries would
need to be correlated with the outer query on both columns, e.g. for PFA Data:

WHERE [PFA Data].[SSAN] = [Cadets 2].[SSAN]
AND [PFA Data].[Last name] = [Cadets 2].[Last Name])

Names are unreliable as parts of a key, however as they can be duplicated.
I've mentioned here before my experience at a clinic where two patients
turned up on the same day, both female, both with the same first and last
names and both with the same date of birth. As the hospital uses the
combination of these values as a supposedly unique patient identifier a
certain amount of confusion ensued. A key of a unique number is the only
reliable approach where people are concerned. I'm assuming that SSAN (Social
Security Account Number?) is such a number, akin to our National Insurance
Number, which is in fact an alphanumeric string.

Ken Sheridan
Stafford, England

I've asked this question before and got an answer from Mr. Allen Brown but
that answer doesn't seem to work, even after multiple tries and attempts to
do so.

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
Security Clearance

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 5 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.

I can't just look for a specific date period as we have cadets who have been
with us for 4 - 5 years and we need their AFOQT data (most current) to show
up on the report/query only.

Now for the PFA Data cadets are required to take the PFA once each semester
and twice during the spring semester of the sophomore year and twice during
their last semester of the graduating year. Again I only need the most
current data to show up and this is not happening in either case with Mr.
Browns recommendations as it still duplicates the record on the report
showing multiple entries on the same person with all data on them the same
except the PFA data.

Here is the previous post I had with Mr. Brown's reply:

http://www.microsoft.com/communitie...cess&mid=e7cfbd4c-d054-4da9-a6d7-4ddac6e61ba2

The database is to large to upload as its over 200MB with all the data in it
and its protected under the Privacy Act.

Hopefully this helps.

v/r
Matt

 

[mguffey5]

Sorry it took a couple of days to respond but its been busy at work. Here is
the complete SQL from the PFA Query that I use:

SELECT Cadets2.[AS Year], [PFA Data].[Last Name], Max([PFA Data].[PFA Date])
AS [MaxOfPFA Date], [PFA Data].[PFA Status], [PFA Data].[Push Ups], [PFA
Data].[Push Up Points], [PFA Data].[Sit Ups], [PFA Data].[Sit Up Points],
[PFA Data].[Run Time], [PFA Data].[Run Points], [PFA Data].Waist, [PFA
Data].[Waist Points], [PFA Data].Weight, [PFA Data].Height, [PFA Data].MAW,
Cadets2.[Body Fat Percentage], Cadets2.Birthdate, [PFA Data].SSAN
FROM Cadets2 INNER JOIN [PFA Data] ON (Cadets2.[Last Name] = [PFA
Data].[Last Name]) AND (Cadets2.SSAN = [PFA Data].SSAN)
GROUP BY Cadets2.[AS Year], [PFA Data].[Last Name], [PFA Data].[PFA Status],
[PFA Data].[Push Ups], [PFA Data].[Push Up Points], [PFA Data].[Sit Ups],
[PFA Data].[Sit Up Points], [PFA Data].[Run Time], [PFA Data].[Run Points],
[PFA Data].Waist, [PFA Data].[Waist Points], [PFA Data].Weight, [PFA
Data].Height, [PFA Data].MAW, Cadets2.[Body Fat Percentage],
Cadets2.Birthdate, [PFA Data].SSAN, Cadets2.[Membership Status]
HAVING ((((Cadets2.[AS Year])<>"Disenrolled" Or (Cadets2.[AS
Year])="Commissioned" Or (Cadets2.[AS Year])="Dropped") And (Cadets2.[AS
Year])<>"Dropped") AND ((Max([PFA Data].[PFA Date]))>#1/1/2009# And (Max([PFA
Data].[PFA Date]))<#5/8/2009#) AND (((Cadets2.[Membership
Status])<>"Commissioned" Or (Cadets2.[Membership Status])="Dropped" Or
(Cadets2.[Membership Status])="Disenrolled") And (Cadets2.[Membership
Status])<>"Dropped" And (Cadets2.[Membership Status])<>"Applicant")) OR
(((Cadets2.[AS Year])="Applicant") AND (((Cadets2.[Membership
Status])<>"Commissioned" Or (Cadets2.[Membership Status])="Dropped" Or
(Cadets2.[Membership Status])="Disenrolled") And (Cadets2.[Membership
Status])<>"Dropped" And (Cadets2.[Membership Status])<>"Applicant"));

Post back with the complete SQL for the query. It sounds as if you might be
using aggregation operations in the outer query, probably the MAX operator.
I suspect you may now be mixing the approach suggested by Allen Brown with
that suggested by me involving the use of subqueries to restrict the outer
query to the latest dates. Using the subqueries you should not need to use
any aggregation operators at all in the outer query only in the subqueries.

Essentially you just need to join all the tables in the query and return
whatever columns you want from them. Unrestricted this will give you
multiple rows per cadet of course, but by restricting each referencing table
(i.e. all but Cadets 2) by a subquery which returns the latest date per
cadet, the query should then return only one row per cadet with the data from
each referencing table being that from the row where the date matches that
returned by the relevant subquery.

This does assume that the joins are all INNER JOINs, i.e. each referencing
table includes at least one row per cadet. If LEFT OUTER JOINs are used,
which would be necessary if any of the referencing tables does not include at
least one matching row for each cadet, then the query won't work as you can
only restrict a query in such cases on columns on the left side of the join,
i.e. columns in the Cadets 2 table. In that scenario you would have to
firstly create 5 separate queries to each return the row from each
referencing table with the latest date. Again you'd have to use a subquery
for this, e.g. for the PFA Data

SELECT *
FROM [PFA Data] AS PFA1
WHERE PFA1.[PFA Date] =
(SELECT MAX([PFA Date])
FROM [PFA Data] AS PFA2
WHERE PFA2.SSAN = PFA1.SSAN);

You'd then LEFT OUTER JOIN the Cadets 2 table to each of these 5 queries on
the SSAN columns to return the final result set for the report. You could of
course adopt this multi-query approach even if INNER JOINs are involved
rather than using subqueries to restrict a single query.

Ken Sheridan
Stafford, England

I tried the below and I either get a blank query with nothing in it or I get
errors saying the following:

You tried to execute a query that does not include the specified expression
'SSAN' as part of an aggregate function.

And now I'm completely lost.

Matt

You'll need to use subqueries in the query's WHERE clause to restrict each of
the referenced tables to the row with the latest date. So the query's WHERE
clause would be something like this:

WHERE [PFA Data].[PFA Date] =
(SELECT MAX([PFA Date])
FROM [PFA Data]
WHERE [PFA Data].[SSAN] = [Cadets 2].[SSAN])
AND [PDT Data].[PDT Date] =
(SELECT MAX([PDT Date])
FROM [PDT Data]
WHERE [PDT Data].[SSAN] = [Cadets 2].[SSAN])
AND [FTU Data].[FTU Date] =
(SELECT MAX([FTU Date])
FROM [FTU Data]
WHERE [FTU Data].[SSAN] = [Cadets 2].[SSAN])
AND [AFOQT Data].[AFOQT Date] =
(SELECT MAX([AFOQT Date])
FROM [AFOQT Data]
WHERE [AFOQT Data].[SSAN] = [Cadets 2].[SSAN])
AND [Security Clearance].[Security Clearance Date] =
(SELECT MAX([Security Clearance Date])
FROM [Security Clearance]
WHERE [Security Clearance].[SSAN] = [Cadets 2].[SSAN])

I've assumed in the above that the SSAN columns are the keys, even though
you do mention the Last Name columns in this context. If the keys are the
solely the SSAN columns then the Last Name columns in the referencing tables
are of course redundant and should be deleted as they leave the door open to
inconsistent data being entered. If the SSAN values are not unique to each
cadet, however, and the keys are composite ones each of the subqueries would
need to be correlated with the outer query on both columns, e.g. for PFA Data:

WHERE [PFA Data].[SSAN] = [Cadets 2].[SSAN]
AND [PFA Data].[Last name] = [Cadets 2].[Last Name])

Names are unreliable as parts of a key, however as they can be duplicated.
I've mentioned here before my experience at a clinic where two patients
turned up on the same day, both female, both with the same first and last
names and both with the same date of birth. As the hospital uses the
combination of these values as a supposedly unique patient identifier a
certain amount of confusion ensued. A key of a unique number is the only
reliable approach where people are concerned. I'm assuming that SSAN (Social
Security Account Number?) is such a number, akin to our National Insurance
Number, which is in fact an alphanumeric string.

Ken Sheridan
Stafford, England

:

I've asked this question before and got an answer from Mr. Allen Brown but
that answer doesn't seem to work, even after multiple tries and attempts to
do so.

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
Security Clearance

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 5 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.

I can't just look for a specific date period as we have cadets who have been
with us for 4 - 5 years and we need their AFOQT data (most current) to show
up on the report/query only.

Now for the PFA Data cadets are required to take the PFA once each semester
and twice during the spring semester of the sophomore year and twice during
their last semester of the graduating year. Again I only need the most
current data to show up and this is not happening in either case with Mr.
Browns recommendations as it still duplicates the record on the report
showing multiple entries on the same person with all data on them the same
except the PFA data.

Here is the previous post I had with Mr. Brown's reply:

http://www.microsoft.com/communitie...cess&mid=e7cfbd4c-d054-4da9-a6d7-4ddac6e61ba2

The database is to large to upload as its over 200MB with all the data in it
and its protected under the Privacy Act.

Hopefully this helps.

v/r
Matt

 

[Ken Sheridan]

Try the following, in which:

1. I've removed the redundant Last Name from the join.

2. I've removed the MAX aggregation operation on the PFA date from the
outer query.

3. I've changed the HAVING clause to a WHERE clause. The former was
created by your having entered criteria in design view on grouped columns. A
HAVING clause acts on data after grouping, whereas the former acts before
grouping, which is what you want here. In design view you do this by
entering the criteria in separate columns with the 'show' check box
unchecked. I've assumed the underlying logic is correct for how you want to
restrict the query.

4. I've removed the date range from WHERE clause and included it in the
subquery. Note that as defined the range is from 1 January 2009 to 7 May
2009 inclusive. If you want to include 8 May then define the end of the
range with < #5/9/2009#

5. I've added a subquery to restrict the outer query to the latest date
within the date range per cadet.

SELECT Cadets2.[AS Year], [PFA Data].[Last Name],
[PFA Data].[PFA Date], [PFA Data].[PFA Status],
[PFA Data].[Push Ups], [PFA Data].[Push Up Points],
[PFA Data].[Sit Ups], [PFA Data].[Sit Up Points],
[PFA Data].[Run Time], [PFA Data].[Run Points],
[PFA Data].Waist, [PFA Data].[Waist Points],
[PFA Data].Weight, [PFA Data].Height, [PFA Data].MAW,
Cadets2.[Body Fat Percentage], Cadets2.Birthdate,
[PFA Data].SSAN
FROM Cadets2 INNER JOIN [PFA Data]
ON Cadets2.SSAN = [PFA Data].SSAN
WHERE ((((Cadets2.[AS Year])<>"Disenrolled"
OR (Cadets2.[AS Year])="Commissioned"
OR (Cadets2.[AS Year])="Dropped")
And (Cadets2.[AS Year])<>"Dropped")
AND (((Cadets2.[Membership Status])<>"Commissioned"
OR (Cadets2.[Membership Status])="Dropped"
OR (Cadets2.[Membership Status])="Disenrolled")
AND (Cadets2.[Membership Status])<>"Dropped"
AND (Cadets2.[Membership Status])<>"Applicant"))
OR (((Cadets2.[AS Year])="Applicant")
AND (((Cadets2.[Membership Status])<>"Commissioned"
OR (Cadets2.[Membership Status])="Dropped"
OR (Cadets2.[Membership Status])="Disenrolled")
AND (Cadets2.[Membership Status])<>"Dropped"
AND (Cadets2.[Membership Status])<>"Applicant"))
AND [PFA Data].[PFA Date] =
(SELECT MAX([PFA Date])
FROM [PFA Data]
WHERE [PFA Data].SSAN = Cadets2.SSAN
AND [PFA Data].[PFA DATE] >= #1/1/2009#);
[PFA Data].[PFA DATE] < #5/8/2009#);

To include data from the other 4 referencing tables you'd add them to the
query, joining each to Cadets 2 on SSAN, and add 4 further subqueries to
return the latest date from each in the same way as with the subquery above.

Ken Sheridan
Stafford, England

Sorry it took a couple of days to respond but its been busy at work. Here is
the complete SQL from the PFA Query that I use:

SELECT Cadets2.[AS Year], [PFA Data].[Last Name], Max([PFA Data].[PFA Date])
AS [MaxOfPFA Date], [PFA Data].[PFA Status], [PFA Data].[Push Ups], [PFA
Data].[Push Up Points], [PFA Data].[Sit Ups], [PFA Data].[Sit Up Points],
[PFA Data].[Run Time], [PFA Data].[Run Points], [PFA Data].Waist, [PFA
Data].[Waist Points], [PFA Data].Weight, [PFA Data].Height, [PFA Data].MAW,
Cadets2.[Body Fat Percentage], Cadets2.Birthdate, [PFA Data].SSAN
FROM Cadets2 INNER JOIN [PFA Data] ON (Cadets2.[Last Name] = [PFA
Data].[Last Name]) AND (Cadets2.SSAN = [PFA Data].SSAN)
GROUP BY Cadets2.[AS Year], [PFA Data].[Last Name], [PFA Data].[PFA Status],
[PFA Data].[Push Ups], [PFA Data].[Push Up Points], [PFA Data].[Sit Ups],
[PFA Data].[Sit Up Points], [PFA Data].[Run Time], [PFA Data].[Run Points],
[PFA Data].Waist, [PFA Data].[Waist Points], [PFA Data].Weight, [PFA
Data].Height, [PFA Data].MAW, Cadets2.[Body Fat Percentage],
Cadets2.Birthdate, [PFA Data].SSAN, Cadets2.[Membership Status]
HAVING ((((Cadets2.[AS Year])<>"Disenrolled" Or (Cadets2.[AS
Year])="Commissioned" Or (Cadets2.[AS Year])="Dropped") And (Cadets2.[AS
Year])<>"Dropped") AND ((Max([PFA Data].[PFA Date]))>#1/1/2009# And (Max([PFA
Data].[PFA Date]))<#5/8/2009#) AND (((Cadets2.[Membership
Status])<>"Commissioned" Or (Cadets2.[Membership Status])="Dropped" Or
(Cadets2.[Membership Status])="Disenrolled") And (Cadets2.[Membership
Status])<>"Dropped" And (Cadets2.[Membership Status])<>"Applicant")) OR
(((Cadets2.[AS Year])="Applicant") AND (((Cadets2.[Membership
Status])<>"Commissioned" Or (Cadets2.[Membership Status])="Dropped" Or
(Cadets2.[Membership Status])="Disenrolled") And (Cadets2.[Membership
Status])<>"Dropped" And (Cadets2.[Membership Status])<>"Applicant"));

Post back with the complete SQL for the query. It sounds as if you might be
using aggregation operations in the outer query, probably the MAX operator.
I suspect you may now be mixing the approach suggested by Allen Brown with
that suggested by me involving the use of subqueries to restrict the outer
query to the latest dates. Using the subqueries you should not need to use
any aggregation operators at all in the outer query only in the subqueries.

Essentially you just need to join all the tables in the query and return
whatever columns you want from them. Unrestricted this will give you
multiple rows per cadet of course, but by restricting each referencing table
(i.e. all but Cadets 2) by a subquery which returns the latest date per
cadet, the query should then return only one row per cadet with the data from
each referencing table being that from the row where the date matches that
returned by the relevant subquery.

This does assume that the joins are all INNER JOINs, i.e. each referencing
table includes at least one row per cadet. If LEFT OUTER JOINs are used,
which would be necessary if any of the referencing tables does not include at
least one matching row for each cadet, then the query won't work as you can
only restrict a query in such cases on columns on the left side of the join,
i.e. columns in the Cadets 2 table. In that scenario you would have to
firstly create 5 separate queries to each return the row from each
referencing table with the latest date. Again you'd have to use a subquery
for this, e.g. for the PFA Data

SELECT *
FROM [PFA Data] AS PFA1
WHERE PFA1.[PFA Date] =
(SELECT MAX([PFA Date])
FROM [PFA Data] AS PFA2
WHERE PFA2.SSAN = PFA1.SSAN);

You'd then LEFT OUTER JOIN the Cadets 2 table to each of these 5 queries on
the SSAN columns to return the final result set for the report. You could of
course adopt this multi-query approach even if INNER JOINs are involved
rather than using subqueries to restrict a single query.

Ken Sheridan
Stafford, England

I tried the below and I either get a blank query with nothing in it or I get
errors saying the following:

You tried to execute a query that does not include the specified expression
'SSAN' as part of an aggregate function.

And now I'm completely lost.

Matt

:

You'll need to use subqueries in the query's WHERE clause to restrict each of
the referenced tables to the row with the latest date. So the query's WHERE
clause would be something like this:

WHERE [PFA Data].[PFA Date] =
(SELECT MAX([PFA Date])
FROM [PFA Data]
WHERE [PFA Data].[SSAN] = [Cadets 2].[SSAN])
AND [PDT Data].[PDT Date] =
(SELECT MAX([PDT Date])
FROM [PDT Data]
WHERE [PDT Data].[SSAN] = [Cadets 2].[SSAN])
AND [FTU Data].[FTU Date] =
(SELECT MAX([FTU Date])
FROM [FTU Data]
WHERE [FTU Data].[SSAN] = [Cadets 2].[SSAN])
AND [AFOQT Data].[AFOQT Date] =
(SELECT MAX([AFOQT Date])
FROM [AFOQT Data]
WHERE [AFOQT Data].[SSAN] = [Cadets 2].[SSAN])
AND [Security Clearance].[Security Clearance Date] =
(SELECT MAX([Security Clearance Date])
FROM [Security Clearance]
WHERE [Security Clearance].[SSAN] = [Cadets 2].[SSAN])

I've assumed in the above that the SSAN columns are the keys, even though
you do mention the Last Name columns in this context. If the keys are the
solely the SSAN columns then the Last Name columns in the referencing tables
are of course redundant and should be deleted as they leave the door open to
inconsistent data being entered. If the SSAN values are not unique to each
cadet, however, and the keys are composite ones each of the subqueries would
need to be correlated with the outer query on both columns, e.g. for PFA Data:

WHERE [PFA Data].[SSAN] = [Cadets 2].[SSAN]
AND [PFA Data].[Last name] = [Cadets 2].[Last Name])

Names are unreliable as parts of a key, however as they can be duplicated.
I've mentioned here before my experience at a clinic where two patients
turned up on the same day, both female, both with the same first and last
names and both with the same date of birth. As the hospital uses the
combination of these values as a supposedly unique patient identifier a
certain amount of confusion ensued. A key of a unique number is the only
reliable approach where people are concerned. I'm assuming that SSAN (Social
Security Account Number?) is such a number, akin to our National Insurance
Number, which is in fact an alphanumeric string.

Ken Sheridan
Stafford, England

:

I've asked this question before and got an answer from Mr. Allen Brown but
that answer doesn't seem to work, even after multiple tries and attempts to
do so.

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
Security Clearance

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 5 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.

I can't just look for a specific date period as we have cadets who have been
with us for 4 - 5 years and we need their AFOQT data (most current) to show
up on the report/query only.

Now for the PFA Data cadets are required to take the PFA once each semester
and twice during the spring semester of the sophomore year and twice during
their last semester of the graduating year. Again I only need the most
current data to show up and this is not happening in either case with Mr.
Browns recommendations as it still duplicates the record on the report
showing multiple entries on the same person with all data on them the same
except the PFA data.

Here is the previous post I had with Mr. Brown's reply:

http://www.microsoft.com/communitie...cess&mid=e7cfbd4c-d054-4da9-a6d7-4ddac6e61ba2

The database is to large to upload as its over 200MB with all the data in it
and its protected under the Privacy Act.

Hopefully this helps.

v/r
Matt

 

[mguffey5]

Ok

I copied and pasted the below into the query replacing all the old data in
the SQL and when I try to save it I get the following:

Characters found after end of SQL statement.

When I click ok it highlights [PFA Data] on the last line of the SQL coding.

Matt

Try the following, in which:

1. I've removed the redundant Last Name from the join.

2. I've removed the MAX aggregation operation on the PFA date from the
outer query.

3. I've changed the HAVING clause to a WHERE clause. The former was
created by your having entered criteria in design view on grouped columns. A
HAVING clause acts on data after grouping, whereas the former acts before
grouping, which is what you want here. In design view you do this by
entering the criteria in separate columns with the 'show' check box
unchecked. I've assumed the underlying logic is correct for how you want to
restrict the query.

4. I've removed the date range from WHERE clause and included it in the
subquery. Note that as defined the range is from 1 January 2009 to 7 May
2009 inclusive. If you want to include 8 May then define the end of the
range with < #5/9/2009#

5. I've added a subquery to restrict the outer query to the latest date
within the date range per cadet.

SELECT Cadets2.[AS Year], [PFA Data].[Last Name],
[PFA Data].[PFA Date], [PFA Data].[PFA Status],
[PFA Data].[Push Ups], [PFA Data].[Push Up Points],
[PFA Data].[Sit Ups], [PFA Data].[Sit Up Points],
[PFA Data].[Run Time], [PFA Data].[Run Points],
[PFA Data].Waist, [PFA Data].[Waist Points],
[PFA Data].Weight, [PFA Data].Height, [PFA Data].MAW,
Cadets2.[Body Fat Percentage], Cadets2.Birthdate,
[PFA Data].SSAN
FROM Cadets2 INNER JOIN [PFA Data]
ON Cadets2.SSAN = [PFA Data].SSAN
WHERE ((((Cadets2.[AS Year])<>"Disenrolled"
OR (Cadets2.[AS Year])="Commissioned"
OR (Cadets2.[AS Year])="Dropped")
And (Cadets2.[AS Year])<>"Dropped")
AND (((Cadets2.[Membership Status])<>"Commissioned"
OR (Cadets2.[Membership Status])="Dropped"
OR (Cadets2.[Membership Status])="Disenrolled")
AND (Cadets2.[Membership Status])<>"Dropped"
AND (Cadets2.[Membership Status])<>"Applicant"))
OR (((Cadets2.[AS Year])="Applicant")
AND (((Cadets2.[Membership Status])<>"Commissioned"
OR (Cadets2.[Membership Status])="Dropped"
OR (Cadets2.[Membership Status])="Disenrolled")
AND (Cadets2.[Membership Status])<>"Dropped"
AND (Cadets2.[Membership Status])<>"Applicant"))
AND [PFA Data].[PFA Date] =
(SELECT MAX([PFA Date])
FROM [PFA Data]
WHERE [PFA Data].SSAN = Cadets2.SSAN
AND [PFA Data].[PFA DATE] >= #1/1/2009#);
[PFA Data].[PFA DATE] < #5/8/2009#);

To include data from the other 4 referencing tables you'd add them to the
query, joining each to Cadets 2 on SSAN, and add 4 further subqueries to
return the latest date from each in the same way as with the subquery above.

Ken Sheridan
Stafford, England

Sorry it took a couple of days to respond but its been busy at work. Here is
the complete SQL from the PFA Query that I use:

SELECT Cadets2.[AS Year], [PFA Data].[Last Name], Max([PFA Data].[PFA Date])
AS [MaxOfPFA Date], [PFA Data].[PFA Status], [PFA Data].[Push Ups], [PFA
Data].[Push Up Points], [PFA Data].[Sit Ups], [PFA Data].[Sit Up Points],
[PFA Data].[Run Time], [PFA Data].[Run Points], [PFA Data].Waist, [PFA
Data].[Waist Points], [PFA Data].Weight, [PFA Data].Height, [PFA Data].MAW,
Cadets2.[Body Fat Percentage], Cadets2.Birthdate, [PFA Data].SSAN
FROM Cadets2 INNER JOIN [PFA Data] ON (Cadets2.[Last Name] = [PFA
Data].[Last Name]) AND (Cadets2.SSAN = [PFA Data].SSAN)
GROUP BY Cadets2.[AS Year], [PFA Data].[Last Name], [PFA Data].[PFA Status],
[PFA Data].[Push Ups], [PFA Data].[Push Up Points], [PFA Data].[Sit Ups],
[PFA Data].[Sit Up Points], [PFA Data].[Run Time], [PFA Data].[Run Points],
[PFA Data].Waist, [PFA Data].[Waist Points], [PFA Data].Weight, [PFA
Data].Height, [PFA Data].MAW, Cadets2.[Body Fat Percentage],
Cadets2.Birthdate, [PFA Data].SSAN, Cadets2.[Membership Status]
HAVING ((((Cadets2.[AS Year])<>"Disenrolled" Or (Cadets2.[AS
Year])="Commissioned" Or (Cadets2.[AS Year])="Dropped") And (Cadets2.[AS
Year])<>"Dropped") AND ((Max([PFA Data].[PFA Date]))>#1/1/2009# And (Max([PFA
Data].[PFA Date]))<#5/8/2009#) AND (((Cadets2.[Membership
Status])<>"Commissioned" Or (Cadets2.[Membership Status])="Dropped" Or
(Cadets2.[Membership Status])="Disenrolled") And (Cadets2.[Membership
Status])<>"Dropped" And (Cadets2.[Membership Status])<>"Applicant")) OR
(((Cadets2.[AS Year])="Applicant") AND (((Cadets2.[Membership
Status])<>"Commissioned" Or (Cadets2.[Membership Status])="Dropped" Or
(Cadets2.[Membership Status])="Disenrolled") And (Cadets2.[Membership
Status])<>"Dropped" And (Cadets2.[Membership Status])<>"Applicant"));

Post back with the complete SQL for the query. It sounds as if you might be
using aggregation operations in the outer query, probably the MAX operator.
I suspect you may now be mixing the approach suggested by Allen Brown with
that suggested by me involving the use of subqueries to restrict the outer
query to the latest dates. Using the subqueries you should not need to use
any aggregation operators at all in the outer query only in the subqueries.

Essentially you just need to join all the tables in the query and return
whatever columns you want from them. Unrestricted this will give you
multiple rows per cadet of course, but by restricting each referencing table
(i.e. all but Cadets 2) by a subquery which returns the latest date per
cadet, the query should then return only one row per cadet with the data from
each referencing table being that from the row where the date matches that
returned by the relevant subquery.

This does assume that the joins are all INNER JOINs, i.e. each referencing
table includes at least one row per cadet. If LEFT OUTER JOINs are used,
which would be necessary if any of the referencing tables does not include at
least one matching row for each cadet, then the query won't work as you can
only restrict a query in such cases on columns on the left side of the join,
i.e. columns in the Cadets 2 table. In that scenario you would have to
firstly create 5 separate queries to each return the row from each
referencing table with the latest date. Again you'd have to use a subquery
for this, e.g. for the PFA Data

SELECT *
FROM [PFA Data] AS PFA1
WHERE PFA1.[PFA Date] =
(SELECT MAX([PFA Date])
FROM [PFA Data] AS PFA2
WHERE PFA2.SSAN = PFA1.SSAN);

You'd then LEFT OUTER JOIN the Cadets 2 table to each of these 5 queries on
the SSAN columns to return the final result set for the report. You could of
course adopt this multi-query approach even if INNER JOINs are involved
rather than using subqueries to restrict a single query.

Ken Sheridan
Stafford, England

:

I tried the below and I either get a blank query with nothing in it or I get
errors saying the following:

You tried to execute a query that does not include the specified expression
'SSAN' as part of an aggregate function.

And now I'm completely lost.

Matt

:

You'll need to use subqueries in the query's WHERE clause to restrict each of
the referenced tables to the row with the latest date. So the query's WHERE
clause would be something like this:

WHERE [PFA Data].[PFA Date] =
(SELECT MAX([PFA Date])
FROM [PFA Data]
WHERE [PFA Data].[SSAN] = [Cadets 2].[SSAN])
AND [PDT Data].[PDT Date] =
(SELECT MAX([PDT Date])
FROM [PDT Data]
WHERE [PDT Data].[SSAN] = [Cadets 2].[SSAN])
AND [FTU Data].[FTU Date] =
(SELECT MAX([FTU Date])
FROM [FTU Data]
WHERE [FTU Data].[SSAN] = [Cadets 2].[SSAN])
AND [AFOQT Data].[AFOQT Date] =
(SELECT MAX([AFOQT Date])
FROM [AFOQT Data]
WHERE [AFOQT Data].[SSAN] = [Cadets 2].[SSAN])
AND [Security Clearance].[Security Clearance Date] =
(SELECT MAX([Security Clearance Date])
FROM [Security Clearance]
WHERE [Security Clearance].[SSAN] = [Cadets 2].[SSAN])

I've assumed in the above that the SSAN columns are the keys, even though
you do mention the Last Name columns in this context. If the keys are the
solely the SSAN columns then the Last Name columns in the referencing tables
are of course redundant and should be deleted as they leave the door open to
inconsistent data being entered. If the SSAN values are not unique to each
cadet, however, and the keys are composite ones each of the subqueries would
need to be correlated with the outer query on both columns, e.g. for PFA Data:

WHERE [PFA Data].[SSAN] = [Cadets 2].[SSAN]
AND [PFA Data].[Last name] = [Cadets 2].[Last Name])

Names are unreliable as parts of a key, however as they can be duplicated.
I've mentioned here before my experience at a clinic where two patients
turned up on the same day, both female, both with the same first and last
names and both with the same date of birth. As the hospital uses the
combination of these values as a supposedly unique patient identifier a
certain amount of confusion ensued. A key of a unique number is the only
reliable approach where people are concerned. I'm assuming that SSAN (Social
Security Account Number?) is such a number, akin to our National Insurance
Number, which is in fact an alphanumeric string.

Ken Sheridan
Stafford, England

:

I've asked this question before and got an answer from Mr. Allen Brown but
that answer doesn't seem to work, even after multiple tries and attempts to
do so.

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
Security Clearance

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 5 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.

I can't just look for a specific date period as we have cadets who have been
with us for 4 - 5 years and we need their AFOQT data (most current) to show
up on the report/query only.

Now for the PFA Data cadets are required to take the PFA once each semester
and twice during the spring semester of the sophomore year and twice during
their last semester of the graduating year. Again I only need the most
current data to show up and this is not happening in either case with Mr.
Browns recommendations as it still duplicates the record on the report
showing multiple entries on the same person with all data on them the same
except the PFA data.

Here is the previous post I had with Mr. Brown's reply:

http://www.microsoft.com/communitie...cess&mid=e7cfbd4c-d054-4da9-a6d7-4ddac6e61ba2

The database is to large to upload as its over 200MB with all the data in it
and its protected under the Privacy Act.

Hopefully this helps.

v/r
Matt

 

[Ken Sheridan]

The subquery got rather mangled. It should have been:

AND [PFA Data].[PFA Date] =
(SELECT MAX([PFA Date])
FROM [PFA Data]
WHERE [PFA Data].SSAN = Cadets2.SSAN
AND [PFA Data].[PFA DATE] >= #1/1/2009#
AND [PFA Data].[PFA DATE] < #5/8/2009#);

I'm assuming the restriction on the date range is a genuine requirement. If
it was just an attempt to limit the results to the row with the latest date
then you won't need it, merely:

AND [PFA Data].[PFA Date] =
(SELECT MAX([PFA Date])
FROM [PFA Data]
WHERE [PFA Data].SSAN = Cadets2.SSAN);

Ken Sheridan
Stafford, England

Ok

I copied and pasted the below into the query replacing all the old data in
the SQL and when I try to save it I get the following:

Characters found after end of SQL statement.

When I click ok it highlights [PFA Data] on the last line of the SQL coding.

Matt

Try the following, in which:

1. I've removed the redundant Last Name from the join.

2. I've removed the MAX aggregation operation on the PFA date from the
outer query.

3. I've changed the HAVING clause to a WHERE clause. The former was
created by your having entered criteria in design view on grouped columns. A
HAVING clause acts on data after grouping, whereas the former acts before
grouping, which is what you want here. In design view you do this by
entering the criteria in separate columns with the 'show' check box
unchecked. I've assumed the underlying logic is correct for how you want to
restrict the query.

4. I've removed the date range from WHERE clause and included it in the
subquery. Note that as defined the range is from 1 January 2009 to 7 May
2009 inclusive. If you want to include 8 May then define the end of the
range with < #5/9/2009#

5. I've added a subquery to restrict the outer query to the latest date
within the date range per cadet.

SELECT Cadets2.[AS Year], [PFA Data].[Last Name],
[PFA Data].[PFA Date], [PFA Data].[PFA Status],
[PFA Data].[Push Ups], [PFA Data].[Push Up Points],
[PFA Data].[Sit Ups], [PFA Data].[Sit Up Points],
[PFA Data].[Run Time], [PFA Data].[Run Points],
[PFA Data].Waist, [PFA Data].[Waist Points],
[PFA Data].Weight, [PFA Data].Height, [PFA Data].MAW,
Cadets2.[Body Fat Percentage], Cadets2.Birthdate,
[PFA Data].SSAN
FROM Cadets2 INNER JOIN [PFA Data]
ON Cadets2.SSAN = [PFA Data].SSAN
WHERE ((((Cadets2.[AS Year])<>"Disenrolled"
OR (Cadets2.[AS Year])="Commissioned"
OR (Cadets2.[AS Year])="Dropped")
And (Cadets2.[AS Year])<>"Dropped")
AND (((Cadets2.[Membership Status])<>"Commissioned"
OR (Cadets2.[Membership Status])="Dropped"
OR (Cadets2.[Membership Status])="Disenrolled")
AND (Cadets2.[Membership Status])<>"Dropped"
AND (Cadets2.[Membership Status])<>"Applicant"))
OR (((Cadets2.[AS Year])="Applicant")
AND (((Cadets2.[Membership Status])<>"Commissioned"
OR (Cadets2.[Membership Status])="Dropped"
OR (Cadets2.[Membership Status])="Disenrolled")
AND (Cadets2.[Membership Status])<>"Dropped"
AND (Cadets2.[Membership Status])<>"Applicant"))
AND [PFA Data].[PFA Date] =
(SELECT MAX([PFA Date])
FROM [PFA Data]
WHERE [PFA Data].SSAN = Cadets2.SSAN
AND [PFA Data].[PFA DATE] >= #1/1/2009#);
[PFA Data].[PFA DATE] < #5/8/2009#);

To include data from the other 4 referencing tables you'd add them to the
query, joining each to Cadets 2 on SSAN, and add 4 further subqueries to
return the latest date from each in the same way as with the subquery above.

Ken Sheridan
Stafford, England

Sorry it took a couple of days to respond but its been busy at work. Here is
the complete SQL from the PFA Query that I use:

SELECT Cadets2.[AS Year], [PFA Data].[Last Name], Max([PFA Data].[PFA Date])
AS [MaxOfPFA Date], [PFA Data].[PFA Status], [PFA Data].[Push Ups], [PFA
Data].[Push Up Points], [PFA Data].[Sit Ups], [PFA Data].[Sit Up Points],
[PFA Data].[Run Time], [PFA Data].[Run Points], [PFA Data].Waist, [PFA
Data].[Waist Points], [PFA Data].Weight, [PFA Data].Height, [PFA Data].MAW,
Cadets2.[Body Fat Percentage], Cadets2.Birthdate, [PFA Data].SSAN
FROM Cadets2 INNER JOIN [PFA Data] ON (Cadets2.[Last Name] = [PFA
Data].[Last Name]) AND (Cadets2.SSAN = [PFA Data].SSAN)
GROUP BY Cadets2.[AS Year], [PFA Data].[Last Name], [PFA Data].[PFA Status],
[PFA Data].[Push Ups], [PFA Data].[Push Up Points], [PFA Data].[Sit Ups],
[PFA Data].[Sit Up Points], [PFA Data].[Run Time], [PFA Data].[Run Points],
[PFA Data].Waist, [PFA Data].[Waist Points], [PFA Data].Weight, [PFA
Data].Height, [PFA Data].MAW, Cadets2.[Body Fat Percentage],
Cadets2.Birthdate, [PFA Data].SSAN, Cadets2.[Membership Status]
HAVING ((((Cadets2.[AS Year])<>"Disenrolled" Or (Cadets2.[AS
Year])="Commissioned" Or (Cadets2.[AS Year])="Dropped") And (Cadets2.[AS
Year])<>"Dropped") AND ((Max([PFA Data].[PFA Date]))>#1/1/2009# And (Max([PFA
Data].[PFA Date]))<#5/8/2009#) AND (((Cadets2.[Membership
Status])<>"Commissioned" Or (Cadets2.[Membership Status])="Dropped" Or
(Cadets2.[Membership Status])="Disenrolled") And (Cadets2.[Membership
Status])<>"Dropped" And (Cadets2.[Membership Status])<>"Applicant")) OR
(((Cadets2.[AS Year])="Applicant") AND (((Cadets2.[Membership
Status])<>"Commissioned" Or (Cadets2.[Membership Status])="Dropped" Or
(Cadets2.[Membership Status])="Disenrolled") And (Cadets2.[Membership
Status])<>"Dropped" And (Cadets2.[Membership Status])<>"Applicant"));


:

Post back with the complete SQL for the query. It sounds as if you might be
using aggregation operations in the outer query, probably the MAX operator.
I suspect you may now be mixing the approach suggested by Allen Brown with
that suggested by me involving the use of subqueries to restrict the outer
query to the latest dates. Using the subqueries you should not need to use
any aggregation operators at all in the outer query only in the subqueries.

Essentially you just need to join all the tables in the query and return
whatever columns you want from them. Unrestricted this will give you
multiple rows per cadet of course, but by restricting each referencing table
(i.e. all but Cadets 2) by a subquery which returns the latest date per
cadet, the query should then return only one row per cadet with the data from
each referencing table being that from the row where the date matches that
returned by the relevant subquery.

This does assume that the joins are all INNER JOINs, i.e. each referencing
table includes at least one row per cadet. If LEFT OUTER JOINs are used,
which would be necessary if any of the referencing tables does not include at
least one matching row for each cadet, then the query won't work as you can
only restrict a query in such cases on columns on the left side of the join,
i.e. columns in the Cadets 2 table. In that scenario you would have to
firstly create 5 separate queries to each return the row from each
referencing table with the latest date. Again you'd have to use a subquery
for this, e.g. for the PFA Data

SELECT *
FROM [PFA Data] AS PFA1
WHERE PFA1.[PFA Date] =
(SELECT MAX([PFA Date])
FROM [PFA Data] AS PFA2
WHERE PFA2.SSAN = PFA1.SSAN);

You'd then LEFT OUTER JOIN the Cadets 2 table to each of these 5 queries on
the SSAN columns to return the final result set for the report. You could of
course adopt this multi-query approach even if INNER JOINs are involved
rather than using subqueries to restrict a single query.

Ken Sheridan
Stafford, England

:

I tried the below and I either get a blank query with nothing in it or I get
errors saying the following:

You tried to execute a query that does not include the specified expression
'SSAN' as part of an aggregate function.

And now I'm completely lost.

Matt

:

You'll need to use subqueries in the query's WHERE clause to restrict each of
the referenced tables to the row with the latest date. So the query's WHERE
clause would be something like this:

WHERE [PFA Data].[PFA Date] =
(SELECT MAX([PFA Date])
FROM [PFA Data]
WHERE [PFA Data].[SSAN] = [Cadets 2].[SSAN])
AND [PDT Data].[PDT Date] =
(SELECT MAX([PDT Date])
FROM [PDT Data]
WHERE [PDT Data].[SSAN] = [Cadets 2].[SSAN])
AND [FTU Data].[FTU Date] =
(SELECT MAX([FTU Date])
FROM [FTU Data]
WHERE [FTU Data].[SSAN] = [Cadets 2].[SSAN])
AND [AFOQT Data].[AFOQT Date] =
(SELECT MAX([AFOQT Date])
FROM [AFOQT Data]
WHERE [AFOQT Data].[SSAN] = [Cadets 2].[SSAN])
AND [Security Clearance].[Security Clearance Date] =
(SELECT MAX([Security Clearance Date])
FROM [Security Clearance]
WHERE [Security Clearance].[SSAN] = [Cadets 2].[SSAN])

I've assumed in the above that the SSAN columns are the keys, even though
you do mention the Last Name columns in this context. If the keys are the
solely the SSAN columns then the Last Name columns in the referencing tables
are of course redundant and should be deleted as they leave the door open to
inconsistent data being entered. If the SSAN values are not unique to each
cadet, however, and the keys are composite ones each of the subqueries would
need to be correlated with the outer query on both columns, e.g. for PFA Data:

WHERE [PFA Data].[SSAN] = [Cadets 2].[SSAN]
AND [PFA Data].[Last name] = [Cadets 2].[Last Name])

Names are unreliable as parts of a key, however as they can be duplicated.
I've mentioned here before my experience at a clinic where two patients
turned up on the same day, both female, both with the same first and last
names and both with the same date of birth. As the hospital uses the
combination of these values as a supposedly unique patient identifier a
certain amount of confusion ensued. A key of a unique number is the only
reliable approach where people are concerned. I'm assuming that SSAN (Social
Security Account Number?) is such a number, akin to our National Insurance
Number, which is in fact an alphanumeric string.

Ken Sheridan
Stafford, England

:

I've asked this question before and got an answer from Mr. Allen Brown but
that answer doesn't seem to work, even after multiple tries and attempts to
do so.

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
Security Clearance

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 5 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.

I can't just look for a specific date period as we have cadets who have been
with us for 4 - 5 years and we need their AFOQT data (most current) to show
up on the report/query only.

Now for the PFA Data cadets are required to take the PFA once each semester
and twice during the spring semester of the sophomore year and twice during
their last semester of the graduating year. Again I only need the most
current data to show up and this is not happening in either case with Mr.
Browns recommendations as it still duplicates the record on the report
showing multiple entries on the same person with all data on them the same
except the PFA data.

Here is the previous post I had with Mr. Brown's reply:

http://www.microsoft.com/communitie...cess&mid=e7cfbd4c-d054-4da9-a6d7-4ddac6e61ba2

The database is to large to upload as its over 200MB with all the data in it
and its protected under the Privacy Act.

Hopefully this helps.

v/r
Matt

 

[mguffey5]

Well I got rid of the error but it still shows all the fitness tests for each
cadet when I only need it to show the most current one taken.

The subquery got rather mangled. It should have been:

AND [PFA Data].[PFA Date] =
(SELECT MAX([PFA Date])
FROM [PFA Data]
WHERE [PFA Data].SSAN = Cadets2.SSAN
AND [PFA Data].[PFA DATE] >= #1/1/2009#
AND [PFA Data].[PFA DATE] < #5/8/2009#);

I'm assuming the restriction on the date range is a genuine requirement. If
it was just an attempt to limit the results to the row with the latest date
then you won't need it, merely:

AND [PFA Data].[PFA Date] =
(SELECT MAX([PFA Date])
FROM [PFA Data]
WHERE [PFA Data].SSAN = Cadets2.SSAN);

Ken Sheridan
Stafford, England

Ok

I copied and pasted the below into the query replacing all the old data in
the SQL and when I try to save it I get the following:

Characters found after end of SQL statement.

When I click ok it highlights [PFA Data] on the last line of the SQL coding.

Matt

Try the following, in which:

1. I've removed the redundant Last Name from the join.

2. I've removed the MAX aggregation operation on the PFA date from the
outer query.

3. I've changed the HAVING clause to a WHERE clause. The former was
created by your having entered criteria in design view on grouped columns. A
HAVING clause acts on data after grouping, whereas the former acts before
grouping, which is what you want here. In design view you do this by
entering the criteria in separate columns with the 'show' check box
unchecked. I've assumed the underlying logic is correct for how you want to
restrict the query.

4. I've removed the date range from WHERE clause and included it in the
subquery. Note that as defined the range is from 1 January 2009 to 7 May
2009 inclusive. If you want to include 8 May then define the end of the
range with < #5/9/2009#

5. I've added a subquery to restrict the outer query to the latest date
within the date range per cadet.

SELECT Cadets2.[AS Year], [PFA Data].[Last Name],
[PFA Data].[PFA Date], [PFA Data].[PFA Status],
[PFA Data].[Push Ups], [PFA Data].[Push Up Points],
[PFA Data].[Sit Ups], [PFA Data].[Sit Up Points],
[PFA Data].[Run Time], [PFA Data].[Run Points],
[PFA Data].Waist, [PFA Data].[Waist Points],
[PFA Data].Weight, [PFA Data].Height, [PFA Data].MAW,
Cadets2.[Body Fat Percentage], Cadets2.Birthdate,
[PFA Data].SSAN
FROM Cadets2 INNER JOIN [PFA Data]
ON Cadets2.SSAN = [PFA Data].SSAN
WHERE ((((Cadets2.[AS Year])<>"Disenrolled"
OR (Cadets2.[AS Year])="Commissioned"
OR (Cadets2.[AS Year])="Dropped")
And (Cadets2.[AS Year])<>"Dropped")
AND (((Cadets2.[Membership Status])<>"Commissioned"
OR (Cadets2.[Membership Status])="Dropped"
OR (Cadets2.[Membership Status])="Disenrolled")
AND (Cadets2.[Membership Status])<>"Dropped"
AND (Cadets2.[Membership Status])<>"Applicant"))
OR (((Cadets2.[AS Year])="Applicant")
AND (((Cadets2.[Membership Status])<>"Commissioned"
OR (Cadets2.[Membership Status])="Dropped"
OR (Cadets2.[Membership Status])="Disenrolled")
AND (Cadets2.[Membership Status])<>"Dropped"
AND (Cadets2.[Membership Status])<>"Applicant"))
AND [PFA Data].[PFA Date] =
(SELECT MAX([PFA Date])
FROM [PFA Data]
WHERE [PFA Data].SSAN = Cadets2.SSAN
AND [PFA Data].[PFA DATE] >= #1/1/2009#);
[PFA Data].[PFA DATE] < #5/8/2009#);

To include data from the other 4 referencing tables you'd add them to the
query, joining each to Cadets 2 on SSAN, and add 4 further subqueries to
return the latest date from each in the same way as with the subquery above.

Ken Sheridan
Stafford, England

:

Sorry it took a couple of days to respond but its been busy at work. Here is
the complete SQL from the PFA Query that I use:

SELECT Cadets2.[AS Year], [PFA Data].[Last Name], Max([PFA Data].[PFA Date])
AS [MaxOfPFA Date], [PFA Data].[PFA Status], [PFA Data].[Push Ups], [PFA
Data].[Push Up Points], [PFA Data].[Sit Ups], [PFA Data].[Sit Up Points],
[PFA Data].[Run Time], [PFA Data].[Run Points], [PFA Data].Waist, [PFA
Data].[Waist Points], [PFA Data].Weight, [PFA Data].Height, [PFA Data].MAW,
Cadets2.[Body Fat Percentage], Cadets2.Birthdate, [PFA Data].SSAN
FROM Cadets2 INNER JOIN [PFA Data] ON (Cadets2.[Last Name] = [PFA
Data].[Last Name]) AND (Cadets2.SSAN = [PFA Data].SSAN)
GROUP BY Cadets2.[AS Year], [PFA Data].[Last Name], [PFA Data].[PFA Status],
[PFA Data].[Push Ups], [PFA Data].[Push Up Points], [PFA Data].[Sit Ups],
[PFA Data].[Sit Up Points], [PFA Data].[Run Time], [PFA Data].[Run Points],
[PFA Data].Waist, [PFA Data].[Waist Points], [PFA Data].Weight, [PFA
Data].Height, [PFA Data].MAW, Cadets2.[Body Fat Percentage],
Cadets2.Birthdate, [PFA Data].SSAN, Cadets2.[Membership Status]
HAVING ((((Cadets2.[AS Year])<>"Disenrolled" Or (Cadets2.[AS
Year])="Commissioned" Or (Cadets2.[AS Year])="Dropped") And (Cadets2.[AS
Year])<>"Dropped") AND ((Max([PFA Data].[PFA Date]))>#1/1/2009# And (Max([PFA
Data].[PFA Date]))<#5/8/2009#) AND (((Cadets2.[Membership
Status])<>"Commissioned" Or (Cadets2.[Membership Status])="Dropped" Or
(Cadets2.[Membership Status])="Disenrolled") And (Cadets2.[Membership
Status])<>"Dropped" And (Cadets2.[Membership Status])<>"Applicant")) OR
(((Cadets2.[AS Year])="Applicant") AND (((Cadets2.[Membership
Status])<>"Commissioned" Or (Cadets2.[Membership Status])="Dropped" Or
(Cadets2.[Membership Status])="Disenrolled") And (Cadets2.[Membership
Status])<>"Dropped" And (Cadets2.[Membership Status])<>"Applicant"));


:

Post back with the complete SQL for the query. It sounds as if you might be
using aggregation operations in the outer query, probably the MAX operator.
I suspect you may now be mixing the approach suggested by Allen Brown with
that suggested by me involving the use of subqueries to restrict the outer
query to the latest dates. Using the subqueries you should not need to use
any aggregation operators at all in the outer query only in the subqueries.

Essentially you just need to join all the tables in the query and return
whatever columns you want from them. Unrestricted this will give you
multiple rows per cadet of course, but by restricting each referencing table
(i.e. all but Cadets 2) by a subquery which returns the latest date per
cadet, the query should then return only one row per cadet with the data from
each referencing table being that from the row where the date matches that
returned by the relevant subquery.

This does assume that the joins are all INNER JOINs, i.e. each referencing
table includes at least one row per cadet. If LEFT OUTER JOINs are used,
which would be necessary if any of the referencing tables does not include at
least one matching row for each cadet, then the query won't work as you can
only restrict a query in such cases on columns on the left side of the join,
i.e. columns in the Cadets 2 table. In that scenario you would have to
firstly create 5 separate queries to each return the row from each
referencing table with the latest date. Again you'd have to use a subquery
for this, e.g. for the PFA Data

SELECT *
FROM [PFA Data] AS PFA1
WHERE PFA1.[PFA Date] =
(SELECT MAX([PFA Date])
FROM [PFA Data] AS PFA2
WHERE PFA2.SSAN = PFA1.SSAN);

You'd then LEFT OUTER JOIN the Cadets 2 table to each of these 5 queries on
the SSAN columns to return the final result set for the report. You could of
course adopt this multi-query approach even if INNER JOINs are involved
rather than using subqueries to restrict a single query.

Ken Sheridan
Stafford, England

:

I tried the below and I either get a blank query with nothing in it or I get
errors saying the following:

You tried to execute a query that does not include the specified expression
'SSAN' as part of an aggregate function.

And now I'm completely lost.

Matt

:

You'll need to use subqueries in the query's WHERE clause to restrict each of
the referenced tables to the row with the latest date. So the query's WHERE
clause would be something like this:

WHERE [PFA Data].[PFA Date] =
(SELECT MAX([PFA Date])
FROM [PFA Data]
WHERE [PFA Data].[SSAN] = [Cadets 2].[SSAN])
AND [PDT Data].[PDT Date] =
(SELECT MAX([PDT Date])
FROM [PDT Data]
WHERE [PDT Data].[SSAN] = [Cadets 2].[SSAN])
AND [FTU Data].[FTU Date] =
(SELECT MAX([FTU Date])
FROM [FTU Data]
WHERE [FTU Data].[SSAN] = [Cadets 2].[SSAN])
AND [AFOQT Data].[AFOQT Date] =
(SELECT MAX([AFOQT Date])
FROM [AFOQT Data]
WHERE [AFOQT Data].[SSAN] = [Cadets 2].[SSAN])
AND [Security Clearance].[Security Clearance Date] =
(SELECT MAX([Security Clearance Date])
FROM [Security Clearance]
WHERE [Security Clearance].[SSAN] = [Cadets 2].[SSAN])

I've assumed in the above that the SSAN columns are the keys, even though
you do mention the Last Name columns in this context. If the keys are the
solely the SSAN columns then the Last Name columns in the referencing tables
are of course redundant and should be deleted as they leave the door open to
inconsistent data being entered. If the SSAN values are not unique to each
cadet, however, and the keys are composite ones each of the subqueries would
need to be correlated with the outer query on both columns, e.g. for PFA Data:

WHERE [PFA Data].[SSAN] = [Cadets 2].[SSAN]
AND [PFA Data].[Last name] = [Cadets 2].[Last Name])

Names are unreliable as parts of a key, however as they can be duplicated.
I've mentioned here before my experience at a clinic where two patients
turned up on the same day, both female, both with the same first and last
names and both with the same date of birth. As the hospital uses the
combination of these values as a supposedly unique patient identifier a
certain amount of confusion ensued. A key of a unique number is the only
reliable approach where people are concerned. I'm assuming that SSAN (Social
Security Account Number?) is such a number, akin to our National Insurance
Number, which is in fact an alphanumeric string.

Ken Sheridan
Stafford, England

:

I've asked this question before and got an answer from Mr. Allen Brown but
that answer doesn't seem to work, even after multiple tries and attempts to
do so.

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
Security Clearance

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 5 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.

I can't just look for a specific date period as we have cadets who have been
with us for 4 - 5 years and we need their AFOQT data (most current) to show
up on the report/query only.

Now for the PFA Data cadets are required to take the PFA once each semester
and twice during the spring semester of the sophomore year and twice during
their last semester of the graduating year. Again I only need the most
current data to show up and this is not happening in either case with Mr.
Browns recommendations as it still duplicates the record on the report
showing multiple entries on the same person with all data on them the same
except the PFA data.

 

[Ken Sheridan]

I don't think I can take you any further with this from here. Its now down
to you to debug it. You should by now have an understanding of the principle
involved in queries of this type, which is that the query is restricted to
the latest date per cadet either by means of a correlated subquery returning
the latest, or by joining it to a separate query which returns the latest
date per cadet. In the first case the subquery and outer query are
correlated on a unique identifier per cadet (SSAN in this case); in the
latter case the separate query is grouped on the same unique identifier and
the queries are joined on this column.

Ken Sheridan
Stafford, England

Well I got rid of the error but it still shows all the fitness tests for each
cadet when I only need it to show the most current one taken.

The subquery got rather mangled. It should have been:

AND [PFA Data].[PFA Date] =
(SELECT MAX([PFA Date])
FROM [PFA Data]
WHERE [PFA Data].SSAN = Cadets2.SSAN
AND [PFA Data].[PFA DATE] >= #1/1/2009#
AND [PFA Data].[PFA DATE] < #5/8/2009#);

I'm assuming the restriction on the date range is a genuine requirement. If
it was just an attempt to limit the results to the row with the latest date
then you won't need it, merely:

AND [PFA Data].[PFA Date] =
(SELECT MAX([PFA Date])
FROM [PFA Data]
WHERE [PFA Data].SSAN = Cadets2.SSAN);

Ken Sheridan
Stafford, England

Ok

I copied and pasted the below into the query replacing all the old data in
the SQL and when I try to save it I get the following:

Characters found after end of SQL statement.

When I click ok it highlights [PFA Data] on the last line of the SQL coding.

Matt

:

Try the following, in which:

1. I've removed the redundant Last Name from the join.

2. I've removed the MAX aggregation operation on the PFA date from the
outer query.

3. I've changed the HAVING clause to a WHERE clause. The former was
created by your having entered criteria in design view on grouped columns. A
HAVING clause acts on data after grouping, whereas the former acts before
grouping, which is what you want here. In design view you do this by
entering the criteria in separate columns with the 'show' check box
unchecked. I've assumed the underlying logic is correct for how you want to
restrict the query.

4. I've removed the date range from WHERE clause and included it in the
subquery. Note that as defined the range is from 1 January 2009 to 7 May
2009 inclusive. If you want to include 8 May then define the end of the
range with < #5/9/2009#

5. I've added a subquery to restrict the outer query to the latest date
within the date range per cadet.

SELECT Cadets2.[AS Year], [PFA Data].[Last Name],
[PFA Data].[PFA Date], [PFA Data].[PFA Status],
[PFA Data].[Push Ups], [PFA Data].[Push Up Points],
[PFA Data].[Sit Ups], [PFA Data].[Sit Up Points],
[PFA Data].[Run Time], [PFA Data].[Run Points],
[PFA Data].Waist, [PFA Data].[Waist Points],
[PFA Data].Weight, [PFA Data].Height, [PFA Data].MAW,
Cadets2.[Body Fat Percentage], Cadets2.Birthdate,
[PFA Data].SSAN
FROM Cadets2 INNER JOIN [PFA Data]
ON Cadets2.SSAN = [PFA Data].SSAN
WHERE ((((Cadets2.[AS Year])<>"Disenrolled"
OR (Cadets2.[AS Year])="Commissioned"
OR (Cadets2.[AS Year])="Dropped")
And (Cadets2.[AS Year])<>"Dropped")
AND (((Cadets2.[Membership Status])<>"Commissioned"
OR (Cadets2.[Membership Status])="Dropped"
OR (Cadets2.[Membership Status])="Disenrolled")
AND (Cadets2.[Membership Status])<>"Dropped"
AND (Cadets2.[Membership Status])<>"Applicant"))
OR (((Cadets2.[AS Year])="Applicant")
AND (((Cadets2.[Membership Status])<>"Commissioned"
OR (Cadets2.[Membership Status])="Dropped"
OR (Cadets2.[Membership Status])="Disenrolled")
AND (Cadets2.[Membership Status])<>"Dropped"
AND (Cadets2.[Membership Status])<>"Applicant"))
AND [PFA Data].[PFA Date] =
(SELECT MAX([PFA Date])
FROM [PFA Data]
WHERE [PFA Data].SSAN = Cadets2.SSAN
AND [PFA Data].[PFA DATE] >= #1/1/2009#);
[PFA Data].[PFA DATE] < #5/8/2009#);

To include data from the other 4 referencing tables you'd add them to the
query, joining each to Cadets 2 on SSAN, and add 4 further subqueries to
return the latest date from each in the same way as with the subquery above.

Ken Sheridan
Stafford, England

:

Sorry it took a couple of days to respond but its been busy at work. Here is
the complete SQL from the PFA Query that I use:

SELECT Cadets2.[AS Year], [PFA Data].[Last Name], Max([PFA Data].[PFA Date])
AS [MaxOfPFA Date], [PFA Data].[PFA Status], [PFA Data].[Push Ups], [PFA
Data].[Push Up Points], [PFA Data].[Sit Ups], [PFA Data].[Sit Up Points],
[PFA Data].[Run Time], [PFA Data].[Run Points], [PFA Data].Waist, [PFA
Data].[Waist Points], [PFA Data].Weight, [PFA Data].Height, [PFA Data].MAW,
Cadets2.[Body Fat Percentage], Cadets2.Birthdate, [PFA Data].SSAN
FROM Cadets2 INNER JOIN [PFA Data] ON (Cadets2.[Last Name] = [PFA
Data].[Last Name]) AND (Cadets2.SSAN = [PFA Data].SSAN)
GROUP BY Cadets2.[AS Year], [PFA Data].[Last Name], [PFA Data].[PFA Status],
[PFA Data].[Push Ups], [PFA Data].[Push Up Points], [PFA Data].[Sit Ups],
[PFA Data].[Sit Up Points], [PFA Data].[Run Time], [PFA Data].[Run Points],
[PFA Data].Waist, [PFA Data].[Waist Points], [PFA Data].Weight, [PFA
Data].Height, [PFA Data].MAW, Cadets2.[Body Fat Percentage],
Cadets2.Birthdate, [PFA Data].SSAN, Cadets2.[Membership Status]
HAVING ((((Cadets2.[AS Year])<>"Disenrolled" Or (Cadets2.[AS
Year])="Commissioned" Or (Cadets2.[AS Year])="Dropped") And (Cadets2.[AS
Year])<>"Dropped") AND ((Max([PFA Data].[PFA Date]))>#1/1/2009# And (Max([PFA
Data].[PFA Date]))<#5/8/2009#) AND (((Cadets2.[Membership
Status])<>"Commissioned" Or (Cadets2.[Membership Status])="Dropped" Or
(Cadets2.[Membership Status])="Disenrolled") And (Cadets2.[Membership
Status])<>"Dropped" And (Cadets2.[Membership Status])<>"Applicant")) OR
(((Cadets2.[AS Year])="Applicant") AND (((Cadets2.[Membership
Status])<>"Commissioned" Or (Cadets2.[Membership Status])="Dropped" Or
(Cadets2.[Membership Status])="Disenrolled") And (Cadets2.[Membership
Status])<>"Dropped" And (Cadets2.[Membership Status])<>"Applicant"));


:

Post back with the complete SQL for the query. It sounds as if you might be
using aggregation operations in the outer query, probably the MAX operator.
I suspect you may now be mixing the approach suggested by Allen Brown with
that suggested by me involving the use of subqueries to restrict the outer
query to the latest dates. Using the subqueries you should not need to use
any aggregation operators at all in the outer query only in the subqueries.

Essentially you just need to join all the tables in the query and return
whatever columns you want from them. Unrestricted this will give you
multiple rows per cadet of course, but by restricting each referencing table
(i.e. all but Cadets 2) by a subquery which returns the latest date per
cadet, the query should then return only one row per cadet with the data from
each referencing table being that from the row where the date matches that
returned by the relevant subquery.

This does assume that the joins are all INNER JOINs, i.e. each referencing
table includes at least one row per cadet. If LEFT OUTER JOINs are used,
which would be necessary if any of the referencing tables does not include at
least one matching row for each cadet, then the query won't work as you can
only restrict a query in such cases on columns on the left side of the join,
i.e. columns in the Cadets 2 table. In that scenario you would have to
firstly create 5 separate queries to each return the row from each
referencing table with the latest date. Again you'd have to use a subquery
for this, e.g. for the PFA Data

SELECT *
FROM [PFA Data] AS PFA1
WHERE PFA1.[PFA Date] =
(SELECT MAX([PFA Date])
FROM [PFA Data] AS PFA2
WHERE PFA2.SSAN = PFA1.SSAN);

You'd then LEFT OUTER JOIN the Cadets 2 table to each of these 5 queries on
the SSAN columns to return the final result set for the report. You could of
course adopt this multi-query approach even if INNER JOINs are involved
rather than using subqueries to restrict a single query.

Ken Sheridan
Stafford, England

:

I tried the below and I either get a blank query with nothing in it or I get
errors saying the following:

You tried to execute a query that does not include the specified expression
'SSAN' as part of an aggregate function.

And now I'm completely lost.

Matt

:

You'll need to use subqueries in the query's WHERE clause to restrict each of
the referenced tables to the row with the latest date. So the query's WHERE
clause would be something like this:

WHERE [PFA Data].[PFA Date] =
(SELECT MAX([PFA Date])
FROM [PFA Data]
WHERE [PFA Data].[SSAN] = [Cadets 2].[SSAN])
AND [PDT Data].[PDT Date] =
(SELECT MAX([PDT Date])
FROM [PDT Data]
WHERE [PDT Data].[SSAN] = [Cadets 2].[SSAN])
AND [FTU Data].[FTU Date] =
(SELECT MAX([FTU Date])
FROM [FTU Data]
WHERE [FTU Data].[SSAN] = [Cadets 2].[SSAN])
AND [AFOQT Data].[AFOQT Date] =
(SELECT MAX([AFOQT Date])
FROM [AFOQT Data]
WHERE [AFOQT Data].[SSAN] = [Cadets 2].[SSAN])
AND [Security Clearance].[Security Clearance Date] =
(SELECT MAX([Security Clearance Date])
FROM [Security Clearance]
WHERE [Security Clearance].[SSAN] = [Cadets 2].[SSAN])

I've assumed in the above that the SSAN columns are the keys, even though
you do mention the Last Name columns in this context. If the keys are the
solely the SSAN columns then the Last Name columns in the referencing tables
are of course redundant and should be deleted as they leave the door open to
inconsistent data being entered. If the SSAN values are not unique to each
cadet, however, and the keys are composite ones each of the subqueries would
need to be correlated with the outer query on both columns, e.g. for PFA Data:

WHERE [PFA Data].[SSAN] = [Cadets 2].[SSAN]
AND [PFA Data].[Last name] = [Cadets 2].[Last Name])

Names are unreliable as parts of a key, however as they can be duplicated.
I've mentioned here before my experience at a clinic where two patients
turned up on the same day, both female, both with the same first and last
names and both with the same date of birth. As the hospital uses the
combination of these values as a supposedly unique patient identifier a
certain amount of confusion ensued. A key of a unique number is the only
reliable approach where people are concerned. I'm assuming that SSAN (Social
Security Account Number?) is such a number, akin to our National Insurance
Number, which is in fact an alphanumeric string.

Ken Sheridan
Stafford, England

:

I've asked this question before and got an answer from Mr. Allen Brown but
that answer doesn't seem to work, even after multiple tries and attempts to
do so.

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
Security Clearance

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 5 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.

I can't just look for a specific date period as we have cadets who have been
with us for 4 - 5 years and we need their AFOQT data (most current) to show
up on the report/query only.

Now for the PFA Data cadets are required to take the PFA once each semester
and twice during the spring semester of the sophomore year and twice during
their last semester of the graduating year. Again I only need the most

 

[mguffey5]

Ken,

I want to thank you for all you help in this matter, I actually figured it
out. I found that there are 2 rows for the criteria when you look in the
design view of the query and the SELECT MAX info was only showing up in one
row not the other so I copied and pasted it in that row and it now works and
all my calculations are done correctly. Again thanks for helping me through
this. I have filed these notes away for future reference.

Matt

I don't think I can take you any further with this from here. Its now down
to you to debug it. You should by now have an understanding of the principle
involved in queries of this type, which is that the query is restricted to
the latest date per cadet either by means of a correlated subquery returning
the latest, or by joining it to a separate query which returns the latest
date per cadet. In the first case the subquery and outer query are
correlated on a unique identifier per cadet (SSAN in this case); in the
latter case the separate query is grouped on the same unique identifier and
the queries are joined on this column.

Ken Sheridan
Stafford, England

Well I got rid of the error but it still shows all the fitness tests for each
cadet when I only need it to show the most current one taken.

The subquery got rather mangled. It should have been:

AND [PFA Data].[PFA Date] =
(SELECT MAX([PFA Date])
FROM [PFA Data]
WHERE [PFA Data].SSAN = Cadets2.SSAN
AND [PFA Data].[PFA DATE] >= #1/1/2009#
AND [PFA Data].[PFA DATE] < #5/8/2009#);

I'm assuming the restriction on the date range is a genuine requirement. If
it was just an attempt to limit the results to the row with the latest date
then you won't need it, merely:

AND [PFA Data].[PFA Date] =
(SELECT MAX([PFA Date])
FROM [PFA Data]
WHERE [PFA Data].SSAN = Cadets2.SSAN);

Ken Sheridan
Stafford, England

:

Ok

I copied and pasted the below into the query replacing all the old data in
the SQL and when I try to save it I get the following:

Characters found after end of SQL statement.

When I click ok it highlights [PFA Data] on the last line of the SQL coding.

Matt

:

Try the following, in which:

1. I've removed the redundant Last Name from the join.

2. I've removed the MAX aggregation operation on the PFA date from the
outer query.

3. I've changed the HAVING clause to a WHERE clause. The former was
created by your having entered criteria in design view on grouped columns. A
HAVING clause acts on data after grouping, whereas the former acts before
grouping, which is what you want here. In design view you do this by
entering the criteria in separate columns with the 'show' check box
unchecked. I've assumed the underlying logic is correct for how you want to
restrict the query.

4. I've removed the date range from WHERE clause and included it in the
subquery. Note that as defined the range is from 1 January 2009 to 7 May
2009 inclusive. If you want to include 8 May then define the end of the
range with < #5/9/2009#

5. I've added a subquery to restrict the outer query to the latest date
within the date range per cadet.

SELECT Cadets2.[AS Year], [PFA Data].[Last Name],
[PFA Data].[PFA Date], [PFA Data].[PFA Status],
[PFA Data].[Push Ups], [PFA Data].[Push Up Points],
[PFA Data].[Sit Ups], [PFA Data].[Sit Up Points],
[PFA Data].[Run Time], [PFA Data].[Run Points],
[PFA Data].Waist, [PFA Data].[Waist Points],
[PFA Data].Weight, [PFA Data].Height, [PFA Data].MAW,
Cadets2.[Body Fat Percentage], Cadets2.Birthdate,
[PFA Data].SSAN
FROM Cadets2 INNER JOIN [PFA Data]
ON Cadets2.SSAN = [PFA Data].SSAN
WHERE ((((Cadets2.[AS Year])<>"Disenrolled"
OR (Cadets2.[AS Year])="Commissioned"
OR (Cadets2.[AS Year])="Dropped")
And (Cadets2.[AS Year])<>"Dropped")
AND (((Cadets2.[Membership Status])<>"Commissioned"
OR (Cadets2.[Membership Status])="Dropped"
OR (Cadets2.[Membership Status])="Disenrolled")
AND (Cadets2.[Membership Status])<>"Dropped"
AND (Cadets2.[Membership Status])<>"Applicant"))
OR (((Cadets2.[AS Year])="Applicant")
AND (((Cadets2.[Membership Status])<>"Commissioned"
OR (Cadets2.[Membership Status])="Dropped"
OR (Cadets2.[Membership Status])="Disenrolled")
AND (Cadets2.[Membership Status])<>"Dropped"
AND (Cadets2.[Membership Status])<>"Applicant"))
AND [PFA Data].[PFA Date] =
(SELECT MAX([PFA Date])
FROM [PFA Data]
WHERE [PFA Data].SSAN = Cadets2.SSAN
AND [PFA Data].[PFA DATE] >= #1/1/2009#);
[PFA Data].[PFA DATE] < #5/8/2009#);

To include data from the other 4 referencing tables you'd add them to the
query, joining each to Cadets 2 on SSAN, and add 4 further subqueries to
return the latest date from each in the same way as with the subquery above.

Ken Sheridan
Stafford, England

:

Sorry it took a couple of days to respond but its been busy at work. Here is
the complete SQL from the PFA Query that I use:

SELECT Cadets2.[AS Year], [PFA Data].[Last Name], Max([PFA Data].[PFA Date])
AS [MaxOfPFA Date], [PFA Data].[PFA Status], [PFA Data].[Push Ups], [PFA
Data].[Push Up Points], [PFA Data].[Sit Ups], [PFA Data].[Sit Up Points],
[PFA Data].[Run Time], [PFA Data].[Run Points], [PFA Data].Waist, [PFA
Data].[Waist Points], [PFA Data].Weight, [PFA Data].Height, [PFA Data].MAW,
Cadets2.[Body Fat Percentage], Cadets2.Birthdate, [PFA Data].SSAN
FROM Cadets2 INNER JOIN [PFA Data] ON (Cadets2.[Last Name] = [PFA
Data].[Last Name]) AND (Cadets2.SSAN = [PFA Data].SSAN)
GROUP BY Cadets2.[AS Year], [PFA Data].[Last Name], [PFA Data].[PFA Status],
[PFA Data].[Push Ups], [PFA Data].[Push Up Points], [PFA Data].[Sit Ups],
[PFA Data].[Sit Up Points], [PFA Data].[Run Time], [PFA Data].[Run Points],
[PFA Data].Waist, [PFA Data].[Waist Points], [PFA Data].Weight, [PFA
Data].Height, [PFA Data].MAW, Cadets2.[Body Fat Percentage],
Cadets2.Birthdate, [PFA Data].SSAN, Cadets2.[Membership Status]
HAVING ((((Cadets2.[AS Year])<>"Disenrolled" Or (Cadets2.[AS
Year])="Commissioned" Or (Cadets2.[AS Year])="Dropped") And (Cadets2.[AS
Year])<>"Dropped") AND ((Max([PFA Data].[PFA Date]))>#1/1/2009# And (Max([PFA
Data].[PFA Date]))<#5/8/2009#) AND (((Cadets2.[Membership
Status])<>"Commissioned" Or (Cadets2.[Membership Status])="Dropped" Or
(Cadets2.[Membership Status])="Disenrolled") And (Cadets2.[Membership
Status])<>"Dropped" And (Cadets2.[Membership Status])<>"Applicant")) OR
(((Cadets2.[AS Year])="Applicant") AND (((Cadets2.[Membership
Status])<>"Commissioned" Or (Cadets2.[Membership Status])="Dropped" Or
(Cadets2.[Membership Status])="Disenrolled") And (Cadets2.[Membership
Status])<>"Dropped" And (Cadets2.[Membership Status])<>"Applicant"));


:

Post back with the complete SQL for the query. It sounds as if you might be
using aggregation operations in the outer query, probably the MAX operator.
I suspect you may now be mixing the approach suggested by Allen Brown with
that suggested by me involving the use of subqueries to restrict the outer
query to the latest dates. Using the subqueries you should not need to use
any aggregation operators at all in the outer query only in the subqueries.

Essentially you just need to join all the tables in the query and return
whatever columns you want from them. Unrestricted this will give you
multiple rows per cadet of course, but by restricting each referencing table
(i.e. all but Cadets 2) by a subquery which returns the latest date per
cadet, the query should then return only one row per cadet with the data from
each referencing table being that from the row where the date matches that
returned by the relevant subquery.

This does assume that the joins are all INNER JOINs, i.e. each referencing
table includes at least one row per cadet. If LEFT OUTER JOINs are used,
which would be necessary if any of the referencing tables does not include at
least one matching row for each cadet, then the query won't work as you can
only restrict a query in such cases on columns on the left side of the join,
i.e. columns in the Cadets 2 table. In that scenario you would have to
firstly create 5 separate queries to each return the row from each
referencing table with the latest date. Again you'd have to use a subquery
for this, e.g. for the PFA Data

SELECT *
FROM [PFA Data] AS PFA1
WHERE PFA1.[PFA Date] =
(SELECT MAX([PFA Date])
FROM [PFA Data] AS PFA2
WHERE PFA2.SSAN = PFA1.SSAN);

You'd then LEFT OUTER JOIN the Cadets 2 table to each of these 5 queries on
the SSAN columns to return the final result set for the report. You could of
course adopt this multi-query approach even if INNER JOINs are involved
rather than using subqueries to restrict a single query.

Ken Sheridan
Stafford, England

:

I tried the below and I either get a blank query with nothing in it or I get
errors saying the following:

You tried to execute a query that does not include the specified expression
'SSAN' as part of an aggregate function.

And now I'm completely lost.

Matt

:

You'll need to use subqueries in the query's WHERE clause to restrict each of
the referenced tables to the row with the latest date. So the query's WHERE
clause would be something like this:

WHERE [PFA Data].[PFA Date] =
(SELECT MAX([PFA Date])
FROM [PFA Data]
WHERE [PFA Data].[SSAN] = [Cadets 2].[SSAN])
AND [PDT Data].[PDT Date] =
(SELECT MAX([PDT Date])
FROM [PDT Data]
WHERE [PDT Data].[SSAN] = [Cadets 2].[SSAN])
AND [FTU Data].[FTU Date] =
(SELECT MAX([FTU Date])
FROM [FTU Data]
WHERE [FTU Data].[SSAN] = [Cadets 2].[SSAN])
AND [AFOQT Data].[AFOQT Date] =
(SELECT MAX([AFOQT Date])
FROM [AFOQT Data]
WHERE [AFOQT Data].[SSAN] = [Cadets 2].[SSAN])
AND [Security Clearance].[Security Clearance Date] =
(SELECT MAX([Security Clearance Date])
FROM [Security Clearance]
WHERE [Security Clearance].[SSAN] = [Cadets 2].[SSAN])

I've assumed in the above that the SSAN columns are the keys, even though
you do mention the Last Name columns in this context. If the keys are the
solely the SSAN columns then the Last Name columns in the referencing tables
are of course redundant and should be deleted as they leave the door open to
inconsistent data being entered. If the SSAN values are not unique to each
cadet, however, and the keys are composite ones each of the subqueries would
need to be correlated with the outer query on both columns, e.g. for PFA Data:

WHERE [PFA Data].[SSAN] = [Cadets 2].[SSAN]
AND [PFA Data].[Last name] = [Cadets 2].[Last Name])

Names are unreliable as parts of a key, however as they can be duplicated.
I've mentioned here before my experience at a clinic where two patients
turned up on the same day, both female, both with the same first and last
names and both with the same date of birth. As the hospital uses the
combination of these values as a supposedly unique patient identifier a
certain amount of confusion ensued. A key of a unique number is the only
reliable approach where people are concerned. I'm assuming that SSAN (Social
Security Account Number?) is such a number, akin to our National Insurance
Number, which is in fact an alphanumeric string.

Ken Sheridan
Stafford, England

:

I've asked this question before and got an answer from Mr. Allen Brown but
that answer doesn't seem to work, even after multiple tries and attempts to
do so.

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
Security Clearance

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 5 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