Budget and Collections(Actuals)

[dref]

Hullo,
I have a database attached.
my problem is connecting budget to Collections (QryCollections_Monthly with Qrybudget_Monthly amounts)
for example Kyalimpa Rose is in Supplements section(SecID) but has targets(Budget) for KFM & Supplements. I want to pick monthly budget figure for KFM and match it with Collections for KFM and Suuplements with supplemets in the frmCollections_Monthly(Form) or in a query.
See the details in the attached file.
Thanks for your help.
Rgds
Dref

[jzwp11]

I don't understand your table structure. Why do you have two fields for the section in both tblBudgets and tblCollections? Can you explain in words, what your database is intended to do and what your relationships are?

Can a staff member have many sections that they are responsible for?


Also, although Access has the capability of having lookups (combo and list boxes) at the table level, it is generally not a good idea as described here
The combo and list boxes are best left for forms.

[dref]

Hi jzwp11,
It is intended to compile monthly collection figures per person and match with corresponding monthly budget figures. And maybe get the performance variance.
However, one person has two or three different categories of targets(budget).

A pesron is responsible for different sections but belongs to the Home Section. SecID is the Home Section(Main),Sec(Is the section according to budget). E.g Bahwayo Elizabeth the first person in the tblBudget is in Display(Home Section) but she has a budget for KFM and Supplements. Kyarimpa Rose is in supplements and has a budget for Supplements and KFM. In the tblCollections table, all performance is combined for all sections for the same person.

Yes i used Combos in tables but since the database is attached please alter to suit.

thanks
Fred"

[jzwp11]

I made the corrections to the tables, and started to look at your queries. I don't understand your aggregate queries specifically a section of the GROUP BY clause as shown in red below. Could you explain?

SELECT DISTINCTROW tblCollections.StaffID, tblCollections.SecID, tblCollections.Sec AS Expr1, Format$(tblCollections.RctDate,'mmmm yyyy') AS [Month], Sum(tblCollections.RctAmount) AS RctAmount
FROM tblCollections
GROUP BY tblCollections.StaffID, tblCollections.SecID, tblCollections.Sec, Format$(tblCollections.RctDate,'mmmm yyyy'), Year(tblCollections.RctDate)*12+DatePart('m',tblCollec tions.RctDate)-1;


SELECT DISTINCTROW Format$(tblBudget.bgtDate,'mmmm yyyy') AS [Month], tblBudget.StaffID, tblBudget.SecID, tblBudget.Sec AS Sec, Sum(tblBudget.bgtAmount) AS bgtAmount
FROM tblBudget
GROUP BY Format$(tblBudget.bgtDate,'mmmm yyyy'), tblBudget.StaffID, tblBudget.SecID, tblBudget.Sec, Year(tblBudget.bgtDate)*12+DatePart('m',tblBudget. bgtDate)-1;

[dref]

Hi jzwp11,
You could just ignore my queries. what i wanted to do is query the information from both tables into months such that i get monthly comparisons of budget against Collections. i used the query wizard to query into months.

You can create your own query as long as it gives us what we want.
Thanks
Fred

[dref]

I tried using this but it did not work:

Private Sub StaffID_AfterUpdate()
Me.bgtAmount = DLookup("[bgtAmount]", "QryBudget_Monthly", "[StaffID]=" & Me.StaffID & "')AND([Sec]=" & Me.[Sec] & ")AND([Month] = " & Me.[Month] & "")

End Sub

[jzwp11]

Assuming that you want to compare budget and collection totals by section within staff member within period, then I would first create a totals query for the budget amounts.

query name: qryBudgetSummaryByPeriodStaffSection

SELECT Month(tblBudget.bgtDate) & "/" & Year(tblBudget.bgtDate) AS Period, tblBudget.StaffID, tblBudget.SecID, Sum(tblBudget.bgtAmount) AS SumOfbgtAmount
FROM tblBudget
GROUP BY Month(tblBudget.bgtDate) & "/" & Year(tblBudget.bgtDate), tblBudget.StaffID, tblBudget.SecID;

Then create a similar query for the collections:

query name: qryCollectionSummaryByPeriodStaffSection

SELECT Month(tblCollections.RctDate) & "/" & Year(tblCollections.RctDate) AS Period, tblCollections.StaffID, tblCollections.SecID, Sum(tblCollections.RctAmount) AS SumOfRctAmount
FROM tblCollections
GROUP BY Month(tblCollections.RctDate) & "/" & Year(tblCollections.RctDate), tblCollections.StaffID, tblCollections.SecID;


Now create a another query that uses the two queries above. Assuming that all staff members/sections have budgets then you would do a left join between the qryBudgetSummaryByPeriodStaffSection and the qryCollectionSummaryByPeriodStaffSection. You will need three left joins: period, staffID and SecID

We'll also have to take care if there have been no collections for a certain person/section in a period, so I added and IIF() function in the query as follows:

query name: qryBase

SELECT qryBudgetSummaryByPeriodStaffSection.Period, qryBudgetSummaryByPeriodStaffSection.StaffID, qryBudgetSummaryByPeriodStaffSection.SecID, qryBudgetSummaryByPeriodStaffSection.SumOfbgtAmoun t, IIf(IsNull([SumOfRctAmount]),0,[SumOfRctAmount]) AS SumOfCollections
FROM qryBudgetSummaryByPeriodStaffSection LEFT JOIN qryCollectionSummaryByPeriodStaffSection ON (qryBudgetSummaryByPeriodStaffSection.SecID = qryCollectionSummaryByPeriodStaffSection.SecID) AND (qryBudgetSummaryByPeriodStaffSection.StaffID = qryCollectionSummaryByPeriodStaffSection.StaffID) AND (qryBudgetSummaryByPeriodStaffSection.Period = qryCollectionSummaryByPeriodStaffSection.Period);


Now, we can create a final query that joins qryBase back to get section and staff tables to get the names for the person and the section (rather than just the ID fields)

query name: qryFinal

SELECT qryBase.Period, tblStaff.stfName, tblSections.Sec, qryBase.SumOfbgtAmount, qryBase.SumOfCollections
FROM (qryBase INNER JOIN tblSections ON qryBase.SecID = tblSections.SecID) INNER JOIN tblStaff ON qryBase.StaffID = tblStaff.StaffID;


The DB is attached.

[dref]

Thanks jzwp11. This should work.

[jzwp11]

You're welcome. Good luck with your project.

[dref]

Hi jzwp11,
If i use qryBase or i cant get quarterly or annual reports.
Your help is highly appreciated.
Thanks
Fred

[jzwp11]

Why wouldn't you be able to get quarterly and yearly summaries?

You can actually use qryFinal and create a new aggregate query. You will have to play around with the period field and some of Access' built-in functions in order to get the summary you need.

A yearly summary query would look something like this:

SELECT clng(Right(qryFinal.Period,4)) AS SummaryYear, qryFinal.stfName, qryFinal.Sec, Sum(qryFinal.SumOfbgtAmount) AS SumOfSumOfbgtAmount, Sum(qryFinal.SumOfCollections) AS SumOfSumOfCollections
FROM qryFinal
GROUP BY clng(Right(qryFinal.Period,4)), qryFinal.stfName, qryFinal.Sec;

A quarterly summary may look like this:

SELECT "Qtr" & DatePart("q",DateSerial(CLng(Right(qryFinal.Period ,4)),CLng(Left(qryfinal.period,Len(qryFinal.period )-5)),1)) & "/" & Right(qryFinal.Period,4) AS QTRYEAR, qryFinal.stfName, qryFinal.Sec, Sum(qryFinal.SumOfbgtAmount) AS SumOfSumOfbgtAmount, Sum(qryFinal.SumOfCollections) AS SumOfSumOfCollections
FROM qryFinal
GROUP BY "Qtr" & DatePart("q",DateSerial(CLng(Right(qryFinal.Period ,4)),CLng(Left(qryfinal.period,Len(qryFinal.period )-5)),1)) & "/" & Right(qryFinal.Period,4), qryFinal.stfName, qryFinal.Sec;

I've added the 2 queries to the database I posted earlier; it is attached.

[dref]

Thanks jzwp11.
Am now ,still in the same DB creating a form where all the data collects.
See the attachment.
am trying these codes to sort data dislayed in vain:

Code 1-

Private Sub cboStaff_Change()
Dim strSQL As String
Dim strstfName As String
Dim strMonth As Variant
Dim strSec As String
Dim strtxtSalesAct_Supp As Integer
strSec = Me.Sec
strMonth = Me.txtMonth
strstfName = Me.cboStaff
txtSalesAct_Supp = Me.txtSalesAct_Supp.Value

If Not IsNull(cboStaff) Then
strSQL = "SELECT IIf(IsNull([InvoiceAmount]),0,[InvoiceAmount])" & _
"FROM frmqry" & _
"WHERE frmqry.[Period]='"& strMonth &"'& _
"AND frmqry.[stfName]='"& strstfName &"'& _
"AND frmqry.[Sec] ='Supplements';"
txtSalesAct_Supp = strSQL
DoCmd.RunSQL "strSQL"


End If
End Sub

Code 2- so close
Private Sub cboStaff_Change()
Dim strstfName As String
Dim strMonth As Variant
Dim strSec As String
Dim strtxtSalesAct_Supp As Integer
Dim strStaffID As Integer

strSec = Forms![wiz2]![Sec]
strMonth = Forms![wiz2]![txtMonth]
strstfName = Forms![wiz2]![cboStaff]
strStaffID = Forms![wiz2]![StaffID]
txtSalesAct_Supp = Forms![wiz2]![txtSalesAct_Supp]
If (Me.StaffID) <> 0 Then
txtSalesAct_Supp = DLookup("[InvoiceAmount]", "frmqry", "[StaffID]='" & Forms![wiz2]![StaffID] & "'AND [txtmonth]='" & Forms![wiz2]![txtMonth] & "'AND [Sec]= 'Supplements' '")
End If
NB:

KFm is radio.
i have deleted all other queries & forms to reduce on weight.
am picking data from a query-frmqry that picks form tblcollections.
Look at the form and the Vb codes and advise
Thanks
i

[jzwp11]

I could not get your form to show any data in the DB you provided. I created a new form (frmCollections) that I believe does what you intended. The revised DB is attached. I am not sure what you are trying to show in the footer of the form. Could you please explain?

[dref]

Thanks my friend,
can we pick monthly budgets (eg from qryFinal) or any where else and insert them in the unbound text boxes i hav added in the footer. Also the collection totals of every section when our new form displays.

That is, when it displays details in the sub from(frmcollectiondetails),it gives us totals in the footer of the form and pick corresponding budget figures from qryFinal or any other source.
Thanks alot
Fred

[jzwp11]

Since qryFinal is based on the person and period, you can just add an additional subform to frmCollections that uses a filtered version of qryFinal (see qryFinal2). When you use the combo boxes in the header of the form, it filters both subforms. The revised DB is attached.