Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Pennsylvania, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Method of programing (2000/2002)

    Have a bit of a problem

    I have to track the cost per Item sold from our Inventory, currently I am storing this cost amount in my SalesOrderLineItem table. The problem arises, when the cost amount changes. I have to go into the table and change this manually, because I may still have stock at the old cost. This is necessary because we only pay for what we
    threecrow

    Don't make excuses. It's what you do, not why you didn't.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Method of programing (2000/2002)

    How exactly do you determine which records should be updated?

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Pennsylvania, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Method of programing (2000/2002)

    Hans

    I pretty much use the SWAG (Scientific Wild A%% Guess) method. And a little brain matter.
    I look at my remaining stock at the old cost, prior to the date of the new cost. Then I just pick and poke till stock at old cost is gone.
    I've have some troubles with this method, as it could take time to clear all stock at old cost and I'm overlapping new cost.

    What I'm attempting to create a First In First Out rutine.
    threecrow

    Don't make excuses. It's what you do, not why you didn't.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Method of programing (2000/2002)

    You could create a query based on your table, with criteria on the date field to select only those records you want to update.
    Switch to Datasheet view and back to verify this.
    Select Query | Update Query. This will make a new line appear in the query grid: Update to.
    Enter the new price or cost in the appropriate column.
    Select Query | Run or click the Run button. Access will put up a message that you are about to update ... records, and let you choose to continue or not.

    (It would be a good idea to practice this on a copy of the database first)

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Pennsylvania, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Method of programing (2000/2002)

    Yes Sir, I've done this a number of times, sometimes had to go back and make corrections.
    There has to be a way of doing this through code and queries and maybe temptables.
    threecrow

    Don't make excuses. It's what you do, not why you didn't.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Method of programing (2000/2002)

    The update query I described is a fairly automated way of setting the cost. If you like, you can run it by clicking a command button on a form. If it doesn't do what you want, please explain in what way it fails.

  7. #7
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Pennsylvania, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Method of programing (2000/2002)

    The update query does not fail, as I have stated I have used this method, it
    threecrow

    Don't make excuses. It's what you do, not why you didn't.

  8. #8
    Star Lounger
    Join Date
    Jan 2003
    Location
    Manchester, Lancashire, England
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Method of programing (2000/2002)

    No answer is 'wrong' you just have to decide how you want to run your inventory.

    You already said you wanted a FIFO system so that gives you your answers.

    If I understand correctly that you only pay your supplier when you sell the item on then anything but a FIFO solution will probably give your supplier problem!!

  9. #9
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Method of programing (2000/2002)

    How many different items are you talking about? And what is usually the highest quantity of each item? You many want to break up your inventory into multiple tables, and instead of storing a number (qty) for each item in your inventory, just create multiple records. Have a column to mark each item as sold (to calculate total), and create a form that will filter for the top (from oldest to newest) X number of records for that product. Mark them as sold with a unique value (to designate buyer/date) and you should be able to track/calculate this pretty easily. Through careful form design and a little VBA code, I see my way as a simple solution to your seemingly complex problem.... If you're worried about creating multiple records for each product, you should be able to use an unbound input form, with a text box that you may put in a value (the qty of items you received), and reference that text box to in your VBA code add that many duplicates of your record....This should make checking your inventory as simple as a crosstab query.

    I know this didn't provide many answers, but hopefully it let you step outside your "box" (current mindset...we all get like that), to try to employ a different solution....
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  10. #10
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Pennsylvania, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Method of programing (2000/2002)

    You are correct "no answer is wrong", but some are just not right.

    My Inventory Quantity is (and I believe should be) straight forward, product Received minus product Sales.

    We pay the supplier only for what has been paid for by the customer.

    The supplier knows the Quantity sent at .81 cents, and sent .85 cents, And they know the Quantity at each price that we have paid for. Because I send a payment voucher giving them quantity and money amount paid. Then I am sent a Statement telling me if I'm correct or incorrect. But telling a money amount, not broken down.
    There are no PO# or Transactions identifiers (other than the one's that I use, which mean nothing to the supplier) to track all of this. The supplier only uses Date Shipped, Quantity Shipped, and Cost at the time of Shipping.

    Your might be thinking, this is a terrible way to run a business.
    And I would agree. Until I started with this company, this was a done on paper. (I wonder how many times the supplier was over paid and said nothing)

    Yes Sir a FIFO system is my answer, I am having problems coming up one.
    If you could point me the direction of a model for a FIFO system I would be in your debt.

    Thank You Kindly for your time
    threecrow

    Don't make excuses. It's what you do, not why you didn't.

  11. #11
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Pennsylvania, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Method of programing (2000/2002)

    Jeremy

    I do not store the Quantity of my Inventory, this is calculated with a query (Received minu Sold(Shipped))
    Prior to your suggestion, I have create few tables. One to store information of a cost change, ItemNumber, DateReceived, QuantityRec, Cost. This I populate via append query, only when the cost changes. And another to track Inventory Transactions, such as, TransactionDate, Document, ItemNumber, Sold, Received, Cost. This is also populated via append queries from my various entry forms.

    Your suggestions are valid and given me food for thought. Through explaining this so many times I think that I've come up with salution.
    A portion of your thinking I'll use to try to get this to work.

    Thank You Kindly for the push in this direction.
    If I just get that forest out the way, I might be able to see those trees.
    threecrow

    Don't make excuses. It's what you do, not why you didn't.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •