I have two final problems in creating my report....... 1

[NeilBelgium]

I have two final problems in creating my report.......

I want my report to be based on two queries.
The first is simple, and is completed, and look something like this:

QuestionNumber Boss Peer Self Average
1 5 4 4 4.3
2 4 4 4 4
3 3 3 3 3
4 "" "" "" ""
5 "" "" "" ""
6 "" "" "" ""

this goes on for 24 questions, i'm sure you get the idea.
HOWEVER, I also need to build a query that will give totals for the columns
for six questions at a time. all 24 is easy, but how split into sixes, and
produce a query that looks like this:

QuestionGroup Boss Peer Self Average
1 -6 5 4 4 4.3
7-12 4 4 4 4
13-18 3 3 3 3
19-24 4 4 5 3.7
(i know-the numbers don't add up but it's not important!!)

that's major (to me) problem one.

Problem two is that the report,a s well as displaying the results from this
second query , I'm going to add some written reports.

Let's take the first group of questions. If the overall average is between
1-3, I want one written report to appear. If the average is 4-5, another
written report is produced.
Now, I have all these written reports, but in what format do I store them,
and how on earth do I call upon them in the access report?

muchos thankyous to anyone that can assist!!

yours, neil

 

[tomschafer]

Major problem one solution- Look in help for "iif". If you need more help with that, post the SQL and I'll look at it.
Major problem two, I don't understand.

 

[NeilBelgium]

yep, it seems the second problem is baffling a few people, no doubt because I haven't explained it very well!!

the report so far will generate a lot of numerical data.
As well as this, i'd like to give some comments, but based upon the scores.
As i have totals calculated for each section, i'd like the report to be able to do the following:
if avg score<3 then display &quot;you are good&quot;
if avg score>2 then display &quot;you are even better&quot;
and so on.
I have the 12 or so texts ready, but how can I have the report call upon them?

neil

 

[tomschafer]

I would stick them in a meta-data table and use a query to pull them onto the report.

 

[NeilBelgium]

OK. Could you explain how to do this?
regards, neil

 

[NeilBelgium]

ok, there still appears to be one small issue: (surprise, surprise!!)

I have created a table with the textreports:

QuestionGroup
low
medium
high

which houses the twelve options.

How on earth do I synchronize this with the query I have which produces the report, which is, after all, based on grouping and numerical averages.
The report won't accept both table and query to produce the report, so somehow I've gotta get these memo fields into a query from which I can use it.
does this make sense?????
neil

 

[tomschafer]

I'd create a table...

ScoreMessages
Score int
Message text

examle data...
Score Message
3 You Are Good
2 You Are Even Better

Then have the ControlSource of the field on your report be something like...

select Message from ScoreMessages where Score=[avg(Scores)]

 

[tomschafer]

My last post was in reference to...
OK. Could you explain how to do this?
regards, neil

Your last post I don't fully understand...How does the table QuestionGroup relate to the report?

 

[NeilBelgium]

i foresee two problems here!
firstly, the table can't be added to the report - I need to add the messages to the existing queries, I think.
Or can I get the report to draw from the table? I'd love to know how!!
secondly, the averages are goin to be annoying scores such as 2.3, 3.7 etc., so score= won't work. I need If score < or IF score >

I appreciate your help!!

neil

 

[tomschafer]

The report can draw from the table.
And you don't have to use &quot;=&quot; you can use whatever operand you'd like, that's just the one I used in my example. And sorry I never explained, a meta-data table is a table that houses information that does not change. i.e. Score Levels and their respective messages.

Now let's try and sort this out. What are you using for your record source for the report?
Or you could e-mail me your db and I could look at it for ya.

 

[NeilBelgium]

I'm gonna email the db (but what is your address??)

then to explain:
I have two tables - &quot;fromexcel&quot; is literally imported from excel. i've put sample data in the fields to see how things work.
Table two holds the 12 messages

Queries:
I'm using the &quot;average&quot; query to produce the bulk of the report- it has the averages from each question group. dead useful.
there are several more, none of which are too relevant- except &quot;from excel query&quot; which allows me to produce a second report detailing ALL scores for ALL questions.

There is only one report! which is causing all the trouble!

It's grouped by question group so i can insert chart to show the data for each question group, and add a comment based on question group and score.
FYI i guess that scores less than 2.5 are low, more than 2.5 average, and more than 3.7 high.

i owe you one. Do you have nothing else to do that assist the terminally amatuer?
I'm sure gonna learn a lot from this!

many , many thanks once again, neil

 

[NeilBelgium]

you still there? i need an email address to send the db to!!!

thanks, neil

 

[tomschafer]

Sorry bud I went home for the day. My email is twschafer@west.com