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

O

Oscar42

New member
Local time
Tomorrow, 02:39
Joined
Apr 29, 2024
Messages
11
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!
 
simple query will, i think, do it for you:

Select Cars, IIF(Cars = "Blue", ABS, Null) As TheABS From YourTableName
 
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!
 
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.
 
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.
 
show as a mock-up of your table and the expected result.
 
I have this now but it's missing a pile of IFF statements:

1725700567552.png
 
can you copy and paste it as Text (not image).
note that your current table is not normalized (google table normalization).
 
I tried but it wouldn't let me post. It says it thinks it is some dodgy code :(
 
you see </> above the text editor, click it and paste the sql inside it.
 
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];
 
i don't see any missing IIF(), they all match the bracket.
 
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?
 
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:

SQL: 
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];
 
Last edited:
Bit smaller :)
Code: 
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
Code: 
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];
 
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
 
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. :(
 
Last edited:
Also you seem to have selected some fields twice, aliasing one. Why is that?
SQL: 
...
  [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],
...
 
Last edited:
You must log in or register to reply here.

Similar threads

N
Group BY or ORDER BY in a UNION SELECT Query
Replies
4
Views
180
Pat Hartman
P
D
Trying to append records to a SharePoint list. Getting "set 1 field(s) to Null due to a type conversion failure." Can query redefine type?
Replies
2
Views
144
GPGeorge
GPGeorge
D
Need help with SQL in an Access query that has UNION for the non-relational queries/tables
2
Replies
26
Views
473
dlhappy
D
I
MS Access SQL Error Adding a static value via UNION to a SELECT Distinct Query
Replies
10
Views
419
ebs17
E
T
Union query missing various column data
Replies
4
Views
881
Trocergian
T

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom