Select Unique Record in Table Based on Paramter Value

[southbean]

Hello All,

I have an Access table – tbl_BgtYrHrs – that has three fields and values like:

client_name	budget_yr	total_hrs
AcmeCo		 2006		 500
GenCo		  2006		 200
DataCom		2006		 300
AcmeCo		 2007		 600

I have a report where users can choose a budget year as a parameter value. I want to use the value of the ‘total_hrs’ field in a formula for use in other calculations.

I’m trying to write a formula that’s placed in group and report footers like:
If {tbl_BgtYrHrs.budget_yr} = Year(CurrentDate) Then "Grand Total (" & CStr({tbl_BgtYrHrs.total_hrs },0) &"): "

However, I’m not getting the correct values for clients that have two records in the table like ‘AcmeCo’.

Can anyone tell me how to get this right?

Any/all help would be greatly appreciated!

Thanks,

- tm

 

[HardJeans]

I'm guessing you want to add the hours if the client has 2 records.

If it's grouped by client name then replace the formula as follows:

If {tbl_BgtYrHrs.budget_yr} = Year(CurrentDate) Then "Grand Total (" & CStr(sum({tbl_BgtYrHrs.total_hrs },{tbl_BgtYrHrs.client_name}),0) &"): "

--
"I'm not talking to myself, I'm just the only one who's listening." - JCS

 

[southbean]

Hey HardJeans,

Thank you for your reply!

That's along the lines of what I'm looking for. However, I don't want to add/sum the hours for multiple years. I just want the formula to grab the correct hours for the current year.

The formula I originally posted grabs the correct year but not the correct hours - or the hours are blank/null.

Would be grateful for any other tips or ideas!!!

Thanks again,

-tm

 

[HardJeans]

If you don't want to show other years, use the select expert to select just the years in the parameter. If you want to show multiple years, group it by the year, and put the formulas in that group instead. This way it will show:
AcmeCo
2006 - 500
2007 - 600
GenCo
2006- 200

and so forth.

If this isn't what you're looking for, please tell me what your end result should look like (like I did above), and what your report is currently grouped by.

--
"I'm not talking to myself, I'm just the only one who's listening." - JCS

 

[synapsevampire]

You say that you have a group, but not what the group is, please take the time to accurately describe requirements, your posts are skimpy and vague.

If you go to the Report->Selection formula->record and place something like:

{tbl_BgtYrHrs.budget_yr} = Year(CurrentDate)

Then you have this years data, but since you say that you're using a parameter, it should be:

{tbl_BgtYrHrs.budget_yr} = Year({?MyParameter})

Now the data returned to the report is limited to that year, so you don't need to qualify any aggregates.

-k