UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Importing Test Results, Access 2010    
 
   
kjohnson
post Apr 28 2017, 11:36 AM
Post#1



Posts: 360
Joined: 2-May 12
From: South Central Texas


Hello all!

I'm not sure if this is the best place for my issue. We do health screens on lots of animals every 3 months. I'm looking for a way to be able to capture the results so that I can spit it into reports/letters to email out to the owners of the animals and give them a summary of the animal's test results. We have about 170 animals tested every 3 months, so I'd rather not make word documents by hand every quarter, so I thought Access might be good as I can then look up whatever data/results my boss would like at any given time and once the database is built, it should only require importing the results and spitting out letters, so less man-hours than doing everything manually.

So that's the gist of the situation. What I have so far are 3 tables - tblPI (list of owners where their email addresses are stored for retrieval for sending the results via email attachment), tblResults (has the owner(s), room number, # animals tested, test date, and results of each pathogen), and tblCriteria (stores which owners to generate reports for (user selected)).

I can enter the data by hand and the report works the way I'd like it to. Now I'm trying to see if I can automate the data entry. I'm able to export data from the testing facility as and XML file with report data, CSV (comma delimited), PDF, MHTML (web archive), Excel, TIFF file, or Word. Those were taken from the list of options on their website. If I export as an excel file, I can get a spreadsheet like the one attached. It will group by room, but each test is listed separately and the results are given as fractions (1/2 = 1 out of 2 tested positive).

I'm thinking of doing a loop-type process. The code will look at the room number and test date in tblResults and add a new record if one doesn't exist. It will then read the first test, EIDM in this case, and record the result in tblResults.EDIM. Then loop to the next excel row, see that the room number and test date exist in tblResults, and edit the record to add the next test results, MHV, in tblResults.MHV. (I hope this is making sense). The one issue that I have with this process (you might have noticed) is that there are no owner names on the excel spreadsheet. I could tell the website to add them, but the names are often our own abbreviations and some rooms have animals from multiple owners so sometimes the "owner" is listed as "various." I figured it would be pretty easy for somebody to add the owners names to the room numbers after the data is imported while they're reviewing the imported data for errors.

Finally, my issue/question/reason I'm here is that I'm wondering if this is the best way to manage this process or if there is a different approach that I haven't thought of. Then, if this is the best approach, how can I get it to read part of the fraction? The denominator is the total number tested and the numerator is the number positive (which is what would go in tblResults.EDIM (or tblResults.MHV, etc - you get it)). If there is a way to do that, I think I can write the looping code OK - may have a few hiccups along the way...

Let me know if this is too confusing - I can zip a demo copy of the database I have so far (with owner names retracted, of course) if you think that will help. Thanks in advance!
Attached File(s)
Attached File  Report.zip ( 16.01K )Number of downloads: 17
 
Go to the top of the page
 
kjohnson
post Apr 28 2017, 03:46 PM
Post#2



Posts: 360
Joined: 2-May 12
From: South Central Texas


Ok, so here's what I came up with for the fractions:

I made a query on the table that the excel spreadsheet imported to. Besides Room#, TestName, & Results (columns from the import) I added two more:

#Positive: Left([Results],InStr([Results]," / ")-1)
#Tested: Mid([Results],InStr([Results]," / ")+3)

So now my code to put the imported data into tblResults will just need to pull the value from either field.

Cool, cool. Now I'll just start working on the loop... unless there are better ideas? compute.gif
Go to the top of the page
 
kjohnson
post May 5 2017, 04:47 PM
Post#3



Posts: 360
Joined: 2-May 12
From: South Central Texas


So! I'm slowly figuring it out. I'm just having an issue at one part of the code that I can't seem to fix:

The room number field is a text field. When it runs through the line strSQL = "SELECT * from tblResults where RoomNumber = " & strRoom & " And [TestDate] = " & Forms!frmHome!txtDateImport
in the loop, it never finds any records, even if there is a record that matches the room number and test date. I've stepped through the code and printed strSQL in the immediate window, which I've then copy/paste into a query SQL. When I look at the room criteria in design view, the room number looks like "001B" (with the quotes). If I delete the quotes and have the criteria be just 001B (without quotes), it will pull up the record I'm expecting.

How in the heck do I write that SELECT statement so that there aren't quotes around the room number? I know I'm missing something so obvious, but I just can't figure it out!

CODE
Private Sub cmdAdd_Click()
On Error GoTo ErrorHandler

If Me.txtDateImport = "" Then
    MsgBox "Please enter the date of the test results.", , "Blank Results Date"
Else
End If
Dim LResponse As Integer

If Me.chkQuartImport = False Then
    LResponse = MsgBox("You have not selected that the results are Quarterly Test results. Are you sure you want to continue?", vbYesNo, "Quarterly Test")
        If LResponse = vbNo Then
            Exit Sub
        Else
        
        End If
Else
End If


Dim strSQL As String
Dim db As Database
Dim rs As Recordset
Dim strRoom As String
Dim strColumn As String
Dim strRecord As Recordset


Set db = CurrentDb
Set rs = db.OpenRecordset("qryImportResults")

With rs
    If Not .BOF And Not .EOF Then
        .MoveLast
        .MoveFirst
        
        While (Not .EOF)
            strRoom = rs!Room
            strSQL = "SELECT * from tblResults where RoomNumber = " & strRoom & " And [TestDate] = " & Forms!frmHome!txtDateImport
            Set strRecord = db.OpenRecordset(strSQL)
                strColumn = rs!TestName
                
            If strRecord.RecordCount = 0 Then
                With strRecord
                .AddNew
                !RoomNumber = rs!Room
                !TestDate = Forms!frmHome!txtDateImport
                .Fields([strColumn]) = rs![Positive]
                !AnimalsTested = rs![Tested]
                .Update
                .Close
                Set strRecord = Nothing
                End With
            Else
                db.Execute "Update tblResults" _
                & "Set strRecord.fields([strColumn]) = rs![Positive]" _
                & "AnimalsTested = rs![Tested]" _
                & "Where Room = rs!Room And TestDate = " & Forms!frmHome.txtDateImport
            End If
        
            'strRecord.Close
            .MoveNext
        Wend
    End If
    .Close
    strRecord.Close
    Set rs = Nothing
    Set strRecord = Nothing
End With

ExitSub:
    Set rs = Nothing
    Set strRecord = Nothing
    Exit Sub
ErrorHandler:
    MsgBox "There was an Error: " & Err & ": " & Error(Err)
    Resume ExitSub
End Sub


Also, I know there is some extraneous code up in there, but I'm doing this from scratch, so I'm kinda writing it one piece at a time and making sure it's working the way I'd like it before I expand on its tasks. (for example the LResponse up there at the top in the If...then statement.) Right now I'm just focusing on getting that query to run correctly.

Thanks in advance!!!
Go to the top of the page
 
RJD
post May 5 2017, 09:02 PM
Post#4


UtterAccess VIP
Posts: 7,813
Joined: 25-October 10
From: Gulf South USA


Hi: Without going through the whole code, let's address just the SELECT line ...

QUOTE
The room number field is a text field. When it runs through the line strSQL = "SELECT * from tblResults where RoomNumber = " & strRoom & " And [TestDate] = " & Forms!frmHome!txtDateImport


If the room number is text (which I would also assume from your 001B example) then it must be expressed differently, with single quotes. Plus, if TestDate is a date field type, then that also must be expressed differently, with # signs around the comparison. So I would start with ...

strSQL = "SELECT * from tblResults where RoomNumber = '" & strRoom & "' And [TestDate] = #" & Forms!frmHome!txtDateImport & "#"

See if that makes any difference in your results. Then we can explore from there into your code if necessary ...

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
kjohnson
post May 6 2017, 11:33 AM
Post#5



Posts: 360
Joined: 2-May 12
From: South Central Texas


Hey Joe,

OK so I changed the select statement and I knew that about dates, but it was pulling in the right date in the immediate window, so I just left it well enough alone.
Now I have:
strSQL = "SELECT * from tblResults where RoomNumber = '" & strRoom & "' And [TestDate] = #" & Forms!frmHome!txtDateImport & "#"

The code found a record, so it bopped down to the "else" statement with the Update tbl which it didn't like. (syntax error in UPDATE statement)
Again, there's probably something simple that I'm not seeing. I've messed around with the syntax and single quotes and # and other things, but can't get it to like it. This is how I've been doing this code too - I wrote out the process (it was difficult to think of the order of the loop for this) and then have been going back and checking spelling, syntax, order, etc then stepping through, see where issues are and fix them one at a time.

So next issue is this statement - tweaked from the first code as I've been playing around with it:

CODE
db.Execute "Update tblResults" _
                & "Set strRecord.Fields([strColumn]) = rs![Positive]," _
                & "AnimalsTested = rs![Tested]" _
                & "Where RoomNumber = '" & strRoom & "' And TestDate = #" & Forms!frmHome.txtDateImport & "#"
Go to the top of the page
 
RJD
post May 6 2017, 06:52 PM
Post#6


UtterAccess VIP
Posts: 7,813
Joined: 25-October 10
From: Gulf South USA


Hi: Once again, I am not scrubbing your whole VBA code, just looking at the syntax of the UPDATE query, and I have nothing to test this against, but try this ...

db.Execute "Update tblResults" _
& " Set strRecord.Fields([strColumn]) = '" & rs![Positive] & "'," _
& " AnimalsTested = '" & rs![Tested] & "'" _
& " Where RoomNumber = '" & strRoom & "' And TestDate = #" & Forms!frmHome.txtDateImport & "#"

Note the quotes around the two rs fields, assuming they are text. If [Positive] and [Tested] are not text, then this syntax is not correct, and you'll have to let us know what field type they are. Note also that the rs fields are outside the double-quote sets, just as in the WHERE clause. Plus check the spaces. Looks like you had things running together where they should not be.

See how far this gets you.

It would be helpful if we had the db to work with, then we could test our suggestions.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
kjohnson
post May 7 2017, 10:51 AM
Post#7



Posts: 360
Joined: 2-May 12
From: South Central Texas


Not problem - I don't expect help on the whole code, just the part I'm currently stuck at. :-)

I've scrubbed and uploaded the DB. The Positive and Tested fields I think are text fields. They are generated in the query from tblImport.Results, which is a text field whose data is like "0 / 2". The query puts the numerator as [Positive] and the denominator as [Negative] so I would assume calculated fields stay the same as their source field?
Attached File(s)
Attached File  Sentinel_DB_Take_2_Demo.zip ( 88.84K )Number of downloads: 8
 
Go to the top of the page
 
RJD
post May 7 2017, 11:32 AM
Post#8


UtterAccess VIP
Posts: 7,813
Joined: 25-October 10
From: Gulf South USA


Hi: Thanks for posting the db. I'll take a look at it later. But did you try the revision I posted last? You might test that and see if it works, or what errors it produces.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
kjohnson
post May 7 2017, 11:38 AM
Post#9



Posts: 360
Joined: 2-May 12
From: South Central Texas


Yea I tried that, triple checked my spacing, spelling, and everything and it still gives a syntax error. I'm trying everything I can think of and googling to try other things while I wait for noon to hit so I can go home (supervisor on duty this weekend). So no worries about not getting to it until later - I'm just gonna keep chugging along and if something works, I'll update ya. :-)
Go to the top of the page
 
RJD
post May 7 2017, 04:31 PM
Post#10


UtterAccess VIP
Posts: 7,813
Joined: 25-October 10
From: Gulf South USA


Okay, you had a number of problems - with data as well as with the query syntax.

1. In tblImport you had two records with Null fields (835, 836). So that caused a problem when qryImportResults was used in the procedure to insert the Null fields into the UPDATE query. I added an Is Not Null to the criteria for Room in the query to eliminate these. But you should clean up the table - that was hard to find.

2. In tblImport you spelled a TestName as "MyCoplasma pulmonis", with the embedded space - whereas the field name does NOT contain a space. This errored the UPDATE query since it was trying to resolve what came out as two words - giving an error message that was not helpful. I used a Replace function in the procedure to eliminate the space coming from the query. But you should correct the tblImport TestName values to remove the space.

3. The field AnimalTested is numeric, whereas you were trying to insert a field value from the query that was text. I changed the procedure to indicate numeric syntax and use CInt on the feeder value to force it to numeric.

Here's the revised UPDATE query in the procedure...

CODE
db.Execute "Update tblResults" _
                & " Set [" & Replace([strColumn], " ", "") & "] = '" & rs![Positive] & "'," _
                & " AnimalsTested = " & CInt(rs![Tested]) _
                & " Where RoomNumber = '" & strRoom & "' And TestDate = #" & Forms!frmHome.txtDateImport & "#"

But I also made other changes in the procedure as well. See the revision attached.

This was a bit onerous to decipher since there were several problems in several places. My suggestion in the future is that you insert MsgBox lines in key places in the code to see what has been created by the code and indicate where you are before you see the error message. That's the technique I used to step-by-step isolate and deal with the issues. It's a good technique to have in your toolbox.

Another thing you should consider is re-visiting the design of the record you are updating. It contains field names that contain data - the test names. This is not good design. Better to have multiple records with one test per record (fields TestName and TestValue, rather than a field in each record for each test name). Tall and thin is better than short and wide. And you can get at the test names and results handily. Just a thought as you work with your db.

HTH
Joe
Attached File(s)
Attached File  Sentinel_DB_Take_2_Demo_Rev1.zip ( 100.31K )Number of downloads: 7
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
kjohnson
post Jun 2 2017, 05:01 PM
Post#11



Posts: 360
Joined: 2-May 12
From: South Central Texas


Yea, so the tblImport data troubles originated with trying to import the data from an excel table. I can download the results from the testing website in a number of different configurations, but most of the configurations have some of the data grouped, which I'm not sure if access would be able to import grouped data. For example, I can generate an excel spreadsheet that has Test Name, PI name, Room Number, and Results in any order. If I choose that order, it would list an organism in cells C15 - C66 as one merged cell, with the first investigator name in D15-D16 as one merged cell, and the two rooms that the investigator has in E15 and E16 as separate cells and their results in F15 and F16 in separate cells. (If that makes sense)

I made a macro that would split the merged cells and copy the data in all so that each row was entirely separate (because I didn't want to do that by hand that many times over). If there is a way to import grouped data into access, I think many of the weird stuff might go away in the tblImport. I'm just not sure if that's possible.



I do understand about more tables and less columns is better, I just couldn't think of how it would all work out to get the data from the testing website into access. So I now made a table for the pathogens, "tblPathogen" with PathogenID and PathogenName. Also a species table as we test multiple species and my director might want data about that someday, so "tblSpecies" with SpeciesID and SpeciesName. Then I revamped tblResults, but I'm not sure if I'd be able to make it any narrower - I now have: ResultsID, TestDate, RoomNumber, #Tested, QuarterlyTest (chk box), PIID, PathogenID, SpeciesID, #Positive.

I think one of the biggest issues I'm going to have is that PIID is a multi-value field since some rooms have more than one investigator (and some investigators have more than one room) and it is not static - investigator's animals get moved around, some are added, some are removed, etc. The way to be the most sure the results get into the database correctly would be for somebody to add each one in individually, but with how many animals get tested, that would take a ridiculously long time, hence trying the looping code to add the imported results.



Do you see a better way to approach this? Or any other advice that might make building this easier? I know that I will eventually get it to work so that it spits out the data my director wants, but it would be nice to be directed to the elevator instead of the cliff to get to the top, if you catch my meaning... I can explain more about the whole situation in private messages, I just can't go into too much more detail in a public setting.
Go to the top of the page
 
RJD
post Jun 3 2017, 10:27 AM
Post#12


UtterAccess VIP
Posts: 7,813
Joined: 25-October 10
From: Gulf South USA


Hi again: Well, your db and your last post gives us a lot to absorb. First, I really urge you to abandon the PIID Lookup field with multivalues. This, IMHO, will cause you many bad headaches trying to use your db. Instead, eliminate the field entirely from tblResults, and create a junction table called, say, tblResultsPIID. This table will link tblResults to tblPI on a many-to-many basis. The table would have a record ID, say RPIID, the tblResults record ID (Results2ID) and the tblPI record ID (PIID). So, for a tblResults record there can be any number of PIIDs in the junction table and for any PIID there can be any number of tblResults records. This is a standard way to handle such relationships, and GREATLY eases the task of extracting PI related items, as well as lists of PIs associated with tblResults data.

Second, your question regarding "import grouped data." I am not sure what you mean by this, but you can always import the un-grouped records, using an intermediate Access table, then manipulate them in Access anyway you want and put the results wherever needed.

Third, your comment about "more tables less columns." I think this come from my comments that actually tried to focus on "more records fewer columns" all in the same table.

As I said, this is a lot to absorb, and we cannot know everything you do about your data, your protocols and everything you are trying to accomplish with your application.

See if these suggestions help.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
kjohnson
post Oct 13 2017, 04:54 PM
Post#13



Posts: 360
Joined: 2-May 12
From: South Central Texas


Well after a long time with no spare moment to work on this, I'm back with a fresh mind. I (mostly) started over using your suggestion of one test result per record in tblResults and then pairing results with PIs in a separate table. I've attached some screenshots to hopefully help make it clear. So I first got down importing data from the testing facility directly into tblImport without having to clean up the excel spreadsheet first. Then on my main form, I added a button to compare the imported test pathogens to the pathogen names in tblPathogen to see if we need to add a pathogen name or maybe there is a spelling discrepancy that we should fix before adding data to results. Also on my form is an option group (not utilized just yet) to designate what category of testing the results are from so I can filter results if all I need from one room are quarterly results. There are also boxes to add what date the import results were from and what species was tested (this is a combo box from tblSpecies).

I'm now contemplating what I should do for my next step. I'm thinking the user needs to review some of the data before it can be imported - at least before the results and PIs go into tblResultsPIID. Sometimes there are mistakes with room numbers and PI names from the testing facility, so if the user can just look the data over and fix the couple issues before zapping it into the tables, it might prevent headaches later. Also, since PI names are sometimes misspelled or abbreviated (or there is no first initial and we have multiple Dr. Smiths, for example) the user can define which Dr. Smith goes in that room.

To do this, I'm thinking of running a query which populates a form maybe? So I'm mainly getting stuck at the best approach to actually input the data into the table. I can do the import data into the results table and reviewing that just fine, but matching up PIs with results is where it's getting really tricky. The rooms with multiple PIs are given as "Investigator1/Investigator2" at least with the data I'm working with right now. I need to go through older reports and see if they entered the PIs in the same fashion.

Any thoughts, ideas, direction, suggestions?

Process summary:
step 1 - import from excel file to tblImport
step 2 - compare test name in tblImport to pathogen names in tblPathogen
step 3 - define test category
step 4 - identify species and test date
step 5 - ???
Attached File(s)
Attached File  Relationships.JPG ( 33.22K )Number of downloads: 1
Attached File  tblImport.JPG ( 16.01K )Number of downloads: 0
Attached File  tblImport_Data.JPG ( 38.11K )Number of downloads: 3
Attached File  Form.JPG ( 36.07K )Number of downloads: 2
 
Go to the top of the page
 
RJD
post Oct 14 2017, 11:39 AM
Post#14


UtterAccess VIP
Posts: 7,813
Joined: 25-October 10
From: Gulf South USA


Hi again:

QUOTE
I'm now contemplating what I should do for my next step. I'm thinking the user needs to review some of the data before it can be imported - at least before the results and PIs go into tblResultsPIID.

You might consider importing into a temporary table (actually, a permanent table that houses temporary records, records to be deleted after editing and transfer to the main table). Use a form to display and work with the imported records, then use a query or procedure to send them to the appropriate table. Then delete the records from the temp table, ready for the next batch.

Thanks for the screen shots, helpful, but we work better with an actual db. You can cut it down as appropriate, use example data, and zip and attach it to your post. With the db, we can test the operation and even possibly make some alterations for you to look at and test yourself.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
kjohnson
post Oct 16 2017, 09:25 AM
Post#15



Posts: 360
Joined: 2-May 12
From: South Central Texas


Yea I wasn't sure how much time I had left on Friday to scrub the data - usually Friday at 4:30 or 5 is when animals seem to decide to get sick or emergency situations pop up, but I wanted to put up something before the weekend... So here's the take 3 demo. The data is already imported into a perma-temp table, so that step is done. Is there a way to display the data so it can group different test results from the same sentinel - so that if somebody needed to fix the PI name for that sentinel or room, that change will also be reflected in all the tests for that animal?

Example from tblImport:
ImportIDs 1253, 1264, 1275, 1286, 1297, 1308, 1319, 1330, 1341, 1352, 1363, 1374, 1385, 1396, 1407, & 1418 are all different pathogens from the same animal/sample - 1509 - ABC. (1509 being the room number, ABC meaning that animal is monitoring the health status of racks A, B, & C.) The Client ID column has a unique number to that animal as well - T17-41. The ID column is what the testing facility numbers the animals, in this case, they put #1-11, so this animal would be ID #1. Just quickly looking at the .csv files from other cases submitted, it looks like the testing facility always numbers the samples 1 through whatever. So this ID column should be a way the pathogens can be grouped by animal/sample, yes?
Attached File(s)
Attached File  Sentinel_DB_Take_3_Demo.zip ( 51.64K )Number of downloads: 2
 
Go to the top of the page
 
RJD
post Oct 16 2017, 10:36 PM
Post#16


UtterAccess VIP
Posts: 7,813
Joined: 25-October 10
From: Gulf South USA


Hi again: I'm really not sure where we are going with this - I know you are immersed in your process and understand what you need, but I am not comfortable that I understand what I need to know to advise you correctly.

So ... I created a new query and form to list the imported records - and winnow these down by a couple of factors. Take a look at the new form, and select the room or ID to see how this limits the list. Is this what you are trying to do, so that you can change data in a sub-list? You can, of course, add any criteria (through comboboxes) to limit the list - and if this is going in the right direction, we can discuss how you want to change the sub-list data - manually or automatically in the whole group.

If this looks promising, take a look at how this is put together. Then you can discuss where you need to go from there.

HTH
Joe
Attached File(s)
Attached File  Sentinel_DB_Take_3_Demo_Rev1.zip ( 61.5K )Number of downloads: 3
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
kjohnson
post Oct 17 2017, 04:08 PM
Post#17



Posts: 360
Joined: 2-May 12
From: South Central Texas


Yes, I think this is going in the right direction. I can give a little more background info that might make it easier:

Essentially, we have about 100 researchers who have long-term mouse or rat colonies here. In order to monitor the research animals without sacrificing them, we use sentinel mice or sentinel rats. Most rodent diseases are sub-clinical (there are no external signs) so you can have mouse parvo virus running rampant in a research colony and not know it. Some pathogens are pretty harmless, but others might skew the research results, which would mean a huge loss of animal life, money, and time. The way sentinel rodents work is there is a pair of mice in one cage on a rack with the colony animals. Every 1-2 weeks, all colony animal cages get changed and while doing so, the caretakers will take one scoop of dirty bedding and poop and put it into the sentinel's new cage. So the sentinels are exposed to dirty bedding and feces from a max of 70-90 colony cages - the majority of pathogens are transmitted via fecal-oral route. Then every three months we collect one of the mice in the sentinel pair, send a blood sample, fecal sample, and a swab of the fur to a testing lab to look for pathogens and internal and external parasites. This will give us an overall picture of the colony health with the loss of only one animal - if there is a colony cage that has say, parvo, the sentinels will pick it up, seroconvert, and test positive.

I normally get the results from the testing lab in pdf format (see attached) and if there are positives that we don't want, we isolate the room. Otherwise, we go on our merry way. This all came to a head when one of our researchers discovered that her colony has a pathogen called MNV, which we don't exclude for and she flipped. I've been here 5 years and her colony has been positive as far back as 2011 when my electronic records end. So if MNV truly affects her results (still unknown), then all the research, animals, time, money, everything for the last 6 years (at least) have all been for nothing. We've had to spend a ton of time and money cleaning MNV out of her colony and so my director wants me/us (my vet techs) to send letters out to each researcher informing them of their colony's health status after every quarterly testing round. Since we can't send out the PDF directly as it has everybody listed and some researchers have proprietary research, it would have to be a hand-made letter to 100 researchers and that's just... we don't have time for that.

I know how to auto-generate emails and put attachments on them and I have contact info for all researchers, so I thought if I had a way to keep it in a database, I could automate most of the letters PLUS have a way to look at data trends if need be. The part that's the trickiest is making sure the data gets entered correctly. If you look at the screen shot, there are some rooms that have multiple PIs - they would have much smaller colonies and so instead of using one sentinel cage per PI, they share a sentinel, reducing animals used and time taken to process. So the relevant data (on this example) is the PI name, room number, and pathogen name/result. The user would have to look to make sure the PI names match the ones in the database table (tblPI) or else there will be multiple PI names that are actually the same person, which won't help me generate letters. :-) The situations where this is the most difficult is in these multi-PI rooms like room 144. ClientID 17-017 is one mouse from one sentinel cage getting bedding from Tu & B's colony cages and in that same room, ClientID 17-018 is a different mouse from a different cage getting bedding from G's colony cages. I would need 3 letters in this case, one to Tu, one to B, and one to G. The results in Tu and B's letter will be the same as they are coming from the same mouse, while G's might be different, however, I don't want Tu and B to see G's results.

These mice were each tested for 8 pathogens (Mouse IgG is just checking to make sure the test sample is good), so the .csv file from the testing facility will have 16 rows on the spreadsheet for room 144 (one pathogen result per row times two mice). If there is a way to group the 8 pathogens on a form on one line, then if the PI name needs to be changed for one of the mice, the user wouldn't have to change it 8 times over. The user doesn't really need to see each pathogen name either as long as they run the pathogen name check against the tblPathogen. If the testing facility messes up entering in the PI name for 17-017, then all rows in the .csv file will be the same wrong name (I checked). So if all data looks good, then there will be 24 records for room 144 going into the Results table - 8 pathogen results for Tu, 8 pathogen results for B, and 8 pathogen results for G.

.... I'm probably rambling by now and this is probably way too much info than you needed to know.... but does it make it clearer about what I'm trying to accomplish? Or maybe give you some ideas on a better system?
Thank you for any time you've put into this - I really, really do appreciate you. Anything to help me not have to hand-write all these results into letters every 3 months....
Attached File(s)
Attached File  Results2.JPG ( 66.53K )Number of downloads: 4
Attached File  Results1.JPG ( 173.23K )Number of downloads: 5
 
Go to the top of the page
 
RJD
post Oct 18 2017, 07:59 PM
Post#18


UtterAccess VIP
Posts: 7,813
Joined: 25-October 10
From: Gulf South USA


Hmmm ... that's a lot to absorb. I have been busy elsewhere today, and will be most of tomorrow. But I will try to get back to this soon, when I have time to dig into this - and see what advice I can come up with...

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
RJD
post Oct 22 2017, 09:26 AM
Post#19


UtterAccess VIP
Posts: 7,813
Joined: 25-October 10
From: Gulf South USA


Well, yesterday and today I have read and re-read your last post and attachments a number of times - and I think you may need some serious help beyond what I can provide at this time. Perhaps you will need to rethink your design and/or approach. Or perhaps someone else can see a way to more easily alter your design to accomplish what you are trying to do.

I will put out a call to the VIPs and see if anyone would be willing to tackle this.

Sorry I could not be of more assistance at this time.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
kjohnson
post Oct 25 2017, 10:43 AM
Post#20



Posts: 360
Joined: 2-May 12
From: South Central Texas


I had an idea this morning - I made a crosstab query. First I did a query on tblImport to get the fields I want: SampleID ([Case Number] :: [ID]), Investigator, Bldg/Room (maybe change to just Room), then the rest of the column headers are the test names with their field results being (first). This turned out like what I think I'm going for. If I put this in a form, the user can then check PI names and room number to make corrections before they get fully imported into the database.

If I make a bound form with this crosstab query as the data source and a combobox for the investigator name, would it work to have the combobox bound to tblImport.Investigator with the row source be the tblPI list? Hmm.... I'll have to play around with that. I've attached the zipped demo with my crosstab query for perusal.
Attached File(s)
Attached File  Sentinel_DB_Take_3_Demo_Rev1.zip ( 74.35K )Number of downloads: 4
 
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search
RSSSearch   Top   Lo-Fi    10th December 2017 - 09:25 PM