Help producing queries

[skidia]

Hi,



I have taken over a new job which includes managing a pre-existing membership database, found in the form of an Access table. I have used Access before, but only when someone else has written the table, forms, queries etc for me! I am reasonably good at Excel

I would like Access to give me of emails addresses for people whose membership has expired, which I can then use in a mail merge with Outlook. However I don't have a foggiest idea of how best to do this! Should I use a query or a report? Where do I put a formula? The field is Expiry Date on the table.

The query/report should also check the data against a check box column called 'Not Renewing' for people who have told us they are not renewing their membership. I don't want to email them with a renewal letter.

Lastly, I would like the query to check against a check box column called 'Postal list' for people who have opted out of email, and remove them from the report. Some of the 'Postal list' people have email addresses listed for emergencies, so the fields may not be blank. I will need to write a new query to give me their addresses.

[Rawb]

If you plan on regularly modifying/updating the database or want to design any new databases, I'd strongly recommend finding a good book on learning Access. I'm sure there are plenty of people here that could point you in the right direction there (I'm not one of them, unfortunately. I learned Access the hard way :/).

There are several ways to do this, depending on just how much work you want to put into it and what, exactly, it will end up doing.

The easiest way is to just make two Queries. One that returns a list of expiring e-mails, and one that returns the address information of people that don't want to receive e-mails. Although it's the easiest way to set up, it also requires the most work each time you run it (because you're still doing all the Outlook/Word stuff yourself).

Once you have the data from those two Queries, then you can cut and paste it into Outlook (or Word, etc. for the mailings).

An example of the Query to get e-mail addresses would be something like this:

SELECT
  [EMailAddress]
FROM
  MyTable
WHERE
  [EMailAddress] IS NOT NULL AND
  [DoNotEMail]=False AND
  [MembershipExpires]<Date()

The above would return a list of every e-mail address listed in MyTable where they haven't selected [DoNotEMail] and where their [MembershipExpires] date occurred before today (the day the Query was run).