M
Manuel Indacochea via AccessMonster.com
My Access skills are basic and I'm dealing now with the following:
Table1 has following fields:
(Country, PlantCode, InvoiceType, Department)
Table2 has:
(PlantCode, Department, Office)
Table3 has
(Country, PlantCode, Department)
I made a simple query matching "PlantCode" and "Department" on Table1 &
Table2 in order to assign "Office" field. This part is working fine. Let's
call the resulting query "Invoices_by_Office"
Now (this is the part I need help with) the data on Table3 are Departments
that needs to be deleted from "Invoices_by_Office". They can be deleted
only if "Country", "PlantCode" and "Department" match in both sides. This
is because (for example) a Department can repeat in another country.
I tried a delete query but do not know what should be the logic to match 3
criteria in order to do the exclusion.
I also tried adding another field named "DeleteCriteria" on table1 and
table3. This field may contain a concatenation of "Country", "PlantCode"
and "Department" which will make the search easier since only need to match
1 field in both tables. This was easy for table3 since it is a fixed table,
but I tried an UpdateQuery on Table1 to fill out the "DeleteCriteria"
column, but do not know how to make it concatenate those 3 fields. I
already used "&" operator but only worked for record#1, the rest is
tracking incorrect data.
Can anyone please recommend how to resolve my problem?
Table1 has following fields:
(Country, PlantCode, InvoiceType, Department)
Table2 has:
(PlantCode, Department, Office)
Table3 has
(Country, PlantCode, Department)
I made a simple query matching "PlantCode" and "Department" on Table1 &
Table2 in order to assign "Office" field. This part is working fine. Let's
call the resulting query "Invoices_by_Office"
Now (this is the part I need help with) the data on Table3 are Departments
that needs to be deleted from "Invoices_by_Office". They can be deleted
only if "Country", "PlantCode" and "Department" match in both sides. This
is because (for example) a Department can repeat in another country.
I tried a delete query but do not know what should be the logic to match 3
criteria in order to do the exclusion.
I also tried adding another field named "DeleteCriteria" on table1 and
table3. This field may contain a concatenation of "Country", "PlantCode"
and "Department" which will make the search easier since only need to match
1 field in both tables. This was easy for table3 since it is a fixed table,
but I tried an UpdateQuery on Table1 to fill out the "DeleteCriteria"
column, but do not know how to make it concatenate those 3 fields. I
already used "&" operator but only worked for record#1, the rest is
tracking incorrect data.
Can anyone please recommend how to resolve my problem?