SQL Query using IF Statement???

[bishmedia]

I'm not sure if ive taken the right path, the code below pulls 4 stock location tables together and asks the user firstly for an aisle which is 2 left characters (Expr1) and then the level which is the Right end character (Expr2) e.g A, B, C, D etc
This is my question, On Level 'A' I have 'G' as well and on Level 'B' I have 'H' as well, now I could run 2 separate reports one for A and one for G and the same for B and H.
Is there a way that when someone requests Level A it automatically includes G and also if someone requests Level B it automatically includes H ????

SELECT Left([Location],2) AS Expr1, Right([Location],1) AS Expr2, Opus.*FROM Opus
WHERE (((Left([Location],2))=[Enter Aisle]) AND ((Right([Location],1))=[Enter Level]))


UNION SELECT Left([Location],2) AS Expr1, Right([Location],1) AS Expr2, Wrigley_Res.*
FROM Wrigley_Res
WHERE (((Left([Location],2))=[Enter Aisle]) AND ((Right([Location],1))=[Enter Level]))


UNION SELECT Left([Location],2) AS Expr1, Right([Location],1) AS Expr2, Glenmark_SAP.*
FROM Glenmark_SAP
WHERE (((Left([Location],2))=[Enter Aisle]) AND ((Right([Location],1))=[Enter Level]))


UNION SELECT Left([Location],2) AS Expr1, Right([Location],1) AS Expr2, Wrigley_Act.*
FROM Wrigley_Act
WHERE (((Left([Location],2))=[Enter Aisle]) AND ((Right([Location],1))=[Enter Level]));

Hope this makes sense

[June7]

Why do you have 4 tables? Why not 1 table with another field for site (Wrigley_Res, Glenmark_SAP, etc)?

Don't do filtering in UNION. Treat the UNION as the normal table it should be. Build another query that references the UNION.

So you want to do this compound filtering only for these two pairs? Not for C, D, etc?

I think better build VBA code to dynamically construct filter criteria. Review: http://allenbrowne.com/ser-62.html

[bishmedia]

Sadly It has to be 4 tables because each table is linked to an Excel sheet which is refreshed via ODBC from 4 different systems Opus, PKMS, SAP etc :-(
I am new to this which is why I felt I may have taken the wrong path, the example works great but just wanted 1 report instead of 2, it's only A&G and B&H..... c, d, d, e etc are all ok

[isladogs]

Keep your 4 linked Excel files but import from those to one table.
Just because the data originates from different sources, it doesn't mean it needs to be stored separately.

[June7]

Can be 1 report. The VBA constructs filter criteria and then passes the criteria to report in the OpenReport method.


I thought a conditional in query would be rather complex, hence the recommendation for VBA, but the following might accomplish:

WHERE Expr1 = [Enter Aisle] AND (Expr2 = [Enter Level] OR Expr2 = Switch([Enter Level]="A", "G", [Enter Level]="B", "H", True, [Enter Level]))


I never use dynamic parameterized query, especially not with input popup - can't validate user input. Have the parameter reference a control on form for input.

[bishmedia]

Ok my report didn't like that it seemed to have listed everything
I'm going to look into merging all 4 tables into 1 table as mentioned above then use VBA as I think the SQL could be a bit tricky for me :-)

[June7]

You may have read my previous post before I did an edit on the criteria expression. Review again.

Don't entirely agree with the merged table idea since these worksheets are updated periodically. Maintaining that table can be a complicated process of its own.

A merged table won't resolve the report issue. Get your UNION and the report working first. Look at the merging later if you think that is worthwhile.

[bishmedia]

Yeah definitely confused now, are you saying replace the WHERE line like below, I'm getting a 'Syntax Error'

SELECT Left([Location],2) AS Expr1, Right([Location],1) AS Expr2, Opus.*
FROM Opus
WHERE Expr1 = [Enter Aisle] AND (Expr2 = [Enter Level] OR Expr2 = Switch([Enter Level]="A", "G", [Enter Level]="B", "H", True, [Enter Level]))


UNION SELECT Left([Location],2) AS Expr1, Right([Location],1) AS Expr2, Wrigley_Res.*
FROM Wrigley_Res
WHERE Expr1 = [Enter Aisle] AND (Expr2 = [Enter Level] OR Expr2 = Switch([Enter Level]="A", "G", [Enter Level]="B", "H", True, [Enter Level]))


UNION SELECT Left([Location],2) AS Expr1, Right([Location],1) AS Expr2, Glenmark_SAP.*
FROM Glenmark_SAP
WHERE Expr1 = [Enter Aisle] AND (Expr2 = [Enter Level] OR Expr2 = Switch([Enter Level]="A", "G", [Enter Level]="B", "H", True, [Enter Level]))


UNION SELECT Left([Location],2) AS Expr1, Right([Location],1) AS Expr2, Wrigley_Act.*
FROM Wrigley_Act
WHERE Expr1 = [Enter Aisle] AND (Expr2 = [Enter Level] OR Expr2 = Switch([Enter Level]="A", "G", [Enter Level]="B", "H", True, [Enter Level])));

[June7]

I said in post #2 not to do filtering in the UNION, build another query that references the UNION query. Apply filter in the second query.


If you do this filter in the UNION will have to repeat the Left() and Right() calcs in the WHERE clause everywhere the Expr1 and Expr2 alias names are used.


Shouldn't you have gotten syntax error with the first version?

[bishmedia]

Ok, I may have bitten off more than I can chew.
Tomorrow I will look at the terminology UNION as well as creating a new query as I'm totally confused.

[June7]

UNION will not return duplicate records. Use UNION ALL if you want every record even if duplicates. You are using wildcard to return fields. This assumes fields are the same in all tables and in the same order in table design.

The first SELECT defines field names and alias field names do not have to be repeated. Calculate a field to identify the source table. Suggest you use more meaningful names than Expr1 and Expr2. Consider:

SELECT Left([Location],2) AS Aisle, Right([Location],1) AS Lvl, "O" AS Source, Opus.* FROM Opus
UNION SELECT Left([Location],2), Right([Location],1), "WR", Wrigley_Res.* FROM Wrigley_Res
UNION SELECT Left([Location],2), Right([Location],1), "GS", Glenmark_SAP.* FROM Glenmark_SAP
UNION SELECT Left([Location],2), Right([Location],1), "WA", Wrigley_Act.* FROM Wrigley_Act;

Save that query and treat it like a table (except cannot edit data via UNION) and build another query that references the saved UNION query name and apply suggested filter criteria. I tested the filter syntax and it works for me.

[bishmedia]

I'm almost there, I have setup the query as you mentioned above and called it QueryTableMerge but i used UNION ALL as I needed duplicates. Ive setup another query to select the Aisle/Level records. All works ok but Ive tried adding your previous WHERE statement so that when 'A' is selected it includes 'G' but I only get 'G' and if I enter 'B' I only get 'H' ??

SELECT qryTableMerge.Aisle, qryTableMerge.Lvl, qryTableMerge.Location, qryTableMerge.Product, qryTableMerge.Description, qryTableMerge.Reference, qryTableMerge.Quantity, qryTableMerge.Customer, qryTableMerge.Date, qryTableMerge.Batch, qryTableMerge.SourceFROM qryTableMerge
WHERE ((qryTableMerge.Aisle)=[Enter Aisle]) AND ((qryTableMerge.Lvl)=Switch([Enter Level]="A", "G", [Enter Level]="B", "H", True, [Enter Level]));

Any thoughts???

[June7]

You did not exactly replicate the suggested WHERE:

WHERE Expr1 = [Enter Aisle] AND (Expr2 = [Enter Level] OR Expr2 = Switch([Enter Level]="A", "G", [Enter Level]="B", "H", True, [Enter Level]))

so

WHERE qryTableMerge.Aisle=[Enter Aisle] AND (qryTableMerge.Lvl=[Enter Level] OR qryTableMerge.Lvl=Switch([Enter Level]="A", "G", [Enter Level]="B", "H", True, [Enter Level]));

[bishmedia]

Thankyou June7, it's been quite a learning curve for me this week but with your help this works really great and to boot with what Ive learnt this week I can use it elsewhere.

Once again, thankyou.