SQL Question

[Jon Lewis]

I have a main Form frmCustomers with subforms for Contacts, Installs and
Business.

Each of the recordsource tables for the main form and the subforms has a
Delete Yes/No field by which users request the database administrator to
delete records (either subform records or main customer records).

I need to generate a RecordSource for frmCustomers that includes all
Customers to be deleted plus all Customers where any subform data needs
deleting.

Setting frmCustomer's RecordSource to the saved query below gives me the
desired recordset, but as it's a Union query, the recordset is not
updateable. In some cases the administrator will refuse a delete request
and need to edit the record accordingly.

Can anyone help with restructuring the query to be updatable or perhaps
filtering frmCompanies with just tblCompanies as its RecordSource to achieve
the same result.

TIA (query is below

SELECT tblCompanies.* FROM tblCompanies WHERE tblCompanies.Delete=True UNION
SELECT tblCompanies.*
FROM tblCompanies INNER JOIN tblContacts ON tblCompanies.CompanyID =
tblContacts.CompanyID
WHERE tblContacts.Delete=True UNION SELECT tblCompanies.*
FROM tblCompanies INNER JOIN tblBusiness ON tblCompanies.CompanyID =
tblBusiness.CompanyID
WHERE tblBusiness.Delete=True UNION SELECT tblCompanies.*
FROM tblCompanies INNER JOIN tblInstalls ON tblCompanies.CompanyID =
tblInstalls.CompanyID
WHERE tblInstalls.Delete=True;

 

[Dirk Goldgar]

I have a main Form frmCustomers with subforms for Contacts, Installs
and Business.

Each of the recordsource tables for the main form and the subforms
has a Delete Yes/No field by which users request the database
administrator to delete records (either subform records or main
customer records).

I need to generate a RecordSource for frmCustomers that includes all
Customers to be deleted plus all Customers where any subform data
needs deleting.

Setting frmCustomer's RecordSource to the saved query below gives me
the desired recordset, but as it's a Union query, the recordset is not
updateable. In some cases the administrator will refuse a delete
request and need to edit the record accordingly.

Can anyone help with restructuring the query to be updatable or
perhaps filtering frmCompanies with just tblCompanies as its
RecordSource to achieve the same result.

TIA (query is below

SELECT tblCompanies.* FROM tblCompanies WHERE
tblCompanies.Delete=True UNION SELECT tblCompanies.*
FROM tblCompanies INNER JOIN tblContacts ON tblCompanies.CompanyID =
tblContacts.CompanyID
WHERE tblContacts.Delete=True UNION SELECT tblCompanies.*
FROM tblCompanies INNER JOIN tblBusiness ON tblCompanies.CompanyID =
tblBusiness.CompanyID
WHERE tblBusiness.Delete=True UNION SELECT tblCompanies.*
FROM tblCompanies INNER JOIN tblInstalls ON tblCompanies.CompanyID =
tblInstalls.CompanyID
WHERE tblInstalls.Delete=True;

Try this and see if it works:

SELECT tblCompanies.* FROM tblCompanies
WHERE tblCompanies.Delete=True
OR CompanyID IN
(SELECT CompanyID FROM tblContacts
WHERE tblContacts.Delete = True)
OR CompanyID IN
(SELECT CompanyID FROM tblBusiness
WHERE tblBusiness.Delete = True)
OR CompanyID IN
(SELECT CompanyID FROM tblInstalls
WHERE tblInstalls.Delete = True)

 

[Jon Lewis]

Awesome Dirk!!

Works perfectly, thanks for the swift reply.
Must get my head around nested Select statements sometime!

Thanks again.

Jon