Back to basics: I think I need my hand holding to get an access report 1

[NeilBelgium]

Back to basics: I think I need my hand holding to get an access report ready by the end of this week!

Dear All, it occurs to me having read the feedback to my earlier questions (and the input is VERY much appreciated) that I really need to learn some basics. fast.

The solution I need to design is probably quite straightforward - data from tables (input will be manual - once I've crossed this bridge I'll look at collectin gthe info using ASP) needs to generate a report.

The fields are all numerical, but some have to be combined. eg. field1, field2 and field3 need to be combined to make a total. However, i've tried to put the formula into the table (like one would in excel) but it seems this is not the way to go!
So- is this something that I need a query for? or can the report generate this? and where in access do I telel the program to add three values and present the resulting figure? I'm aware somewhere there is an engine so that i can quickly enter = [field1] + [field2] etc., but I really don't know whjere. Certainly not in tables!

If someone can give me a blow-by-blow of how the (for example) four calculated values are produced in the report from (again for example ) 24 fields (field1-field24) from the raw data stage, I'd be eternally grateful!

thanks once again everyone, neil

 

[rayna]

Neil,

While in the design view of the report you are putting together, find the text box you wish to show the result of your calculation. Right click on it and go to Properties. Find the field called Control Source. In that blank put a mathematical equation using the names of the text boxes that you want to add together.

For instance, if you have a text box that displays number of apples called NumApples, and one called NumOranges that displays number of oranges and you want the total of the two to end up in a text box called NumFruit, find the control source in Properties of NumFruit, and enter:

=[NumApples]+[NumOranges]

Occasionally I have run into needing parentheses like so:

=([NumApples])+([NumOranges])

Either way, that should do it.

Hope that I understood your question and this helps!

Rayna

 

[NeilBelgium]

many thanks for the input. Someone has suggested to me I need to go back another step and start by organising my tables.

So, how do i organize the following:

fields:
name

for each "name" the following data is being collected:
results (one numerical value) of questions 1-5 from peers
results of questions 1-5 from subordinates
results of questions 1-5 from customers
results of questions 1-5 from bosses
total for this section (now I know to use a query!!)

This needs repeating for questions 6-10, 11-15, 16-20.

very messy, I know! thanks, neil

 

[rayna]

Neil,

So, let me make sure I have this:

Your table has 17 fields?

Name
Peers 1-5
Subordinates 1-5
Customers 1-5
Bosses 1-5
Peers 6-10
:
:
Peers 11-15
:
:
And so on?

And what exactly do you want to display in your report?

Rayna

 

[NeilBelgium]

OK, I'll try to clarify!

for this first stage, i'll have some data-inputters type in from paper questionnaires the results of a 20-question questionnaire.

in an ideal world, I'd just have five fields - name, plus four numerical values.
HOWEVER, the reports I need to create have to differentiate between scores given from bosses to scores given from peers.
So for every set of questions (yes - 1-5,6-10,11-15,16-20) there are actually four differnt scores (peer, customer, boss, subordinate) which need to be displayed in the report, PLUS the average. eg. the four groups rate the person 5,4,2,4 respectively, the final field - average - would be 3.75.

this figure I believez I get from a simple query, but a couple of people have suggested that using this single table is, basically, a mess, and I should reorganize!

i appreciate your trying to help out a complete novice!

neil

 

[rayna]

OK I think I see where you are!

As long as you have the 17 fields I mentioned in my last post I can tell you how to get what you want, and as far as I can see no query is really necessary.

You want a textbox with the name, and then one for each of the four scores for 1-5, and then the average of those 4 scores from 1-5. Then you want one for each of the four scores for 6-10, and then the average of those 4 scores from 6-10. And so on....is this correct?

If so, and your report uses the same naming scheme I used in my last post:

Peers 1-5
Subordinates 1-5
Customers 1-5
Bosses 1-5

Then create a box called Average 1-5 (in the Detail Section with the other four textboxes) and in the Control Source blank put:

=([Peers 1-5]+[Subordinates 1-5]+[Customers 1-5]+[Bosses 1-5])/4

Then do the same for 6-10, 11-15, and 16-20.

I played with the AVG function but found it does not do what we want in this case.

I am not absolutely sure this is what you are trying to accomplish, but it's what I got from your last post. Hope I am right!

Rayna

 

[NeilBelgium]

I'm gonna try it out immediately! expect some renewed panic within the hour!!

thanks once again, neil

 

[NeilBelgium]

eureka! it works! many thanks.

Now, if you're still in the mood, here's the other part:

I'm about to write 16 separate reports, this is the tedoius part. for each set of questions I need to write a report for a low score, high score, middle score etc., explaining what they can read, what the scores mean etc. Don't worry, I'm not trying to enlist your help on this one!!!!

however, what i need to do is put something in the report (and I'm still not TOO cofortable with the design view) that says if total (the expresion we built) <2, then print &quot;not very good report&quot;, if total >2 and <4 then print &quot;actually not bad report&quot; etc............

This will basically complete the job!!

I owe you one, neil

 

[rayna]

Neil,

I fear I may have outlived my usefulness to you.

I am guessing the only way to accomplish something like that is through Visual Basic code with a basic if-else if-then statement (like - if the average score is less than 2, then textbox text is &quot;This is a god-awful score&quot; - etc...) that runs behind the report.

While I know VB and could perhaps write the snippet of code for you, I would not have the first idea on where to put it, or how to get it to work.

I am sure someone here must know how to do that. All my experience in Access is limited to SQL and queries and that sort of thing.

Sorry!

Rayna

 

[NeilBelgium]

Maybe not! I have several issues cropping up as I progress:
In fact, I'd appreciate it if someone could have a look at my db!
New problems:
-I want one decimal place shown on the averages in my report. Have used the properties field and it says one decimal place, but the report preview still shows about 20 dps. why???
-(complex one!) - I have a table containing messages, thus:
questiongroup1 messageHighscore messageLowscore
questiongroup2 messageHighscore messageLowscore

etc.
These &quot;questiongroups&quot; are, surprisingly enough, groups of questions - 1-6,7-12,13-18,19-24. I have calculated the average scores form all thes egroups in the report, but also want to show messages dependent on which questiongroup is being displyed. Can I incorporate this info into the same query as all my numerical data? If not, how do I call these messages in the group footer, where all the calculations are?

-actually, this is about it!

I'll gladly send the db to anyone to have a look at - this project is getting to the panic stage!!

thanks,, neil

 

[rayna]

Neil,

I'll take a look for you...and do what I can.

Since this is my work address and we have very stringent email policies, I am not permitted to publicly post it...

Can you post yours? I'll email you privately.

Rayna

 

[NeilBelgium]

mine is ntrain@theppinetwork.com
or neil.train@pandora.be

i really appreciate this!!

here's the post i sent earlier explaining the setup:
1.the report is grouped by question group, so this is fine, jsut what i
wanted. However, for each groupi need to insert text from a table,
&quot;textreports&quot;. The message displayed depends upon the score in each of the
four groupings.
For example, in question grouping one, one of three messages should be
displayed according to the grouping number field, and the average score for
that set of questions. lower than 2.5, display message &quot;lowscore&quot;, between
2.5 but < 3.7, display &quot;averagescore&quot; and higher display &quot;high score&quot;. I'm
sure I need to use the visibility control, but my issue is actually getting
the data into the report. The report won't accept the table, as it is
working from a query which is unrelated.

second point, and probably the simpler one , is to display a chart for each
grouping area, showing graphically the sum totals which are already
calulated as displayed as digits.
getting access to understand to display four graphs dependent upon question
grouping (ie. four sections, four different graphs) is beyond me!

if anyone can sort this, and have a look, I owe you a beer next time you're
passing throufgh belgium!

many thanks, neil

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.

if anyone can sort this, and have a look, I owe you a beer next time you're
passing throufgh belgium!

THIS HAS CHANGED SLIGHTLY:
key tables are &quot;fromexcel&quot; and &quot;textreports&quot;
queries are &quot;everything&quot; - ie. an overview, and &quot;averages&quot; query, which is what I want to base the report on!!
there's also a messgaes query, with the text messages in.

THERE ARE TWO REPORTS _&quot;this is the one&quot; is, well, the one!
the averages have all been calculted, it's inserting the text messages and charts (and somehow making integers out of all those decimals!!)
the other &quot;good report&quot; is one I've done to cover some oter ground, and is actually in working order!!!

thanks!