Getting a query result by comp'ing a date in a child table to a # in the parent

[Anchoress]

I am running Access2000 for a simple parent-child DB tracking stores in the parent and contacts with salespeople in the child.

I want to use a field in the parent table to store the maximum time that should pass between contacts, then use that number in a calculation with the most recent contact date to generate a list of overdue contacts.

The date format in the child table is MM/DD/YYYY, and I already have a successful query that generates the date of most recent contact for each store in the parent table.

Any help would be welcome for the following points:

1. What data format is best for storing the maximum time between contacts (stored in the parent table)? Should it be a number field? If so, containing numbers of days, weeks, or months? Or should it be some other format?

2. Any help building the query. Below is a list of the fields in the grouped query I'm currently using to show the most recent contact:

Salesperson (Parent table, text)
Company Name (Parent table, text)
Company Address (Parent table, text)
Notes (Parent table, memo)
MaxOfP/V Date (Child table, date)

The key field in the parent table is CID, and in the child it's IID.

 

[pbaldy]

A number field sounds like the best solution. It would represent the number of days, weeks or months, as appropriate to your needs. You could use that in your query:

DateAdd("d", IntervalField, MaxDateField)

 

[Anchoress]

Thanks PBaldy, the expression you suggested worked perfectly, I'm so excited!!