Delete Query based in a table (different scenario) and fields combination.

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

 

[Michel Walsh]

Hi,


Something like

DELETE DISTINCTROW table3.*
FROM table3 INNER JOIN table1
ON table3.Country=table1.Country
AND table3.PlantCode = table1.PlantCode
AND table3.Department = table1.Department



sure, always make your experimentation after you have a good backup, or on
not-critical data.


Hoping it may help,
Vanderghast, Access MVP

 

[Manuel Indacochea via AccessMonster.com]

Wonderful...this worked fine, now I have a new question: Can the deletion
be done from a query, based on table criteria?.

 

[Michel Walsh]

Hi,


I am not sure I understand. Sure, you can add a WHERE clause that
implied a field (from one of the implied table) and a parameter. If you use
DoCmd.RunSQL, the parameter can be of the syntax
FORMS!FormName!ControlName but if you use CurrentDb.Execute, you will need
to resolve that kind of parameter all by yourself, kind of:

Dim qdf As QueryDef : Set qdf=CurrentDb.QueryDefs("queryname")
Dim i As Long
For i = 0 to qdf.Parameters.Count - 1
qdf.Parameters(i).Value = eval( qdf.Parameters(i).Name )
Next i

' all parameters now have their value, execute the action query
CurrentDb.Execute qdf




Hoping it may help,
Vanderghast, Access MVP

 

[Manuel Indacochea via AccessMonster.com]

Thanks for you help. What I meant with the last question is the following:

At the beginning I tried to delete records on Table1 based on Table2. Now,
what if I would like to delete records on Query1 based on Table2? I tried
the same like in the first example but not worked, it says "Could not
delete from specified table" (of course it can't since it is a Query not a
Table).

Your help with this will be really appreciated.

 

[Michel Walsh]

Hi,


I see. No, you can't, since you need the physical table name. Query1
may have 2, 3, 4 tables, so which one will be the table from which the
DELETE would occur? Assume query1 has data from tableXYZ, then

DELETE tableXYZ.* FROM query1

won't work, since the table name, even if present in query1, has been lost
of sight, and SQL will claim you have an illegal reference about a tableXYZ;
but



DELETE tableXYZ.*
FROM query1 INNER JOIN tableXYZ
ON query1.tableXYZ.primaryKey = tableXYZ.primaryKey

would have better chances to work (I haven't tried, but I guess that I would
need to add DISTINCTROW)




Hoping it may help,
Vanderghast, Access MVP