Writing a Query based on times in two different tables

[AimeeRae]

Ok. Another tricky Access question. Same database, different querry.

I have a table called tbl_Responses and my tbl_Airing…similar to my question about linking Airings to Rates, I now need to link Airings to Responses.

For example, an ad airs on 2/12/2007 at 7:05am,10:20am, 1:45pm, 3:55pm, 8:20pm(this data will be stored on the Airing table). From that Airing, I get 34 responses with differing times through out the day. Is there a way to link the response to which Airing it came from? The threshold for the link between the Response and the Airing will be 30 mins, after 30 minutes it is considered “Unsourced”—which means we are unable to tell which Airing the Response belongs to.

In my Response table I have the following headers and info:

Station
Trans Date (date of Response)
Trans Time (time of Response)


In my Airing table I have the following headers and info:

Station
DoA (Date of Airing)
ToA (Time of Airing)

Any help you can give would be greatly appreciated!!!

Thanks!

[Rabbit]

My question is what happens to the response if there's an airing schedule as follows.

Ad A airs on Station KWOD at 7:05 AM
Ad B airs on Station KLCG at 7:05 AM
Ad C airs on Station KWOD at 7:08 AM

What I'm wondering is if you'll need to add fields in your Responses table to link it to your Airing table such as an Ad ID and Station ID.

[AimeeRae]

My question is what happens to the response if there's an airing schedule as follows.

Ad A airs on Station KWOD at 7:05 AM
Ad B airs on Station KLCG at 7:05 AM
Ad C airs on Station KWOD at 7:08 AM

What I'm wondering is if you'll need to add fields in your Responses table to link it to your Airing table such as an Ad ID and Station ID.

You think of EVERYTHING! =)

Actually, there will only be one Ad running per station, however, I will need to link the Station on the Airing table to the Station on the Responses table. They will always be in the same format: (KDWB-AM or WLS-AM...3 or 4 letters and AM/FM).

Thanks!!! You are such a great help.

[Rabbit]

You're more than welcome.

[AimeeRae]

You're more than welcome.

=)

So I will need to link tbl_Responses and tbl_Airing by Station and by Date, then do an IF statement to link my response times to my airing times. I wrote this:

SELECT tbl_Responses.Station,
       tbl_Responses.[Trans Date],
       tbl_Responses.[Trans Time],
       IIf(([tbl_Airing.ToA]-[tbl_Responses.Trans Time])<=#12/30/1899 0:30:0#,[tbl_Airing.ToA],“unsourced”) AS Expr1,
       tbl_Airing.[Length of Spot],
       tbl_Airing.[Type of Read],
       tbl_Airing.[Aired Offer]
FROM tbl_Airing INNER JOIN tbl_Responses
  ON (tbl_Airing.DoA = tbl_Responses.[Trans Date])
 AND (tbl_Airing.Station = tbl_Responses.Station);

However it's not working and giving me back way too many records!

[Rabbit]

Here's what's happening. If you have multiple records with the same Date of Airing and Station but with different times. And then you have different responses with the same Date of Response and Station. Then with an inner join, you're linking every airing with every response. So you get [# of Airings] * [# of responses] records.

My question is if you need any information from unsourced responses. If not, then in the criteria for Expr1, I'd filter out the "unsourced".

Here's another problem. If you have an airing at 7 AM and another airing at 7:15 AM, then the response would be linked to both airings.

If either of these are an issue, the only way to get around this is to use ToA in the Response table as an Airing ID to link them.

Also, your syntax should be:

IIf((tbl_Airing.[Trans Time]-tbl_Responses.[ToA])<=#12/30/1899 0:30:0#,[

Assuming that Trans Time will be later than the ToA.

[AimeeRae]

Here's what's happening. If you have multiple records with the same Date of Airing and Station but with different times. And then you have different responses with the same Date of Response and Station. Then with an inner join, you're linking every airing with every response. So you get [# of Airings] * [# of responses] records.

My question is if you need any information from unsourced responses. If not, then in the criteria for Expr1, I'd filter out the "unsourced".

Here's another problem. If you have an airing at 7 AM and another airing at 7:15 AM, then the response would be linked to both airings.

If either of these are an issue, the only way to get around this is to use ToA in the Response table as an Airing ID to link them.

Also, your syntax should be:

IIf((tbl_Airing.[Trans Time]-tbl_Responses.[ToA])<=#12/30/1899 0:30:0#,[

Assuming that Trans Time will be later than the ToA.

Unfortunately, I do need the "Unsourced" information. I need to see how many responses are considered "unsourced. The response being linked to both airings is not going to be an issue. We will never have 2 airings that close to each other. So here is my second attempt at SQL:

IIf((tbl_Airing.Station = tbl_Responses.Station), And (tbl_Airing.[DoA] = tbl_Responses.[Trans Date]), (tbl_Airing.[ToA]-tbl_Responses.[Trans Time])<=#12/30/1899 0:30:0#,tbl_Airing.[ToA],"unsourced")

Of course, it's still not working...but I feel like I'm really close!

[Rabbit]

Disregarding the incorrect syntax of the iif, you have extra commas that don't belong in there, this runs into the same problem as before. Here's the problem.

If you have:

Airings Table
Station  DoA  ToA
KWOD  1/1/2007  7:00AM
KWOD  1/1/2007  7:00PM
 
Responses Table
Station  DoR  ToR
KWOD  1/1/2007  7:15AM
KWOD  1/1/2007  7:18PM
 
What you get is
Station  DoA  ToA  ToR  Expr1
KWOD  1/1/2007  7:00AM  7:15AM  7:00AM
KWOD  1/1/2007  7:00PM  7:15AM  Unsourced
KWOD  1/1/2007  7:00AM  7:18PM  Unsourced
KWOD  1/1/2007  7:00PM  7:18PM  7:00PM
 

Now compound this problem by the number of Stations you have and by the number of airings you have per day on the same station.

I'm thinking you may need to keep the query the way it was in the beginning and then run a second query on it to remove all "unsourced" records that aren't really unsourced. Perhaps:

Expr2: iif(Expr1 = "unsourced", iif(Dcount("*", "qry1", "Station = '" & Station & "' AND DoA = " & DoA & " AND ToR = " & ToR & " AND Expr1 <> 'unsourced'") > 0, "", Expr1), Expr1)

This will check if Expr1 is unsourced. If it's unsourced, it will count to see if there are any records with the same Station, Date of Airing, and Time of Response that is sourced. If there is such a record, it will return a null string. If not, it will return "unsourced". And if Expr1 is sourced in the first place, it will just return Expr1 which is the ToA. And then, you just filter out the null strings.

Another problem, your expr1 field will be considered a string field because you return "unsourced" for some of the records. Dates/Times are stored as a number signifying the difference from an arbitrary date. What you will end up seeing is that number, i.e. 7 for 7 days from the chosen date. If the chosen date is 1/1/2007 then in Expr1 you will see 7 rather than 1/8/2007.

[AimeeRae]

Disregarding the incorrect syntax of the iif, you have extra commas that don't belong in there, this runs into the same problem as before. Here's the problem.

If you have:

Airings Table
Station  DoA  ToA
KWOD  1/1/2007  7:00AM
KWOD  1/1/2007  7:00PM
 
Responses Table
Station  DoR  ToR
KWOD  1/1/2007  7:15AM
KWOD  1/1/2007  7:18PM
 
What you get is
Station  DoA  ToA  ToR  Expr1
KWOD  1/1/2007  7:00AM  7:15AM  7:00AM
KWOD  1/1/2007  7:00PM  7:15AM  Unsourced
KWOD  1/1/2007  7:00AM  7:18PM  Unsourced
KWOD  1/1/2007  7:00PM  7:18PM  7:00PM
 

Now compound this problem by the number of Stations you have and by the number of airings you have per day on the same station.

I'm thinking you may need to keep the query the way it was in the beginning and then run a second query on it to remove all "unsourced" records that aren't really unsourced. Perhaps:

Expr2: iif(Expr1 = "unsourced", iif(Dcount("*", "qry1", "Station = '" & Station & "' AND DoA = " & DoA & " AND ToR = " & ToR & " AND Expr1 <> 'unsourced'") > 0, "", Expr1), Expr1)

This will check if Expr1 is unsourced. If it's unsourced, it will count to see if there are any records with the same Station, Date of Airing, and Time of Response that is sourced. If there is such a record, it will return a null string. If not, it will return "unsourced". And if Expr1 is sourced in the first place, it will just return Expr1 which is the ToA. And then, you just filter out the null strings.

Another problem, your expr1 field will be considered a string field because you return "unsourced" for some of the records. Dates/Times are stored as a number signifying the difference from an arbitrary date. What you will end up seeing is that number, i.e. 7 for 7 days from the chosen date. If the chosen date is 1/1/2007 then in Expr1 you will see 7 rather than 1/8/2007.

Thank you!!! Sorry for the delay! I just saw this now!

[Rabbit]

Not a problem. Let us know how you do and if you have any more problems.