472,145 Members | 1,415 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,145 software developers and data experts.

Crosstab queries, multiple joins (?), show all rows

579 512MB
I'm working on creating a dynamic report that is based on a crosstab query. It's similar to the method described at the following link: http://support.microsoft.com/kb/328320

I've tweaked the method above to make the report display how I want, but the underlying query is the part that I'm having trouble with.

I know I will need a couple, if not more, queries to create the crosstab, but I'm having trouble getting the select queries to return the data I need.

Here's the requirements for the crosstab:
- I have a query called qryFormsCCC. The FormName field needs to be the row heading and needs to include ALL the forms in the list...not just the ones that have data.
- I have a query called qryPrograms. The ProgramInitials field is the column heading. I know how to get all the column headings to appear, so this isn't a big deal.
- I have another query called qryFormCompliance that checks to see if the forms for a patient are compliant or not. If the form is compliant, the 'ITF T/F' expression field will be a 0, otherwise it will be a 1. A form can only be counted once, so if it's on the qryFormCompliance more than once (because there were multiple things wrong with it), it will still only be counted for compliance once. So, if patient #01 has three 'Psych Evals' and two of them are non-compliant, but one isn't, then there should be a 1 in the value field for the program that the patient belongs to in order to indicate that the overall status for 'Psych Evals' for that patient is non-compliant.

I really hope this makes sense. I've attached a shell of the database I'm working with.

Thanks for the help and let me know if I need to clarify.

Oh, by the way...here's a link to another thread that is similar and explains some of the question: http://bytes.com/topic/access/answer...ain-conditions
Attached Files
File Type: zip Deficiency DB Shell.zip (736.8 KB, 189 views)
May 7 '09 #1
13 4134
32,499 Expert Mod 16PB
Beacon, I cannot help with cross-tab queries (and like many of us if I can't understand a question without downloading a database I will generally ignore it), but if you can post the meta data for the data you're working with, I may be able to help with some of the individual sub-queries. Assuming as you say, that this is where some of your problems are.
May 8 '09 #2
579 512MB
Sorry about that Neo...is this more along the lines of what you want to see?

Query: qryFormsCCC (qryFormsCCC is an exact replica of the Forms table with the exception of the selection criteria)
Expand|Select|Wrap|Line Numbers
  1. FormID (PK)
  2. FormName
  3. FormNo
  4. FormStatus (WHERE FormStatus = True)
  5. ComplianceCalculationConsideration (WHERE ComplianceCalculationConsideration = True)
SQL: qryFormsCCC
Expand|Select|Wrap|Line Numbers
  1. SELECT qryForms.FormID,
  2.        qryForms.FormName,
  3.        qryForms.FormNo,
  4.        qryForms.FormStatus,
  5.        qryForms.ComplianceCalculationConsideration
  7. FROM qryForms
  9. WHERE (((qryForms.FormStatus)=True) AND 
  10.       qryForms.ComplianceCalculationConsideration)=True))
  12. ORDER BY qryForms.FormName;
Query: qryPrograms (qryPrograms is an exact replica of the Programs table)
Expand|Select|Wrap|Line Numbers
  1. ProgramID (PK)
  2. ProgramInitials
  3. Program
  4. CampusID (FK for Campus table)
SQL: qryPrograms
Expand|Select|Wrap|Line Numbers
  1. SELECT tblPrograms.ProgramID, 
  2.        tblPrograms.ProgramInitials, 
  3.        tblPrograms.Program, 
  4.        tblPrograms.CampusID
  6. FROM tblPrograms
  8. ORDER BY tblPrograms.ProgramInitials;
Query: qryFormsCompliance
Expand|Select|Wrap|Line Numbers
  1. PatientDetailID (PK on PatientDetail table)
  2. Episode
  3. DischargeDate
  4. Form: ([FormName])
  5. ProgramInitials
  6. CorrectedDate
  7. ITF T/F: IIf(IsNull([FormName]) Or ([correcteddate]<([dischargedate]+30)) Or (IsNull([correcteddate]) And (Date()<[dischargedate]+30)),0,1)
SQL: qryFormsCompliance
Expand|Select|Wrap|Line Numbers
  1. SELECT qryPatientDetail.PatientDetailID, 
  2.        qryPatientDetail.Episode, 
  3.        qryPatientDetail.DischargeDate, 
  4.        ([FormName]) AS Form, 
  5.        qryPrograms.ProgramInitials, 
  6.        qryEpisodeDetail.CorrectedDate, 
  7.        IIf(IsNull([FormName]) Or ([correcteddate]<([dischargedate]+30)) Or (IsNull([correcteddate]) And (Date()<[dischargedate]+30)),0,1) AS [ITF T/F]
  9. FROM qryPrograms 
  11. RIGHT JOIN ((qryForms 
  12.      RIGHT JOIN (qryDeficiencyType 
  13.           RIGHT JOIN qryEpisodeDetail 
  14.           ON qryDeficiencyType.DeficiencyTypeID = qryEpisodeDetail.DeficiencyIDFK) 
  15.      ON qryForms.FormID = qryEpisodeDetail.FormIDFK) 
  16.      RIGHT JOIN qryPatientDetail 
  17.      ON qryEpisodeDetail.PatientDetailIDFK = qryPatientDetail.PatientDetailID) 
  18.      ON qryPrograms.ProgramID = qryPatientDetail.ProgramIDFK
  20. ORDER BY qryPatientDetail.PatientDetailID;
May 8 '09 #3
32,499 Expert Mod 16PB
Very much so (generally the SQL is enough, but where not I'll ask).

So, for each set of SQL, can you explain what you want to have that you don't at the moment?

One at a time is best, but if you're in a hurry we can try them all together (more likely to slip errors in that way though).

PS. I've posted some questions myself, so I truly understand how easy formulating a question that makes sense isn't ;)
May 8 '09 #4
579 512MB
Ok...I'll give it a shot.

Two of the queries, qryFormsCCC and qryPrograms, have the information needed for the row and column headings of the crosstab. qryFormsCCC will be the row heading and qryPrograms will be the column heading.

Part of the problem I'm having involves not all of the forms showing up on the crosstab. If I have 10 forms in qryFormsCCC, I need 10 forms to show up on the crosstab query, regardless of whether or not there is any detail associated with it. Typically, for the timeframe that will be used to look at the data, all 10 forms will be present, but I need to prevent the possibility that the timeframe doesn't since the goal of the database is to get to 100% compliance. In my database, 100% compliance means that there is no detail for any forms. This explains why it's important to still have the forms show up because we need to see that zero data equals 100% compliance.
May 8 '09 #5
579 512MB
Here's the next part.

On the qryFormCompliance, it shows a patient and all the forms with any accompanying mistakes. If there are no mistakes, the form field is blank. If a form does have a mistake, it may have multiple mistakes, which means that the form will appear twice on the table because the deficiency that's associated with it is different and causes the row to not be distinct.

If I remove the deficiency field (which is what I did) and add distinct to the SQL, it will combine the fields together, but the problems lies in the compliance indicator. If a form has one deficiency that was, say missing a signature, and another that was missing a date, then that form has two deficiencies. If one of the deficiencies is corrected, but the other isn't, that form isn't in compliance. Only when all deficiencies have been corrected can the form be deemed compliant.

The value for the crosstab query needs to tally a form for a patient if it isn't in compliance, but only once.

ChipR helped me get a query to show if a patient's chart was in compliance by putting a one if the form was out of compliance and a zero if it was in compliance and then adding them together. If the sum was greater than zero, then the patient's chart was out of compliance.

Not only do I need to see that the patient's chart was out of compliance, I need to see which forms were out of compliance to mark the value in the crosstab.

If a SELECT query would show all forms and all programs and whether it was non-compliant, but also show blanks...I think I would have everything I need. Now, getting it to do that is what has been the problem and what has given me this headache that won't go away. :)
May 8 '09 #6
32,499 Expert Mod 16PB
This sounds like something I'll need to stop and give some serious consideration to, simply to get my head around exactly what you need. I will post when I manage to get some time available to give this the attention it requires.

I just thought I'd post to reassure you I'm not ignoring this. I was busy over the weekend so I have some catching up to do generally.
May 11 '09 #7
579 512MB
I understand Neo and, as always, thank you for your help.
May 11 '09 #8
579 512MB
Just bumping this since I haven't seen any responses or views today.
May 12 '09 #9
32,499 Expert Mod 16PB
Not forgotten Beacon (but perfectly fine to bump).

I've just been busier than usual recently. I'll see what I can manage today.

Unfortunately there are a few threads I need to catch up with atm.
May 13 '09 #10
32,499 Expert Mod 16PB
First, remember I'm not able to help much (if at all really) with the Cross-Tab side of things.

However, you say you need all the forms to exist in the data that the Cross-Tab will process. For this you will need to remove the WHERE clause of [qryFormsCCC].

You will need to handle this (more raw) data differently at the higher level I would expect.

PS. I've just reread the first quoted post here and I suspect you may be referring to all the forms as meaning all those returned by [qryFormsCCC]. If so, then this question pertains to how you process the information you already have in your Cross-Tab query. I believe I already mentioned I can't help there.
May 13 '09 #11
32,499 Expert Mod 16PB
The trouble here is that you don't give me any reference points I can work from.

I'm sure if I knew and understood your database what you say make more sense. Unfortunately almost everything you say is in reference to something else, which I know nothing about. It makes building an understanding of your problem very difficult for me.

I was trying to lead you with my earlier posts to explaining things in a controlled and defined way.

There are plenty more references to the Cross-Tab (which can only indicate that you're not working with me to isolate an individual oproblem I may be able to help with).

Before getting anywhere near a Cross-Tab, you sould be designing and testing the underlying datasources. This is not exclusively for my benefit (although it makes working with you much easier of course). It will also help you though. Building on top of data sources that are not thoroughly sound and stable (do exactly what you need) is a recipe for problems.

You need to explain your problem, with a particular data source (query), in terms of what that query is expected to produce. I hope I have explained this clearly enough to make sense.
May 13 '09 #12
579 512MB
Hi Neo,

Just an update...I think I was able to get what I needed. I had to create 3 SELECT queries that used a method that Chip showed me in the post that this thread links to and then was able to put that in the crosstab.

The crosstab actually wasn't where I needed to focus my attention. It took spelling everything out for you guys in here for me to finally understand. My problem was that I knew what the end result needed to be, but I wasn't sure how to walk backward in my steps to make that result appear.

Thank you for your help...even when you don't specifically have a solution for me, it's still a huge help. Helps me get my mind wrapped around the problem a little better to try to explain it to someone else since I'm the only one at my work that does anything at all with Access.
May 15 '09 #13
32,499 Expert Mod 16PB
That sounds very much what I was trying to tell you :)

I'm pleased that helped. Sometimes, learning how to think the right way can be so valuable, and so difficult to accept. The fact you've taken it onboard says a lot about you.


PS. Thanks for updating us.
May 18 '09 #14

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Nathan Bloomfield | last post: by
2 posts views Thread by Catherine | last post: by
4 posts views Thread by Judy | last post: by
6 posts views Thread by tizmagik | last post: by
reply views Thread by Saiars | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.