Access 101 ? 1

[rdjohnso]

Still very new to this VB Module coding thing.... need point in the right dir.

I have a report with a need to populate a grid of information using 2 other modules I've written....
Goal: I need to cumlitivly get totals per month of persons hours and cost grouped by organization....

I have a table: tbl_Person with every employee and has their Organization
stored in there as a string....(may not be best normalized but have reasons why)..

I have a module that will get a single person's hours for a given month
and another module that willt ake those hours and get their cost for
same month.

I figure if I get a DISTINCT list of Organizations and then loop that around the Person table ... calling the Hours and Rate modules... I can get total hours and cost per month groupe by Organization...

Here is the code I started..... not even sure how to create or add to an array in Access... any help would be appreciated!!!

--------------------------------------------------------------------
Option Explicit

'Get Total running Hours for currentHours and nextHours
Public mPersonID As Integer

'Get Array of Person ID's grouped by Organization'
Public Function ResourceForecast() As Integer

Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rsResourceData As ADODB.Recordset
Dim sConnString As String
Dim iCtr As Integer
Dim iOrg As String

sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentProject.Path & "\Blue&White.mdb;"

conn.Open sConnString
Set cmd.ActiveConnection = conn

cmd.CommandText = "SELECT DISTINCT Organization FROM tbl_Person"
cmd.CommandType = adCmdText
Set rsResourceData = cmd.Execute

'Outside Loop - get Distinct list of Orgaization's
Do While Not rsResourceData.EOF
For iCtr = 0 To rsResourceData.Fields.Count - 1
'OutPuts Name and Value of each field
Debug.Print rsResourceData.Fields(iCtr).Name & ": " & _
rsResourceData.Fields(iCtr).Value

'** CREATE An Array called iOrg
iOrg = rsResourceData!Organization

Next


'Inside Loop - get/add to person hours and cost modular variable

'** Use this new array to loop over persons while calling the other 2 functions 1) PersonHrs(PersonID, StartDate, EndDate) and 2) PersonCost(PersonID, PersonHrs, StartDate, EndDate) to get my data


Loop

End Function

 

[MajP]

Why are you coding? Can't this be done in a simple query? Explain your tables and relevant fields.

 

[stix4t2]

Can this be done in a query? Person, Org, Hrs, Hrs * Rate?

 

[rdjohnso]

I wish it was that easy... however.. I have to run a loop over 2 other functions to get the correct hours and cost for an individual and then evenetually get the complete total of all those people broken down by group....

so for instance..... if I were trying to fill Jan 06 cell for group 'ITS' I would need to run my functions
PersonHrs() and then PersonCost() against anyone with 'ITS'
to ultimately get the total cost of ITS for month of Jan 06.

Does this make sense?

 

[MajP]

I would thinks so. Explain your relevant tables and relationships. Include primary and foriegn keys. Something like

tblPersons
autoPersonID (Primary Key)
strOrganization (organization Name)

tblRates
Field (Primary Key)
Field (foriegn key)
Field

 

[rdjohnso]

ok MajP.... would be happy to send the dB if easier...

tbl_Person
PersonID (PK - Auto)
FName (Text)
LName (Text).....
Organization (Text) ..values like: ITS, GWHIS, Idea

tbl_PersonRate
RateID (PK - Auto)
PersonID (FK)
Rate (Number)
RateStartDate (Date/Time)
RateEndDate (Date/Time)

What makes this issue very complex is that I HAVE to run it against these other 2 functions because it pulls the proper hours based upon a)if the person is allocated hours for that month based upon their AllocStartDate and AllocEndDate
and run it against DeltaDays() to determine the correct # of working days for that month. Its alot more complex....

Would sincerly appreciate some helpbut would require some effort from someone to better understand the task before helping me solve it....

 

[MajP]

I believe it is complex, but there are a few guys on this site who can do some incredible SQL. I do not include myself as one of them. I can hang when it comes to code, but they amaze me with their SQL expertise. . You will see their names on the right under MVPs. PHV, Skip, Zameer,Roy, Remou, and more. If you can do it through SQL you are almost always better or then writing looping code
I can probably write the code to do this, but your better off defining your problem clearly then telling us to fix your code as written. A lot of these guys will come up with an approach that is much simpler, and you may have not thought of it. I often fall back to coding, and someone will show me a query that does it quite simply.
At first glance what you want to do does not look overly complex, but I will probably have to ask for a few more details/questions after looking at it.

 

[stix4t2]

You can put function calls into your queries if they are public and in a module. So if you are using a start and end date as parameters, you can pass those parameters to the function deltaDays() as a column of the query to return the number of working days.

 

[MajP]

Your functions reference these variables

StartDate, EndDate, PersonHrs

Where do these come from? Are these fields in a table or calculated. If calculated what are they calculated on?

Also, I suggest that you do not create arrays for this, but if you have to loop you may want to write to a table. This allows you to use the power of access to your advantage.

 

[MajP]

Can this be done? Build the following query

qryNeededData
person ID
organization ID
intPersonHours: personHours(personID, startDate,endDate)
intPersonCost: PersonCost(PersonID, intPersonHrs,
StartDate,EndDate)

Now just aggregate the data on Organization.

 

[rdjohnso]

hey Majp... (and any other willing suitors)

can I email you my Db so you can see what I am doing
and then be able to give be a better direction?

LMK, Thanks

Ron

 

[MajP]

I would not want to post my email, because then every time I give bad advice people would flood me with hate mail. Is there an anonymous FTP site you know that you can hang it on? How big is it?

 

[rdjohnso]

if willing, I will post the Db on my server and provide a direct link to the file...

LMK

Ron

 

[MajP]

I look. Hope I can help.

 

[rdjohnso]

thanks Majp.. hope u can too....

http://207.195.226.110/lnrconsultingCom/htdocs/misc/BlueWhite.mdb

The Module I am working on is called:
mResourceInfo

In it, I need to call the other functions in the other modules... mPersonHrs & mPersonCost...

Thx for willingness to even look..

Ron

 

[MajP]

Not sure if this is what you want. This is the sum of Cost, and sum of Hours, for the period 1 Jan - 1 Feb:

Month of January
Organization SumOfPersonHrs SumOfPersonCost
Da 870 $108,750.00
GW 9112 $1,051,000.00
GW 12030 $1,459,750.00
Id 931 $116,375.00
IT 653 $80,750.00
Other 1356 $169,500.00
Trio 3184 $296,500.00

My assumption is that start date and end date is the reporting date. In this case 1/1/2006, and 2/1/2006

Does this make sense, or am I missing the point.

 

[rdjohnso]

did you run it against those functions or is this just a sumnation of total hours grouped by organization?

 

[MajP]

ran against those functions.

 

[MajP]

If that makes sense. This is what I did. I made two queries.
In the first query I hard coded start and stop dates for test purposes, but it would be easy to include as many periods that you want.

qryCost_Hours
SELECT tbl_Person.Organization, #1/1/2006# AS dtmStart, #2/1/2006# AS dtmEnd, personHrs([PersonID],[dtmStart],[dtmEnd]) AS PersonHrs, personcost([PersonID],[PersonHrs],[dtmStart],[dtmEnd]) AS PersonCost
FROM tbl_Person
ORDER BY tbl_Person.Organization;

Then I aggregated the data

qrySumCost_Hours
SELECT qryCost_Hours.Organization, Sum(qryCost_Hours.PersonHrs) AS SumOfPersonHrs, Sum(qryCost_Hours.PersonCost) AS SumOfPersonCost
FROM qryCost_Hours
GROUP BY qryCost_Hours.Organization;

 

[MajP]

How to do this for an entire year

refTblPeriods
strMonth

qryMonthPeriods
strMonth
dtmMonthStart (calculate this using a function and strMonth)
dtmMonthEnd (same as above)

now do a cartesian join with tblPerson

You will get a monthPeriod record associated with every person.

The rest is the same except you group on month and person.