Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160

    Unanswered: Sorted Line numbers in a table

    Hi all,

    I have a table call tblOrders. I add orders to the table by using the following code

    SQLtext = " INSERT INTO tblOrders (fkeyID,Item,Part_No,Description,Category,List_Pri ce)" & _
    " VALUES (" & _
    "'" & RefID & "'" & _
    ",'" & Item & "'" & _
    ",'" & Part_No & "'" & _
    ",'" & Description & "'" & _
    ",'Hardware'" & _
    "," & List_Price & _
    ")"


    the above code adds the part to tblOrders and puts Hardware in the category field so i can group on it when creating a report. I have the same code else where which put 'Software' in the category field and that all works fine.

    I need to add Line numbers to the parts I add to the table. Starting at 001 and incrementing as i add more parts. Where i think it will get complicated is if i delete a part. If i delete a part i need the other parts in tblOrders to re-shuffle so i have sequential numbering again for that order.
    If i create a new order i need the numbering to start at 001 again.

    tblOrders

    fkeyID
    Item
    Part_Number
    Description
    Category
    List_Price
    Line

    I've been on it for two days so any help very much appreciated.
    marcus

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why are you adding description to the order line.. surely that comes form a products/parts table? as indeed would the category the list price
    tblorders sounds odd, it may be terminology but to me thats orderdetails not orders....

    to create an item number you need to find the most recent value of itemno in orderdetails

    either use SQL directly
    select max(itemNo) from orderdetails where orderno=blah
    then run that sql
    or use a domain function such as DMAX

    the code to get the next item no should be triggered when you add a record
    OR if you prefer you could put the code in the forms before update event and run it if the current itemno is blank
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    Hi there,

    I am adding description. Below is an example of what will go into a record in tblOrders when I double click the add button.

    Record 1
    fkeyID = 1001/a
    Item = Power Tool SX2
    Part_No = 456-444B
    Description = 12volt Cordless power tool ....blar blar..blar...
    Category = Hardware
    List_Price = 79.99


    Record2
    fkeyID = 1001/a
    Item = AutoCAD
    Part_No = 343-4567a
    Description = Professional Drawing package.....blar blar..blar...
    Category = Software
    List_Price = 299.99

    So both items go into tblOrders. Each record if referenced by the same number 1001/a

    I have added a field called 'Line' in tblOrders.

    I am after help with managing the Line field so it looks something like this..

    Record 1
    fkeyID = 1001/a
    Item = Power Tool SX2
    Part_No = 456-444B
    Description = 12volt Cordless power tool ....blar blar..blar...
    Category = Hardware
    List_Price = 79.99
    Line = 001

    Record2
    fkeyID = 1001/a
    Item = AutoCAD
    Part_No = 343-4567a
    Description = Professional Drawing package.....blar blar..blar...
    Category = Software
    List_Price = 299.99
    Line = 002

    Say I have 10 items in tblOrders under the same order code 1001/a . What happens if i delete an item? I need the other 'Line' numbers to re-shuffle so they are sequentail.

    Hope that makes sense?

    P.S thanks for looking
    Marcus

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    nope
    unless you only ever sell one power tool, only on edrawing package, you shoudl have a tabel containing the products. that table has the stuff that is immediately relevant to the product (eg a part number, a description, a list proice and so one)

    ..why
    its all about data integrity and reliability
    if you type the stuff in at the time the order is taken then you can get typo's for description and or price.
    if you sell multiple power tools then you only want to define the description once.
    a product as a single list price, it may have several discount rates dependign on type of customer, quantity ordered and so on

    so an item ordered may have a list price but one actual price.
    only store a piece of information once.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if yoiu want to play the delete and shuffle game you can do, a far smarter solution is to flag that item as dead/delisted that measn you dojn't have to resequence the list and perhaps more importantly you haven't lost the tracking. the customer may have supplied you with an order in their sequence, they may know item 10 is product X, if you resequence the list they may loose that relationship. its fairly important when reconciling delivery notes with order requests
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    I think i need to explain a bit more.

    Data integrity is preserved.
    I have a table called Products. I enter each product in once. The data in that table is the default data which contains descriptions list prices etc. In the form If I select an item and double click it will go into tblOrders. Why do I not reference it etc. Because it is default data. We have to slightly modify the description sometimes or the price depending on the customer. I want the default (preserved data) set in stone. If I have to modify anything I do not want it to modify existing orders ( hence not relationship) I am using hardware tools as an example, our products are way more complicated.

    cheers
    Marcus

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why would you adjust the product description or price? they are what they are, they should be pulled formt he product table.
    by all means store the actual price the customer bought at, or a discount or whatever but don't double store the price and description. if you have to modify your description then you could have all manner of issues with the likes of sale of goods act.. you cannot change the description once sold. you are leading yourselves wode open to misrepresentation (the item bought is not the same as the itme shipped. if what you are in reality saying is your data is pants, then fix the data, fix the problem not pee around with the symptoms. it may be that what you really mean in the order item is actual price as opposed to list price.
    the only times I coudl see a requirement for a description and list price in the order details is if its a unique product (eg second hand item or one offs otherwise pull the datat from the products table. their is an issue with how you treat a prodcut price (which may change over time, but cannot change in an order detail, once you have agreeed or accepted a price thats the price (unless you have an errors and ommissions term. but you must freeze the price actually charged to the customer.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    This is a quotation database. We are selling the items. We have standard products with descriptions and a list price. This is a starting point. The list price is obviously a starting point as we often get beaten down depending on the customer/competition etc. I put together the quote but sometimes change the descriptions to make it more customer specific. Nothing goes to the customer until every detail has been checked.

    I would like to go back to my original question and hopefully someone could help me out a little. To everyone who has help me in the past I am eternally grateful and I owe a lot of thanks.

    yours hoping!!
    marcus
    Last edited by marcusmacman; 11-09-10 at 17:34.

  9. #9
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    The quick answer is to not store the line item in the database, just create it when you populate the order details on an invoice or report.
    Dave

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so have you tried DMAX
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    i think you're right.

    I am grouping on Category in the report but within the detail section of the report i need the items to list in a particular order. That's why i though i needed to create a line field which i could somehow enter a number into which would determine the order.

    thanks
    marcus

  12. #12
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    not tried DMAX, not sure what to do now. I need to determine the ordering in the report somehow. For the moment I will display the Line field so i can manually enter numbers into it so it will display correctly in the report when i group on it
    ta
    marcus

Posting Permissions

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