Tick Boxes = WHERE clause

[dr4ke1990]

At frequent points throughout every month I have to email customers to make them aware of maintenance; the current set-up is to manually pull out each persons email address from a big list and email them... very time consuming!



what I would like to do is set up a Table with the customer name and the email address of the customer (multiple lines if more than one email address). My plan is then to create a form with all customer names (derived from the table itself using a DISTINCT function) and a tick box next to the name, if the tick box is checked next to the customers name it should feed into a query WHERE and display only those customers.

Is this possible?

[SoftwareMatters]

Yes that all sounds possible

[dr4ke1990]

Yes that all sounds possible

Are you able to offer some assistance ...?

[ItsMe]

Are you able to offer some assistance ...?

I would suggest starting with your table design. If you have more than one email associated with an account you probably have more than one contact name associated with an account. Perhaps there is a need to have two tables to keep track of these entities. One table to describe the account and another table to describe the individuals associate with the account.

[dr4ke1990]

This is how I envisage the table looking (below). So I'd like to have, in this example, 4 customer names and 4 tick boxes; the query would then pull off the customers that have their tick box ticked..

ID	CustomerName	PersonName	PersonEmail
1	Customer1	Person1	Person1@domain.co.uk
2	Customer1	Person2	Person2@domain.co.uk
3	Customer1	Person3	Person3@domain.co.uk
4	Customer2	Person1	Person1@domain.com
5	Customer2	Person2	Person2@domain.com
6	Customer3	Person1	Person1@domain.com
7	Customer4	Person1	Person1@domain.com

[ItsMe]

I envision something like....



tblCustomers

CustPK
CustName
CustAdd1
CustAdd2
CustCity
CustState
CustPostal
etc....

tblContacts

ContPK
CustFK
ContFirst
ContLast
ContGender
ContPH
ContMobile
ContEmail
etc.....

[dr4ke1990]

I don't need all of that information; I only need what I posted above...

[ItsMe]

I don't need all of that information; I only need what I posted above...

tblCustomers

CustPK
CustName


tblContacts

ContPK
CustFK
ContName
ContEmail

[dr4ke1990]

Ok - so how do I make the tick boxes to form part of the Where clause? I am comfortable with what I want my tables to be.

[ssanfu]

OK, how about this?

tblCustomers
CustID_PK	Autonumber
CustName	Text
IsSelected	Y/N (boolean)

tblEmailAddress
EmailID_PK	Autonumber
CustID_FK	Number - Long (Foreign Key to tblCustomers)
sFName	Text
sLName	Text
sEmail	Text

Query to select customers:

SELECT tblCustomers.CustID_PK, tblCustomers.CustName, tblCustomers.IsSelected
FROM tblCustomers
ORDER BY tblCustomers.CustName;

Query to get emails:

SELECT tblCustomers.CustName, tblEmailAddress.sFName, tblEmailAddress.sLName, tblEmailAddress.sEmail
FROM tblCustomers INNER JOIN tblEmailAddress ON tblCustomers.CustID_PK = tblEmailAddress.CustID_FK
WHERE (((tblCustomers.IsSelected)=True))
ORDER BY tblCustomers.CustName;

Would also need code to set "IsSelected" to FALSE every time the form is opened.