Using Query to match exact time bet. time ranges (1 Viewer)

[kilroyscarnival]

I hope I can explain this properly. I'm trying to come up with a way to take a list of items with exact times and match them up with another list of items containing a start and stop time. Specifically, a TV ad is broadcast 1/1/2002 at 17:57, and I want Access to assign the record from another table that contains the program which aired from 17:30 to 18:00 on that date, and its ratings.

I can accomplish this in Excel through a long series of If/Then commands, but I want to create something I can use in a more fluid manner. I'm afraid I've only worked with direct relationship matchups so far, not with ranges. Hope someone can help. Thanks!

--Ann

 

[Alexandre]

If I understand well, you want design a query that would return records where a date range encompass a value you enter?

SELECT TablePrograms.*, TableTVads.TVadName
FROM TablePrograms, TableTVads
WHERE (((TableTVads.TVadTime) Between TablePrograms.[Program_StartingTime] And TablePrograms.[Program_EndingTime]));

However, if there is no relation between your tables, the above will create a carthesian product, which can become to big to handle if you have many records in both of your table. Eventually try work on subsets of records from both tables to manage this problem (use intermediate queries instead of working directl on te tables).

Other approaches are possible, but involve some programming.

Alex

 

[kilroyscarnival]

Alex, that was perfect. Just the memory jog I needed. Thanks!

My new problem is figuring out how to work around one program that runs from 23:30 to 00:30, thereby spanning two different days. In broadcasting (U.S.), this time is often referred to as 24:30 because the broadcasting day is considered to be either 5 a.m. through 5 a.m., or 3a-3a.

My workaround may be to remove the colons and treat times as numbers, and convert the after midnight times to 2400+. I don't suppose there's an easier way?

Thanks again!

--Ann

 

[Alexandre]

Then:
1. Drop between and use full test:
(TableTVads.[TVadTime] >= TablePrograms.[Program_StartingTime]) AND (TableTVads.[TVadTime] <= TablePrograms.[Program_EndingTime])
To avoid #22:00# Between #23:00# AND #2:00# = True

2. Check if your program is across midnight:
[Program_StartingTime] > .[Program_EndingTime]

3. If so, the program matches the add time either one of these conditions is true
[TVadTime] > [Program_StartingTime]
[TVadTime] < [Program_EndingTime]

That is to says matches are given by:
(Add time > Program start time and < Program end time)
OR
(Program is across midnight AND (Add time > Program start time OR < Program end time)

So the query would be:

SELECT TablePrograms.*, TableTVads.TVadName
FROM TablePrograms, TableTVads
WHERE (((TableTVads.TVadTime)>=[Program_StartingTime] AND (TableTVads.TVadTime)<=[Program_EndingTime]))
      OR (((TablePrograms.Program_StartingTime)>=[Program_EndingTime]) AND
          ((TableTVads.TVadTime)>=[Program_StartingTime] OR (TableTVads.TVadTime)<=[Program_EndingTime]));

It may look awfull but the QBE will do it for you.
Use one line for criteria 1.
And another line for both criteria 2. and 3.

Alex

[This message has been edited by Alexandre (edited 03-07-2002).]

 

[kilroyscarnival]

Alex, I can't thank you enough for your help so far. I don't know whether I'm just too inexperienced (I am, really) or am trying to do too much in one query, but I can't seem to get the query to work. I end up with seven matches to every ad instead of one.

If it's not abusing this forum too much, here's the code I ended up with:

SELECT [wk1et]![Date] AS Day1, wk1et.Date, wk1et.Time, Nov01Query.[HH RTG], Nov01Query.[Demo-HH%], [Nov01Query]![HH RTG]*[Nov01Query]![Demo-HH%] AS [ProjP18-49], Nov01Query.RtgOrdr, [ProjP18-49]-[Nov01Query]![RtgOrdr] AS [Over/Under], Nov01Query.[Skew Source]
FROM wk1et INNER JOIN Nov01Query ON wk1et.Date = Nov01Query.Date
WHERE (((wk1et.Date)=[Nov01Query]![Date]) AND ((wk1et.Time) Between [Nov01Query]![StartTm] And [Nov01Query]![StopTm]))
OR ((Nov01Query.StartTm)>[Nov01Query]![StopTm]) AND ((wk1et.Time)>=[Nov01Query]![StartTm]) OR ((wk1et.Time)<=[Nov01Query]![StopTm])
ORDER BY wk1et.Date, wk1et.Time;
______________________________________

I must not have this structured properly. Every time I try to change it I get a different (wrong) result. Sigh.

--Ann

 

[Alexandre]

Forget about that between. It will return you values even idf wek1et.Time = 10 am, Nov01Query!StartTm = 23 pm and Nov01Query!StopTm = 1 am.
See my step1 above.


Eventually send me a copy of the DB, or only the 2 tables involved, with their relationships.

Alex

 

[kilroyscarnival]

Well! The LIGHT BULB just went off for me. For one, I realized I was performing my two queries a bit out of sequence, and that my *first* query should be to compare two tables using the technique you showed me. My second query brings in the rest of the information I needed and that way duplicates are eliminated.

My new problem is that the salesperson who wanted this information has now changed her mind about using individual dates. So I'll have to start over again, but at least I know now what I did wrong in the design process.

Alex, thanks very much for walking me through this. My frustration levels were VERY high last evening and I'm about to do the 'happy dance' down the corridor now.

--Ann

 

[Alexandre]

Most welcome.

Alex