Avatar of Brian Fielding
Brian FieldingFlag for United Kingdom of Great Britain and Northern Ireland

asked on 

Develop Union Query for Access 2013

As part of a service database I have a reference set of scanner reference tables:
      tMake   m -> 1   tModels   1 -> m   tModelPart   m <- 1  tParts

When a Service Report is input to the database a simple query is used to list all the scanner’s parts so that those used on the service can be selected and shown on the service report:
      ModelID (from Service Report)  >  tModelPart  >  tParts

This needs to be extended to enable parts used to be added to reports for hybrid scanners.
Typically a hybrid scanner consists of a few special parts (identified by ModelID) and based on existing scanner / parts data (identified by BaseID).
Now on inputting the Parts used on a Service Report  I need a union? Query to list all parts based on
      ModelID (from Service Report)  >  tModelPart  >  tParts
      BaseID (from Service Report)  >  tModelPart  >  tParts
(note: that BaseID would not be present for existing scanners ! )

I attach the existing SQL.

I have had various attempts but cannot get a suitable query
Can you please help me develop the necessary query.

SELECT DISTINCT refScannerParts.PartID, refScannerParts.PartName, refScannerParts.PartCost, refScannerModelPart.ModelID

FROM refScannerParts INNER JOIN (tblServiceReports INNER JOIN refScannerModelPart 
ON tblServiceReports.ScanModel = refScannerModelPart.ModelID) 
ON refScannerParts.PartID = refScannerModelPart.PartID

WHERE (((refScannerModelPart.ModelID)=[Forms]![frmServiceReportAdd]![ScanModel]));

Open in new window

Microsoft AccessSQL

Avatar of undefined
Last Comment
Brian Fielding
Avatar of Robert Sherman
Robert Sherman
Flag of United States of America image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Actually, upon further reading of your query, I see that you are joining your service reports table to the scanner model part table, so maybe I misunderstood your request.
Avatar of Brian Fielding


Thanks for the prompt reply;  Saturday (in UK) night is not a good night to discuss database systems !!

Your modification actually works !   Thanks - sorry don't mean that in a derogatory way - it looks so simple !!

It is possible that  frmServiceReportAdd]![BaseModel]  is absent (or Null) for non-hybrid scanners so need to handle that  (it may do !)

Essentially I'm looking for a means of listing Parts (from tParts) using two fields (ModelID and BaseID)  in the Service Report  using the table  tModelParts (many to many) to point to parts in tParts.

diagrammatically :

Various fields +

ModelID          >   \
                                 >    tModelPart    >  tParts
BaseID             >  /                

Thanks again
If it throws an error because the BaseModel form control is null, try changing


Open in new window



Open in new window

It looks to me that the query as is will only return records for models that already have atleast one service record in tblServiceReports.   Doesn't that cause an issue for models that don't yet have any service reports?   Or are you saving a new service report record to the table before you reach this part of the process?
Avatar of Brian Fielding


I spent some time experimenting with solution and your solution works.  I think I initially had a solution (using a union query) and was looking for a problem and it would never work.

Because we need to keep service reports for a number of years, when a service report is first input the user has to ensure the scanner details are on the database;  he has to input all necessary data if the scanner is not already there.

So, when a Service report is initially set up it will have the scanner details including Model and Base model as necessary.  The fact that the base model can be absent does not impact the query does not affect as that part of the query will return no data.

Avatar of Brian Fielding


The solution provided is straightforward and works as required .
Microsoft Access
Microsoft Access

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo