compare records?

[gsdude]

Hello, Hopefully someone can help me with this.
I'm using this database to submit a form and emailing it.
It was simple how i was using, I had a table and depending where they where going the requirements where put in the form and emailed.
Well now I run into a problem where they might already have a certain test done within a year which are all valid for 1 year. So I decided to add another table that shows what test was done each time I submit a request.
How can I compare what was done and what will be needed before i submit the request?
I'm currently using vba to fill in the form and emailing.

This is an example of the table
In both tables, all fields are Yes/No except for the Datefield,SiteLocation

tblTestRequirements
SiteLocation
Test1
Test2
Test3
Test4
etc*

tblTestTaken
DateTaken
Test1
Test2
Test3
Test4
etc*

 

[jdraw]

I think there are things involved in your "business" that you have not identified in the post.
For example

What is test1 vs test2 ...? Does a Test have a DateTested? Pass/Fail? A more meaningful name/description?
Is there only 1 type of test?
Who are the clients, customers (people being tested)? They must have emails?

If a Test is valid for 1 year, you could derive "Upcoming Tests Required" (perhaps that's what you're doing, but haven't said explicitly).

Part of your issue may be related to table structure -but more info about your "business" would clarify that.
Bottom line is that more info is needed.
Good luck.

 

[pbaldy]

Both posts were moderated. Posting to trigger email notifications.

 

[gsdude]

Ok,
The people taking the test/exams are employees, before we send them to a different location, they must have 1 or multiple test completed before arriving at new location.
We only email our office , where they take the exams.
Each of our locations require them to take either 1 or multiple test.
So I setup the first table With these Fields
Table1 - This would be the the Location requirements
SiteLocation
Test1-Yes/No
Test2-Yes/No
Test3-Yes/No
Test4-Yes/No

Table2 - Once i Submit a request its adds to this table when and what exams based on Table1.
DateTaken
Test1-Yes/No
Test2-Yes/No
Test3-Yes/No
Test4-Yes/No

I do not want to track pass or fail for each exam, only date taken and what test was taken.

The form is based on each employee, I have a drop down to the locations(table1) they are going to and a button that submits required test for that location.

The only thing i was doing at first was submitting the required test, but when i sent them to take their exams, some of them already current test and was not needed. So i need a way to check the exams already taken.

I tough it was a good idea to go ahead and create a table with date taken and exams taken. what i want to do is when i click the submit button is check table 2 for any exams that are still current and compare to selected drop down location and submit any test that are still needed.

Hopefully this helps a little more.