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

O

Oscar42

New member
Local time
Today, 10:54
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!
 
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:
cheekybuddha said:
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 URL] AS ComponentLink5URL,
  [Supplier #2 URL],
  [Supplier #3 Name] AS ComponentLink6Description,
  [Supplier #3 Name],
...
Click to expand...

Well spotted. At the moment the query result is read by two different 3rd party applications. One of those applications requires fixed naming conventions but it is not read by the user. The second application requires it to be a user friendly name. The non-required field in both cases can be ignored by configuration in those applications.

However, it is confusing in both the query and applications so I intend to break it into two separate queries. One query for each application then I don't have to muck around with ignoring the unrequired respective fields.

Still learning...
 
You must log in or register to reply here.

Similar threads

E
Solved Union Query with Calculations
Replies
9
Views
875
arnelgp
arnelgp
C
Solved Is it possible to use (like) in a query for a field in another table
Replies
11
Views
991
DHookom
DHookom
AOB
UPDATE with LEFT-JOIN (conditional update query)
Replies
9
Views
758
isladogs
isladogs
Papa_Bear1
Getting an error 3825, complaining about multi-valued fields, but none are involved.
2
Replies
25
Views
974
Papa_Bear1
Papa_Bear1
essaytee
Is it possible: Query/SQL to dynamically display a record/order/position number, without underlying field from a table
Replies
11
Views
1,214
essaytee
essaytee

Users who are viewing this thread

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