Query Criteria based on another table

[He_Haa]

Hey to all.

I am in urgent need to find out a solution for a very hectic process. First
let me explain the scenario.
There is one table of accounts with ACCOUNT NO field of importance, and
other details, and on daily basis I’ve to generate a report of random around
200 ACCOUNTS NO out of total 40,000 accounts. Currently I’m using a simple
query of entering ACCOUNT NO and print report one by one. Which really take
most of my time.

What do I want, if we can create another TABLE I which I put the ACCOUNT NO
of all the desired reports and run the query, and through that query
generating reports of all accounts on one click.

Please URGENT suggestion required, this will really ease my life. ITS
URGENT...

Thanks.
mJ's

 

[Jake]

I've been out of the industry for 7 years, but heres an idea.

SELECT round(Rnd() / rnd() * 10000) AS Expr1;

This query will select a range of random numbers. You can maybe use it
within in your query to help with selection criteria. I ran it about 100
times and got results from 56 to 101,000. I'm sure the range extends that.
Let me know if it helps. By the way I'm using Access 2003.

The concept of generating 200 random reports

I would generate a report in access using the wizard that uses the above
query in a loop within an event procedure at the begin of the report
generation. I would only show and count successful hits against my base
tables (account numbers) and when it hit 200 successful hits I would stop
reporting. After its coded you'ld simple press one button and 200 reports at
random would generate. By the way I might also create an array in the code
that tracks the id hit and if by chance I had a dup I wouldn't run the report
for that number.

Note: rnd() is the random function in Access 2003

J

 

[Marshall Barton]

I am in urgent need to find out a solution for a very hectic process. First
let me explain the scenario.
There is one table of accounts with ACCOUNT NO field of importance, and
other details, and on daily basis I’ve to generate a report of random around
200 ACCOUNTS NO out of total 40,000 accounts. Currently I’m using a simple
query of entering ACCOUNT NO and print report one by one. Which really take
most of my time.

What do I want, if we can create another TABLE I which I put the ACCOUNT NO
of all the desired reports and run the query, and through that query
generating reports of all accounts on one click.

You can certainly use another table for this. Once the
other table is populated with the desired 200 account
numbers, then join that to the accounts table in the
report's record source query. E.g.

SELECT *
FROM accounts INNER JOIN othertable
ON accounts.AccountNo = othertable.AccountNo

OTOH, if you truely don't care which 200 accounts appear in
the report, then the report's record source query can pick
them for you:

SELECT TOP 200 *
FROM accounts
ORDER BY Rnd(AccountNo)

You should arrange to execute the Randomize statement in
some code before opening the report.

 

[He_Haa]

You can certainly use another table for this. Once the
other table is populated with the desired 200 account
numbers, then join that to the accounts table in the
report's record source query. E.g.

SELECT *
FROM accounts INNER JOIN othertable
ON accounts.AccountNo = othertable.AccountNo

OTOH, if you truely don't care which 200 accounts appear in
the report, then the report's record source query can pick
them for you:

SELECT TOP 200 *
FROM accounts
ORDER BY Rnd(AccountNo)

You should arrange to execute the Randomize statement in
some code before opening the report.

Thank you brother.


THANKS A MILLION. You really made my life a million times easier

Thanks once again

 

[He_Haa]

Ok lets me try to explain you a little more about the situation I’m in

Accounts Table: Selected Accounts Table Final required report
1 5 5
2 3 3
3 1 1
4 1 1
5 2 2
6 5 5
7 9 9
8 6 6
9 8 8

I hope the situation is clear to you, and you’ll be in better place helping
me out of this.