Please Help with Query!

[Karen C]

I have this query:

StrSQL = "SELECT DistinctRow [Exam Schedule].EXAMLOC,
VanTestHistory.Date, VanTestHistory.Name,
VanTestHistory.CXRClass " & _
"FROM VanTestHistory INNER JOIN [Exam
Schedule] ON VanTestHistory.SSNO = [Exam Schedule].SSNO "
& _
"WHERE [Exam Schedule].EXAMLOC = '" & Me!
EXAMLOC & "' " & _
"AND VanTestHistory.Date Is Not Null " & _
"And VanTestHistory.SSNO = '" & Me!SSNO & "' "

This gives me everyone who has had a Van Test before. I
then have to check if they have had a Chest x-ray in the
last 5 years. I can do all this but I then have to take
everyone who hasn't had a chest x-ray and only take 10% of
them to print on my report that they need a chest x-ray.
I can't use the TOP 10 PERCENT in my query because my
clients wants the oldest individuals that need to have a
xray to make up that 10%. any ideas would be helpful.

Thanks

Karen

 

[Tom Ellison]

Dear Karen:

The TOP 10 PERCENT will take that proportion of the list against any
sort order you prescribe. If you have an Age column available or a
DateOfBirth column, you can simply add:

ORDER BY Age DESC
- or -
ORDER BY DateOfBirth

The TOP 10 PERCENT will be taken against this sort order.

Any report run against this will have to specify it's sort order, even
if it is the same as that of the query.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[John Vinson]

I can't use the TOP 10 PERCENT in my query because my
clients wants the oldest individuals that need to have a
xray to make up that 10%.

If you sort the query by birthdate, the TOP 10% will select the oldest
10% of the people who meet the other criteria.

 

[Guest]

Thanks for your help!!! It works.

Karen

-----Original Message-----
Dear Karen:

The TOP 10 PERCENT will take that proportion of the list against any
sort order you prescribe. If you have an Age column available or a
DateOfBirth column, you can simply add:

ORDER BY Age DESC
- or -
ORDER BY DateOfBirth

The TOP 10 PERCENT will be taken against this sort order.

Any report run against this will have to specify it's sort order, even
if it is the same as that of the query.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

I have this query:

StrSQL = "SELECT DistinctRow [Exam Schedule].EXAMLOC,
VanTestHistory.Date, VanTestHistory.Name,
VanTestHistory.CXRClass " & _
"FROM VanTestHistory INNER JOIN [Exam
Schedule] ON VanTestHistory.SSNO = [Exam Schedule].SSNO "
& _
"WHERE [Exam Schedule].EXAMLOC = '" & Me!
EXAMLOC & "' " & _
"AND VanTestHistory.Date Is Not Null " & _
"And VanTestHistory.SSNO = '" & Me!SSNO & "' "

This gives me everyone who has had a Van Test before. I
then have to check if they have had a Chest x-ray in the
last 5 years. I can do all this but I then have to take
everyone who hasn't had a chest x-ray and only take 10% of
them to print on my report that they need a chest x- ray.
I can't use the TOP 10 PERCENT in my query because my
clients wants the oldest individuals that need to have a
xray to make up that 10%. any ideas would be helpful.

Thanks

Karen

.

 

[Guest]

Thanks for your help!!! It works.

Karen

-----Original Message-----
Dear Karen:

The TOP 10 PERCENT will take that proportion of the list against any
sort order you prescribe. If you have an Age column available or a
DateOfBirth column, you can simply add:

ORDER BY Age DESC
- or -
ORDER BY DateOfBirth

The TOP 10 PERCENT will be taken against this sort order.

Any report run against this will have to specify it's sort order, even
if it is the same as that of the query.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

I have this query:

StrSQL = "SELECT DistinctRow [Exam Schedule].EXAMLOC,
VanTestHistory.Date, VanTestHistory.Name,
VanTestHistory.CXRClass " & _
"FROM VanTestHistory INNER JOIN [Exam
Schedule] ON VanTestHistory.SSNO = [Exam Schedule].SSNO "
& _
"WHERE [Exam Schedule].EXAMLOC = '" & Me!
EXAMLOC & "' " & _
"AND VanTestHistory.Date Is Not Null " & _
"And VanTestHistory.SSNO = '" & Me!SSNO & "' "

This gives me everyone who has had a Van Test before. I
then have to check if they have had a Chest x-ray in the
last 5 years. I can do all this but I then have to take
everyone who hasn't had a chest x-ray and only take 10% of
them to print on my report that they need a chest x- ray.
I can't use the TOP 10 PERCENT in my query because my
clients wants the oldest individuals that need to have a
xray to make up that 10%. any ideas would be helpful.

Thanks

Karen

.