can weekly and monthly data be linked

[werafa]

Hi,

I'm getting a bad case of splinters....

I have a table with weekly user data that includes StaffID, WeekEndingDate and Sales.
I have a second table with monthly TargetSales, StaffID and MonthEndDate.

I have to apportion TargetSales by week.
Can I do this via a query (and if so, how), or is this a job for VBA?

Thanks
Werafa

[OBP]

Most of this kind of work can be done with queries, but I am not sure what you need.
However having monthly sales, but allocating weekly sales seems a bit odd.
How do you do this, divide the monthly sales by 4?

[werafa]

I've calculated the number of days in each week - this must be apportioned across month boundaries too :banghead:

So if I can find a way to call the record, get the record month value, find the month value on the monthly table and return the TargetSales value, I can divide by NumDaysInMonth and multiply by NumDaysInWeek

[werafa]

hmmm, I just learned that this is called rubberducking. Stating this has given the thought that this should be doable in SQL.
and 'guday' again from the antipodes

[OBP]

Yes Access queries are SQL and you can view them in SQL mode if you are more comfortable working that way.
You can connect tables in Access Queries, it is what they are for and do what you want.
Let me know if you need any assistance, a simplified database in Access 2007 format with no personal data would obviously help.

[werafa]

Any Storm in a Port - advice would be welcomed.
not sure how to attach files - but the critical part of the table structure is fairly simple.

the way I enter the data, I may have several pre-existing records to which I must apportion the sales target data - so I'll need to select a recordset for the relevant month and calculate for each record. this means a vba routine attached to a textbox update event.

I'm guessing the SQL will look something like
SELECT dDate, SalesTarget from WeeklyKPIs where StaffID = mystaffID and mMonth = myMonth

send this to a recordset, and get the single row recordset for monthlyKPIs based on month and staffID
DaysInMonth and DaysInWeek can be derived from dDate fairly easily - so 'hunky dory' is a word that hopefully describes the future

Tables are:

SalesStaff
-------------------------
ID | Integer | Primary Key
StaffName | String
etc.

WeeklyKPIs
--------------
ID | Integer | Primary Key
StaffID | Integer | Foreign Key (Sales Staff | One to Many)
dDate | Date (Weekly - date is a Sunday or an end of month)
mMonth | Integer | Calculate from dDate
Sales | Currency
wSalesTarget | Currency (the new field)
etc.

MonthlyKPIs
----------------------
ID | Integer | Primary Key
StaffID | Integer | Foreign Key (SalesStaff | One to Many)
mMonth | Date (is end of month)
TargetSales | Currency (is monthly Target)

[OBP]

You use the "Go Advanced" and then "Manage Attachments", but Access usually has to be zipped.

[werafa]

thanks. I'll have a crack at the code as I've described it, and post a file If I get stuck again

[werafa]

I'll attach this to the monthly target textbox update event and all should be good
(sleep was better than coffee for once....)



'Convert monthly to weekly targets


Public Function ApportionTargets()
'apportion monthly target to weekly kpis table
Dim myDB As DAO.Database
Dim myRS As DAO.Recordset
Dim myForm As Form
Dim mySQL As String
Dim myName As String
Dim myTarget As Currency
Dim myID As Long
Dim myDate As Date, rsDate As Date
Dim monthDays As Long
Dim myCalcs As Double
Dim myDays As Long




Set myDB = CurrentDb
Set myForm = Forms![KPI_Management]

If IsNull(myForm.tbxTarget.Value) Then GoTo NoTarget

'get data from form
myTarget = myForm.tbxTarget.Value
myName = myForm.cName.Value
myDate = myForm.DefaultWEDate.Value
monthDays = Day(DateSerial(Year(myDate), Month(myDate) + 1, 1) - 1)

'get Record ID
mySQL = "SELECT StaffID FROM tblSalesStaff WHERE cName = '" & myName & "'"
Set myRS = myDB.OpenRecordset(mySQL, dbOpenSnapshot)
myID = myRS!StaffID

'get weeklyKPIs recordset
DoCmd.Close acTable, "WeeklyKPIs", acSaveYes 'force table close
mySQL = "SELECT * FROM WeeklyKPIs WHERE StaffName = " & myID & " AND Month(dDate) = " & Month(myDate) & ";"
Set myRS = myDB.OpenRecordset(mySQL, dbOpenDynaset)

'loop through myRS & write apportioned targets
If Not (myRS.BOF And myRS.EOF) Then 'if recorset has records
With myRS
.MoveFirst
Do Until .EOF
rsDate = myRS!dDate.Value
myDays = CalcDaysInWeek(rsDate)
myCalcs = myTarget / monthDays * myDays
.Edit
myRS!Budget = myCalcs
.Update
.MoveNext
Loop
End With
End If

Exit Function
NoTarget:

End Function


Private Function CalcDaysInWeek(ByVal myDate As Date) As Long
'get dDate and calc numDaysInWeek
'where week cannot split across month
'and day 1 = monday
Dim dateFrom As Date
Dim numDays As Long


dateFrom = myDate
Do
dateFrom = dateFrom - 1
If Day(dateFrom) = 1 Then Exit Do
Loop Until Weekday(dateFrom) = 2 'roll date back until Monday or first day of month


numDays = myDate - dateFrom + 1


CalcDaysInWeek = numDays
End Function