Reporting Hell (1 Viewer)

[Kelly Napier]

HI

Some time ago I asked for advice on here and wasn't able to test it as I wasn't able to work on the database. I am self taught so don't understand a lot of the lingo I'm afraid. I was given the advice but I have no idea where to implement the 'solution'.

I have designed a client management database, each client has a Client Reference and this links them to everything in the database. When registering a client they complete the following forms Personal Info/Housing History/Offending History/Alcohol/Drugs/Physical Health/Mental Health. I have been able to design reports for Personal Info/Housing History/Offending History - but I'm now working on Mental Health and the issue I'm having is that there are four fields in this form which all have the same drop down list. So a client can register up to four diagnosed conditions. The fields are Diagnosis/Diagnosis2/Diagnosis3/Diagnosis4. What I want to do is calculate how many clients have Anxiety for example, and I want to count how many times Anxiety has been listed in each field and for that to be totalled.

So I got the following answer but I don't know where to put it, how to set it up or anything really:

Option Compare Database
Option Explicit

Dim TotalAnxiety As Integer


Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
TotalAnxiety = TotalAnxiety + _
(([Diagnosis] & "" = "anxiety") + ([Diagnosis2] & "" = "anxiety") + _
([Diagnosis3] & "" = "anxiety") + ([Diagnosis4] & "" = "anxiety")) * -1

End Sub

Private Sub Report_Load()
TotalAnxiety = 0
End Sub

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
Me.yourUnboundTextbox = TotalAnxiety
End Sub

Please can you help me?

 

[arnelgp]

maybe moved your code on the Detail_Print to the Report s Detail section Format event:

Dim TotalAnxiety As Integer

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
TotalAnxiety = TotalAnxiety + _
(([Diagnosis] & "" = "anxiety") + ([Diagnosis2] & "" = "anxiety") + _
([Diagnosis3] & "" = "anxiety") + ([Diagnosis4] & "" = "anxiety")) * -1
End Sub

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
Me.yourUnboundTextbox = TotalAnxiety
End Sub

 

[Kelly Napier]

Thank you - so do I go into report detail and create an unbound textbox? And then do I enter the properties and add this code somewhere? Sorry for being so dumb, but I honestly don't know as much as I should.

 

[arnelgp]

here is a sample. see the Report in design view.
see the code on the Details section's Format event.

 

[The_Doc_Man]

@Kelly Napier - I offer advice in general, and, taking your introductory comments into account, I will try to explain with terms that are not "jargon" or I will explain my terms.

I strongly suggest that to help you down the road, you should study the topic "Database Normalization." In this forum, you can search for the simple word NORMALIZATION (because this IS a database forum.) On the general web, search for "database normalization" because by itself, the word normalization has meanings for diplomacy, math, chemistry, and a few other things besides databases. IF you do a web search, start with a few articles from .EDU sites. I am NOT saying that the .COM sites don't have good articles, but many of them often have more distracting ads and want to sell you something of a more proprietary nature i.e. not generally applicable.

In simplest terms, normalization is a way of arranging your data to take into account the many inherent relationships between data elements. Most SQL-based databases (and Access is NOT an exception here) do better with normalized data.

If your Diagnosis1, Diagnosis2, ... are each fields side-by-side in a single "client" table, then your table is not properly normalized. You have what is called a "repeating group" (the four diagnoses). The way to eventually tame this beast is that you make a Diagnosis table for which one key field is the Client Reference. For each diagnosis, this "child" table perhaps would include that Client Reference, the date on which the diagnosis was first entered, and the diagnosis name or code that you are storing.

Now, IF you normalize that table, then you get a few benefits: (a) you are not limited to four diagnoses; (b) you can display however many diagnoses you have by using sub-forms and sub-reports, usually in datasheet mode; (c) the space used for your diagnosis information wastes less disk space when you don't have four diagnoses and is therefore more efficient; (d) normalized data layouts are almost always simpler to maintain; (e) if you get a client with five diagnoses, you don't have to redesign everything.

Access helps you with (b) because when you declare a sub-form or sub-report, you can link the parent form and child form through the value that they have in common - in this case, your Client Reference. That is how they would keep everything together that needs to be together and also how they keep things apart that need to be apart.

Now, here's the practical question: Can you keep on doing what you are doing the way you are doing it? Yes. There are many ways to skin a cat, as the old saying goes. However... will you continue to have added complexity with your current method? Absolutely yes.

Normalization allows you to build general code that works right whether you have one diagnosis or a handful - because you treat each diagnosis the same way. Without normalization, you have to make special code to handle each diagnosis as a separate case (because of the separate field names). In essence, you need four copies of whatever code you need to write because you have isolated each diagnosis horizontally. (In database terms, "horizontal" means you made your record "longer", also written as "wider" in some references.) Access is most efficient when multiple fields are vertical (in separate "child" records under a single "parent" record, i.e. "taller").

You would then use what is called a JOIN query when pulling data together. If you had normalized data, your original question of determining the count of diagnoses would be

totDiagnoses = DCOUNT( "[Diagnosis]", "tblClientDiagnoses", "[Client Reference] = " & [Client Reference] )

I made up some names just to show how simply your count requirement could be managed. The above would look for all diagnoses for a given client based on the Client Reference currently visible on a form or report section.

Your "four fields with the same drop-down" becomes a LITTLE more complex but you end up with only a single drop-down and just have to make a little bit of code to save that diagnosis when it is selected. I'll skip details now but if and/or when you choose to go this direction, you can of course come back and ask.

 

[bastanu]

Cross-posted with suggestions @ https://www.accessforums.net/showthread.php?t=83533

 

[Uncle Gizmo]

If you decide to reconstruct your dB into the correct format, as described by Doc, then you should find my transpose Tool useful.

My blog here:-

Excel in Access - Nifty Access

Excel in Access - Problem & Solution! One of the most frequent problems I see is people confusing MS Access with Microsoft Excel. This leads to fundamental design errors. Now, the problem is, this error "A Major Error" does not become apparent straight away. Unfortunately your database will work...

www.niftyaccess.com

Explains the issue in text and video, and there is a download link to my transpose Tool.

If you would like a free copy, contact me for details on how to obtain a free copy.

 

[gemma-the-husky]

The point where you say "up to 4" indicates you have a spreadsheet type table and each of 4 columns might have "anxiety" - so you have to count anxiety in each column, while preventing duplicates and disregarding duplicate rows.

Whereas if you stored the data in a normalised table that showed

patient, condition
1 anxiety
1 something else
2 something else
4 anxiety
7 anxiety
etc

then
a) you aren't limited to 4 conditions per patient
b) you can easily prevent the same condition being entered more than once
c) and you can easily count that 3 patients have anxiety.

 

[Pat Hartman]

I'm going to post a sample that generates sequence numbers so you can assign a sequence number to each diagnosis code. This allows you to assign the primary diagnosis first and ancillary diagnoses in subsequent lines. This will help your analysis since you will always be able to count the primary diagnosis separately if you want to.

To ensure that no diagnosis is duplicated in the tblPatientDiagnosis table, make a unique index (use the "key" wizard. you can't do this by modifying the table definition), on both the PatientID and the DiagnosisID.

Look at the subform example in this sample database.

 

[Kelly Napier]

Ok - thank you firstly everyone.

I have managed to sort out a count of symptoms by creating a query for each individual symptom which counted how many - and I put it in a report as a list box. This works now. So from what I'm reading I have to rearrange my original form or table to have one place for diagnoses but be able to list multiple. Where as now I have four columns, I would only need one? Or would I need a form based on a query of mental health patients + mental health diagnosis? Not sure how this bit works, am happy to normalise but need a bit of guidance if possible.

Thanks in advance.

 

[Uncle Gizmo]

I would suggest that you post a sample database containing the parts that are causing the issue. Probably all that is needed is the relevant forms and tables.

Replace any confidential information with nonsense.

 

[Uncle Gizmo]

If you have issues with posting parts of your database then there are other ways of showing information see my blog here:-

Show Table Structure to a Forum - Nifty Access

Show Your Table on a Forum Show Table Structure to a Forum.The best way is to provide a small sample database. You don't need to provide your whole database. In fact by including too much information you just confuse the issue and make it difficult for anyone to help you. Indeed if you just dump

www.niftyaccess.com

 

[Mike Krailo]

On your mental health form, if the data is normalized as described above previously, then all you need is to add a subform that allows the user to add as many diagnosed problems as they want. The four combo boxes go away and gets replaced with a subform which is linked to the main mental health form using the PatientID or whatever uniquely identifies that single individual on your main form. Sub forms are merely a way to link underlying related tables together.

It's impossible to give you exactly what you need since you did not provide a sample database. There are tons of examples of using a subform on this site and on the web. Learn how to do that, and then it will make more sense.

BTW, reporting hell is almost always due to incorrect table structures. Get the tables right, and reporting is much easier.

 

[Mike Krailo]

Uncle, that's an excellent reminder on how any forum user can provide additional information on their current database without actually posting the database. I didn't know about the copy/paste of the table in datasheet view. Good one.

 

[Uncle Gizmo]

excellent reminder on how any forum user can provide additional information on their current database

There's one caveat, I haven't tried it since the forum upgrade. I assume it will work, but I haven't tested it.

 

[Pat Hartman]

I have managed to sort out a count of symptoms by creating a query for each individual symptom which counted how many

Having a non-normalized schema (symptoms as columns rather than rows) makes this simple task much harder than it needed to be. With all the symptoms as rows in a table, the query would be:

Select Symptom, Count(*) As SymptomCount From YourTable;

With four columns, you need four queries (one for each column that you then union to get them all in one list and then a final query to do the count. You would never under any conditions create a separate query for each type of symptom if that is what you did.