Thread: Television viewing figures (2000)

1. Television viewing figures (2000)

I have a table that lists the precise times that various channels were switched to/on and switched from/off. For example, BBC1 was switched on 07/09/04 10:47 and subsequently switched off 07/09/04 11:18. These details are shown for many different users. I want to be able to count, for example, how many people were watching BBC1 between 11:00 and 11:15 on a date? What would be the first steps to achieving this? I have an idea how to do this, but it is complicated in that some people may have switched between channels during this time period.. Thanks, Andy.

2. Re: Television viewing figures (2000)

If you have some code for the person, then it shouldn't be so difficult - you would count by the person code. Without something to allow you to distinguish between 3 people watching the channel or one person flicking backwards and forwards I can't really see a method of producing the counts you require.

3. Re: Television viewing figures (2000)

It is very difficult to check a range against a range. You may need to take this in two chunks. For example, start by checking if start of viewing was earlier than the first part of your range, and end of viewing was any time after the start of your range. That picks up the obvious records. Then reverse the check for the records that fail the first test, for end of viewing after the end of your range, and start of viewing any time before the end of your range. It sounds logical to me (gr).

4. Re: Television viewing figures (2000)

A question, you only want to count people that were watching the channel the entire time between 11:00 and 11:15, so they would have to have started watching prior to 11:00 and ended after 11:15?

Or, count if a person switched on the channel between 11:00 and 11:15 at least once. What about channel surfers that may have switched on the channel several times between this time period?

5. Re: Television viewing figures (2000)

Just out of curiosity, unrelated to Access: how are you getting your raw data?
thx
Pat

6. Re: Television viewing figures (2000)

Hi

Would the following work for the time part of the query?

SELECT Date_Time.DTID, Date_Time.StartTime, Date_Time.EndTime
FROM Date_Time
WHERE (((Date_Time.EndTime) Between #09/20/2003 23:0:0# And #09/20/2003 23:15:0#))
or
(((Date_Time.StartTime) Between #09/20/2003 23:0:0# And #09/20/2003 23:15:0#))
or
(((Date_Time.StartTime)< #09/20/2003 23:0:0# And (Date_Time.EndTime) > #09/20/2003 23:15:0#))

Regards
WTH

7. Re: Television viewing figures (2000)

The raw data comes from a company that have issued a 'black box' to 2,000 users. Apparently when they 'login' they have to specify who's in the room. All the viewing data is then supplied to another company, called TRK, that produce demographic statistics.. presumably for advertising companies.

8. Re: Television viewing figures (2000)

I only came across these questions yesterday, so I need to have another look at the data. However, I was thinking I could generate a dummy table which displays all the minutes in a day, and then create a query which includes this table and the main table which contains fields called StartTime and EndTime. Is this where you're select statement would be applied?

9. Re: Television viewing figures (2000)

If we presume the data looks something like the following:

<table border=1><td>Viewer</td><td>Channel</td><td>StartTime</td><td>EndTime</td><td>A0001</td><td>BBC1</td><td>01/09/2004 13:00:00</td><td>01/09/2004 13:10:00</td><td>A0001</td><td>BBC2</td><td>01/09/2004 14:00:00</td><td>01/09/2004 14:06:00</td><td>A0001</td><td>BBC1</td><td>01/09/2004 14:01:00</td><td>01/09/2004 14:25:00</td><td>A0002</td><td>BBC1</td><td>01/09/2004 14:03:00</td><td>01/09/2004 14:33:00</td><td>A0003</td><td>BBC2</td><td>01/09/2004 13:06:00</td><td>01/09/2004 13:07:00</td><td>A0004</td><td>BBC1</td><td>01/09/2004 13:00:00</td><td>01/09/2004 15:02:00</td></table>

Calculate a number to represent each five minute block during the day:

eg: for five minute blocks (12 blocks per hour)

StartBlockNo = (Hour([StartTime])*12)+Int(Minute([StartTime])/5)
EndBlockNo = (Hour([EndTime])*12)+Int(Minute([EndTime])/5)
This would give the results as:
<table border=1><td>Viewer</td><td>Channel</td><td>StartBlockNo</td><td>EndBlockNo</td><td>A0001</td><td>BBC1</td><td>156</td><td>158</td><td>A0001</td><td>BBC2</td><td>168</td><td>169</td><td>A0001</td><td>BBC1</td><td>168</td><td>173</td><td>A0002</td><td>BBC1</td><td>168</td><td>174</td><td>A0003</td><td>BBC2</td><td>157</td><td>157</td><td>A0004</td><td>BBC1</td><td>156</td><td>180</td></table>

As you Suggested you would require a dummy table to match this against. I created one called Blocks with a field blocknumbers going from 1 to 288 (24hrs * 12 blocks per hour)

The Query to summarise would be as follows:

SELECT Data.Channel, Blocks.BlockNumbers, Count(Data.Viewer) AS CountOfViewer
FROM Data, Blocks
WHERE (((Blocks.BlockNumbers)>=(Hour([StartTime])*12)+Int(Minute([StartTime])/5) And (Blocks.BlockNumbers)<=(Hour([EndTime])*12)+Int(Minute([EndTime])/5)))
GROUP BY Data.Channel, Blocks.BlockNumbers;

I'm sure there are plenty more people much more capable than me who could offer much better solutions - but it works - always a key thing!

10. Re: Television viewing figures (2000)

Hi, WTH, thanks for the query, but it wasn't for me it was for Andrew -- but, that's the beauty of posting to the Lounge! we can all benefit!
Thanks,
Pat

11. Re: Television viewing figures (2000)

Ah, good! my worried mind ... I often wonder if it's possible for my viewing to be "viewed," but these people are willing participants -- it relieves my mind, and -- it also would be _very_ interesting research, I think! Well, maybe only a few of us wierdos might think so <gg> but of course, the industry is interested in the preferences of the consumer.
thx
Pat

12. Re: Television viewing figures (2000)

It sounds like a project I'd be really interested in too - how come I get projects i find dull !

13. Re: Television viewing figures (2000)

Hi

Apologies to Pat for posting incorrectly, in my rush to provide a possible solution I obviously was not too careful in posting my reply.

My example was based on the assumption that there is a start and end time in the table. Attached is the sample DB I used to test it. You can build into the query the TV channels and day dates if necessary.

Regards
WTH

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•