multiple (+1000) queries; add-on maybe?

[hmgeri]

I don't think this is possible, but here goes: I need to create queries to validate data in a table. My boss wants every variable checked. There would be about 6 tables, 200 variables in each - I need to run them through query to see if the values are within ranges. Can't happen as data is entered into table, unfortunately. Thoughts?

 

[AlexCuse]

I take it these variables are numeric values? What you want to do is certainly possible, but without knowing how these variables are stored (such as, are there several in one column, just one per column, etc...) it is hard for anyone to say how this should be done.

I would do this using VBA (and set it up to run in scheduler on off-hours), looping through tables in an outer loop, and then in the inner loop checking each variable. You will want to <do something> only if a variable fails the check, presumably either fix it or send a notification.

Why exactly can't you do this as it is entered in the table? Some code on the entry form could make your life A LOT easier!

Anyway, with more information I am sure people in here would be able to help you put together an effective strategy.

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[hmgeri]

Sorry, Alex
Each variable's value will be numeric and can be made to be between 0 and 9. I can't do the validation as they populate the table because they come in from a different program, and from a different location. These tables are then exported (imported) into Access and I do my thing.
Thanks so much

 

[MichaelRed]

I would do the V&V on import of the data. It is, for me at least, a standard practice anyway. I 'always' import to a temp something, run the V&V, then move the data which passes the tests to a more permanant structure. Data which 'fails' is returned to sender with annotation to indicate which records/fields failed. I get a bit of flack over this from individuals the first time or two, but they soon get the message and many start some type of V&V before sending me data ... presumably just to avoid receiveing the returs as I continue to use the same approach, I just notice fewer returns.

From your discription, it hardly seems necessary to actually visit each field of each record. as a couple of simple aggregate (Min/Max of each field) queries can readily identify which fields have invalid data. Then, of course, the recordsets which pass need no further investigation. Those which fail would be processsed according to the bussiness rule (reject/return entire recordset to supplier, discard failed records, ...



MichaelRed

 

[AlexCuse]

Some simple queries (select columns from table where Variable > 9 or Variable < 0) should really be all you need for this. Of course, if you need to do this for 200 columns per table that could be a problem, but not really. If you think of how long it takes access to run such a query (and export it even, if you want to mail something), you are really only looking at a few minutes here. It will take a lot of time to write, but I think it'll be worth it (provided you cannot ask the people the data's coming from to perform the checks on their side).

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom