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    
 
   
RJD
post Oct 25 2017, 08:35 PM
Post#21


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


If you want to edit the results of the crosstab query - that will not work. The crosstab is not editable.

QUOTE
...would it work to have the combobox bound to tblImport.Investigator with the row source be the tblPI list?

Not at all sure how you envision this (I don't see a form for this in your db). Again, I am not comfortable that I really understand what you are trying to do here, and the crosstab results, as I said, cannot be directly edited.

I asked the VIPs to take a look if interested. Looks like several did - but had no response. Perhaps they are unsure of your intent/design as I am.

Sorry we aren't of more assistance. Seems you may need someone sitting next to you there to analyze the situation and help with the design and functionality.

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 Nov 7 2017, 05:57 PM
Post#22



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


Okay, okay, okay! ! ! I've been ever so slowly working on it bit by bit when I get a few minutes here and there and I'm *almost* there!!!! (attached what I have so far - there are probably more elegant solutions, but it's doing what I want it to do [so far])

I got it to import results from the testing facilities excel file, it checks pathogen names, it checks room numbers and allows the user to assign building names to the rooms, and it brings up an easy-to-read chart for the user to look for any mistakes before adding to the results table. Then, it imports the results and at the same time I added a table to link the case number with the type or category of test so that if my director wanted to see a report on quarantine testing, I can bring those up.

Next, I just need to find an easy/easier way to assign PIs to rooms and associate them in the results-PI table.

Just airing thoughts from here till the end:
- I have tblPI that lists the PIs
- I have tblResultsPIID that links the results with PIs
- In tblResults, there are multiple records for each individual animal/sample tested, so if I use the ResultsID, I would have multiple records in tblResultsPIID to associate one sample with one PI....
- In tblResults, there is Sample#, which is just 1 through whatever, assigned by the testing facility, per case number. So [case number] :: [sample#] would be a unique identifier for each animal/sample tested.
- Instead of using the ResultsID, I could link the PIID with the concatenated identifier.
- But would it be easy to pull that data into an easily readable report?

so for example, in case number 12345-2017, 5 animals were tested for MPV, MNV, and Mites. After importing, tblResults would have 15 records:

(ResultsID, Case, Sample, room, pathogen, result) ---- among a few other fields not relevant here ---

[11].....12345-2017.....1....... [room#].....MPV.......result
[12].....12345-2017.....1....... [room#].....MNV.......result
[13].....12345-2017.....1....... [room#].....Mites......result

et cetera

If tblResultsPIID used ResultsID and PIID, for PI #4, it would be:

(ResultsPIID, ResultsID, PIID)

[1].....11.....4
[2].....12.....4
[3].....13.....4

and with the [Case::Sample] instead of ResultsID it would be:

[1].....12345-2017 :: 1.....4

So pulling a report for PI# 4 I should still be able to find all case number/sampleID records in the results table. So I'm slowly, slowly, getting closer....
Attached File(s)
Attached File  Sentinel_DB_Take_3_Demo_Rev1.zip ( 96.32K )Number of downloads: 2
 
Go to the top of the page
 
kjohnson
post Nov 9 2017, 02:08 PM
Post#23



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


OMG I did it! I got it to import and organize the data! I ended up adding a new table, tblSample, that has SampleID (AK), CaseNumber, ID, ClientID, RoomID, Type. Now tblResults has ResultsID (AK), SampleID, TestDate, Species, Pathogen, Result. Then, to tie in the Investigators, I have tblSamplePI, that has SamplePIID (AK), SampleID, PIID. Since there could be dozens of records in tblResults for one sample and it will be the same PI for that sample - it would never have different PIs for each pathogen tested on that sample.

Next step - make reports for both my director and for sending to each PI to give them a summary of the health of their colonies. Yay!!

I've attached the database and two sets of results - two different Case Numbers - in case anybody wanted to play. For resolving the multiple PIs, the last step is looking at the imported samples (from tblSamplePI) and making sure all PI names match, and if there are more than one, you can click the "add" button and it will duplicate sampleID and let you choose the second PI.
Attached File(s)
Attached File  Sentinel_DB_Take_3_Demo_Rev1.zip ( 111.91K )Number of downloads: 5
Attached File  B_Core_PCR.zip ( 10.09K )Number of downloads: 3
Attached File  B_Core_Serology.zip ( 13.2K )Number of downloads: 3
 
Go to the top of the page
 
RJD
post Nov 11 2017, 01:28 PM
Post#24


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


Looks like you are making good progress on this, and have things in-hand. Was there anything specific with which we could help you, as you move this forward?

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 Nov 14 2017, 09:34 AM
Post#25



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


Not at the moment. Though I'm sure I'll probably run into more questions as I continue working on it. Thank you for your help and your suggestions! I wouldn't have been able to do it the way I very first started. Now it's just baby steps until it does exactly what I need it to do.
Go to the top of the page
 
kjohnson
post Dec 8 2017, 04:29 PM
Post#26



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


Okay I've got a question!

I'm trying out different report formats to see which one would suit our needs the best. I have made a query that pulls up each sample and pathogen result. So one sample would have say, 10 records - one for each type of pathogen tested. On the report, I've grouped by SampleID (unique to each sample) Still following? OK - in the report details section, I have the pathogen field and the result field. This returns a list of the pathogens with the results for each sample like the screenshot below. I'm wondering if it's possible to I guess "cycle" through the data? So that in the sampleID field, I can have the first SampleID, then add another field that is SampleID + 1 etc. Like a loop SampleID + n or something. So in the design view, there would be multiple SampleIDs going across the page and the ClientID/Results would be lookup based on that sampleID? or .... hrmm...

The pathogens will be a variable number on each report as will the number of samples.... I can do a crosstab form, but I can't get it to format (or I don't know how) so that it will split into a second sub-form on the report instead of continuing to the right forever.... hrrmmm, hrrmmm, hrmmm...
Attached File(s)
Attached File  Capture.JPG ( 44.6K )Number of downloads: 1
Attached File  Capture2.JPG ( 63.67K )Number of downloads: 0
Attached File  Capture3.JPG ( 38.26K )Number of downloads: 0
 
Go to the top of the page
 
RJD
post Dec 8 2017, 09:35 PM
Post#27


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


Hi again: Well, there is no built-in feature to give you what you want. Crosstab may give you the query result, but it just isn't going to present well in the report the way you are approaching this. You will have to do some manipulating to get there. There are several approaches, some involving VBA, but I prefer the sequencing approach. Since your data already had 1, 2, 3 etc. sequencing built into the data, I used that. In cases where the sequencing is NOT in such a format, you will have to generate a sequence number for the appropriate sequential (1, 2, 3, etc.) field value to use. This approach does NOT use crosstab, although the results may look like it.

Take a look at qryImportRearrange for how the field is split into the component parts These really should be two fields to begin with - you are combining two distinct components into a single field, and that is a db no-no. Then see qryDataForReport for how the fields are gathered based on the sequence number, in a Totals (group) query. Run that query to see how it produces the results. I stopped at four columns, but you can expand that using the same process to as many as you need.

Then see how this is used in rptMyReport to get the layout you seem to want.

This may not get you completely where you want to go, but it might help you get there.

HTH
Joe
Attached File(s)
Attached File  Sentinel_DB_Take_3_Demo_Rev2.zip ( 64.83K )Number of downloads: 1
 

--------------------
"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
 
2 Pages V < 1 2


Custom Search
RSSSearch   Top   Lo-Fi    12th December 2017 - 02:52 PM