Even as I write this I know I've set it up wrong.....

[anthony.carter]

Hi,

The database has a form which feeds into a table. The table has been
set up so that it contains a persons ID and then five fields in which
there is a choice of training course. The range of courses that can be
chosen for each field is exactly the same (from a combo box). There
would not be a repetition of a course in a row. However the same
course could turn up in different rows and different fields.

In mitigation I would claim that I did it this way for the ease of the
people using the form.

I think you know what's coming........

How can I compile a query and then a report which will tell me how
many such and such a course has been requested, and also a general
query / report that details how many requests there have been for each
course?

Cheers,

Regards,

TC

 

[Jason Lepack]

Next time, go with your gut.... I also gave you suggestions on this
two weeks ago.

However, if you must really do it this way, then you do this:

SELECT *
FROM yourTable
WHERE field1 = yourCriteria
OR field2 = yourCriteria
OR field3 = yourCriteria
OR field4 = yourCriteria
OR field5 = yourCriteria

That will work, but ewww... not to mention the fact that if "someone"
decides to add a sixth column then you have to modify every query
about this table.

Cheers,
Jason Lepack

 

[anthony.carter]

Thanks Jason,

It's actually a different database with a slightly different query
need but very similar in principle. I'll give your suggestion a try.

Cheers,

Regards,

TC

 

[John Spencer]

You can use a UNION ALL query to normalize the data. Then use the UNION ALL
query as the basis for your summary queries and your reports.

You cannot build a UNION ALL or a UNION query in the QBE (Design View grid).
You have to build it in the SQL View.

The base normalized query would look like the following and be saved as
qNormalCourses (or whatever name you want)
SELECT PersonID, Course1 as Course
FROM YourTable
UNION ALL
SELECT PersonID, Course2
FROM YourTable
UNION ALL
SELECT PersonID, Course3
FROM YourTable
UNION ALL
SELECT PersonID, Course4
FROM YourTable
UNION ALL
SELECT PersonID, Course5
FROM YourTable

To count the number of people taking a course, you would use
SELECT Course, Count(PersonID) as PersonsInCourse
FROM qNormalCourses
GROUP BY Course

To report the person and number of courses
SELECT Count(Course) as NumCourses, PersonID
FROM qNormalCourses
GROUP BY PersonID

To get all persons taking a specific course
SELECT PersonID, Course
FROM qNormalCourses
WHERE Course ="Algebra"
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Jason Lepack]

That's a nifty little Bandaid... (Adds that one to the memory banks)