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

[beacon]

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

[NeoPa]

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.

[beacon]

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)

FormID (PK)
FormName
FormNo
FormStatus (WHERE FormStatus = True)
ComplianceCalculationConsideration (WHERE ComplianceCalculationConsideration = True)
 

SQL: qryFormsCCC

SELECT qryForms.FormID,
       qryForms.FormName,
       qryForms.FormNo,
       qryForms.FormStatus,
       qryForms.ComplianceCalculationConsideration
 
FROM qryForms
 
WHERE (((qryForms.FormStatus)=True) AND 
      qryForms.ComplianceCalculationConsideration)=True))
 
ORDER BY qryForms.FormName;
 
 

Query: qryPrograms (qryPrograms is an exact replica of the Programs table)

ProgramID (PK)
ProgramInitials
Program
CampusID (FK for Campus table)
 

SQL: qryPrograms

SELECT tblPrograms.ProgramID, 
       tblPrograms.ProgramInitials, 
       tblPrograms.Program, 
       tblPrograms.CampusID
 
FROM tblPrograms
 
ORDER BY tblPrograms.ProgramInitials;
 

Query: qryFormsCompliance

PatientDetailID (PK on PatientDetail table)
Episode
DischargeDate
Form: ([FormName])
ProgramInitials
CorrectedDate
ITF T/F: IIf(IsNull([FormName]) Or ([correcteddate]<([dischargedate]+30)) Or (IsNull([correcteddate]) And (Date()<[dischargedate]+30)),0,1)
 

SQL: qryFormsCompliance

SELECT qryPatientDetail.PatientDetailID, 
       qryPatientDetail.Episode, 
       qryPatientDetail.DischargeDate, 
       ([FormName]) AS Form, 
       qryPrograms.ProgramInitials, 
       qryEpisodeDetail.CorrectedDate, 
       IIf(IsNull([FormName]) Or ([correcteddate]<([dischargedate]+30)) Or (IsNull([correcteddate]) And (Date()<[dischargedate]+30)),0,1) AS [ITF T/F]
 
FROM qryPrograms 
 
RIGHT JOIN ((qryForms 
     RIGHT JOIN (qryDeficiencyType 
          RIGHT JOIN qryEpisodeDetail 
          ON qryDeficiencyType.DeficiencyTypeID = qryEpisodeDetail.DeficiencyIDFK) 
     ON qryForms.FormID = qryEpisodeDetail.FormIDFK) 
     RIGHT JOIN qryPatientDetail 
     ON qryEpisodeDetail.PatientDetailIDFK = qryPatientDetail.PatientDetailID) 
     ON qryPrograms.ProgramID = qryPatientDetail.ProgramIDFK
 
ORDER BY qryPatientDetail.PatientDetailID;
 

[NeoPa]

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 ;)

[beacon]

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.

[beacon]

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. :)

[NeoPa]

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.

[beacon]

I understand Neo and, as always, thank you for your help.

[beacon]

Just bumping this since I haven't seen any responses or views today.

[NeoPa]

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.

[NeoPa]

@beacon
@beacon
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.

[NeoPa]

@beacon
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.

[beacon]

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.

[NeoPa]

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.

Nice.

PS. Thanks for updating us.