MS Access help?

Question

I have a database of patients at my office, and would like to send reminder cards every year after thier exam. Currently I do this by going through the database, and copy/paste to a blank database to print the labels.
What I would like to do is set up a query to do this for me. I messed around a little and got it set up. but I didn't know what to put in criteria.
The records that I want included:
Had an exam in the current month within the past 5 years.

Alright, I wasn't expecting it to be this technical. The fields I have are Last Name, First Name, Address (street, state, and zip), Date Last Exam, and Examining OD (the doctor they last saw).

Basically what I need is to take the number of the month (I can just type this is, say 4 for April) then have it scan the records and have it pull up all the exams from every April for the past five years.

Thanks for the quick answers!

If you want I can send a copy of this file if it would help. Of course I would have to remove all of the records because of HIPPA privacy laws, but if it would help to know what we're dealing with I can send it.

Answer 1

You can add math computations to a query within the query builder by right clicking on the column and selecting build.

The query builder will help you graphically lay out a query but you can also view and/or edit the SQL (Structured Query Language) commands by clicking on SQLview.

The following query will return all records from TABLE1 which the have five or more years since the last visit for the month of April


SELECT Table1.*, Int((Now()-[lastvisiton])/365) AS yearsSince, Format([LastVisitOn],"mm") AS MonthLastVisit
FROM Table1
WHERE (((Int((Now()-[lastvisiton])/365))>=5) AND ((Format([LastVisitOn],"mm"))=4));

Answer 2

Set up a query with all the fields you need for your labels. Add one more field to be the month of the last exam, like this: MonthNo: DatePart("m",[Date Last Exam]). Put in two criteria. For the [Date Last Exam] field, put >DateAdd("yyyy", -5, Date()). For the MonthNo field, put either a number like 4 for April or DatePart("m", Date()).
Explanation: DatePart is an Access function that extracts part of a date or a time. The "m" means you want the month ("yyyy" gives the year, "q" gives the quarter, "d" gives the day, etc.). DateAdd is another function that adds or subtracts from a date. The "yyyy" means add years ("m" means add months, "d" means add days, etc.). The -5 means add -5 years (or subtract 5 years). Date() is the last function, which gives today's date.

Answer 3

The record is propietary ergo i think of you may purely run it using MS get admission to. you may even with the undeniable fact that browse it and edit it with a utility called "MDB Browser", google that. you additionally can hyperlink to and question the documents presented you have the suitable drivers put in.

Answer 4

you must write down some of your database table structure.
we can't give you good advice if we didn't know what's in your database.

eg. if you have table like this :
create table CLIENT
(FirstName varchar(20),
LastName varchar(20),
Address varchar(200),
LastExam date)

make a query like :
SELECT * FROM client WHERE (TODAY - LastExam <= 1825)
notes : 5 * 365 days = 1825

Answer 5

check http://msaccess.batcave.net

Answer 6

would have to know what fields you have to tell you if this can be done for sure.