Countif type code

[RobP86]

Hi guys
This is my first post so please accept my apologies for any breaches in protocols etc.

I have a table [tblTimings] with many records. Each record contains 40 fields containing values from 1 to 100. The same value can appear in more than one field within the record.

What I want to do is create a query that counts the number of times that each value between 1 and 100 occurs in each record. I know this will create a query with 100 columns (plus the ID) and can easily do this in Excel using the Countif function but can't work out how to do it in Access.

Any help would be much appreciated
Many thanks
RobP86

 

[MarkK]

The easy way to do this is with two tables, tblTiming and tblTimingDetail, where tblTimingDetail has 40 records for each row in tblTiming. Database functions work on columns. If you embed multiple "sibling" data points in the same row, addressing them field by field is practically impossible. "Sibling" data points should be distinct rows.

 

[RobP86]

Thanks MarkK for posting the reply. I'm a litle new to Access and unfortunately don't really understand your reply. I don't understand what "sibling" data points means. I have tried Googling but to no avail.

I'm also not sure how the tblTimingDetail you refer to would be created. Are you saying that each of the records i have in tblTiming should have their own separate table with 40 records in them. As i have many records (in excess of 1000), the database would soon become impossibly complicated.

As i say, i'm quite new to Access so may have misunderstood the concept you were proposing

 

[MarkK]

Let's say you have a CD collection. One CD has many tracks, right? One artist has many CDs, right? One song might belong to many Genres. These are called "one-to-many relationships" between things.

In a database, each type of object gets its own table, and is related to other objects (one-to-many) using keys. In your CD database you would have a CD table, a Track table, and Artist table, a Genre table. Each instance of each type of thing you are concerned with gets a table, because one row is one thing, indivisible, discrete.

In your case you . . .

. . . have a table [tblTimings] with many records. Each record contains 40 fields containing values from 1 to 100.

Those 40 fields are each the same type of data--same type of thing--right? And they belong to the individual Timing, right? So those 40 fields are like tracks on the CD, they are the same type of thing compared to each other, and they are different from--but belong to--the timing. So the timing (the row in tblTiming) is the parent of each of those fields, one-to-many, and each of those 40 fields should, in fact, be rows in a related table.

In this respect a database is completely unlike a spreadsheet. If you use database tables as if they were spreadsheets, then you defeat the power of the relational model.

One thing you might google is "ERD", or "Entity-Relationship Modelling" and understand how to relate objects to each other. "Database Normalization" is also good to Google to start to understand how database tables are completely unlike spreadsheets, and how you go about modelling a real-world problem.

But in short, if you mix parent and child data in the same row, then summarizing the child data, finding it back, is almost impossible.

Hope this helps,