Create a Report in MS Access

RuggieBear

Hi All,

I am very much a newbie to Access and I'm hoping someone can help me. I have set up a database in order to collect data from questionnaires, and to hopefully produce reports summarising this data.

So, I have a table showing details of television programmes (including information like the title, time & date it was shown, presenter name etc.). I have assigned each one of these programmes a unique reference number and included that number in the table.

For each of these programmes viewers have completed a questionnaire giving their opinions e.g.

1. What did you think of the content? Viewers must choose from 'Brilliant', 'OK', or 'Rubbish'

2. What did you think of the presenter? Viewers must choose from 'Brilliant', 'OK', or 'Rubbish'

3. How did you rate the programme length? Viewers must choose from 'Just Right', 'Too Long' or 'Too Short'

I am trying to create a report where when I input a programme reference number, up comes a simple report showing the number of people who selected the answer e.g. the report would look something like:

AM1000 - American Idol - 16/06/2007

Question 1 - What did you think of the content?

Brilliant = 16
OK = 4
Rubbish = 5

......and so on.....

That is, I don't want the report to list out every row (i.e. not for every questionnaire that was input for programme reference AM100) - I just want the results totalled up for each question.

I have tried creating a query that allows me to set the criteria [Enter Programme Reference:] and using COUNT but although this works fine for each separate query, for each separate answer, for each question, I can't use multiple queries on the same report! This has meant I have created a separate query for every single variable, for every question.....I just didn't know how else to do it......but it doesn't let me put all these queries on the same report (even aside from the fact that I must be doing it wrong if I'm having to do ever variable separately!)

I don't use SQL but I'm not sure if this helps to explain what I'm doing?!

SELECT tblQuestionnaires.[Reference Number], tblQuestionnaires.[1 Content]
FROM tblProgrammeList INNER JOIN tblQuestionnaires ON tblProgrammeList.[Reference Number]=tblQuestionnaires.[Reference Number]
WHERE (((tblQuestionnaires.[Reference Number])=[Enter Reference Number:]) AND ((tblQuestionnaires.[1 Content])="Brilliant"));

Obviously this just shows me the 'Brilliant' results for Question 1 about the content, for whichever reference number I put in. I have done separate queries for each of the variables on each question, and then used those queries to COUNT the number of lines for each result! (Very long-winded so I'm sure I've done it wrong!)

However, even after all that when I try to put together a report it won't let me put 2 or more queries on together, so all this work is pretty useless! lol!

So, I have googled, looked at all kinds of weird websites, and I have been all through the MS website but I just can't see how I should be approaching this instead. I have been trying to get this right for days and it's driving me mad!

I think that the problem is I don't know what I am looking for e.g. if I should be looking for instructions on a specific type of query I don't know the name of!

I'm sorry if this is a really stupid query, I know the answer must be obvious as it seems like the kind of thing that should be really easily done! Go easy on me please, I'm a database newbie and I've had to teach myself as I've gone along!

I'd really appreciate any ideas. You'll save me from going nuts! Thank you!

masterclass

Hi there,

What you can do is create a form with a drop down list of your questions.
Use this value then as a paramater into a query, which in turn is fed into the report.

You can PM me if you like and I can do a sample for you.

Cheers

Evil Phil

Folks,

Can we keep the questions and responses in thread. I'm introducing a new charter rule preventing people from asking for pm's to provide answers.

cheers
E.P.

MoominPapa

You need a cross tab query as the basis of your report. If you haven't looked at these yet you should as they are very useful.

RuggieBear

Thanks a million, that might be just what I need. I had never heard of them before (told you I was a beginner!), but it looks like it might do the trick.

I'll let you know how I get on. Thanks again.