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.
Thanks
Brian

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

Eur0star1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Robert ShermanOwnerCommented:
So is it that you want to add a second field to [Forms]![frmServiceReportAdd] to also allow a Base Model to be specified for products that are composed of an existing model with design changes that incorporate additional parts?

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]

OR 

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

Open in new window


That should get you all possible parts for both the ScanModel and BaseModel.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Robert ShermanOwnerCommented:
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.
0
Eur0star1Author Commented:
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 :

tServiceReport                  
Various fields +

ModelID          >   \
                                 >    tModelPart    >  tParts
BaseID             >  /                


Thanks again
Brian
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Robert ShermanOwnerCommented:
If it throws an error because the BaseModel form control is null, try changing

(refScannerModelPart.ModelID)=[Forms]![frmServiceReportAdd]![BaseModel]

Open in new window


to

(refScannerModelPart.ModelID)=Nz([Forms]![frmServiceReportAdd]![BaseModel],0)

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?
0
Eur0star1Author Commented:
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.

Thanks
Brian
0
Eur0star1Author Commented:
The solution provided is straightforward and works as required .
KISS !
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.