Report Source 1

[sync123]

Hi,

My database consists of a fin_Account table that contains the following fields

-account which is of the format
###-##-###-######-###-#-######
-account description

-month_x_actual which refers to the balance for month x
-month_x_prev whic refers to the balance for month x in the prev yr

-month_x_budget which refers to the budget for month x

(The table consists of month actual,month_prev and month budget for all 12 months. So the table has fields month_1_actual,month_2_actual,month_3_actual
month_1_budget,month_2_budget,month_3_budget etc)

Now the report I want to generate is based on an account that starts with
001-21-712-813000-207-1-* .

I have a form where the user choses a particular month for which they want the report. Now depending on the month that the user choses I would need to select the corresponding column in my report.

For example if the user choses january as the month then my sql for the report record source should look something like this

select month_1_actual as month_actual
month_1_budget as month_budget
month_1_prev as month_prev
from fin_Account

So I need to set the record souce of the report based on the month that the user enters.
Currently when the user clicks the 'generate' button on the form I open the report. In the report OnLoad event I have entered the following code.

On_Load()
Dim theMonth As String
Dim fiscalMonth As String
Dim db As Database

'theMonth stores the month the user chose
theMonth = Forms!activity_level_Summary_rpts!lstPeriod

Dim strSql As String
Set db = CurrentDb()
On Error Resume Next
db.QueryDefs.Delete ("rehab month&quot

'get the numeric fiscal equivalent of the chosen month
fiscalMonth = getPeriod(theMonth)

'create the select statemnet based on the month
strSql = "Select MONTH_" & fiscalMonth & "_PREV_YEAR AS MONTH_PREV, MONTH_" & fiscalMonth & "_CUR_YEAR AS MONTH_ACTUAL,MONTH_" & fiscalMonth & "_BUDGET AS MONTH_PLAN,ACCOUNT FROM FINANCE_INF_ACCOUNT WHERE ACCOUNT LIKE '001-21-712-813000-207-1-*'"

db.CreateQueryDef "rehab_month", strSql
Me.RecordSource = "rehab_month"
Set db = Nothing

End If


I am not sure if I am doing this correcty. Is it possible to change the record source property in the onload event of the report or should I change it somewhere else.


My report should look something like this
---------------------------------
Month:April 2003

actual_last_yr actual this_yr Budget

Admission (select cols for account 001-21-712-813-207- 1-4011240)

Patient days (select cols for account 001-21-712-813-207- 1-4031240)

Adult_Child (select cols for account 001-21-712-813-207-1-4000000 and 001-21-712-813-207-1-4000100)


So I want to make controls for each row where I basically just select the appropriate column from the rehab_query that is my report source.


So the admissions for the prev_year column would have something like this in the control box

select month_prev from rehab_month where account='001-21-712-813-207-1-4011240'

I have tried doing this but its not workign correctly I think it has to do with the report source of my report.
Any help would greatly be appreciated.
Thanks.
sync123

 

[dhookom]

You are suffering from a severe case of "commiting spreadsheet". If I were to create a table to store this information it would have Account, Mth, Yr, Amt, BudOrAct.

If changing your structure isn't an option, you could try choose the proper columns in your query with a no code solution. In your query, create columns like...

Month_Actual:Choose(Forms!activity_level_Summary_rpts!lstPeriod, month_1_actual,month_2_actual,...etc, month_12_actual)

Month_Budget:Choose(Forms!activity_level_Summary_rpts!lstPeriod, month_1_Budget,month_2_Budget,...etc, month_12_Budget)



Duane
MS Access MVP

 

[sync123]

Hi,

I haven't created the table structure. It was created a long time ago by someone and now its just too much work to fix it.
Unfortunatley changing the table structure is not an option and I have to live with it.

I want to chose the correct cols according to the month the user choses. So if the user choses febuary then I want to select month_2_actual,month_2_prev,month_2_budget.
Can you explain you solution further .

Thanks.Appreciate your quick reply!

sync123

 

[dhookom]

Did you try the solution I suggested? All you have to do is create the calculated columns in your query as suggested.
Month_Actual:Choose(Forms!activity_level_Summary_rpts!lstPeriod, month_1_actual,month_2_actual,...etc, month_12_actual)

Month_Budget:Choose(Forms!activity_level_Summary_rpts!lstPeriod, month_1_Budget,month_2_Budget,...etc, month_12_Budget)


Duane
MS Access MVP

 

[sync123]

Hi,

I am not really sure I understand your solution. Wouldn't this chose all 12 months when all I want is a particular month.
Where would I be doing this?

sync123

 

[dhookom]

Check out the Choose() function in Help (from a module). The first argument is an integer (same as your month selection on the form). The remaining arguments are "offsets" based on the first argument. For instance:
Choose(3,[Field1],[Field2],[Field3],[Field4]) = [Field3]
or
Choose(2,"Red","Green","Yellow","Blue&quot = "Green"

Whatever month number you select in lstPeriod will be used to return the appropriate field.

Duane
MS Access MVP

 

[sync123]

Thanks. It makes sense! Wasn't aware of this chose function.
I have another question ,some of my controls would need a value based on one particular account from my account table .

How could I set up the control source so it select the appropriate month using the chose finction for each column <b> where the account =&quot;......&quot; </b>
So I want to be able to use chose() with a criteria


Sync123

 

[dhookom]

I would need you to show a concrete example of what you want rather than a hypothetical. Kinda like
If my Account=&quot;Blue&quot; then I don't want to show month x but do want to show month y.

Duane
MS Access MVP

 

[sync123]

This is something I would like to do :

My report would look something like this
---------------------------------
Month:April 2003


actual_last_yr actual this_yr Budget


Inpatient Srvs:

Admissions - (select cols where account=' 001-21-712-813-207-1-4011240')

Patient days- (select cols where account=' 001-21-712-813-207-1-4031240')

Adult_Child- (select cols where account=' 001-21-712-813-207-1-4000000' or account ='001-21-712-813-207-1-4000100')


So I need to select the appropriate col according to the month chosen and I need information about some particular accounts.

-I guess I won't be able to do this without using some code.

Sync123

 

[dhookom]

I thought you columns were repeating months. If the report is April 2003 then these are selected from the query.

Are you suggesting that based on Account, you want to select different month columns from your original table? Your query could include the &quot;aliased&quot; columns as well as the original columns.

Duane
MS Access MVP

 

[sync123]

I'll try an explain my question again. I guess I wasn't very clear.

I want to generate a financial report. The user choses the month for which they want the financial report. My report will be based on a query called selCols. So selcols is my report source.
Due to my table structure I need to select appropriate cols according to the month the user choses

eg.User picks January

selCols qry looks like:
select month_1_actual as month_actual ,month_1_prev as month_prev,month_1_budget as month_budget,account from fin_account

-User picks febuary
selCols qry looks like: select month_2_actual as month_actual,month_2_prev as month_prev,month_2_budget as month_budget,account from fin_account

-The report source would be based on this select query.

Then for my control source for the cell (dmissions row intersects the month actual column) I would say

select month_actual from selCols where account='blah blah'

-So my question is that how can I correctly create the selCols qry (my report source) so that it has the correct sql statment based on the month picked.

I can create the correct sql string using vba (code). The problem I am having is that I am not sure how to assign this qry as the report source and where exactly I should be doing this.
Is it in the onload event of the report?If you can provide me with an example or any help on this that would be great.

Thanks for your patience
sync123

 

[dhookom]

My first response should provide your solution. I assume you have a control on a form that allows the user to select a month number. Use this value in a Choose() function in a query to assign the appropriate field to an alias. Base the report on this query. No code required. Do you understand how the Choose() function works?

Duane
MS Access MVP

 

[sync123]

Thanks duane for all your help. I created the query using the choose function and it works great.I have a small question for you though.

My report source is called qryActivityRehab.The Fields in my query are
account,Month_Prev,Month_Actual,Month_Budget

Now for one part of my report I want to get information about one particular account

actual_last_yr actual this_yr Budget

Admissions - x


x:
To set the control source for x I have entered this as the control source by right clicking the control and going to the data tab).

=(SELECT [Month_Prev] from qryActivity_Rehab where [account]='001-21-712-813000-207-1-4011240')

When I run my report it gives me Name?.
Now I know that usually the name error pops up when their is spelling mistake but i double checked my field names and I also created this query in my database ,ran it and it worked.
I think its somethign wrong with my syntax.Is this the correct way of setting a query as a control source.
I am doing this in the report footer -could that be the problem?

Thanks
sync123

 

[dhookom]

You can't use a query as a control source. DLookup() provides some of this functionality.

Duane
MS Access MVP