counting the last checked yes/no box in a cascade database

[laona]

I’m working with three table in a cascade type of join or relationships; the table names are, Villages, Landfills and Site Visits. In the Village table there’s a field named “Village” that can’t have duplicate entries and is the Key field. There’s 32 Villages we work with. In each village I have 2 to 3 Landfills. So I have a table called Landfills and there’s about 50 entries in it with the Key field called, “LandfillName” which has no duplicate entries. In the Landfill table there’s also a field called “Village”, which is tied to the Village Table, this field has duplicate entries. The next table I have is called Site Visits and the Key field for it is called “LandfillVisit” which has no duplicate entries. There’s also a field called “LandfillName” and this field is connected to the Landfill Table and can have duplicate entries. The Site Visits Table is the one that will have the most entries, because every time I visit a landfill I input the information there. Now what I need to do is count the number of villages that has an incinerator in their landfill. In the Site Visit table there’s a yes/no field for incinerators and I check the field if the landfill has an incinerator, the next time I visit that village again, I check the box again if I see there’s an incinerator. I want to run a query that counts the number of villages that has an incinerator out of the 32 villages. I have up to 90 entries however in my Site Visit Table, because We've visited each village multiple times. I want to only count the last visit to each village and if there’s a yes checked for incinerator. This question was hard for me to figure out how to write and ask, if you need more clarification please ask and I’ll keep trying to get out what I’m trying to ask out. I need help! Thanks for trying to help.

[orange]

A Village may have 1 or more Landfills
A Landfill may have 0 or 1 incinerator
An Employee makes 0 or more SiteVisits
A Site visit is made to 1 Landfill and is associated with a SiteVisitDate.

Village ---> Landfill-->LandfillSiteVisit<---Employee

tblVillage
VillageID PK
VillageName

tblLandfill
LandfillID PK
LandfillName
HasIncineratorYN
VillageIDFK fk to tblVillage

tblLandfillSiteVisit
LandfillSiteVisitID PK
LandfillIDFK fk to tblLandfill
LandfillVisitDate
LandfillVisitorIDFK fk to tblEmployee
LandfillVisitComment

I would suggest a composite unique index be made from the fields in orange(to prevent duplicates).

tblEmployee
EmployeeID PK
EmployeeFirstName
EmployeeLastName
other Employee attributes

I have looked at your post. I think this represents a model of your tables based on the post.

Good luck.