Reference specific records for a caculation

[whynot]

Hi,

I'm not entirely sure that this post is being made in the right topic, but here goes.

I'm trying to design a database that takes care of daily and weekly production data for a TV show. Anyway, using two layouts I am able to take care of daily calculations (such as total tape recording time) with ease. However, because we handle operations, we only allocate some things on a weekly basis (such as total studio time). Using a second table to track this stuff is no biggie.

However, I do have problems when I want to compute that for a given day we have used x% of our weekly studio time. So my question really is how can I have a calculation from the weekly table reference a specific cell in my daily table on a given date?

Really, I'd like to be able to reference the daily records for Monday through Friday in order to do end of week calculations on them using data from the weekly table.

Sorry if that is a bit repetitive; any help would be great.

Thanks

[CobaltSky]

Hi whynot,

Have you thought of creating a relationship between your weekly table and your daily table which matches on a pair of date fields for the start and end of the week. Ie a relationship definition along the lines of:

.........WeekStartDate >= DayDate

AND .WeekEndDate <= DayDate

You will then be able to view the day records for the whole week in a portal on the corresponding Week layout, and to summarize the activity for the week using calcs that gather data via aggregating functions such as Count( ) and Sum( ), via the relationship to the day table.

[whynot]

CobaltSky,

You are brilliant. That worked perfectly; thank you ever so much.