Thread: Help with code

    #1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jan 2013
    Posts
    5
    Rep Power
    0

    Help with code


    Hi

    Iím trying to create a stockpile database for my farm fertiliser stockpile. Fertiliser with a certain percentage of lime is delivered to my stockpile. The fertiliser is delivered to the front of the stockpile so itís a first in first off scenario. I can add new deliveries with different percentages to my database and a query works out the overall kgs and overall weighted percentage of lime. When I remove an amount of fertiliser from the stockpile I have a query which calculates the remaining total kgs and total weighted percentage of lime. I would like to create a query which removes fertiliser first delivered to the stockpile. If I use 5kgs from a full stockpile and the first delivery was 50kg at 10 precent lime and the total stockpile amount was 200kgs at 15% lime. The query would remove 5 kgs from the first delivery of 50kgs leaving the remainder of 45kgs in the stockpile and also removing 10% lime from the weighted average. The stockpile delivered table would store all deliveries, stockpile used would store used amounts of fertilizer. What type of query would I use to create a first on first off system. I am able to work out the total kgís and weighted percentage but do know how to create the query that looks for the first on by date and then subtract a portion of an existing delivery or taken two deliveries to equal one used fertiliser record. Any help would be appreciated
  2. #2
  3. 0x800A0C93
    ASP Super Hero (2500 - 2999 posts)

    Join Date
    Jan 2009
    Location
    Ridin' through this world...
    Posts
    2,975
    Rep Power
    1310
    It occurs to me that if the fertilizer is dumped in front of a large existing pile and then picked up form the same point, it is last in first out, not first in first out, so this may be all wrong but we can deal with that,

    Okay this is going to be weird. And someone will tell me I am effing up. Bear with me. Based on what I reading, this might be the best for you.

    I think of a record that says 75 kgs as summary information. It is a count. A different way to think about it would be to think about it as 75 records- each record representing one kilo at a specified strength. So you could have a data structure that looked like:

    Code:
    deliv_id	strength	used
    1		0.15		0
    2		0.15		0
    3		0.15		0
    4		0.15		0
    5		0.1		0
    6		0.1		0
    7		0.2		0
    8		0.2		0
    9		0.2		0
    10		0.2		0
    Higher delivery_id means later delivery. I think the deliv_id field could be a simple autonumber. So this table has 4 kgs of 15 percent, 2 at 10 percent, and 4 high test at 20 percent. The 4 kgs of 15 percent are the oldest (back of the pile) and the 4 kgs of 20 percent are the newest (front of the pile). So if you have 75,000 kilos on hand you have 75,000 records. 75K records or 375K records is nothing for a database- it doesn't care.

    I said this was weird. Give it a minute.

    Anyway, with that data structure, you can tell how much you have on hand with this:
    Code:
    SELECT Count(deliv_id) AS [amount on hand], 
    format(Sum(strength)/count(deliv_id), "#,##0.00") AS [average strength]
    FROM deliveries
    where used=0
    which will output:
    Code:
    amount on hand	average strength
    10		0.16

    Now, let's say you use 3 Kilos:
    Code:
    deliv_id	strength	used
    1		0.15		0
    1		0.15		0
    1		0.15		0
    1		0.15		0
    2		0.1		0
    2		0.1		0
    3		0.2		0
    3		0.2		1
    3		0.2		1
    3		0.2		1
    Your "on hand" query now says:
    Code:
    amount on hand	average strength
    7		0.14
    so that is good.

    What you need to make this work is something I don't know how to do. I think Alan or somebody would or maybe you could Google.

    What you want are two pieces of VBscript code. The first one you use for input. It queries you on the amount being added to the pile and the strength of it. If you say 5 kilos at 12 percent, it enters 5 new records at 12 percent into your table. Could just be a simple little loop, don't know.

    Anyway, then you need a second piece of code that does an update when you use some of your stuff and take it from the pile. With the above original data, when you reported taking 3 kilos, the update would set the used field for three records with the highest id to used. If you took 5 units you would set "used" in the top 5 records ordered by id to 1:
    Code:
    UPDATE deliveries
    SET used=1
    WHERE deliv_id in (
    select top 5 deliv_id
    from deliveries
    order by deliv_id desc)
    That would give you data like:
    Code:
    deliv_id	strength	used
    1		0.15		0
    1		0.15		0
    1		0.15		0
    1		0.15		0
    2		0.1		0
    2		0.1		1
    3		0.2		1
    3		0.2		1
    3		0.2		1
    3		0.2		1
    and on hand results like:

    Code:
    amount on hand	average strength
    5		0.14
    In the real world you will have the situation where the database says you have 5 kg and the pile is empty just because of counting errors or spillage or whatever . You'll have to deal with reconciling that.

    So. I said it was weird. And I'm sorry it is incomplete because I don't know how to write little module things in Access. I would guess the entry code and use code coudl be ties to little forms.
    Brinkster- free development account. Not affiliated, but I sure like free. Brinkster.com
  4. #3
  5. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jan 2013
    Posts
    5
    Rep Power
    0
    Thanks its a great starting point

Similar Threads

  1. Add code for font size under php echo code
    By newphpcoder in forum HTML, JavaScript And CSS Help
    Replies: 3
    Last Post: November 14th, 2010, 01:17 PM
  2. Replies: 0
    Last Post: November 2nd, 2010, 06:00 PM
  3. Code for reading xml code doesnít work anymore
    By littleNewbie in forum .NET Development
    Replies: 1
    Last Post: May 18th, 2009, 02:23 AM
  4. Code Bank Forum - only for code samples
    By Shadow Wizard in forum Code Bank
    Replies: 1
    Last Post: August 16th, 2005, 09:17 AM
  5. Moving code to code behind file causing weird problem...
    By palomar in forum Visual Basic Programming
    Replies: 0
    Last Post: June 21st, 2005, 07:10 PM

IMN logo majestic logo threadwatch logo seochat tools logo