Can Complex SQL Query for Immunizations be done simply?

[startup]

I need to open up a recordset for my report which only lists those students who have problems with their immunizations, (ie. they are missing one or the other immunization according to what age they are, etc). My frustration is, when I try to create the query to do this, it already has 10 huge sections in the WHERE portion and I can see it going up to 30 or 40 different OR parts, which is overwhelming to think about. I could make it much shorter if I was doing a program (for example: on 4 different polio dates I would look through each one and see if the date existed and then do x = x + 1 and whatever x was at the end would determine whether the record would display or not). I don't know how to do this in a query though.

Anyone have any ideas?

Thanks

 

[psemianonymous]

This is where UNION queries work wonders. Here we go:

1. Get a standard format for your "irregularities." I'd do it in the following format:

[PersonID      ][type            ][other std. details]
345             PT is a vampire   
378             PT is a vampire
399             PT too lazy to take pills

Make a query and find all the people who have problems in one particular way. Make a second, third, fourth, etc. up until you have a query that finds an irregularity for EVERY irregularity.

Now. Make what I call the "performance monster UNION query"--this will probably be incredibly slow, but you will have to live with this. Do the format:

SELECT * FROM qryMYFIRSTQUERY
UNION ALL
SELECT * FROM qryMYOTHERQUERY
UNION ALL
SELECT * FROM etcetc

Now you have a list of all irregularities, which can be easily modified to add/remove new irregularities if you wish.




The only other way to achieve your goal would be to store the irregularity information in a flag field, i.e. "is Irregular field". This, believe it or not, is far more complex to do right than using the mega-UNION query.

 

[startup]

foolio12,

Thank you for the response, which is very helpful. I hadn't heard of UNION queries and it will definitely be helpful. I don't think it will work in my case because there are such complex formulas... (ie, if the kid is this age he has to have these shots, but if he's older he has to have another shot plus a shot from another category in a different record, and if he's in this or that grade level the requirements are different.) I think I will have to do something with marking the fields as irregular.