Is it possible to create a query of queries without using UNION

[Oscar42]

I've got 28 queries collecting a sub-set of table data based on a single criteria in a field of the QBE. Now some of the those 28 queries exclude certain fields.
What I ideally want is a final query result that combines all those queries. I have tried UNION but that is not compatible with my ODBC connection to a 3rd party application. What is compatible is a table or a basic select query.

But maybe I am doing this all wrong (newbie here). Is it possible to control which QBS fields are added into the result of a query on a per record basis through criteria defined in another QBS field? Or is it a subquery solution I should be looking at??

Basically I need the query to decide if the data in a field for each record is added or not to a final output datasheet based on criteria listed in a master field.

An example: Final Query result --> If(Car == Blue) THEN display related ABS number, ELSE set to NULL

Cars ABS
Blue 656
Green 243

Output Query:
Cars ABS
Blue 656
Green

There are about 40 fields, 5 of which may or may not have their data passed to the output depending on the value in the master field for each record.
I hope this makes some vague sense!

 

[arnelgp]

simple query will, i think, do it for you:

Select Cars, IIF(Cars = "Blue", ABS, Null) As TheABS From YourTableName

 

[theDBguy]

Hi. Congrats on your first post. Not sure if I understand your question, but you should be able to use the IIf() function in your SQL query to return whichever field you want.

Edit: Oops, too slow... Welcome to AWF!

 

[Pat Hartman]

When you make a union query, all the queries must have the same fields in the same order. When the queries you want to union are not consistent, you need to make them consistent. The easy way would be to make sure that all the variable fields in your queries are at the end. That allows you to use Select *, " " as dummy1, " " as dummy2, " " as dummy3, " " as dummy4, 0 as dummy5.

If the column order is variable then you need to be specific with your selects and embed the dummy values where appropriate for each query. Use " " for text and null or zero for numbers.

 

[Oscar42]

Thanks all. I get it now that I don't need to use a UNION. Just some complex IFF statements in the select query. This will make the select query enormous. Any recommendations on how to break up a very complex/large SELECT query? I'm going to try and use Access Studio to help at least
visualise.

 

[arnelgp]

show as a mock-up of your table and the expected result.

 

[Oscar42]

I have this now but it's missing a pile of IFF statements:

 

[arnelgp]

can you copy and paste it as Text (not image).
note that your current table is not normalized (google table normalization).

 

[Oscar42]

I tried but it wouldn't let me post. It says it thinks it is some dodgy code

 

[arnelgp]

you see </> above the text editor, click it and paste the sql inside it.

 

[Oscar42]

Yeah I've tried that, but I still get...

 

[Oscar42]

SELECT [Standard Parts].[Contromax Part Number], [Standard Parts].[Part Catalog], [Standard Parts].Description, [Standard Parts].[Schematic Comment] AS SchComment, [Standard Parts].[Symbol Path] AS [Library Path], [Standard Parts].[Symbol Name] AS [Library Ref], [Standard Parts].[Footprint Path], [Standard Parts].[Footprint Name #1] AS [Footprint Ref], [Standard Parts].[Footprint Name #2] AS [Footprint Ref 2], [Standard Parts].[Footprint Name #3] AS [Footprint Ref 3], [Standard Parts].[Manufacturer Name], [Standard Parts].[Manufacturer Part Number], [Standard Parts].[Manufacturer Status], [Standard Parts].[Manufacturer LT (Wks)], [Standard Parts].[Supplier #1 Name] AS ComponentLink4Description, [Standard Parts].[Supplier #1 Name], [Standard Parts].[Supplier #1 Part Number], [Standard Parts].[Supplier #1 URL] AS ComponentLink4URL, [Standard Parts].[Supplier #1 URL], IIf([Datasheet #1 Path]="N/A","","DATASHEET") AS ComponentLink1Description, IIf([Datasheet #1 Path]="N/A","","S:\" & Mid$([Datasheet #1 Path],23,255) & "\" & [Datasheet #1 File Name] & ".pdf") AS ComponentLink1URL, IIf([Datasheet #1 Path]="N/A","","" & Mid$([Datasheet #1 Path],14,255) & "\" & [Datasheet #1 File Name] & ".pdf") AS [Datasheet #1 URL], [Standard Parts].Mounting, [Standard Parts].[Procurement Type], [Standard Parts].[Unit Of Measure], [Standard Parts].[Serial Number], [Standard Parts].[Date Of Manufacture], [Standard Parts].ClassName, [Standard Parts].Resistance, [Standard Parts].Capacitance, [Standard Parts].Inductance, [Standard Parts].Impedance, [Standard Parts].Tolerance, [Standard Parts].Voltage, [Standard Parts].Power, [Standard Parts].RoHS, [Standard Parts].Qualifications, [Standard Parts].[Reviewed / Approved], [Standard Parts].[Supplier #2 Name] AS ComponentLink5Description, [Standard Parts].[Supplier #2 Name], [Standard Parts].[Supplier #2 Part Number], [Standard Parts].[Supplier #2 URL] AS ComponentLink5URL, [Standard Parts].[Supplier #2 URL], [Standard Parts].[Supplier #3 Name] AS ComponentLink6Description, [Standard Parts].[Supplier #3 Name], [Standard Parts].[Supplier #3 Part Number], [Standard Parts].[Supplier #3 URL] AS ComponentLink6URL, [Standard Parts].[Supplier #3 URL], [Standard Parts].[Datasheet #2 Description] AS ComponentLink2Description, IIf([Datasheet #2 Path]="N/A","","S:\" & Mid$([Datasheet #2 Path],23,255) & "\" & [Datasheet #2 File Name] & ".pdf") AS ComponentLink2URL, IIf([Datasheet #2 Path]="N/A","","" & Mid$([Datasheet #2 Path],14,255) & "\" & [Datasheet #2 File Name] & ".pdf") AS [Datasheet #2 URL], [Standard Parts].[Datasheet #3 Description] AS ComponentLink3Description, IIf([Datasheet #3 Path]="N/A","","S:\" & Mid$([Datasheet #3 Path],23,255) & "\" & [Datasheet #3 File Name] & ".pdf") AS ComponentLink3URL, IIf([Datasheet #3 Path]="N/A","","" & Mid$([Datasheet #3 Path],14,255) & "\" & [Datasheet #3 File Name] & ".pdf") AS [Datasheet #3 URL]
FROM [Standard Parts]
WHERE ((([Standard Parts].[Part Catalog])="CAPACITOR"))
ORDER BY [Standard Parts].[Contromax Part Number];

 

[Oscar42]

Hyper links removed..

 

[arnelgp]

i don't see any missing IIF(), they all match the bracket.

 

[Oscar42]

Yes, it's functionally fine, just difficult to read and maintain. Is there any way to break such a thing up into more manageable portions? Like the WITH stmt?

 

[cheekybuddha]

As Arnel suggests, you should really consider studying and understanding Normalisation, and apply it to your schema design (folks here can probably help if you need assistance).

You can make your query more readable by formatting it - unfortunately Access will lose your formatting every time you save the query.

Since you are selecting form a single table, you don't need to qualify every field using the table name - it just adds extra noise:

SELECT
  [Contromax Part Number],
  [Part Catalog],
  [Description],
  [Schematic Comment] AS SchComment,
  [Symbol Path] AS [Library Path],
  [Symbol Name] AS [Library Ref],
  [Footprint Path],
  [Footprint Name #1] AS [Footprint Ref],
  [Footprint Name #2] AS [Footprint Ref 2],
  [Footprint Name #3] AS [Footprint Ref 3],
  [Manufacturer Name],
  [Manufacturer Part Number],
  [Manufacturer Status],
  [Manufacturer LT (Wks)],
  [Supplier #1 Name] AS ComponentLink4Description,
  [Supplier #1 Name],
  [Supplier #1 Part Number],
  [Supplier #1 URL] AS ComponentLink4URL,
  [Supplier #1 URL], 
  IIf([Datasheet #1 Path]="N/A","","DATASHEET") AS ComponentLink1Description,
  IIf([Datasheet #1 Path]="N/A","","S:\" & Mid$([Datasheet #1 Path],23,255) & "\" & [Datasheet #1 File Name] & ".pdf") AS ComponentLink1URL,
  IIf([Datasheet #1 Path]="N/A","","" & Mid$([Datasheet #1 Path],14,255) & "\" & [Datasheet #1 File Name] & ".pdf") AS [Datasheet #1 URL],
  Mounting,
  [Procurement Type],
  [Unit Of Measure],
  [Serial Number],
  [Date Of Manufacture],
  ClassName,
  Resistance,
  Capacitance,
  Inductance,
  Impedance,
  Tolerance,
  Voltage,
  Power,
  RoHS,
  Qualifications,
  [Reviewed / Approved],
  [Supplier #2 Name] AS ComponentLink5Description,
  [Supplier #2 Name],
  [Supplier #2 Part Number],
  [Supplier #2 URL] AS ComponentLink5URL,
  [Supplier #2 URL],
  [Supplier #3 Name] AS ComponentLink6Description,
  [Supplier #3 Name],
  [Supplier #3 Part Number],
  [Supplier #3 URL] AS ComponentLink6URL,
  [Supplier #3 URL],
  [Datasheet #2 Description] AS ComponentLink2Description,
  IIf([Datasheet #2 Path]="N/A","","S:\" & Mid$([Datasheet #2 Path],23,255) & "\" & [Datasheet #2 File Name] & ".pdf") AS ComponentLink2URL,
  IIf([Datasheet #2 Path]="N/A","","" & Mid$([Datasheet #2 Path],14,255) & "\" & [Datasheet #2 File Name] & ".pdf") AS [Datasheet #2 URL],
  [Datasheet #3 Description] AS ComponentLink3Description,
  IIf([Datasheet #3 Path]="N/A","","S:\" & Mid$([Datasheet #3 Path],23,255) & "\" & [Datasheet #3 File Name] & ".pdf") AS ComponentLink3URL,
  IIf([Datasheet #3 Path]="N/A","","" & Mid$([Datasheet #3 Path],14,255) & "\" & [Datasheet #3 File Name] & ".pdf") AS [Datasheet #3 URL]
FROM [Standard Parts]
WHERE [Part Catalog] = "CAPACITOR"
ORDER BY
  [Contromax Part Number];

 

[Gasman]

Bit smaller

SELECT [Contromax Part Number], [Part Catalog], Description, [Schematic Comment] AS SchComment, [Symbol Path] AS [Library Path], [Symbol Name] AS [Library Ref], [Footprint Path], [Footprint Name #1] AS [Footprint Ref], [Footprint Name #2] AS [Footprint Ref 2], [Footprint Name #3] AS [Footprint Ref 3], [Manufacturer Name], [Manufacturer Part Number], [Manufacturer Status], [Manufacturer LT (Wks)], [Supplier #1 Name] AS ComponentLink4Description, [Supplier #1 Name], [Supplier #1 Part Number], [Supplier #1 URL] AS ComponentLink4URL, [Supplier #1 URL], IIf([Datasheet #1 Path]="N/A","","DATASHEET") AS ComponentLink1Description, IIf([Datasheet #1 Path]="N/A","","S:\" & Mid$([Datasheet #1 Path],23,255) & "\" & [Datasheet #1 File Name] & ".pdf") AS ComponentLink1URL, IIf([Datasheet #1 Path]="N/A","","" & Mid$([Datasheet #1 Path],14,255) & "\" & [Datasheet #1 File Name] & ".pdf") AS [Datasheet #1 URL], Mounting, [Procurement Type], [Unit Of Measure], [Serial Number], [Date Of Manufacture], ClassName, Resistance, Capacitance, Inductance, Impedance, Tolerance, Voltage, Power, RoHS, Qualifications, [Reviewed / Approved], [Supplier #2 Name] AS ComponentLink5Description, [Supplier #2 Name], [Supplier #2 Part Number], [Supplier #2 URL] AS ComponentLink5URL, [Supplier #2 URL], [Supplier #3 Name] AS ComponentLink6Description, [Supplier #3 Name], [Supplier #3 Part Number], [Supplier #3 URL] AS ComponentLink6URL, [Supplier #3 URL], [Datasheet #2 Description] AS ComponentLink2Description, IIf([Datasheet #2 Path]="N/A","","S:\" & Mid$([Datasheet #2 Path],23,255) & "\" & [Datasheet #2 File Name] & ".pdf") AS ComponentLink2URL, IIf([Datasheet #2 Path]="N/A","","" & Mid$([Datasheet #2 Path],14,255) & "\" & [Datasheet #2 File Name] & ".pdf") AS [Datasheet #2 URL], [Datasheet #3 Description] AS ComponentLink3Description, IIf([Datasheet #3 Path]="N/A","","S:\" & Mid$([Datasheet #3 Path],23,255) & "\" & [Datasheet #3 File Name] & ".pdf") AS ComponentLink3URL, IIf([Datasheet #3 Path]="N/A","","" & Mid$([Datasheet #3 Path],14,255) & "\" & [Datasheet #3 File Name] & ".pdf") AS [Datasheet #3 URL]
FROM [Standard Parts]
WHERE ((([Part Catalog])="CAPACITOR"))
ORDER BY [Contromax Part Number];

or formatted

SELECT [contromax part number],
       [part catalog],
       description,
       [schematic comment]                               AS SchComment,
       [symbol path]                                     AS [Library Path],
       [symbol name]                                     AS [Library Ref],
       [footprint path],
       [footprint name #1]                               AS [Footprint Ref],
       [footprint name #2]                               AS [Footprint Ref 2],
       [footprint name #3]                               AS [Footprint Ref 3],
       [manufacturer name],
       [manufacturer part number],
       [manufacturer status],
       [manufacturer lt (wks)],
       [supplier #1 name]                                AS
       ComponentLink4Description,
       [supplier #1 name],
       [supplier #1 part number],
       [supplier #1 url]                                 AS ComponentLink4URL,
       [supplier #1 url],
       Iif([datasheet #1 path] = "n/a", "", "datasheet") AS
       ComponentLink1Description,
       Iif([datasheet #1 path] = "n/a", "", "s:\" &
       Mid$([datasheet #1 path], 23, 255)
                                            & "\" & [datasheet #1 file name] &
                                            ".pdf")      AS ComponentLink1URL,
       Iif([datasheet #1 path] = "n/a", "", "" &
       Mid$([datasheet #1 path], 14, 255) &
                                            "\" & [datasheet #1 file name] &
                                            ".pdf")      AS [Datasheet #1 URL],
       mounting,
       [procurement type],
       [unit of measure],
       [serial number],
       [date of manufacture],
       classname,
       resistance,
       capacitance,
       inductance,
       impedance,
       tolerance,
       voltage,
       power,
       rohs,
       qualifications,
       [reviewed / approved],
       [supplier #2 name]                                AS
       ComponentLink5Description,
       [supplier #2 name],
       [supplier #2 part number],
       [supplier #2 url]                                 AS ComponentLink5URL,
       [supplier #2 url],
       [supplier #3 name]                                AS
       ComponentLink6Description,
       [supplier #3 name],
       [supplier #3 part number],
       [supplier #3 url]                                 AS ComponentLink6URL,
       [supplier #3 url],
       [datasheet #2 description]                        AS
       ComponentLink2Description,
       Iif([datasheet #2 path] = "n/a", "", "s:\" &
       Mid$([datasheet #2 path], 23, 255)
                                            & "\" & [datasheet #2 file name] &
                                            ".pdf")      AS ComponentLink2URL,
       Iif([datasheet #2 path] = "n/a", "", "" &
       Mid$([datasheet #2 path], 14, 255) &
                                            "\" & [datasheet #2 file name] &
                                            ".pdf")      AS [Datasheet #2 URL],
       [datasheet #3 description]                        AS
       ComponentLink3Description,
       Iif([datasheet #3 path] = "n/a", "", "s:\" &
       Mid$([datasheet #3 path], 23, 255)
                                            & "\" & [datasheet #3 file name] &
                                            ".pdf")      AS ComponentLink3URL,
       Iif([datasheet #3 path] = "n/a", "", "" &
       Mid$([datasheet #3 path], 14, 255) &
                                            "\" & [datasheet #3 file name] &
                                            ".pdf")      AS [Datasheet #3 URL]
FROM   [standard parts]
WHERE  (( ( [part catalog] ) = "capacitor" ))
ORDER  BY [contromax part number];

 

[Oscar42]

OK, Many Thanks for helping out a newbie. As you say Access won't keep the formatting so maybe I'll edit and version control it through another editor. Cheers

 

[Gasman]

If it is only ever one table, you can remove the table name.
However as soon as you edit it, Access will put the table name in again. For most queries that is not so much of a problem, but when you get to your size, it can get unweildy, with all the brackets and such.

 

[cheekybuddha]

Also you seem to have selected some fields twice, aliasing one. Why is that?

...
  [Supplier #1 Name] AS ComponentLink4Description,
  [Supplier #1 Name],
  [Supplier #1 Part Number],
  [Supplier #1 URL] AS ComponentLink4URL,
  [Supplier #1 URL],
...
  [Supplier #2 Name] AS ComponentLink5Description,
  [Supplier #2 Name],
  [Supplier #2 Part Number],
  [Supplier #2 URL] AS ComponentLink5URL,
  [Supplier #2 URL],
  [Supplier #3 Name] AS ComponentLink6Description,
  [Supplier #3 Name],
  [Supplier #3 Part Number],
  [Supplier #3 URL] AS ComponentLink6URL,
  [Supplier #3 URL],
...