Get Values for a Range Update - Access Developers


	Back to Access Developers Comments List

Get Values for a Range Update

Kevin Carmichael

4 years ago

Hi all, I have a problem with a database i am working on at the moment, the problem is,
This is a Feedlot Database, I have one table that has fields for PenID, RationMixID, FeederID and an AMkg and PMkg for the amount of feed fed in kilograms to that Pen one in the morning and the one in the afternoon.
there might be more than one feeder which can be of a different carry weight eg: A=9000 kgs, B=10000, C=12000, which can be associated to certain pens, but maybe not in an ascending order, here is the problem, I need to work out the pens which are to be fed by that feeder, but each load cannot be more than the feeder can carry, the feeder might have 10 pens to feed which in total adds up to 35000 to 42000 kgs, the kgs fed is worked out by how many cattle are in the pen eg Pen 1 has 2500 kgs, pen 2 1050 kgs, pen 3 3150 pen 4 850, I have tried with Dsum greater than and less than but never works out, so there is the problem, if anyone can help I can send a cut down version to see what I mean, thank you hear soon.
Cheers,
Kevin Carmichael

	Scott Axton @Reply 4 years ago
Man I thought dealing with birds was a challenge... That must be fun dealing with all the pp! I'd love to see your operation. Have you completed the Developer 19** lessons? That may give you ideas. Basically were talking one huge recipe. What is the question(s) you are trying to answer? There is a lot of data input but what is the result you are looking for?

Kevin Carmichael OP @Reply

4 years ago

Hi Scott,
Thank you for your reply I have just noticed it, I have had a look at Dev 19, will run thru it again.
The result I am after is to get the load that a feed wagon can feed a pen of cattle, but each load CANNOT be more than what the wagon can carry, the kgs for each pen is entered by the feed lot owner, for eg 4500 kgs, next pen might be 3250 and so on, now, they might have 2 or 3 feed wagons that might be 9000 kgs capacity or 10000 or 14000 or 15000 or 20000 kgs, when they select a pen and allocate a wagon to it, the wagon might feed 15 pens, now we need to find how many loads it will take to feed those pens without each load exceeding the CAPACITY of the wagon, so Scott that is what I am after, I have tried Dsum with not much success, the variation between wagon capacities always ends up wrong, if you have any ideas that would be great, if you need a cut down version to see what I mean, just let me know and I will upload it, thank you very much again. Kevin carmichael

	Richard Rost @Reply 4 years ago
That is definitely a job for DSum.

Kevin Carmichael OP @Reply

4 years ago

Hi Richard, thank you very much for your reply, I know you are very busy and don't cosult any more, Yes I am using DSum, the problem I am having is when the capacity of the Feed truck changes bigger or lower, the loads don't work out, if you have any suggestions I would be very grateful, I have a Dsum Running Total to get the pen values as the Dsum calculates, the I have another column to work out the first load then the next an so on, here are the functions:
RunningAMTotal: DSum("[AMKgs]","[PenListID]","[RationMixID] =" & [PenListID]![RationMixID] & " And [FeederID] =" & [PenListID]![FeederID] & " AND [PenNameID]<=" & [PenNameGroup])
AMLoad1: IIf([RunningAMTotal]>0 And [RunningAMTotal]<[Capacity],[AMKgs])
AMLoad2: IIf([RunningAMTotal]>[Capacity] And [RunningAMTotal]<=([Capacity]*1.9),[AMKgs])
I have tried in a Query and Recordset, just can't get the formula right, thanks again Richard, hear from you soon.
Cheers,
Kevin

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developers.

Next Unseen