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

ASKER CERTIFIED SOLUTION

Robert Sherman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Robert Sherman

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.

Brian Fielding

ASKER

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

Robert Sherman

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

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

Open in new window

(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?

Brian Fielding

ASKER

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

Brian Fielding

ASKER

The solution provided is straightforward and works as required .
KISS !