How can I code this logic?

cgttsp01
cgttsp01 used Ask the Experts™
on
I need to code the following logic is a CF page.
If I list each requirement, could someone help with the code?

These are the two DB tables I already have.
MS Access DB table 'A' has the following fields:
INumber
ICodeNumber
Total
Actuals

MS Access DB table 'B' has the following fields:
Employee
Curdate
INumber
ICodeNumber
HoursAssigned
TotalHours

This is what I need the CF code to do in one CF page:

1.  query the 'A' table and get the difference between the 'Total' and 'Actuals' fields for each INumber and call it 'hours'.
2.  List each Employee from 'B' table.
3.  Spread the available 'hours' evenly for each employee, display the hours and display the 'INumber' (write this value to 'B' table, field 'INumber' )and 'ICodeNumber'(write this value to 'B' table, field 'ICodeNumber').
4.  A maximum allocation of 9 hours per employee, per INumber.
(write this value to 'B' table, field 'HoursAssigned')
5.  A maximum of 43 hours per employee total for all INumber hours allocated per employee.
(write this value to 'B' table, field 'TotalHours')

If you have any questions, please let me know.



Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Hi there,

I'd give this a try ... & get back to u asap

K'Rgds
Anand

Commented:
this is what i cld figure out ...

but u need to provide me with a list of INumber so that i can run a loop on the INumber & carry on the code below

<CFLOOP index="i" FROM="1" TO="#ListLen(INUmber)#"><!--- this wld run on diff INUmbers u wld provide thru a list --->

 <cfquery NAME="Query_A"><!--- get the hrs --->
    select (Total-Actuals) as Hours from tableA where INumber = #ListGetAt(INUmber,i)#
 </CFQUERY>
 
 <cfquery NAME="Query_B"><!--- get total users in B --->
    select * from tableB  
 </CFQUERY>
 
 <cfloop QUERY="Query_B"><!--- update all records in tbaleB with matching INUmber & assign equal HoursAssigned --->
   
    <cfquery NAME="Get_Hours">
       Select HoursAssigned from TableB where INUmber = #Query_A.INumber#
    </CFQUERY>
   
    <CFIF INt(Hrs/Query_B.recordcount) GTE 9>
       
        <CFIF Get_Hours.HoursAssigned LTE 34><!--- 9 less than 43 --->
            <cfquery NAME="Update_B">
                update tableB set HoursAssigned = 9,    
                ICodeNumber = #Query_A.ICodeNumber#    
                where INUmber = #Query_A.INumber#
            </CFQUERY>
        </CFIF>
       
    <CFELSE>
   
        <CFIF Get_Hours.HoursAssigned LTE Evaluate(43-INt(Hrs/Query_B.recordcount))><!--- INt(Hrs/Query_B.recordcount) less than 43 --->
            <cfquery NAME="Update_B">
                update tableB set HoursAssigned = #INt(Hrs/Query_B.recordcount)#,    
                ICodeNumber = #Query_A.ICodeNumber#    
                where INUmber = #Query_A.INumber#
            </CFQUERY>
        </CFIF>
       
    </CFIF>
   
 </CFLOOP>
 
</CFLOOP>

let me know

K'Rgds
Anand

Author

Commented:
Anand,

Here is a set of INumbers.
This is not all the numbers.
/********************************/
359845 .6
363622 .1
363622 .2
363622 .3
363622 .4
363622 .6
365123 .0
366197 .0
367277 .1
368129 .0
368344 .0
368500 .1
368540 .1
368820 .3
368839 .3
368844 .1
368946 .1
369209 .0
369209 .9
369233 .1
369255 .31
369292 .10
369292 .2
369429 .7
369584 .1
369627 .1
369737 .1
369836 .8
369982 .1
370594 .1
370624 .15
370669 .3
370734 .0
370898 .3
370907 .1
371103 .1
371302 .2
371302 .2A
371302 .3
371302 .5
371469 .0
371469 .1
371479 .1
371479 .4
371480 .1
371480 .10
371480 .8
371492 .1
371496 .1
371498 .1
371570 .1
371643 .0
371815 .1
371859 .2
371859 .3
371881 .0
371949 .1
/******************************/

Thanks for your help!
Expert Spotlight: Joe Anderson (DatabaseMX)

We’ve posted a new Expert Spotlight!  Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

Nathan Stanford SrSenior Programmer

Commented:
Sounds like you should use a Cross Reference Table to assign them that way you can continue to balance the load.

If your problem has not been solved let me know. I will continue on it.

the above INumbers is the numbers after the Dot the hours? or what?

Author

Commented:
Yes, the numbers after the '.' is the hours available for that INumber.

This is just a subset of the INumbers and Hours in the table.  I included this subset in order to allow anyone working on this an idea of what to use for testing.  I would imagine using 5-10 employess in the test with this subset of INumbers and Hours would work.

I appreciate your work on this!

Commented:
Ok so u do have a set of INUmbers ... gr8 !

now u can just make a minor change in the code i wrote above & repalce the ListGetAt(INumber,i)

with the usuage of ListGetAt(listFirst(INumber,'.'),i) - this will give u the INumber

& for the Hours u cld use ListGetAt(listlast(INumber,'.'),i)

let me know

if u tried this & thigns r working fine !

K'Rgds
Anand

Author

Commented:
Anand,

I am testing your code suggestion and I get this Loop error message:

Error Diagnostic Information
Attribute set validation error in tag CFLOOP

The tag has an invalid attribute combination: the most likely attribute combination is

Required attributes: 'FROM,INDEX,TO'. Optional attributes: 'STEP'.
Other possible combinations are
Required attributes: 'COLLECTION,ITEM'. Optional attributes: None.
Required attributes: 'INDEX,LIST'. Optional attributes: 'DELIMITERS'.
Required attributes: 'QUERY'. Optional attributes: 'ENDROW,STARTROW'.
Required attributes: 'CONDITION'. Optional attributes: None.
The error occurred while processing an element with a general identifier of (CFLOOP), occupying document position (8:1) to (8:68).

Commented:
Hi,

This is just a loop syntax error - as u can see ive used all the 3 required attributes.

+ teh error has come up b/cos it has not found the INumber list ... pls use that along with the code.

also what ive given is a skeleton for u - it will work perfectly when u use ur variables & query values in it.
I hope ur getting waht i am trying to say here.

just make small changes wherever required as per ur requirement & thigns will work fine !

let me know

K'Rgds
Anand

Author

Commented:
I still get the same error.
Can you be more specific, please.

Commented:
ok in that case - i wonder why the error is being popped up - cos all the required parameters r there.

u can even look up the help docuemnttion of CF.

but id like to see ur list of INUmber

pls send it to me

K'Rgds
Anand

Author

Commented:
Anand,

The 'INumbers' i'm using are coming from a query to Access table 'A'

Do you want me to send the Access DB to you?  It is simply a test DB with the fields I listed in my original question above.

The error seems to be a syntax error, or at least it appears that way to me.  So, I change the loop around several ways and still get the error.

Is there a way you can test your code using my sample Access DB if I email it to you?

Commented:
NO just paste the INUmber list u get from the access DB - !

K'Rgds
Anand

Author

Commented:
Anand,

Here are the fields of the three test records I have in the table.  The query would simply pull the current records from this table to use in your code example:

id     INumber     ICodeNumber     Total     Actuals
1     337755.1     PMCD2002     340     210
2     338866.2     PMCD2450     120     75
3     339977.4     PMCD2330     45     25

Author

Commented:
Anand,

Disregard the trailing '.(number)' of the INumber.  This is where I combined the INumber and the Phase number. Just use the first 6 numbers of the INumber.

Commented:
so ur INumber list wld be as follows -

 "337755.1,338866.2,339977.4"

Am i right ???

in that case - i dont see why when u run the loop it shld throw up a error !

if u want - u can mail me the entire file at anand@direct2s.com & i'll have a look at it & get back to u on it

but pls send all the required info ... so that i can just set the thigns right :)

cheers
Anand

Author

Commented:
Anand,

I haven't heard back from you on this question in a while.  

Will you be able to help me out on this one?

Thanks
here is the code.

 <cfquery NAME="Query_AB" datasource="prs_codes" dbtype="ODBC">
select INumber,IcodeNumber,(Total-Actuals) as Hours
From A
</CFQUERY>

<cfloop index="i" from="1" to="#listlen(valuelist(query_ab.INumber))#">

<!--- get total users in B --->
<cfquery NAME="Query_B" datasource="prs_codes" dbtype="ODBC">
select * from b
</CFQUERY>

<!--- update all records in table C with matching INUmber & assign equal HoursAssigned --->
<cfloop QUERY="Query_B">

<cfquery NAME="Get_Hours" datasource="prs_codes" dbtype="ODBC">
Select sum(Hours) as thours
from c
where inumber ='inumber'
</CFQUERY>

<CFIF INt(listgetat(valuelist(Query_AB.Hours),i) /Query_B.recordcount) GTE 9>


<CFIF Get_Hours.tHours LTE 43>
<cfquery NAME="Update_B" datasource="prs_codes" dbtype="ODBC">
insert into c (Hours,INumber,ICodeNumber,emp)
values ('9','#listgetat(valuelist(Query_A.INumber),i)#','#listgetat(valuelist(Query_A.ICodeNumber),i)#','#Query_b.employee#')
</CFQUERY>
</CFIF>

<CFELSE>

<!--- INt(Hrs/Query_B.recordcount) less than 43 --->
<CFIF  Get_Hours.tHours LTE 43>
<cfquery NAME="Update_c" datasource="prs_codes" dbtype="ODBC">
insert into c (Hours,INumber,ICodeNumber,curdate,emp)
values ('#INt(listgetat(valuelist(Query_AB.hours),i)/Query_B.recordcount)#',
'#listgetat(valuelist(Query_AB.INumber),i)#','#listgetat(valuelist(Query_AB.IcodeNumber),i)#',#Now()#,'#Query_b.employee#')
</CFQUERY>
</CFIF>
</CFIF>
</CFLOOP>
</CFLOOP>

Author

Commented:
Great job, thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial