creating report in access

[cliff]

I have the following data :-

date no1. no.2 no.3 no.4 no.5
05/01/08 12 16 25 30 05
06/01/08 6 12 24 31 08
07/01/08 10 16 30 24 13
08/01/08 05 24 31 17 08

I would like to create report on number of times nos. hit for eg : 05 hit
twice, 24 hit thrice and numbers from 1-31 that are not hit. Please help to
solve this

thanks

 

[Douglas J. Steele]

Your data is denormalized, which makes it much harder to query. Consider
writing a normalization query, and then using that query as the basis for
analysis. Your normalization query (let's call it "qryNormal") would be
something like:

SELECT MyDate, no1 AS Field1, 1 AS Position
FROM MyTable
UNION
SELECT MyDate, no2, 2
FROM MyTable
UNION
SELECT MyDate, no3, 3
FROM MyTable
UNION
SELECT MyDate, no4, 4
FROM MyTable
UNION
SELECT MyDate, no5, 5
FROM MyTable

(Note that I renamed the first field from date: date is a reserved word, and
should never be used for your own purposes. For a comprehensive list of
names to avoid, see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html )

To determine how many times each number was selected, use

SELECT Field1, Count(*)
FROM qryNormal
GROUP BY Field1

That won't tell you which numbers weren't selected, though. If you really
need that, you'll need to create a table that has 1 row for each possible
number, and join that table to the analysis query above using a Left Join.