Query to see who is missing

[rakoon]

I have a database made for a ministry for my church. It tracks attendance by week. There is a table with first, last and date of first attending. I have another table with first last and day attended. I have a form to update each week as to who is there and who isn't. I want to run a query that will show me who hasn't shown up in the last 3 months. What would be the best way to build that query?

[pbaldy]

Build a query of who has shown up in the last 3 months, and then use the unmatched query wizard to compare that to the list of everybody.

[CJ_London]

use the query builder


On the face of it you would use the first table with a query something like

SELECT *
From FirstTable
WHERE LastDate<datediff("m",-3,Date())


but for any more focussed help you need to clarify what your two tables contain - what's the difference between 'first, last and date of first attending' and 'first last and day attended'

[cbende2]

It may help if you add another field [Attended] which would be a checkbox.

This will make it a little easier when building the query, you can set a date range of 3 months where [Attended] is equal to 'No'.

[rakoon]

I have a query that shows me who has not attended since say 1-1-2015. (Under the day field in the query for criteria i have <[Enter Date]). What i need is for that list of names to not have those that have come since 1-1-2015.
Ajax - the difference between the two tables is that the first table has the date that they first came. The second table day field shows what days they have attended. The database is two years old and adding a check box is not something i would want to do unless I had to.

[CJ_London]

to repeat

but for any more focussed help you need to clarify what your two tables contain

provide the table names, field names and some example data

[RayMilhon]

Just to make sure I understand you have 2 tables

Table1
Firstname
lastname
FirstAttendance

Table2
Firstname
lastname
Attendance

Where table 1 would have 1 entry per person and table 2 would have multiple entries per person.

example

Table 1

John Smith 01/01/2015

Table 2
John Smith 01/08/2015
John Smith 01/15/2015
John Smith 01/22/2015
John Smith 02/05/2015

Is this correct?

[rakoon]

yest is correct

[RayMilhon]

Based on the structure you have You need to do 3 steps

First build a union query to get all of the data in 1 place

Select lastname, firstname, attendance date
From table 1
Union all
Select lastname, firstname, attendance date
from table 2

That put's all of your data in 1 query Then you need a groupby

Select lastname, firstname, lastof(attendance date) as lastday
from unionquery
group by lastname, firstname

Finally

Select lastname, firstname
from qrystep2
where datediff("m",lastday,now()) >=3

Although at some point you may want to restructure your database to make querying easier

Table1
personid
lastname
firstname

table2
personid
attendancedate

with that structure to get the query you want would be

Select table1.lastname, table2.firstname
from table1 inner join table2 on table1.personid=table2.personid
Where Datediff("m",table2.attendancedate,Now())) >= 3

[rakoon]

Thanks that worked great!