multiple iterations of same query

[Nathan]

Running Access 2003 on Vista.

I have a query that chooses 15 records at random. This works fine.

What I want to do is run the same query for approx 110 different users, each
with their own randomly selected 15 records. Obviously, I want to avoid
manually running the same query that many times. How can I get around this?

 

[KARL DEWEY]

Post your query SQL.

 

[PieterLinden via AccessMonster.com]

Running Access 2003 on Vista.

I have a query that chooses 15 records at random. This works fine.

What I want to do is run the same query for approx 110 different users, each
with their own randomly selected 15 records. Obviously, I want to avoid
manually running the same query that many times. How can I get around this?

If this is what I think it is, read this discussion...
http://www.accessmonster.com/Uwe/Fo...w-to-10-random-records-per-user-in-large-file

 

[Nathan]

SELECT TOP 15 dbo_Employee.EEID, [EELast] & ", " & [EEFirst] AS SubroRep,
dbo_Case.CaseID, dbo_Case.CaseClosedDate
FROM dbo_Employee INNER JOIN (dbo_feature INNER JOIN dbo_Case ON
dbo_feature.FeatureAssocClaimNum = dbo_Case.CaseID) ON dbo_Employee.EEID =
dbo_Case.CaseEEID
WHERE (((dbo_Employee.EEID)=[enter ID of user]) AND
((dbo_Case.CaseClosedDate)>Date()-30) AND ((dbo_Case.CaseStatus)=6))
ORDER BY Rnd([featureidnumber]);

 

[Nathan]

Yes, the main thrust of that thread is what I am trying to accomplish, but
I'm not seeing the answer there?


I have the random selection solved - I just need to know how to run it over
and over programattically for my defined list of users.

 

[KARL DEWEY]

UNTESTED UNTESTED
Try this --
SELECT dbo_Employee.EEID, [EELast] & ", " & [EEFirst] AS SubroRep,
dbo_Case.CaseID, dbo_Case.CaseClosedDate
FROM dbo_Employee INNER JOIN (dbo_feature INNER JOIN dbo_Case ON
dbo_feature.FeatureAssocClaimNum = dbo_Case.CaseID) ON dbo_Employee.EEID =
dbo_Case.CaseEEID
WHERE dbo_Case.CaseID = (SELECT TOP 15 [XX].CaseID
FROM dbo_Employee INNER JOIN (dbo_feature INNER JOIN dbo_Case AS [XX] ON
dbo_feature.FeatureAssocClaimNum = [XX].CaseID) ON dbo_Employee.EEID =
dbo_Case.CaseEEID WHERE (((dbo_Case.CaseClosedDate)>Date()-30) AND
((dbo_Case.CaseStatus)=6))
ORDER BY Rnd([featureidnumber]));

--
Build a little, test a little.

SELECT TOP 15 dbo_Employee.EEID, [EELast] & ", " & [EEFirst] AS SubroRep,
dbo_Case.CaseID, dbo_Case.CaseClosedDate
FROM dbo_Employee INNER JOIN (dbo_feature INNER JOIN dbo_Case ON
dbo_feature.FeatureAssocClaimNum = dbo_Case.CaseID) ON dbo_Employee.EEID =
dbo_Case.CaseEEID
WHERE (((dbo_Employee.EEID)=[enter ID of user]) AND
((dbo_Case.CaseClosedDate)>Date()-30) AND ((dbo_Case.CaseStatus)=6))
ORDER BY Rnd([featureidnumber]);

Post your query SQL.

 

[Nathan]

That gives me a syntax error from the Where clause on.

But, in the meantime, I think I figured out a way to do it using two macros
and temporary table.

UNTESTED UNTESTED
Try this --
SELECT dbo_Employee.EEID, [EELast] & ", " & [EEFirst] AS SubroRep,
dbo_Case.CaseID, dbo_Case.CaseClosedDate
FROM dbo_Employee INNER JOIN (dbo_feature INNER JOIN dbo_Case ON
dbo_feature.FeatureAssocClaimNum = dbo_Case.CaseID) ON dbo_Employee.EEID =
dbo_Case.CaseEEID
WHERE dbo_Case.CaseID = (SELECT TOP 15 [XX].CaseID
FROM dbo_Employee INNER JOIN (dbo_feature INNER JOIN dbo_Case AS [XX] ON
dbo_feature.FeatureAssocClaimNum = [XX].CaseID) ON dbo_Employee.EEID =
dbo_Case.CaseEEID WHERE (((dbo_Case.CaseClosedDate)>Date()-30) AND
((dbo_Case.CaseStatus)=6))
ORDER BY Rnd([featureidnumber]));

--
Build a little, test a little.

SELECT TOP 15 dbo_Employee.EEID, [EELast] & ", " & [EEFirst] AS SubroRep,
dbo_Case.CaseID, dbo_Case.CaseClosedDate
FROM dbo_Employee INNER JOIN (dbo_feature INNER JOIN dbo_Case ON
dbo_feature.FeatureAssocClaimNum = dbo_Case.CaseID) ON dbo_Employee.EEID =
dbo_Case.CaseEEID
WHERE (((dbo_Employee.EEID)=[enter ID of user]) AND
((dbo_Case.CaseClosedDate)>Date()-30) AND ((dbo_Case.CaseStatus)=6))
ORDER BY Rnd([featureidnumber]);

Post your query SQL.
--
Build a little, test a little.


:

Running Access 2003 on Vista.

I have a query that chooses 15 records at random. This works fine.

What I want to do is run the same query for approx 110 different users, each
with their own randomly selected 15 records. Obviously, I want to avoid
manually running the same query that many times. How can I get around this?

 

[PieterLinden via AccessMonster.com]

Yes, the main thrust of that thread is what I am trying to accomplish, but
I'm not seeing the answer there?

I have the random selection solved - I just need to know how to run it over
and over programattically for my defined list of users.
[quoted text clipped - 6 lines]

If this is what I think it is, read this discussion...
http://www.accessmonster.com/Uwe/Fo...w-to-10-random-records-per-user-in-large-file

I opened a recordset against the "defined list of users", and then passed
the ID value into an append query that collected the Top N records and
appended them to a temporary table. Couldn't figure out any other way to
return a random set of records for each user in the list...