database design

You can likely find a low cost solution after researching on Fiverr.  https://www.fiverr.com/search/gigs?query=database&source=main_banner&search_in=everywhere&search-autocomplete-original-term=database

To re-word your requirement, you are looking for the most recent positive test date and comparing it to the current day to calculate if 14+ days have passed, correct?

If so, you could query for the maximum date of positive tests by area, then calculate the number of days since that date.

Just to add a bit to what Shaun said, you'd have one record per day per testing area.    It would be have the testing area, date tested, and a true/false field for the pass/fail.

 But what you'd need is a subquery to look back 14 days from each date to see if there was any 14 day period that was all negative free, which is easy enough to do.

Of course there are many ways to do this, but this would be the most straightforward.

Jim.

And just to add a bit more, you'd need two tables:


tblTestAreas
TestAreaID - Autonumber - Primary key
TestAreaName - Text - Candidate Key

tblTestAreaSwabs
TestAreaSwabID - Autonumber - Primary key
TestAreaID - Long - Foreign Key to tblTestAreas
TestDate - Date/Time
Passed - True/False

 You might also need to tack on a table for each plant specimen:

tblPlantSpecimen:
PlantSpecimenID - Autonumber - Primary Key
...and whatever other info you'd want to record about the plant

tblTestAreas
TestAreaID - Autonumber - Primary key
PlantSpecimentID - Long - Foreign Key to tblPlantSpecimen - Candidate Key 1A
TestAreaName - Text - Candidate Key 1B

tblTestAreaSwabs
TestAreaSwabID - Autonumber - Primary key
TestAreaID - Long - Foreign Key to tblTestAreas
TestDate - Date/Time
Passed - True/False


 and possibly a few others to track species and other things.

Jim.

i have attach my database and excel sheet i need to put in a database. As far as subqueries im still learning.
CAPAVS2.accdb

Environmental-Monitoring-CAPA-Form_.xlsx

A sub-query is just a query within a query that executes for each row in your "main" query.

let's say you had 4 dates that you wanted to look at:

4/1
4/2
4/3
4/4

  For each one of those, there is a 14 day look back window.  That's what you use the sub-query for because you'd need to do that for each date.  You'll also need to check for the date if there are 13 measurements prior to that.

  I'll have a look at the DB in a bit.

Jim.

I looked at the spreadsheet and the database.  There's quite a bit there.

 Without knowing a lot more the details of what your trying to model, it's hard to offer anything.   I would certainly though keep track of the swabs in individual records.

  SQL, which is applied to relational databases, is designed to work with sets of records, not fields.   So that's why you always want to Normalize a database (follow a a set of rules for how the tables should be setup).

 So say for the swabs, you did a record that has this:

Day1   Day2  Day3  Day4...Day 14

 There are a number of issues with this.   One SQL (queries) won't work well.   If I want a count of the number of areas that are on their last few days, then I have to look into each record at 14 different fields.  And which 14 days?  Also, what happens if all of a sudden someone wants a 20 day test window?

 You can cheat a bit do this with a single field as a 14 byte string (using one byte per day) and then count the pass/fails or how much is filled in, but that's not the ideal way to do it.

 Let's see if anyone else has other comments before we move on.

Jim.

Taking a look at your database i would go to something like this :
A table holding the task as an entity...eg. if you are checking for "Listeria Innocua" i would have an entry for "Task : Listeria Innocua" & a Number to indicate its order ...and also sets of testing Like :
Task : Listeria Innocua1 --> 14 days test
Task : Listeria Innocua2 --> 20 days test
Then i would have another table that will hold the areas that this task will be applicable
e,g Processing Ln 2,Processing Ln 3,Processing Ln 4....
and you could have assosiations like
Listeria Innocua1 <-->Processing Ln 2,Processing Ln 3
Listeria Innocua2 <-->Processing Ln 4,Processing Ln 7
Then for each of these assosiations i would have a table that will hold the actual swab test...i would go in favor of the single bit (sorry Jim) because of the probably flexible nature of testing...this test requires 14 days...this 20 days....this 100....no need for 100 fields...and normalization in this particular case i don't think is "handy"
So your table would be linked to the main table (Task : Listeria Innocua1) and also to the assosiation (Listeria Innocua1 <-->Processing Ln 2,Processing Ln 3) and you should end up with a 2 row table entry
Now ...i reckon the workflow should be something like this.....each day you open the form the application would ask if you had a negative incident...if NO all records will have an Edit and and the bit field would increment by one (1111 < --4days  ---> 11111 <-- 5 days)...if a test has a negative entry it would write a zero ..a flag of closed will be applied and a new Record for this particular swab test will be inserted.
That's for start.

i would go in favor of the single bit (sorry Jim) because of the probably flexible nature of testing...this test requires 14 days...this 20 days....this 100....no need for 100 fields...and normalization in this particular case i don't think is "handy"

 The issue though is that sooner or later, you'd run into problems.   As I mentioned at the end of my comment, you could use a single text field (characters or bits really doesn't matter - the concept is the same).   Say you went with bits and set aside a 255 byte field.   255 * 8 bits = max test window then is 2040 days.  Now someone comes along and wants a six year test window.   Oops.

 What if someone asks "How many fails have we had in the past thirty days?"  Instead of straight SQL to count the records, you have to visit each record, look at the appropriate bits (tests may have started on different dates), and count them one by one to get the number of fails.

 Using bits or bytes gives you a repeating field within a record, which is not a good relational design.  

 Each swab test should be a record on it's own, not a repeating field in a single record.

Jim.

Probably i didn't wrote it clearly...i meant a bit for each day
On the task ID 152 there was a negative entry on the 5th day and we had to restart it

Probably i didn't wrote it clearly...i meant a bit for each day

 No, I understood what you said that way and the comments I made still stand.  

 And you are not really using bits there, but a byte to represent True/False.   If I did a Len() check on that field, it would be 14 at the end of this test.  With this setup, you'd be limited to a test window of 255 days unless you used a memo field.

Jim.

yes i'm still searching for a solution

I agree with Jim Dettman's approach.  Depending on the volume of data, though, you may run into performance problems using subqueries to calculate the number of consecutive negative days for each sample.

I would suggest modifying Jim's table design by adding a denormalized field with the number of consecutive negative days.

tblTestAreaSwabs
TestAreaSwabID - Autonumber - Primary key
TestAreaID - Long - Foreign Key to tblTestAreas
TestDate - Date/Time
Passed - True/False
DaysNeg - Long - Number of consecutive days with a negative test result

You would not want your users setting this field manually.  You could set it one record at a time if you are using a Form to collect the data by adding code to the Form_BeforeUpdate event.  Or you could write a process to loop through all the records in VBA to set this value prior to running queries or reports on it.

This would provide you a lot of flexibility to write highly performant queries that will give you the data you need.

@Mike,

  That's a great idea.

Jim.