table design, I'm stuck

[mary]

I'm working on a db to track testing that needs to be done on a quarterly basis for about 15,000 line items. Ultimately, I'd like to run a query that would tell me which items where the testing is due or overdue.

I was thinking of a junction table, called tbl Results set up as follows:

idrimary key
id Item:foreign key
date of test: lookup from test date table
result


This setup would not tell me what is due or overdue. I didn't want to add a bunch of test dates to the big "test" table because it doesn't seem to be good design, but I'm having trouble coming up with another way. Any suggestions? I've looked, but I haven't found any similar questions, but I may not be searching the right phrases!

Thanks,
Mary

 

[namliam]

Well from your (as you call it) junction table you want to then fetch the last date using a "aggregate" or "group by" query.

Once you have the latest date you can take today's date (date()) and substract the two. Anything with more than 3 months difference is due or overdue depending on the timeframe...

Look up "Dateadd" and "Datediff" functions you will need them to solve your puzzle.