Date and Time Expression Help with Access

[AimeeRae]

Hi Everyone-

I need to figure out how link up a date and time in one database to a specific value in another database. For example:

I have a radio station ABCD that aired an ad on Monday, Feb. 5th at 2:04pm. We pay out specific rates depending on the time of the airing. The rates for each station will vary depending on the station/day and time. In one database I have the airings that are set up like this: (example of headers from the database)

Station: Date: Time of Airing:


Then I have another database that houses all the rates for each station/day time: (these are the headers for the database). This database has a rate associated to each station/day/time.

Station: Mon-Fri 6am-10am: Mon-Fri 10am-3pm: Mon-Fri 3pm-7pm: Mon-Fri 7pm-12am: Sat 6am-7pm: Sun 6am-7pm:


Based on the time and date of the airing, I would like my querry to pull back the specific rates. How can I do this?

So based on the example information from above, station ABCD aired an ad on a Monday at 2:04pm. Therefore I would need the Mon-Fri 10am-3pm rate to be brought back in a querry. Is this possible?

[Rabbit]

Hi Everyone-

I need to figure out how link up a date and time in one database to a specific value in another database. For example:

I have a radio station ABCD that aired an ad on Monday, Feb. 5th at 2:04pm. We pay out specific rates depending on the time of the airing. The rates for each station will vary depending on the station/day and time. In one database I have the airings that are set up like this: (example of headers from the database)

Station: Date: Time of Airing:


Then I have another database that houses all the rates for each station/day time: (these are the headers for the database). This database has a rate associated to each station/day/time.

Station: Mon-Fri 6am-10am: Mon-Fri 10am-3pm: Mon-Fri 3pm-7pm: Mon-Fri 7pm-12am: Sat 6am-7pm: Sun 6am-7pm:


Based on the time and date of the airing, I would like my querry to pull back the specific rates. How can I do this?

So based on the example information from above, station ABCD aired an ad on a Monday at 2:04pm. Therefore I would need the Mon-Fri 10am-3pm rate to be brought back in a querry. Is this possible?

This is entirely possible.

In the Airings table, you'll need to store the day and time in different fields.
In the Rates table, you'll need to store day, time start, time end in different fields. Be sure this is mutually exclusive, i.e. 3:01 - 4:00, 4:01 - 5:00. Not 3:00 - 4:00, 4:00 - 5:00.

Then, in a query, you link the tables based on day, and include all records from the Airings table where the time is between time start and time end.

The SQL will look like this:

SELECT tbl_Airing.Station, tbl_Airing.Day, tbl_Airing.Time, tbl_Rates.Rate
FROM tbl_Airing LEFT JOIN tbl_Rates ON tbl_Airing.Day = tbl_Rates.Day
WHERE (((tbl_Airing.Time) Between [tbl_Rates].[Time Start] And [tbl_Rates].[Time End]));
 

[code]

[AimeeRae]

This is entirely possible.

In the Airings table, you'll need to store the day and time in different fields.
In the Rates table, you'll need to store day, time start, time end in different fields. Be sure this is mutually exclusive, i.e. 3:01 - 4:00, 4:01 - 5:00. Not 3:00 - 4:00, 4:00 - 5:00.

Then, in a query, you link the tables based on day, and include all records from the Airings table where the time is between time start and time end.

The SQL will look like this:

SELECT tbl_Airing.Station, tbl_Airing.Day, tbl_Airing.Time, tbl_Rates.Rate
FROM tbl_Airing LEFT JOIN tbl_Rates ON tbl_Airing.Day = tbl_Rates.Day
WHERE (((tbl_Airing.Time) Between [tbl_Rates].[Time Start] And [tbl_Rates].[Time End]));
 

[code]

Thanks Rabbit! the only problem is that the Rates database is already set up with thousands and thousands of rows of data. It is set up with the headers:

Station: Mon-Fri 6am-10am: Mon-Fri 10am-3pm: Mon-Fri 3pm-7pm: Mon-Fri 7pm-12am: Sat 6am-7pm: Sun 6am-7pm:

The Rates are already tied to the Mon-Fri 6am-10am etc. So how would could I make the time start and end in separate columns without losing the rate information?

The current database for Rates looks like this:

Station MF6am-10am MF10am-3pm etc..
ABCD 100.00 80.00 etc

[Rabbit]

Okay. Here Goes:

SELECT tbl_Airing.Station, tbl_Airing.DoA, tbl_Airing.ToA, IIf(Weekday([DoA])=1,DLookUp("U6am7pm","tbl_Rates","Station = '" & [Station] & "'"),IIf(Weekday([DoA])=7,DLookUp("S6am7pm","tbl_Rates","Station = '" & [Station] & "'"),IIf([ToA]>=#12/30/1899 6:0:0# And [ToA]<#12/30/1899 10:0:0#,DLookUp("MF6am10am","tbl_Rates","Station = '" & [Station] & "'"),IIf([ToA]>=#12/30/1899 10:0:0# And [ToA]<#12/30/1899 15:0:0#,DLookUp("MF10am3pm","tbl_Rates","Station = '" & [Station] & "'"),IIf([ToA]>=#12/30/1899 15:0:0# And [ToA]<#12/30/1899 19:0:0#,DLookUp("MF3pm7pm","tbl_Rates","Station = '" & [Station] & "'"),IIf([ToA]>=#12/30/1899 19:0:0# And [ToA]<=#12/30/1899 23:59:0#,DLookUp("MF7pm12am","tbl_Rates","Station = '" & [Station] & "'"),"No Rates")))))) AS Rate
FROM tbl_Airing;
 

This is the SQL for the query.

You will have to name the fields: Station, MF6am10am, MF10am3pm, MF3pm7pm,MF7pm12am, S6am7pm, U6am7pm.

This will work if your Airings table has the date and time as seperate fields. ToA for Time of Airing and DoA for Date of Airing in my SQL.

This query will return "No Rates" only for Times of Airing before 6 AM or after Midnight on Mondays through Fridays. I did not include such a check for Saturday and Sunday because I did not think of it at the time.

I used 11:59 PM rather than 12 AM because I didn't want to deal with what happens when the date changes at midnight.

If either of these are of importance, let me know.

[AimeeRae]

Okay. Here Goes:

SELECT tbl_Airing.Station, tbl_Airing.DoA, tbl_Airing.ToA, IIf(Weekday([DoA])=1,DLookUp("U6am7pm","tbl_Rates","Station = '" & [Station] & "'"),IIf(Weekday([DoA])=7,DLookUp("S6am7pm","tbl_Rates","Station = '" & [Station] & "'"),IIf([ToA]>=#12/30/1899 6:0:0# And [ToA]<#12/30/1899 10:0:0#,DLookUp("MF6am10am","tbl_Rates","Station = '" & [Station] & "'"),IIf([ToA]>=#12/30/1899 10:0:0# And [ToA]<#12/30/1899 15:0:0#,DLookUp("MF10am3pm","tbl_Rates","Station = '" & [Station] & "'"),IIf([ToA]>=#12/30/1899 15:0:0# And [ToA]<#12/30/1899 19:0:0#,DLookUp("MF3pm7pm","tbl_Rates","Station = '" & [Station] & "'"),IIf([ToA]>=#12/30/1899 19:0:0# And [ToA]<=#12/30/1899 23:59:0#,DLookUp("MF7pm12am","tbl_Rates","Station = '" & [Station] & "'"),"No Rates")))))) AS Rate
FROM tbl_Airing;
 

This is the SQL for the query.

You will have to name the fields: Station, MF6am10am, MF10am3pm, MF3pm7pm,MF7pm12am, S6am7pm, U6am7pm.

This will work if your Airings table has the date and time as seperate fields. ToA for Time of Airing and DoA for Date of Airing in my SQL.

This query will return "No Rates" only for Times of Airing before 6 AM or after Midnight on Mondays through Fridays. I did not include such a check for Saturday and Sunday because I did not think of it at the time.

I used 11:59 PM rather than 12 AM because I didn't want to deal with what happens when the date changes at midnight.

If either of these are of importance, let me know.

Perfect! So I just change the name of the fields and copy SQL into the "criteria" field in my querry? Thank you! Thank you!

[Rabbit]

Not the criteria field.

There are 2 ways to design a query: Design View and SQL View.

In Design View, if you right-click the title bar, you can select SQL View.

You want to replace what's in there with what I typed.

Here's the problem, in my SQL, the tables are named tbl_Rates and tbl_Airing.
If it won't cause any problems, you can just rename your tables.
If they are linked to other forms, reports, queries, etc. then paste it into Word and use search and replace to change the names of the tables.

[AimeeRae]

Not the criteria field.

There are 2 ways to design a query: Design View and SQL View.

In Design View, if you right-click the title bar, you can select SQL View.

You want to replace what's in there with what I typed.

Here's the problem, in my SQL, the tables are named tbl_Rates and tbl_Airing.
If it won't cause any problems, you can just rename your tables.
If they are linked to other forms, reports, queries, etc. then paste it into Word and use search and replace to change the names of the tables.

YOU ARE A GENIUS! THANK YOU SO MUCH! IT WORKED LIKE A CHARM!

[Rabbit]

YOU ARE A GENIUS! THANK YOU SO MUCH! IT WORKED LIKE A CHARM!

Not a problem at all! If only everyone was so enthusiastic about getting their answers lol.

[Rabbit]

Some additional notes of caution on what the query actually returns:

1) It ignores the time at which the airing occurs on Saturday and Sunday.

2) The time cutoffs for Monday through Friday are 6:00 AM - 9:59 AM, 10:00 AM - 2:59 PM, 3:00 PM - 6:59 PM, 7:00 PM - 11:59 PM.

3) The time fields must assume a date of 12/20/1899. If it works now, as long as you don't change anything, the query will continue to work. Just make sure you don't enter any Date information into the Time field.

How the calculated field works:

1) Checks to see if it's a Sunday, if it is, looks up the rate for U6am7pm.

2) If not, checks to see if it's Saturday, if it is, looks up the rate for S6am7pm.

3) If not, checks to see if ToA is between 6:00 AM and 9:59 AM, if it is, looks up rate for MF6am10am.

4) If not, checks to see if ToA is between 10:00 AM and 2:59 PM, if it is, looks up rate for MF10am3pm.

5) If not, checks to see if ToA is between 3:00 PM and 6:59 PM, if it is, looks up rate for MF3pm7pm.

6) If not, checks to see if ToA is between 7:00 PM and 11:59 PM, if it is, looks up rate for MF7pm12am.

7) If not, returns "No Rates".

So basically it's iif() embedded in iif() embedded in iif() and so on.

[AimeeRae]

Some additional notes of caution on what the query actually returns:

1) It ignores the time at which the airing occurs on Saturday and Sunday.

2) The time cutoffs for Monday through Friday are 6:00 AM - 9:59 AM, 10:00 AM - 2:59 PM, 3:00 PM - 6:59 PM, 7:00 PM - 11:59 PM.

3) The time fields must assume a date of 12/20/1899. If it works now, as long as you don't change anything, the query will continue to work. Just make sure you don't enter any Date information into the Time field.

How the calculated field works:

1) Checks to see if it's a Sunday, if it is, looks up the rate for U6am7pm.

2) If not, checks to see if it's Saturday, if it is, looks up the rate for S6am7pm.

3) If not, checks to see if ToA is between 6:00 AM and 9:59 AM, if it is, looks up rate for MF6am10am.

4) If not, checks to see if ToA is between 10:00 AM and 2:59 PM, if it is, looks up rate for MF10am3pm.

5) If not, checks to see if ToA is between 3:00 PM and 6:59 PM, if it is, looks up rate for MF3pm7pm.

6) If not, checks to see if ToA is between 7:00 PM and 11:59 PM, if it is, looks up rate for MF7pm12am.

7) If not, returns "No Rates".

So basically it's iif() embedded in iif() embedded in iif() and so on.

Thanks for explaining that calculation! That helps alot. The only problem I'm having now is that there are several rates showing up for each day (which is correct), is there any way that I can add into the formula something that would just give me the total rate for the entire day's worth of airings?

[Rabbit]

First I need to know how you calculate the total rate for the entire day. Are you talking total average rate? And what's being included and excluded from these calculations? Total rate for all stations? Or for each station? All of these can be done with a different query that is based off this query to calculate sums. But how you build it will depend on what you want to calculate.

[AimeeRae]

First I need to know how you calculate the total rate for the entire day. Are you talking total average rate? And what's being included and excluded from these calculations? Total rate for all stations? Or for each station? All of these can be done with a different query that is based off this query to calculate sums. But how you build it will depend on what you want to calculate.

Sorry, I should have been more specific. It is the Total Rate(sum) for each station on a daily basis. You have been so much help. Thank you!

[Rabbit]

You're going to have to create another query that is based on the previous one. Just group by Station, Date of Airing, and then Sum of Rate.

If you need the SQL, let me know.

[AimeeRae]

You're going to have to create another query that is based on the previous one. Just group by Station, Date of Airing, and then Sum of Rate.

If you need the SQL, let me know.

Thanks again!!! You are the best. Have a great weekend.

[Rabbit]

You're welcome, you have a nice weekend too.