referencing Queries

[Sheba]

I have now created a new query with ALL fields required in the report. Currently the query is running without a hitch. However the fields will all not fit on one page hence I'd opted for tab control. Besides the tab control, do you have any suggestion as to how I can organised about 40 questions which will have answers from the query on one report?

[June7]

I was partially wrong about tab control on report. It does work in Report View, not in Print Preview (therefore not directly to printer). So if you want to print data, tab control is not useful.

I have reports with more fields than can fit on width of page so I 'multi-line' the record.

[Sheba]

thanks. multiline...where is that property?

[June7]

It's not a property, it's just physically arranging the controls as multiple lines.

[Sheba]

but the font will be tiny hm...

[June7]

Why tiny? Example:

[Sheba]

hi but the above does not look organized to me.. If the Label(s) are in the page header, how would one know which control (in the detail section) belongs to which? Each query answers a question so I need to put the question as the label for the returned answer.. not sure if that's clear or not...?

BTW I tried to create the report afresh using the wizard and when I specified all the fields from my 'new' (combined query), it could not create it. I got a message that there were too many fields. But I need all of them...

[June7]

Users get familiar with report. The first row of labels go with the first row of data. It was the best I could do for the request I was given.

Put labels where you need them. This was just an example.

Quite possibly this is not suitable for your data structure but I don't know your data structure.

Tables are limited to 255 fields, I thought queries had the same limit but review http://support.microsoft.com/kb/199076

[Sheba]

thanks June. It's my first time creating a Report of this nature and there's been plenty of learning curves for me. I went back to the previous report I created and made my combined query it's only record source. So now I have the tab control (haven't yet figured out how I will print it) and decided to manually drag and drop the fields onto the different tab pages...

[June7]

Can't print tab control. I already tested that. See post 17. Only the data from one tab will print and the tab control shows a partial outline.

Maybe you need to do subreports.

[Sheba]

oh yes subreports. I'm in a bit of a pickle June7 about some information that I need in my report. Basically, the query counts how many times each question was answerd Yes, No, etc but there is a field where the user can enter short comments on patient's condition which is not covered by the question. I thought to have a subreport which would 'list' everything in that column where the field is not Null. I tried it as a separate query and it worked but now that I have combined the queries, I don't know how to indicate this in the new query. Not sure if this is clear you or not...

so far each column in my new recordsource counts something or has an aggregate function but this info that I need, will select data. Therefore where the others will produce one line of output, the SELECT will return more than one line. How do I do this since I can't use more than one object to create the report ?

[June7]

Since I don't know your data structure I can't really offer specifics.

[Sheba]

There are as many as 40 questions with this format:

indicate patient's symptoms

• Headache
• Fever
• nausea
• other (specify)

there's an input box beside the last option for the user to specify a symptom. My query therefore has 40 columns, each one representing a question. It would find out from the table, how many times a particular answer was given for a particular question. So basically it will count the records but in addition, I want to list user comments for a specific question. Since the user will enter whatever they choose, I can't use SUM or use an aggregate function on that table field. I can only apply SELECT [other] from mytable filter where the field is not empty.

This will produce many rows. My question is "how can I represent his SELECT query with the aggregate function SUM on the other columns together?

[June7]

Doesn't sound like a normalized data structure.

Can use First() or Last() aggregate function on text fields but that will not be useful here.

So you do a total count of answers for each question and then want to also show all user comments for each question/answer?

I expect this would be fairly easy if the data structure were normalized. If each answer was a record then report could be bound to count query and a subreport could have the related comment records.

A UNION query can be used to manipulate non-normalized data into a normalized structure. Something like:

SELECT RespondentID, AnswerField1 AS Answer, "Q1" AS Question FROM Answers
UNI ON SELECT RespondentID, AnswerField2, "Q2" FROM Answers
UNI ON SELECT RespondentID, AnswerField3, "Q3" FROM Answers;

But I still don't know enough about your data structure - I have no idea where the comments you mentioned are located.

Then UNION query can be used in subsequent queries - such as an aggregate Totals with Group By query.

[Sheba]

no it isn't normalized. Please see illustration below:

Q1
indicate patient's symptoms

• Headache
• Fever
• nausea
• other (text box here)

Q2
why was corticosteroids not indicated?

• due to allergy
• responsive to B2
• asthma
• other reason (text box here)

recordID	patientSymptom	OtherSymptom	Corticosteroids	OtherCortico
21200	Nausea		responsive to B2	patient was unconscious
21201	Fever	extreme weakness	Asthma

Query: column1
Sum(IIf([patientsymptom]="Nausea",1,0))

Query: column2
SELECT [Othersymptom] WHERE Not IsNull([Othersymptom])


Query: column3
Sum(IIf([Corticosteroids]="due to allergy",1,0))


Query: column4
SELECT [OtherCortico] WHERE Not IsNull([OtherCortico])