Conditional calculations in a query?

[Datech]

I have two tables with the necessary fields in bullets:



ResourceAllocation

• ID (Primary Key)
• Resource Start Date
• Resource End Date
• Percent Utilization

tblRA2012

• ID (Primary Key)
• ResourceAlloc (tied to ResourceAllocation Primary Key)
• Weekx (x = 1-52 for a total of 52 fields)

How the data entry works is that a person will fill in the [Resource Start Date] and [Resource End Date] for a given resource allocation. For various reasons, I need that represented in individual weeks as well. In code, it would be something similar to:

Week 1:
If ResourceAllocation![Resource Start Date]<#1/1/2012# AND ResourceAllocation![Resource End Date]>#1/7/2012# Then
tblRA2012!Week1.value = ResourceAllocation![Percent Utilization].value
End If

My question is, can I do that conditional type of stuff in a query? I don't know enough of about SQL yet to put it all together, so I was wondering if anyone could help.

[June7]

Do these tables have a 1-to-1 relationship? Will each ResourceAllocation always have a record, and only one record, in tblRA2012? You will have to create a new year table every year? This is not good design. Have another field for year in the child table and call the table tblRAweeks. Then the tables have 1-to-many relationship.

Calculations can be done in queries but not to set the value of a field in table unless you run an UPDATE or INSERT sql action. Instead of saving percent to table it could probably be calculated whenever needed.

You are not figuring weeks as Sun-Sat? 1/1/2012 happened to fall on a Sunday but that won't be the case for every Jan 1.

Given a date, the Format() function can return the week number for a month or year, example:

Format(#6/17/2012#, "w") = 1
Format(#6/17/2012#, "ww") = 25

[Datech]

June, this goes back to my question a few weeks ago about pivot tables and the column values it can automatically display.

Can you describe the tblRAweeks a little more? I'm not sure I understand it. My guess is a field tying it to the ResourceAllocation table, then a year field, then fields for weeks 1-52 with specific dates based on the year chosen? This sounds like the smart implementation, I just can't quite wrap my brain around how to prepare for it. The table per year was pretty straightforward, which is why I went to it first even though it's bad design

As far as the calculations, I need to display this information about the percentages (or decimals, 100% = 1) per week, per month, and per quarter in pivot tables. Since the data would be stored in a table using my initial design, I can write join that pulls in information about the employee, project, and the resource allocation itself to display how we need it in at least 6 different pivot table layouts. I imagine a pivot table is still possible if it is calculated whenever needed, I just don't know enough about SQL to wrap my head around it yet. Either way, can you provide an example of that type of calculation within SQL?

That was just an example, as you said Sunday just so happened to fall on a Jan 1 this year. I was initially thinking I'd have to hardcode all the dates, but your format function may just make that a heck of a lot easier, so thanks for that!

[June7]

Yes, to make the tblRA2012 into a multi-year dataset, need a field for the year then rename the table.

Unless I misunderstand the resource Start and End dates. Will a resource period be limited to a single year or could it start in one year and end 3 years later?

Back to your percent calculation. I took another look at your original post and I don't understand what you are taking a percentage of for each week. The expression shows:
tblRA2012!Week1.value = ResourceAllocation![Percent Utilization].value

What would Week2 equal?