Results 1 to 12 of 12
  1. #1
    Join Date
    May 2009
    Posts
    15

    Unanswered: Price per repairs (stock, prices and FIFO)

    Hi, I was asked by a friend to do a DB for a transportation company. This DB (the part that I'm doing) is basically just for the vehicle repairs (which they do in house) so here it goes the details.

    So I have a partsin table, a partsout table, and a repair table.

    The partsin table is basically for the introduction of the parts that they buy, it has the following fields, ID, name, price, date, quantity, Receipt number, Provider

    The partsout table is to be used with the repair table (so I can use which parts where used in the repair), it has the following fields, ID, name, quantity, and relationID

    The repair table is where I put all the info like hours spend and all that, and it has the following fields, ID, Repair number, Date, Licenseplate. Then I use a 1 to many relation between the ID and the relationID (from partsout table) so I can select the parts that where used, this creates OFC a subdatasheet of the partsin table, and this is where I choose the parts and the quantity used.

    So now for what I need, I need a way to know How much I spend on the repair at that time, the problem that I'm having is that I don't know how to estabilish a first in first out in access. So although I'm able to get the stock and the price, I'm not able to get a real value per repair since right now is doing something like this

    Number of screws used in the repair * average price per screw.

    So basically it's doing this

    Number of screws used in the repair * (sum of all money spent in screws until today / all screws bought until today)

    And what I want is this

    Number of screws used in the repair * price of first screws available If first screws available don't have enough quantity then take them all and go to the next screws.

    I admit that it's kind of confusing in the way that I explain but it's basically the same problem in here http://www.dbforums.com/database-con...g-methods.html

    Does anyone know a way to get a price per repair using FIFO (first in first out), I have seen a few people with this problem but never saw someone posting a solution.

    P.S. The repair table has OFC more fields but they are not worth mentioning since it's stuff completely unrelated to the problem like comments and who was the mechanic etc...

  2. #2
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    This seems a lot of effort to try and cater for the 'average' screw price?
    Not sure how much these screws cost???

    You would have to be able to access delivery details of screws / prices - when they were delivered (available) for a start - This poses all sorts of questions;

    Did the supplier deliver the ordered quantity - You would have to work from 'delivered' quantities - How often do the users input /update this data?
    How often are their "stock takes"?
    What is the deviance of the stock take to the stock on hand?
    Did the workforce actually use the 'cheaper' screws before the (for example) newly delivered 'more expensive' screws?
    How do you cater for 'lost' or 'defective' screws?
    ...I could go on.

    Personally, I would try and convince them of a simpler model as I'm not sure if this is practical in the real world.

    I think they would be better say to have a sales price per screw and review this sales price based on what they have paid - perhaps quarterly / monthly depending on how volatile the price of screws (or any other component) is.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  3. #3
    Join Date
    May 2009
    Posts
    15
    To answer a few of your questions, the user inputs data a few time a week, the stock for the most part is out a little while after it enters the system, most of the times it doesn't even have time to enter a new a product. Yes the workforce usually uses the older stuff before buying/using the new stuff. The lost or defective screws are returned since I'm not talking about screws (screws is just and example) but more costly parts, in case they can't be return they just stay in as part of the repair cost.

    The price of the components it's quite different since the same part/component usually comes from different sources depending on the price.

    I know a easy way to "solve" the problem that is to put the name like screw1, screw2, screw3, but that isn't really a good solution in my opinion. Also I'm thinking about using dates, like get the price of that part in which the date is closer to the repair date, but I haven't got around it yet.

    I had also though about the quarterly monthly thing you suggested, since it would also compensate for the temporary very low or very high fluctuations, but though to try the FIFO way first.

    BTW Do you know where I can find an example of the quarterly monthly stock/prices system?
    Last edited by HunterPT; 05-07-09 at 14:41.

  4. #4
    Join Date
    May 2009
    Posts
    15
    Does anyone know how to make a list box or combo box, to only show the rows from a query, where the field [query1].[name] is equal to the field [partsout.name].[name] when this list/combo box that I'm trying to make is in the table/form partsout?
    Last edited by HunterPT; 05-08-09 at 11:04.

  5. #5
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    You can refer to the control on the form which has a reference to "Partsin.Name:"

    SELECT Last(Partsin.[Price]) AS Expr1 FROM Partsin WHERE Partsin.Name=Forms!YourFormname.yourformcontrolnam e

    if 'yourformcontrolname' is a combo box then by default the above would reference the first column.

    However, I'm not sure as to how this will help you solve your original question.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  6. #6
    Join Date
    May 2009
    Posts
    15
    It will help because then I will be able to select the last price available for that part, and by selecting the last price and inserting that price on the partsout table, that price will be forever associated with X repair which means I will be able to see how much that repair cost long after the price as changed. BTW thanks for the help I will try it out.

  7. #7
    Join Date
    May 2009
    Posts
    15
    Ok I have tried your sugestion and somehow I wasn't able to put it to work, so here it goes part of the table, so you can see the problem better

    RapidShare: Easy Filehosting <- Office 2007

    RapidShare: Easy Filehosting <- Office 2003

    So as you can see in the repairs table we have a subdatasheet with the partsout table, then I have also introduced a subdatasheet in there of the lastprice query, and what I want is for the price that appears in that query that is in the subdatasheet (of the partsout), to appear in the price field of the parsout table, after OFC I have select the partname. Right now all I can do is for all the last prices (from lastprice query) to appear in the list/combo box named price. So basically what I want is for the price that now appears in the subdatasheet of the partsout table, to appear in the price field of the table after selecting the partsname field.

    P.S. Since I had to translate the names I didn't include all the fields of the tables, but the important is there.

  8. #8
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    "to appear in the price field of the parsout table"

    You cannot and 'should not' store a calculated field in a table.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  9. #9
    Join Date
    May 2009
    Posts
    15
    I need to store the field in a table since the value will keep changing, anyway right now all I have is a manual way to introduce the value, the value appears in a subdatasheet, all I wanted was for the combo box in the price field to only show the option to that value that is already appearing.

  10. #10
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    "I need to store the field in a table since the value will keep changing"

    You should never store this kind of data in a TABLE - If it can be derived or calculated elsewhere you should do this 'on the fly'.

    I think you need to go back to the drawing board so you know where you are going with this database design.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  11. #11
    Join Date
    May 2009
    Posts
    15
    May I ask what your suggestion is? I can only see 2 options, one I store the data like I'm saying, so the database only as to calculate one value and that value is stored for ever. Second option I sum all the prices and divide by the total number of parts of that type bought, I do this in a monthly or quarterly basis, this leads to more unprecise value per repair, which is kind of important since one of the main objectives of this DB, is for the managers to see when a vehicle stop being viable in what concerns cost repair costs vs new vehicle. The first option as the problem of generating more data and more of calculation, but considering the amount of data that it's going to be inputed I don't see space or processing power to be a problem.

    I would OFC prefer to do it "on the fly", since I didn't even had the field price in the partsout table, but I wasn't able to get values as precise as the "new" way, since I am unable to come up with something that gives me the price closer to the date of the repair. Anyway I can choose to keep the price in the partsout table for now, and when I find out how to get the price that is closest to the repair date, all I will have to do is to eliminate the price field in the partsout table and make a few modifications.

  12. #12
    Join Date
    Aug 2003
    Location
    Bulgaria, Plovdiv
    Posts
    36
    Probably you must have inventory management for all the parts and then you must be able to ussue the parts using weighted average cost method.

Posting Permissions

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