Query Criteria based on another table

H

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
 
J

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
 
M

Marshall Barton

He_Haa said:
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.
Click to expand...


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

He_Haa

Marshall Barton said:
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.
Click to expand...

Thank you brother.


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

Thanks once again
 
H

He_Haa

One last favor required;
The accounts in the generated query are in the same order as of selected
accounts list, added manually, but when I generate report the report pages
are in random order.
What I want if it’s possible that I can sort the report pages with same
series as I enter in Selected A/c’s
In others words, I want the report generated in same sequence of another
particular column’s data.

Accounts.list
To sort with
Selected.list
 
M

Marshall Barton

He_Haa said:
One last favor required;
The accounts in the generated query are in the same order as of selected
accounts list, added manually, but when I generate report the report pages
are in random order.
What I want if it’s possible that I can sort the report pages with same
series as I enter in Selected A/c’s
In others words, I want the report generated in same sequence of another
particular column’s data.

Accounts.list
To sort with
Selected.list
Click to expand...


Not sure I understand, but it is important to know that
report sorting really needs to be done using the Sorting and
Grouping window (View menu in report design view).

Mayve(??) you want the first tow rows in Sorting and
Grouping to be:

theaccountfield
theselectedfield
 
H

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