multiple iterations of same query

N

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?
 
P

PieterLinden via AccessMonster.com

N

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]);
 
N

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

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.


Nathan said:
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]);


KARL DEWEY said:
Post your query SQL.
 
N

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.

KARL DEWEY said:
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.


Nathan said:
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]);


KARL DEWEY said:
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?
 
P

PieterLinden via AccessMonster.com

Nathan said:
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]


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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top