交叉表查询，多个连接（？），显示所有行 [英] Crosstab queries, multiple joins (?), show all rows
问题描述
我正在创建一个基于交叉表查询的动态报表。它与以下链接中描述的方法类似：（736.8 KB，96 views）
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: (736.8 KB, 96 views)
推荐答案
Beacon，我忍不住用cro ss-tab查询（和我们许多人一样，如果我不理解一个问题而不下载数据库，我通常会忽略它），但如果你可以发布你正在使用的数据的元数据，我可能会能够帮助一些单独的子查询。假设你说的是，这就是你的一些问题所在。
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.
抱歉Neo ......这更符合你想要看到的内容吗？
查询：qryFormsCCC （qryFormsCCC是Forms表的精确复制品，但选择标准除外）
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)
非常好（一般来说SQL就足够了，但我不会问的地方）。
所以，对于每一套SQL，你能解释一下你不想要的东西吗？目前？
一次一个是最好的，但是如果你匆忙我们可以一起尝试它们（更可能以这种方式出错）。
PS。我自己发了一些问题，所以我真的明白，制定一个有意义的问题并不容易;）
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 ;)
这篇关于交叉表查询，多个连接（？），显示所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
