Looping 1

[reg999]

I think that this should be easy, but I'm having trouble. I'm creating a database for my shooting club. I need to select each shooter's top ten targets from a table. I have a top ten, append query that I'm trying to loop over with a FOR EACH loop. I have a field holding each shooter's ID number in another query. --> SrSt.ShtrID

Here is the query that I'm trying to loop over:

INSERT INTO TopTnG ( ShtrID, Gun, G )
SELECT TOP 10 Targets.ShtrID, Targets.Gun, Targets.G
FROM Targets
WHERE (((Targets.ShtrID)=[Forms]![NameList]![ShtrID]) AND ((Targets.Gun)="Light") AND ((Targets.G) Is Not Null));

The reference '[Forms]![NameList]![ShtrID]' is left over from doing the query from a button on a continuous form.

I haven't done any programming since 1989, so I know that it's obvious that I'm a rusty old critter.
Reg999

 

[TomCologne]

If I understand your SQL correctly your table 'TopTnG' is sort of an extract of table 'Targets'.

Why don't you stick with a SELECT query to create the top ten list?

Even if the additional table is necessary you should not need a loop to append the records, running the qry should suffice.

Or am I missing something?


TomCologne

 

[reg999]

Thank you for the response, Tom. I'm sure that it's me that's just missing something... Could you possibly help me write the query?

My table 'Targets' has these fields. 'ShooterID', 'Gun' & 'G' (for group).
There are 20 shooters ('ShooterID')
Each shooter has two guns 'Light' and 'heavy'
Each gun has shot 40 targets.

I want to select the top ten (by smallest group) targets for each shooters guns.
So, I should end up with a top ten for each of the 40 guns (400 targets) in the result.

Thank you very much for your assistance.
Reg'

 

[PHV]

SELECT T.ShooterID, T.Gun, T.G
FROM Targets T
WHERE T.G IN (SELECT TOP 10 A.G FROM Targets A
WHERE A.ShooterID=T.ShooterID AND A.Gun=T.Gun AND A.G Is Not Null
ORDER BY A.G)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[reg999]

PH,
Does the 'T' operate as a pseudonym for the table 'Targets'? If so, what is 'A'?
With the query exactly as written, access doesn't recognize A.ShooterID or T.ShooterID?
Sorry, I'm not understanding this better.
Reg'

 

[PHV]

re your original post, perhaps you have to replace all ShooterID by ShtrID ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[reg999]

So sorry about my error on the 'ShtrID'. That worked.
If there are duplicate values of 'G' in the tenth position and beyond, then they are included in the result. Is there a way to further limit the result to only ten?
Reg'

 

[reg999]

PH,

SELECT T.ShtrID, T.Gun, T.G
FROM Targets T
WHERE T.G IN (SELECT TOP 10 A.G FROM Targets A
WHERE A.ShtrID=T.ShtrID AND A.Gun=T.Gun AND A.G Is Not Null
ORDER BY A.G)

Jet is keeping duplicates to the tenth values. Is there a way to prevent the duplicates? (I don't require a particular record among the duplicates.) I do have a unique record field in the original table ('ID') if it helps.
A million thanks,
Reg'

 

[PHV]

You may try this:
SELECT T.ShtrID, T.Gun, T.G
FROM Targets T
WHERE T.G IN (SELECT X.G FROM (SELECT TOP 10 A.G, A.ID FROM Targets A
WHERE A.ShtrID=T.ShtrID AND A.Gun=T.Gun AND A.G Is Not Null
ORDER BY A.G, A.ID) X)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[reg999]

PH,
It doesn't recognize T.ShtrID or T.Gun. ???
Reg'

 

[PHV]

And this ?
SELECT T.ShtrID, T.Gun, T.G
FROM Targets T INNER JOIN (
SELECT TOP 10 A.G, A.ID FROM Targets A
WHERE A.ShtrID=T.ShtrID AND A.Gun=T.Gun AND A.G Is Not Null
ORDER BY A.G, A.ID) X
ON T.G = X.G

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[reg999]

PH,
This one gives me the same "Enter Parameter Value" request for T.ShtrID and T.Gun

Is there any part of the table that could be causing trouble?
Fields:
ID as AutoNumber (Long Int)(Primary Key)
ShtrID as Number (Long Int)
Gun as Text
G as Number (Double)
S as Number (Long Int)
Date as Date/Time
TargNo as Number (Int.)

 

[PHV]

And this ?
SELECT T.ShtrID, T.Gun, T.G
FROM Targets T
WHERE T.G & "," & T.ID IN (SELECT TOP 10 A.G & "," & A.ID AS GID FROM Targets A
WHERE A.ShtrID=T.ShtrID AND A.Gun=T.Gun AND A.G Is Not Null
ORDER BY 1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[reg999]

That did it...
PH... you are officially my hero.
If there is ever anything I can do for you (non-technical of course)... I am forever in your debt.
Reg'

 

[PHV]

My pleasure.

 

[reg999]

PHV,
Things have been running quite smoothly with this database. Thank you again.

The query,

SELECT T.ShtrID, T.Gun, T.G
FROM Targets AS T
WHERE T.G & "," & T.ID IN (SELECT TOP 10 A.G & "," & A.ID AS GID FROM Targets A
WHERE A.ShtrID=T.ShtrID AND A.Gun=T.Gun AND A.G Is Not Null
ORDER BY 1);

has worked fine while we have had less than ten targets for 2005. At this time, our target count has reached from 3 for some shooters, to 15 for others. The query is still holding the maximum number of targets per shooter to 10, but they are not necessarily the top 10. The targets that are left out of the top ten don't seem to be random, but will be several targets that are consecutive(when sorted ascending by G).
The results are correct for the first "shooterID" only.

Here is the data.

Input Table Query Result
ID ShtrID Gun G ShtrID Gun G
1780 1 Heavy 4.498 1 Heavy 4.498
1808 1 Heavy 4.948 1 Heavy 4.948
1771 1 Heavy 7.317 1 Heavy 7.317
1715 1 Heavy 9.474 1 Heavy 9.474
1836 1 Heavy 14.596 1 Heavy 14.596
1743 1 Heavy 42.614 1 Heavy 42.614
1710 1 Light 3.56 1 Light 3.56
1669 1 Light 4.65 1 Light 4.65
1179 1 Light 4.99 1 Light 4.99
1775 1 Light 5.425 1 Light 5.425
1315 1 Light 5.914 1 Light 5.914
1803 1 Light 5.926 1 Light 5.926
1689 1 Light 5.97 1 Light 5.97
1679 1 Light 6.61 1 Light 6.61
1338 1 Light 6.87 1 Light 6.87
1361 1 Light 7.065 1 Light 7.065
1738 1 Light 8.123 11 Light 6.76
1766 1 Light 8.236 11 Light 6.876
1201 1 Light 8.94 11 Light 7.952
1831 1 Light 9.24 11 Light 8.457
1223 1 Light 9.42 11 Light 9.27
1230 11 Light 6.76 11 Light 10.819
1342 11 Light 6.876 11 Light 11.12
1365 11 Light 7.952 11 Light 12.5
1208 11 Light 8.457 11 Light 24.823
1680 11 Light 9.27 12 Heavy 10.593
1319 11 Light 10.819 12 Heavy 10.618
1660 11 Light 11.12 12 Heavy 12.61
1670 11 Light 12.5 12 Heavy 13.438
1186 11 Light 24.823 12 Heavy 14.037
1742 12 Heavy 9.216 12 Heavy 14.63
1688 12 Heavy 10.593 12 Heavy 16.548
1324 12 Heavy 10.618 12 Heavy 16.649
1347 12 Heavy 12.61 12 Heavy 17.362
1668 12 Heavy 13.438 12 Heavy 17.631
1678 12 Heavy 14.037 12 Light 10.252
1206 12 Heavy 14.63 12 Light 11.057
1807 12 Heavy 16.548 12 Light 11.197
1779 12 Heavy 16.649 12 Light 11.309
1770 12 Heavy 17.362 12 Light 11.477
1714 12 Heavy 17.631 12 Light 12.923
1228 12 Heavy 19.88 12 Light 13.98
1370 12 Heavy 22.705 12 Light 15.835
1835 12 Heavy 32.322 12 Light 16.262
1184 12 Heavy 75.45 12 Light 20.98
1802 12 Light 7.125 13 Heavy 4.939
1203 12 Light 7.19 13 Heavy 5.058
1774 12 Light 7.774 13 Heavy 10.082
1737 12 Light 9.424 13 Heavy 10.166
1709 12 Light 10.252 13 Heavy 10.963
1366 12 Light 11.057 13 Heavy 12.487
1667 12 Light 11.197 13 Heavy 14.088
1830 12 Light 11.309 13 Heavy 16.141
1343 12 Light 11.477 13 Heavy 16.675
1765 12 Light 12.923 13 Heavy 23.977
1225 12 Light 13.98 13 Light 4.831
1677 12 Light 15.835 13 Light 5.514
1687 12 Light 16.262 13 Light 6.454
1181 12 Light 20.98 13 Light 6.77
1320 12 Light 29.831 13 Light 6.831
1308 13 Heavy 4.939 13 Light 12.867
1331 13 Heavy 5.058 13 Light 14.812
1354 13 Heavy 5.942 13 Light 15.266
1197 13 Heavy 6.796 13 Light 18.498
1726 13 Heavy 7.562 13 Light 23.779
1857 13 Heavy 7.745
1801 13 Heavy 7.759
1676 13 Heavy 10.082
1219 13 Heavy 10.166
1666 13 Heavy 10.963
1698 13 Heavy 12.487
1686 13 Heavy 14.088
1754 13 Heavy 16.141
1175 13 Heavy 16.675
1829 13 Heavy 23.977
1327 13 Light 4.831
1674 13 Light 5.514
1171 13 Light 6.454
1749 13 Light 6.77
1693 13 Light 6.831
1721 13 Light 7.214
1304 13 Light 8.648
1350 13 Light 9.276
1796 13 Light 9.53
1664 13 Light 9.573
1193 13 Light 12.867
1684 13 Light 14.812
1215 13 Light 15.266
1852 13 Light 18.498
1824 13 Light 23.779

Reg

 

[PHV]

You my replace this:
ORDER BY 1);

By this:
ORDER BY A.G, A.ID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

You my replace this:
ORDER BY 1);

By this:
ORDER BY A.G, A.ID);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[reg999]

That appears to be the tweak that we needed.

Thank you yet again, you are a life saver.

Reg