multiple iterations of same query
in [Queries]
|
Prev: Prevent the table modification message in action query
Next: function for date of first and last day through weeknumber
From: KARL DEWEY on 8 Apr 2010 11:03 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" wrote: > 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" wrote: > > > Post your query SQL. > > -- > > Build a little, test a little. > > > > > > "Nathan" wrote: > > > > > 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?
From: Nathan on 8 Apr 2010 12:45 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" wrote: > 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" wrote: > > > 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" wrote: > > > > > Post your query SQL. > > > -- > > > Build a little, test a little. > > > > > > > > > "Nathan" wrote: > > > > > > > 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?
From: PieterLinden via AccessMonster.com on 8 Apr 2010 17:26
Nathan wrote: >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. > >> >Running Access 2003 on Vista. >> > >[quoted text clipped - 6 lines] >> If this is what I think it is, read this discussion... >> http://www.accessmonster.com/Uwe/Forum.aspx/access/126781/how-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... -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201004/1 |