Finding records with dates older than 6 months in a related table

[starz25]

Hello,

I am new to Access and am having difficulty with the last query I need to design in my database.

I have 2 tables [Family] with fields ID, fName, lName, Lic_Num and Archive (yes/no). [Food_Date] with fields ID, Family and Food_Date. There is a one to many relationship between Family.ID (one) and Food_Date.Family(many).

I would like to know the best way to determine families who have dates older than 6 months in the Food_Date.Food_Date field.

Thanks!

 

[alansidman]

Join your tables in the query on your PK and FK. In the criteria for the Food_Date Field put >Date() + 180. That should give you only records that are older than 180 days from the current date.

 

[starz25]

alansidman,

Thanks for the quick reply. It looks like that will give me all the records from the Food_Date table that are older than 6 months. Let me clarify what I am trying to find.

I want to create a form where it lists all the Families from the Family table that have either no records in the Food_Date table or have records that are only 6 months old. I plan to use this to archive those families that are not active (families with dates newer than 6 months ago should be excluded).

 

[alansidman]

ok. Got it. In the criteria for the same field add this to the next line down in the criteria which will create an "Or" statement. Is Null. So this will give you everyone that has a record older than 6 months or has no record.

Alan