Queries for multiple records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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' (run, execute, that
is) 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'),
run 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)
 
=?Utf-8?B?TmlrbyBTYWh0YXJpZGlz?=
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.
Click to expand...

Okay: "select FullName from People Where WeightInKg < 63.2" and so on.
Biggest question I have is what you do with all these lists afterwards:
display them, write letters to them, put them in a report, etc?
But is there an automated way that Access can 'apply' (run, execute,
that is) this query to many 'newcomers',
Click to expand...

First step would be a parameterised query: the query is stored and you
only have to add the 63.2 (etc) at the appropriate time. You don't want
to be re-typing the same query every time do you?
This 'macro' will take this Excel (or
Access) file of 'newcomers' as input (say it contains 100
'newcomers'),
Click to expand...

This sounds like a case for an non-equi join (I think...)

SELECT NewPeople.FullName AS NewPerson,
People.FullName AS PeopleWhoWeighLess
FROM People LEFT JOIN NewPeople
ON People.Weight < NewPeople.Weight
ORDER BY NewPeople.FullName, People.Weight DESC;

This produces a list of all the NewPeople and all the old people who
weigh less than them.

Look up help for the IN clause if you want to ge the NewPeople table from
somewhere other than the CurrentDB.

You can't do the height, resting systolic BP, etc in the same query, but
the same kind of trick is easy to do.
produce the corresponding 100 Access files as output.
Click to expand...

What is a Access File For Output? There may be better ways of achieving
what you want. I am guessing you'll need a bit of VBA skill to do this
most efficiently.
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.
Click to expand...

I am sure you already know this, but Age is a really dumb thing to put in
a table (because it goes out of date so readily). Try using DateOfBirth
and a bit of calculation in the query to get the right age.

Hope that helps


Tim F
 
Back
Top