Performing multiple queries in Access

[Niko Sahtaridis]

I am performing a set of very simple database queries in Microsoft Access,
that I want to automate instead of going through them all manually.

Namely, I possess a database of records as usual (the first field is a name,
the second date and the rest numerical, like height, weight, age etc...).
Lets say they represent persons.

I have another person (a 'newcomer' so to speak) and I try to find all the
records in my database whose height is, say, greater that this person's
height, the weight is 10% smaller than this person's weight etc etc. No
problem, so far. I just type a query and Access finds these records for me.
If I have two 'newcomers', I type the query twice etc.

But is there an automated way that Access can 'apply' this query to many
'newcomers', that I have stored in an Excel (or Access) file, so that I don'
t have to re-type it every time? This 'macro' will take this Excel (or
Access) file of 'newcomers' as input (say it contains 100 'newcomers'),
apply that same query for every newcomer in that file, and produce the
corresponding 100 Access files as output. For brevity, if, for some
newcomers, the outcome of the query is null, its table won't be produced.

Is there any straightforward way to accomplish that in Access or I have to
resort to high-level programming or something? To tell you the truth, I am
no expert in Microsoft Access...

Please feel free to respond directly to (e-mail address removed). Thank you in
advance and Happy New Year to everyone!


Nick Sahtaridis
(e-mail address removed)

 

[Tom Ellison]

Dear Niko:

From your description, it would seem probable you could create a single
query that would do this. The query would show the "newcomers" in one of
more column, and the results of the "search" in additional columns. If
ORDERed BY the newcomers column(s) these would be kept together. It could
make a pretty good report or an interactive display.

I'm thinking a LEFT JOIN from the "newcomers" table to the table being
searched would be a primary way to start this. If there is no basis for
joining, then a cross-product would be in line. The criteria of height,
weight, age, etc. would be filters.

Without complete details of your situation, I cannot make any specific
recommendations how to code this. From what you have told me so far, a
guess is:

SELECT N.[Name], N.height, P.[Name], P.height
FROM Newcomer N, Persons P
WHERE P.height > N.height
AND P.weight <= .9 * N.weight
ORDER BY N.[Name], P.[Name]

You would need to change any column or table names above that are not exact.