Extracting information to include into a Microsoft Access Report

MRO Report CodeI have a drug screen database that is using Number Types zeros and ones for substances that are tested and blank if not tested. I need to create a report that will return specific substances tested for each person and the result (i.e.: if 0 the substance was tested for, and it is negative, if 1 the substance was tested for, and it is positive, if blank the substance was not tested for.

Microsoft Access 16. I have report working for DOT results because they always test for the same substances, which I have each substance field in my report. I cannot get non-DOT to work because there are many possible substances that could potentially be selected for testing. I would like the report to pull for each person what substances they were tested for (only. i.e. each company tests for different substances) and the result.

A line for each substance that can be tested for DOT (i.e.: Marijuana, Cocaine, Amphetamines, PCP, Opioids, etc). Non-DOT has many more potential substances.
=Switch([Negative]="1","NEGATIVE" & IIf([Dilute]="1","/DILUTE","") & "  RESULT",[Positive]="1","POSITIVE" & IIf([Dilute]="1","/DILUTE","") & "  RESULT",[Cancelled]="1","CANCELLED TEST")

I can't figure out how to have the report only list substances specific to a test. I only want the specific substances that are tested to return on the report. On the DOT all substances are set always (never add or delete substances).

I have included images of the DOT report. But again, DOT substances never change. Non-DOT do.MRO Report CodeScan.pdf
Teresa HendersonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
I am not sure because i cannot quite understand from the screenshots but if you want to present both groups...an easy idea would be to use 2 sub reports...1 for DOT...2nd for NON DOT.
If this is not the case then trying to work your way out with multiple IIF will eventually fail
You need table design that serves you not the other way around
So for example a table substances
SubStanceID           Name                  Category
 1                                Marijuana            1
2                               Amphetamines      2
A table Categories
CategoryID       Category
   1                          DOT
   2                          Non - DOT
and it goes on..
Teresa HendersonAuthor Commented:
So a restructuring of the tables is what it will take? Purge out the 1) Substances, 2) Testing authorities, and one for 3) Negative, Positive, Cancelled and Dilute to separate tables each of their own?
Teresa HendersonAuthor Commented:
Another question please? So sorry for my stupidity! I am not sure how to go about this? Looking the database over as it was set up years ago by someone, all test information is in one table.  Each drug test is on a record by itself, which includes in each record every possible substance, testing authority, and possible outcome (negative, positive, etc.). They are all tied to a company account number, which has company information in a separate table.  Is there a way to extract this information into the separate tables without losing any information, or having to start all over from scratch?
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

Mark EdwardsChief Technology OfficerCommented:
Teresa:  What do you mean by " I cannot get non-DOT to work because there are many possible substances that could potentially be selected for testing. I would like the report to pull for each person what substances they were tested for (only. i.e. each company tests for different substances) and the result."

Since DOT always test for the same things, you know by default what substances they were tested for.  There must be somewhere in your database where the list of substances that were tested for a non-DOT specific individual are specified (maybe according to a set list of tests?) and the name (or ID) of the test is in the individual's record.  It should be as easy as linking the test ID in the person's record to the list of substances tested for a specific test.  Is there a separate table that gives the name/ID of the test and what substances are tested for it?  If not, where is this data kept?  You need it in order to do what you want to do.
Teresa HendersonAuthor Commented:
Thank you for your response.  No there is not a separate table that lists the substances.  They are all kept in the individual test records.  Please see attached for the relationships.  Currently there are only 4 tables.  Relationships.docx  I know they should have been split better, but right now I am trying to not reinvent the wheel, but that might not be possible.  The data is there, but it is all in the Billing table.  I was only able to get the DOT to work in the reports because there is only x amount of substances tested - it never changes.  With Non-DOT it changes frequently.
Mark EdwardsChief Technology OfficerCommented:
The reports aren't much help, and the Billing table structure looks like a bunch of checkboxes (Yes/No fields) with the name of the substances in the heading, not the data in the table.  That's going to be a tough one.  Looks like you need to be able to convert a checkbox on a drug field to an entry in a report.  Looks like this is not built to make it easy to make reports, etc.  from the data.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I agree with Mark - your Billing table doesn't appear to be properly normalized (nor does your CompanyInfo table, but that's an aside).

With that said - how do you determine if a Test is DOT or NON-DOT?
John TsioumprisSoftware & Systems EngineerCommented:
Your question would be assisted better if you could share a sample of your data...just substitute real names with fake ones ...for start..
Expanding a bit more based on the relations you should have testing sets...something like
TestingSetID         TestingSet
       1                           TestingSet01
       2                            TestingSet02
Then each testing set should have substances to test
TestingSetID        SubstanceID
      1                                1
      1                                3
      2                                4
      3                                7
      5                                2
      5                                11
Now the client should have testing sets picked for testing
e.g. client 17 picked testing sets 2 &5 ...creating for example ContractID 1238
then on this testing sets you would have entries that record the result for each substance
ContractID      TestingSetID        SubstanceID       Result
1238                               2                  4                        1
1238                               5                  2                        0
1238                               5                 11                       2
Finally on the report you would gather all this info and appropriately display them.
Mark EdwardsChief Technology OfficerCommented:
In SQL, you can take data that is in a column and "pivot" (or "crosstab" is the term in Access) and turn it into column headings (like taking the names of drugs in a column and "pivoting" them into headings on a crosstab query, but there is no easy way to do the reverse.  You can use data, but not column headings, as data in a report or form.  "Properly normalized" table design was developed to put the database table & field design into the best structure possible to use SQL.  Properly designed, you can do just about anything fairly easily.

You aren't the first person to have to deal with this and you won't be the last.  Developers who have the luck to be able to design their databases from scratch, and most professional enterprise-level developers only deal with properly designed databases and can't think of what else to do when encountering a situation like yours.  The only option you have is to fix ("redesign") your db so it can use SQL.

However, all is not lost when you have a relatively small db with "funky" tables that can't make proper use of the data to make reports, etc.  You just have to "re-engineer" the data you have using queries and temporary tables to get it into shape for use.  For example, one query would be to put the name of the drug (column name) as data in a field if the checkbox for that column is checked ("yes" or "true") and leave it blank if unchecked.  You would have to do that for each drug column, and then stack them on top ("union" then) to get a column of drug data into a shape you can use.  This means building a series of queries to reshape your data, and that could lead to performance issues, which may necessitate using temporary tables to hold the re-shaped data, but you have to do what you have to do in cases like this.

I'll post an example database shortly to demo what I'm talking about.  This will take some work....
Mark EdwardsChief Technology OfficerCommented:
Teresa:  A bit of clarification:  In addition to the named drug columns in the Billing table, you also have "Negative", Positive", Cancelled", and "Dilute" columns.  What are they used for and indicated?  Your report suggest that a checked drug column indicates result, not one of these fields.

Mark EdwardsChief Technology OfficerCommented:
It may be just a matter of creating a report for the non-DOT people that has ALL of the drugs that they could be tested for (all of the ones that you have columns for in your Billing table?)  If they aren't tested for a drug (however that is determined), then that result can be indicated (how ever that is done).

If not, then the attached db give a crude example of how to "reverse engineer" your data into something you can easily use in a report.

Hope it helps.
Teresa HendersonAuthor Commented:
I really appreciate all the comments! Let me try to answer some of the questions.  I have extracted some result information without privileged information and have attached. I am certainly not an expert on this, but trying to get things to work more efficiently. It seems this DB is not normalized, and I'm not sure with 60k records how to go about this while keeping each records information together, and without losing information. A lot of things are being done outside of Access (which is wasting time) that really should be done inside (given that the DB is set up correctly).
*A person goes in for a drug test (could be a number of different testing authorities (i.e.: FMCSA, FAA, non-regulated, etc.), or Modes as called in the DB currently. Also many Collection Sites throughout the country, and sent to several different Labs.
*The result comes in. Whichever Substances are tested for come back as Negative, Positive, Negative-Dilute, Positive-Dilute, Cancelled (for many reasons), Hot-Cold, etc. If DOT (FMCSA, FAA, etc) there are a set number of substances tested. If Non-DOT (non-regulated) there could be many substances tested (16-20) this is dependent on what the company has chosen.
*This is reported to the Company - each with a different account number, and then a letter is sent to the company (this is the letter attached previously. This report I am making work because of the set Substances being tested which never changes for DOT.
*At the end of the month, billing is issued to the company for the x amt of tests they had under their account # that month, and a tally of how many Negatives, Positives, etc., and tally of Substances.
It is required by law that these tests be kept for a minimum of 5 years for all positive results, and 1 year for negative results.
Mark EdwardsChief Technology OfficerCommented:
Teresa:  Thanks for the deeper explanations.  It would help to see some records from the Billing table because I still don't know what the actual data on the table looks like or how it is being interpreted.  The mechanical/technical details are key.  

With general information, I can only give general solutions, which is not what you want, but all I can give, although I have a few ideas if I know more.  Like John said, could we see a few records (change any sensitive info like identifying names/ids)?
Teresa HendersonAuthor Commented:
Mark, I am working on getting you the information you requested. I just need to make sure what I have all works. Will post soon. Thank you.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
What we really need is a sample database with all personal information removed, and a good sampling of record (may 100 - 200), and a description of exactly what you're doing with the database. You've given us much of that (the description), but it would help greatly if you would tell us how it's used (i.e. what forms are used, where the data comes from, etc).

Also be aware that Access isn't really considered to be compliant with the requirements for storing medical data. I don't know if that's a concern for you, but it's best to know that up front.
The optimal solution would be to normalize the schema.  However, this will involve a great deal of work to build new forms and reports.  Once you are done, it will be a breeze going forward because new tests and new testing authorities will no  longer result in having to make modifications to the application.  It is possible to convert the existing data without losing anything.  Given the number of columns, I would probably write a code solution to build the queries and do the conversion that way.  If you were dealing with fewer columns to pivot, it would be simple enough to just manually build the necessary queries.  Assuming there are 60 columns of drug tests, the code would loop through each row in the table and write 0-60 rows to the tests table, one for each non-null value.  It doesn't make sense to create rows for the null values.
Teresa HendersonAuthor Commented:
Scott, I am working on getting a sample DB ready to post.  I will have that soon.  

You know more about the Access compliance than I do, but I will do more research on it for sure. This was being used, and I am trying to help get it working more efficiently. This is a small business that is using Microsoft Office 365, which is purported to be HIPAA compliant.  The DB is stored on one computer, with one user. There is encryption, backups are not on Cloud. This information is for employment purposes, not technically considered medical data, but this might be argued as splitting hairs by the lawyers.  I appreciate your input, and again will do further research.

Pat, as mentioned above I am certainly not an expert and would appreciate direction as how to normalize the schema - I have thought that it is going to take a lot of work and time, but would really like to get this working efficiently. I have tried searching several other sites, without any luck. I stumbled upon Experts Exchange this past weekend, and so far have received more help than all the other sites together.  I really appreciate all your help and direction!
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
It's tough to give concrete direction without knowing your full schema. I've "normalized" quite a few databases, and most of them are fairly straight forward, but require a good bit of time (since all your forms, reports, and queries would have to be rewritten).

Could you post the database, with all sensitive data removed?

Regarding HIPAA compliance - Access is NOT part of Office 365 (the cloud), but it can be downloaded and installed with certain subscriptions. As such, it's not really covered by most of the mumbo-jumbo you hear about HIPAA compliance (and data stored in a database is managed differently than email, or documents).

This article seems to discuss the HIPAA compliance of O365, but it pretty much relates to email communications, and O365 online storage (which would NOT be part of Access). https://www.hipaajournal.com/microsoft-office-365-hipaa-compliant/ 

Not saying you'll run afoul of HIPAA compliance, but if it were me, I'd be sending out (and keeping multiple copies of) and email to my superiors where I caution them against storing any sort of HIPAA or medical data in an Access database.
Teresa HendersonAuthor Commented:
I am trying to get this to you with sensitive data removed.  It is taking a bit of time, so I can make sure that all pertinent information will work.

I appreciate your information on HIPAA.  I will certainly research it, and pass it on.  Do you have a different suggestion for a DB?
Hi Teresa,
Any progress on the sample?  Usually when I need to obfuscate data, I  just run an update query that replaces each sensitive field with the first character of what was there.  If there are size requirements then I use the first character plus as many zeros as necessary.  You should only have to obfuscate identifying data such as last name, address, email, phone, SSN, Drivers License, etc.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.