how to use 12 queries in a query (1 Viewer)

[accessfever]

I have 2 queries to check if there is any "double quote" character in any of 12 month columns in a month table of 125K records. I use 2 queries since the maximun criteria in a query is only 8. So I use 8 criteria in the 1st query then use 4 criteria to check the remaining months in the 2nd query.
The month table is refreshed and created every month. The 12 month columns are changing from month to month since they are -13 month to -1 month when the month table is created. For example, if the month table is refreshed and created in May 2013, then the 12 month columns are "May 2012", "Jun 2012" .... and "Apr 2013". If the month table is created in Jun 2013, then the 12-month columns are "Jun 2012", "Aug 2012" .... and "May 2013".
The end user has little knowledge of Access Databse so he seems confusing how to update the 2 queries on new month table.
I am trying to see if there is a way to use 12 criteria in just one query so the end user only deals with one query's update.
If there is a way to automate/improve the update of the queries/query, then it would be the best.
Your any help will be appreciated.

 

[CJ_London]

can you post your 2 queries and explain what the queries are supposed to do - your solution seems overly complex to simply replace double quotes.

 

[accessfever]

Hi, below is the SQL codes for the 2nd query. The first query is pretty much the same except the month columns are from 005/2012 to 012/2012 (8 month columns) for March month table. The 2 queries are to catch if there is any double quote in any of the 12 month columns and not to replace the double quotes (The end user would go back to the IT who created the month data if there is double quote found). In this example, the next month table will drop the 005/2012 column and have a new month column of 005/2013.

SELECT DATA_IMPORT.Product, DATA_IMPORT.Location,DATA_IMPORT.[001/2013 Qty], DATA_IMPORT.[002/2013 Qty], DATA_IMPORT.[003/2013 Qty], DATA_IMPORT.[004/2013 Qty]
FROM DATA_IMPORT
WHERE (((InStr([001/2013 Qty],Chr(34)))>0)) OR (((InStr([002/2013 Qty],Chr(34)))>0)) OR (((InStr([003/2013 Qty],Chr(34)))>0)) OR (((InStr([004/2013 Qty],Chr(34)))>0));

 

[JHB]

.... I use 2 queries since the maximun criteria in a query is only 8. ..........

Never heard about that, so from where do you get that information, (and it is not true)?
Here is a link to the limination of MS-Access:
http://www.databasedev.co.uk/access_specifications.html#content3

You can also find the limination in the MS-Access Help-file.
Seach word is: "Specifications"

 

[Beetle]

The end user would go back to the IT who created the month data if there is double quote found

So it's your IT people who are creating this disaster of a data set that you are importing and then having to look for their mistakes?

 

[spikepl]

The "limit of 8 " is simply the limitation of display in the query design grid. But you can change to the SQL view and manually type/add criteria ad libitum (more or less).

Your problems stem from working with unnormalized data (google database normalization!) - where the same type of data is put in different containers (in this instance: column). This data should all go into one column, with a second column containing the corresponding date. Then your queries would not depend on data and would not have to change. In any case, users should not have to mess with this.

Just because you get your data the way you do, does definitely not mean that you have to store it the same way. You could read it into a (temporary) table as now, but then normalize it, and then work on normalized data, so you would not have these problems.

Finally, the entire process appears fishy. Is it really so that you, e.g. this month will generate a column of data tagged April 2013 and then regenerate it another 12 times over the next year? The same data?

 

[accessfever]

Thanks for the tips. I use the SQL view and successfully have 12 criteria on those 12 month columns in one query. Besides, I copy the SQL codes in a Word document so the end user can revise the codes easily (drop an old month and add a new month) in Word then paste to the query's SQL view.

Thanks very much!

 

[jdraw]

Sounds a little unorganized and prone to error, but if it works for you and management is happy, go for it.

I'd suggest a form with some drop downs for user selection; some code behind the scenes to build the appropriate sql; a message/chance to adjust to ensure the selections an substitutions are correct; and a button to invoke some code that ran the queries in sequence.

 

[gemma-the-husky]

i would automate this anyway in other ways, to do the whole thing in code - so users do not need to "touch" anything

1. import the new data

2. simply change the column headers to month1, month2, month3 etc (this perhaps depends what you are doing with the data in these columns though.)

3. then you don't need to redefine the queries

4. automate the whole thing, so users do not need to change anything.

out of interest what spec is the imported file csv, text, etc? what data are you expecting to see in the columns?

anyway can all be achieved in code. once you do it once, you save yourself a lot of subsequent pain!

 

[Galaxiom]

The "limit of 8 " is simply the limitation of display in the query design grid. But you can change to the SQL view and manually type/add criteria ad libitum (more or less).

The eight rows in the designer is not a real limit either.

More rows can be added. In A2010 click the Insert Rows button in the Query Tools.

I think this was also available in the context menu of the query design grid in earlier versions of Access.