Replacing Specific Row with the data from another table that matches specific condition-CodePudding

I wrote a query in Access that produces the following result.

qryCalculateXY
EquipmentID, ZoneNumber, RowNumber, ColumnNumber, XCoordinate, YCoordinate, ComponentID
1, 0, 1, 1, 500, 600, 1
1, 0, 1, 20, 500, 1200, 1
.
.
.
.

This query quickly calculates 1000 XY co-ordinates of PrimaryComponent based on adding very few rows (maximum 30) in the table. And I am very happy with this so far.

However, there are very few locations (maximum 20) where, I may have ComponentID changed. For that I've created a table as below that lists out all exceptions.

tblException
EquipmentID, ZoneNumber, RowNumber, ColumnNumber, ComponentID
1, 0, 1, 20, 2

I want to generate another query which would list out all rows from qryCalculateXY as it is where it does not find corresponding values of EquipmentID, ZoneNumber, RowNumber, ColumnNumber from table tblException. And replace the value of ComponentID from tblException where values of these four columns match in the table tblException.

The resulting query should look like this -

qryCalculateXYFinal
EquipmentID, ZoneNumber, RowNumber, ColumnNumber, XCoordinate, YCoordinate, ComponentID
1, 0, 1, 1, 500, 600, 1
1, 0, 1, 20, 500, 1200, 2
.
.
.
.

This would save lots of time from my side not to convert query into a table and then change specific values.

How can I achieve this?

Thanks, Nimish

I tried the left joint but to no avail.

CodePudding user response：

assuming:

'table xy
----------------------------------------------------------------------------------------------------------------------------------------------------
|    EquipmentID     |     ZoneNumber     |     RowNumber      |    ColumnNumber    |    XCoordinate     |    YCoordinate     |    ComponentID     |
----------------------------------------------------------------------------------------------------------------------------------------------------
|                  1 |                  0 |                  1 |                  1 |                500 |                600 |                  1 |
----------------------------------------------------------------------------------------------------------------------------------------------------
|                  1 |                  0 |                  1 |                 20 |                500 |               1200 |                  1 |
----------------------------------------------------------------------------------------------------------------------------------------------------

' saved query qryCalculateXY:

SELECT tableXY.*
FROM tableXY;

QryCalculateXYFinal:

'qryCalculateXYFinal

SELECT qryCalculateXY.EquipmentID, qryCalculateXY.ZoneNumber, qryCalculateXY.RowNumber, qryCalculateXY.ColumnNumber, qryCalculateXY.XCoordinate, qryCalculateXY.YCoordinate, DLookUp("ComponentID","tblException","EquipmentID = " & [EquipmentID] & " AND ZoneNumber = " & [ZoneNumber] & " AND RowNumber = " & [RowNumber] & " AND ColumnNumber = " & [ColumnNumber]) AS ExceptionComponentID, IIf([ExceptionComponentID],[ExceptionComponentID],[qryCalculateXY].[ComponentID]) AS ComponentID, GetReplacementComponentID([ComponentID],[EquipmentID],[ZoneNumber],[RowNumber],[ColumnNumber]) AS C2
FROM qryCalculateXY;

'ExceptionComponentID

ExceptionComponentID: DLookUp("ComponentID","tblException","EquipmentID = " & [EquipmentID] & " AND ZoneNumber = " & [ZoneNumber] & " AND RowNumber = " & [RowNumber] & " AND ColumnNumber = " & [ColumnNumber])

'The fixed ComponentID

ComponentID: IIf([ExceptionComponentID],[ExceptionComponentID],[qryCalculateXY].[ComponentID])

'C2

C2: GetReplacementComponentID([ComponentID],[EquipmentID],[ZoneNumber],[RowNumber],[ColumnNumber])


'GetReplacementComponentID in code module

Public Function GetReplacementComponentID(ComponentID As Long, EquipmentID As Long, Zonenumber As Long, RowNumber As Long, ColumnNumber As Long)
Dim returnvalue As Long
returnvalue = Nz(DLookup("ComponentID", "tblException", "(EquipmentID = " & EquipmentID & ") AND (ZoneNumber = " & Zonenumber & ") AND (RowNumber = " & RowNumber & ") AND (ColumnNumber = " & ColumnNumber & ")"), ComponentID)

 GetReplacementComponentID = returnvalue
End Function

as shown above you can look up the appropriate ComponentID in the query. ExceptionComponentID is just there to make reading easier you can replace it with its expression everywhere it occurs or better yet abstract even more of the Calculations to a public function like in c2. You can cut, paste, import the tables and sql into your version of Access. You can also replace the vba functions with a subquery which is not shown.

CodePudding user response：

Use Nz in a simple query with a left join:

Select 
    qryCalculateXY.EquipmentID, 
    qryCalculateXY.ZoneNumber, 
    qryCalculateXY.RowNumber, 
    qryCalculateXY.ColumnNumber, 
    qryCalculateXY.XCoordinate, 
    qryCalculateXY.YCoordinate, 
    Val(Nz([tblException].[ComponentID],[qryCalculateXY].[ComponentID])) As ComponentID
From 
    qryCalculateXY 
Left Join 
    tblException On 
        (qryCalculateXY.ColumnNumber = tblException.ColumnNumber
        (qryCalculateXY.RowNumber = tblException.RowNumber) AND 
        (qryCalculateXY.ZoneNumber = tblException.ZoneNumber) AN
        (qryCalculateXY.EquipmentID = tblException.EquipmentID);

Output: