Swab Tracking Database

[Hammilton]

Hello,

I haven't used Access in a few years (8?) and I've suddenly gotten what I think might be a fairly large task that I need to complete using it for my work.



I need to create a database that I can use to track environmental swabs. We test the work environment for various bacteria on a regular basis, and the current method of tracking the swabs that are completed is cumbersome and I don't know how accurately it's being done, so basically I need to do the following:

Enter new swabs, the date it was taken, where it was taken (selected from a pre-set list of 90-some numbered sites), what the test was for, and whether it is positive or negative.

If negative is selected, it should end there, but if positive is selected I need it to prompt the user to conduct 5 additional tests in the area, so these will have the date it is tested, and where it is taken from (this will be a user-created description of the location) and then whether it is positive or negative. If these locations are positive it should not prompt for an additional 5 tests in the area, only on the initial tests.

Any locations that test positive need to have three consecutive negative tests or it needs to prompt the user to continue testing the location.

I also need a report that'll show any outstanding tests that do not have results entered yet, and ideally to highlight any that are 5 days after the initial testing date so the user will know that we should have had results back on those by now and to look into it.

Has anyone done anything similar? This is possible using Access, correct? It's been so long since I've used access for anything this complicated that I really don't remember if it is, but I think it should be, right? If anyone can offer any guidance for me it would be greatly appreciated!

Thanks,

Ham

[CJ_London]

Has anyone done anything similar?

yes

This is possible using Access, correct?

yes

It's been so long since I've used access for anything this complicated that I really don't remember if it is, but I think it should be, right?

yes

If anyone can offer any guidance for me it would be greatly appreciated!

ok - please clarify what guidance do you require? For any constructive guidance, please provide details of table names, field names and types, example data and their relationships. Also include some example data and examples of the required outcomes. You will also need to clarify the process -where does the data come from, how users are interacting with the data, etc.

You have asked many questions but not explained the process

[orange]

Further to Ajax's response, please describe the process in plain English. That is, do not assume we know anything about your environment or process. Keep it simple like you were talking to 6 year olds. Once we understand WHAT you are trying to do, I'm sure suggestions and options will follow.

Good luck.

[Hammilton]

Okay, I'll do my best. If you have any questions about specifics I'll answer them as soon as I can.

Okay, so on a random basis, a number of sites are selected for swabbing. The EE will then use a form to create an entry in the initial swab database, this will include Swab Number (IS1, IS2, IS3, etc) that will be automatically generated, the date conducted (I'd like it autofilled, but able to be edited if it for some reason it's being added a day late or something), the location (there are 90 some numbered sites, I'd like to enter the number and have the site name filled in), and then whether the swab was positive or negative and if positive, I'd like a drop down box with a few options to become available (Listeria, Salmonella, EB). The step of selecting positive or negative and what it was positive for won't be done for 2-5 days after the initial creation of the entry.

I would like to have a report that will show all outstanding swabs (ie, those that have yet to have results selected), and to highlight those in red that have not had results entered for 4 days.

Then, after any tests come back positive, I need the user to be prompted to conduct 5 additional tests in the area if it was positive for listeria or salmonella, but not for EB. These should go in another database I think, and should be numbered IS1a, IS1b, IS1c, IS1d, IS1e, etc. The initial site that came back positive for S or L needs to be retested until there have been three consecutive tests that were negative, and the user needs to be prompted that this site needs to be retested again and again until three consecutive results have been entered. The five additional sites that are tested are conducted in the area of the initial positive result to make sure that the bacteria isn't showing up on other equipment or structures in the area, and if it is, these sites also need to be continually re-tested until there have been three consecutive negative tests. This database will similar information entered into it- a pre-determined number, the date, a text description of the location (these sites won't have numbers because they're things that the employee will go into the area of the initial positive result and thinks are spots near the first positive that might be areas where bacteria could grow, so the user will need to write in descriptions of the location), but other than that, it's basically the same as the first database, and they'll have a different naming system. Should these be combined into one database, or should it be kept separate and linked?

A site that is positive for EB needs to have two consecutive negative results and does not need to have the additional areas around it tested.

I'd also like to be able to print a report on a positive initial swab and show that site and the 5 additional related sites that were tested, the dates they were tested on and whether the swab from those dates were positive, so it'd read like:

IS1 Conveyer 12/7/15 Positive- Listeria IS1RT1 Conveyer 12/10/15 Negative IS1RT2 12/11/15 Negative IS1RT3 12/13/15 Negative
IS1a Drain 12/10/15 Negative
IS1b North Floor 12/10/15 Positive- Listeria IS1bRT1 North Floor 12/11/15 Negative IS1bRT2 North Floor 12/13/15 Negative IS1bRT3 North Floor 12/16/15 Negative
IS1c Pipe 12/10/15 Negative
1S1d Water hose 12/10/15 Positive- Listeria IS1dRT1 Water hose 12/11/15 Negative IS1dRT2 Water Hose 12/14/15 Positive- Listeria IS1dRT3 Water Hose 12/16/15 Negative IS1dRT4 Water Hose 12/18/15 Negative IS1DRT5 Water hose 12/20/15 Negative
IS1e Light Cover 12/10/15 Negative

Or some of the could read like:
IS1a Drain 12/10/15 Results Pending


As I try doing this more I think maybe I've bitten off more than I can chew. I hope not. If I find that I have, how much would I expect to pay if I were to ask someone to build it for me?

Thanks!

Hammilton

[orange]

Here is some info that may be helpful.

I tried to summarize the process and suggest some "possible" tables.

We have a number of Locations that we test of the presence of certain bacteria.
Swabs are taken and tested, and possibly retested as below:

If a Swab test is positive for A or B, then 5 repeat swab tests are conducted in that area of the original site. These swab tests are repeated daily until there are 3 consecutive swab tests with negative results.

If a Swab test is positive for C, then 2 repeat swab tests are conducted on that site. These swab tests are repeated daily until there are 2 consecutive swab tests with negative results.

The bacteria we test for are (A/Listeria, B/Salmonella and C/EB).


A Swab test includes the swabbing and testing of that swab.


A Result of a swab test identifies the swab test, the test date, the bacteria and the result (positive or negative).


Possible tables:needs more detail review

Location (locationId, locationName, locationDescription)
SwabTest(swabID, swabDate, LocationID..)
--Retest (retestID,swabIdFK,retestDate,LocationID...)
Bacteria(bacteriaID, bacteriaName...)
SwabTestResult(ResultID, ResultDate, swabId, retestId, bacteriaID, Result)

Question:
What exactly is another site in the area of the original test?
Do you identify the bacteria to be tested with the swab?