Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2004
    Posts
    112

    Question Unanswered: Urgently need help

    I have an Orders Table And an Order Details Table. They are linked by 'OrderId'
    The Order Details table can have multiple products each with its own Order Details IdNumber, easy if you just set the Order Details Id Number to an auto number..right!.
    what I want to do .... for every order number entered I want the Order Details Id Number to be the same as the OrderId number +1.

    Order Table
    Order Id 10

    OrderDetails Table
    Order Id 10
    Order DetailsId 10,1
    Order DetailsId 10,2
    Order DetailsId 10,3
    etc.....

    so for each order detail it looks at the OrderId and ads 1.

    Any super dooper Person out there that has the answer.

    Thanks

  2. #2
    Join Date
    Feb 2005
    Location
    The Hague, Netherlands
    Posts
    95
    Quote Originally Posted by saltman
    I have an Orders Table And an Order Details Table. They are linked by 'OrderId'
    The Order Details table can have multiple products each with its own Order Details IdNumber, easy if you just set the Order Details Id Number to an auto number..right!.
    what I want to do .... for every order number entered I want the Order Details Id Number to be the same as the OrderId number +1.

    Order Table
    Order Id 10

    OrderDetails Table
    Order Id 10
    Order DetailsId 10,1
    Order DetailsId 10,2
    Order DetailsId 10,3
    etc.....

    so for each order detail it looks at the OrderId and ads 1.

    Any super dooper Person out there that has the answer.

    Thanks
    1 time move?
    I'd say export the detail table to excel, change the numbering manually (just shift the autonumber-column) and import back

    Although your example confuses me: I thought you wanted the Order detail ID to be one higher then the order ID, so order ID = 10, then corresponding Order Detail ID = 11, so order ID = 11, then corresponding Order Detail ID = 12 etc.

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by saltman
    I have an Orders Table And an Order Details Table. They are linked by 'OrderId'
    The Order Details table can have multiple products each with its own Order Details IdNumber, easy if you just set the Order Details Id Number to an auto number..right!.
    what I want to do .... for every order number entered I want the Order Details Id Number to be the same as the OrderId number +1.

    Order Table
    Order Id 10

    OrderDetails Table
    Order Id 10
    Order DetailsId 10,1
    Order DetailsId 10,2
    Order DetailsId 10,3
    etc.....

    so for each order detail it looks at the OrderId and ads 1.

    Any super dooper Person out there that has the answer.

    Thanks
    Do it yourself ... Make the coulmn NOT an autonumber (either a long or int) and keep track of the next index # by using the DMax function...
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Sep 2004
    Posts
    112
    Not sure what you mean?
    How would I use the dmax and where, I really want the Order Detail table to generate the Number. I will try to explain in more detail what I want.

    1st order

    ------ORDERS TABLE------
    Order no 10 -
    ------Detail Table-------
    10 : 10,(1) Red paint
    10 : 10,(2) Blue Paint
    10 : 10,(3) Green Paint
    10 : 10,(4) Black Paint
    ......... ..................
    2nd order
    Order no 11 -
    11 : 11,(1) Wall Paper red
    11 : 11,(2) Wall Paper mauve
    11 : 11,(3) Wall Paper pink

    etc....etc.....etc

    As you can see Order number is 10,11 and the order details
    are the respective order number with a details number to correspond to
    the line number 1,2,3,4 etc. So if I were to process an individual order I would enter
    in the Order No 101,102,103,104 or 111,112,113etc.

    Do you get what I am trying to do?

    Thanks.

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by saltman
    Not sure what you mean?
    How would I use the dmax and where, I really want the Order Detail table to generate the Number. I will try to explain in more detail what I want.
    Thanks.
    The table can't. If you have an autonumber then you have to live with the sequential nature of it ... Since you want the line item to start at 1 for each order, you have to implement and control that process yourself. You can do this in a function or as part of the process of making the line item record ... It's up to you ...
    Back to Access ... ADO is not the way to go for speed ...

  6. #6
    Join Date
    Sep 2004
    Posts
    112
    Sorry to be a pain... could you give me an example? from your quote "you have to implement and control that process yourself. You can do this in a function or as part of the process of making the line item record ... It's up to you ..."

    What would I do?

    Thanks

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by saltman
    Sorry to be a pain... could you give me an example? from your quote "you have to implement and control that process yourself. You can do this in a function or as part of the process of making the line item record ... It's up to you ..."

    What would I do?

    Thanks
    I see a little handholding is needed here ... An example of what I'm talking about:
    Code:
        ' Add the line items of the P.O.
        SQLString = "SELECT * FROM " & ItemsTbl & ";" '  ORDER BY [Line #] ASC
        MyRecSet.Open SQLString, CurrentProject.Connection
        If MyRecSet.BOF = False Then
            MyRecSet.MoveFirst
            Ndx = 1
            While MyRecSet.EOF = False
                SQLString = "SELECT * FROM [PO Items] WHERE (1=0);"
                TrgRecSet.Open SQLString, MyConnect
                TrgRecSet.AddNew
                ' Tracking #
                TrgRecSet.Fields(0).Value = TrackingNumber
                ' Index #
                TrgRecSet.Fields(1).Value = Ndx
                ' Seq #
                TrgRecSet.Fields(2).Value = MyRecSet.Fields(3).Value
                ' Item ID
                TrgRecSet.Fields(3).Value = MyRecSet.Fields(0).Value
                ' Item Code
                TrgRecSet.Fields(4).Value = MyRecSet.Fields(1).Value & ""
                ' Hazard ID
                TrgRecSet.Fields(5).Value = MyRecSet.Fields(9).Value
                ' Quantity
                TrgRecSet.Fields(6).Value = MyRecSet.Fields(4).Value
                ' Received Quantity
                TrgRecSet.Fields(7).Value = 0
                ' Overage
                TrgRecSet.Fields(8).Value = 0
                ' Quantity Outstanding
                TrgRecSet.Fields(9).Value = MyRecSet.Fields(4).Value
                ' UOM Code
                TrgRecSet.Fields(10).Value = MyRecSet.Fields(8).Value & ""
                ' Unit Cost
                TrgRecSet.Fields(11).Value = MyRecSet.Fields(5).Value
                ' Cost Book
                TrgRecSet.Fields(12).Value = MyRecSet.Fields(6).Value
                ' Tax Code
                TrgRecSet.Fields(13).Value = MyRecSet.Fields(11).Value & ""
                ' W.O. #
                TrgRecSet.Fields(14).Value = UCase(MyRecSet.Fields(16).Value & "")
                ' SI #
                TrgRecSet.Fields(15).Value = MyRecSet.Fields(17).Value
                ' Quote #
                TrgRecSet.Fields(16).Value = MyRecSet.Fields(13).Value & ""
                ' Account Classification
                TrgRecSet.Fields(17).Value = MyRecSet.Fields(14).Value & ""
                ' Comments
                TrgRecSet.Fields(18).Value = Left(MyRecSet.Fields(15).Value & "", 128)
                TrgRecSet.Update
                TrgRecSet.Close
    Pay attention to the varible "Ndx" ... It's my sequence # variable ...
    Back to Access ... ADO is not the way to go for speed ...

  8. #8
    Join Date
    Sep 2004
    Posts
    112
    Nice work.... I'll give that a go.
    Dont suppose you know of any sites like the one I use for Visual basic -Planet-Source-code .com that is specially for MsAccess to show examples, full programs etc??

    Ta

  9. #9
    Join Date
    Feb 2005
    Location
    Blacksburg, Virginia
    Posts
    43
    Maybe I'm missing something here, but it all seems a little simpler than we're making it out to be.

    You already seem to have a one-to-many relationship set up from your orders table to your details table, so why preserve the order number in more than one place? Here's what I mean.

    Order Table
    ----------------
    Order Number: 10

    Order Details Table
    -------------------
    Order Number: 10
    Item Number: 1

    Order Number: 10
    Item Number: 2

    Order Number: 10
    Item Number: 3

    so-on and so-forth.

    This way, if you need to find stuff, you can just look at the order number and pull up all items, or generate advanced queries to look at the order and item number.

    The reason I suggest this is because if you start coding fancy stuff to generate key fields, then you have to generate even more fancy stuff to do advanced manipulations on them. If you're not incredibly schooled in VBA and ADO, you're going to find yourself spending much of your time looking for code examples rather than getting things done.

Posting Permissions

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