Results 1 to 12 of 12
  1. #1
    xAkademiks is offline Novice
    Windows XP Access 2010 (version 14.0)
    Join Date
    Oct 2010
    Posts
    8

    Angry Complicated loop help

    Ok .. so .. I'll try and explain this as best as possible.

    Example:
    I have a part with a trace number, it's a one-to-one relationship.
    Parts are made of parts which are made of parts, so on and so forth.
    I need to display a list of parts which belong to a particular part.
    But I also need to display the list of parts which belong to that particular part inside the particular part we're listing.
    So for example it will look something like.

    Part: F9999

    Level Part Number Trace
    1 B1234 N1234
    2 B1111 N1111
    3 B1999 N1021
    2 B1921 N0001
    1 A1212 N1999
    2 A9999 N9882
    2 A1011 N5555

    So in the first section ...
    Level 1 is the completed part which belongs in the top level part


    Level 2 are sub parts that belong in part 1
    And the level 3 part belongs to the level 2 part which is above.
    Confusing, I know.

    So I need to be able to return a record set of level 1s, which loops through and displays all parts under the first level 1 part, then go back to the level 1 part and display the next level and all the levels under that part, then when all parts are done under the first level 1 part, I need to go to the next level 1 part and continue. FML

  2. #2
    help_me_with_access is offline help_me_with_excel
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    262
    Confusing, I know.


    actually, not really. Do you realize that you're "issue" could be considered a pretty good foundation framework for software architecture.

    Personally, I wouldn't start with the code strategy. start with the purpose. what exactly is the purpose? loops mean nothing unless the output of the data and the display of it has a purpose. right?

    for starters, you need 2 pieces of knowledge documented in your mdb file for referencing purposes, or this won't work at all:

    1) the data that serves as the relationship indication between your top-level data and the lower-level data.
    2) what tool you want to use to organize the data as your gathering it (e.g. - array, table, etc...)

  3. #3
    xAkademiks is offline Novice
    Windows XP Access 2010 (version 14.0)
    Join Date
    Oct 2010
    Posts
    8
    2) An array? Is what I've been using so far, or it could be a table.

    This is great you're trying to help? But it doesn't seem like you really are?

  4. #4
    help_me_with_access is offline help_me_with_excel
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    262
    well you have to realize sir, that posting a question of the nature you did, don't expect an answer "just like that" even from the most respected visual basic expert in the entire world. (are there any left!?...I'm doubtin it!).

    I can talk forever on this subject, but again the answer is not simple. If you're a programmer the solution is easy to get into your head, but then ya gotta type it out.

    so how would you like me to help you? type out a solution? give you the primary progression points of different code scenarios? I'm not sure how to help you because the information you posted literally has 10+ solutions, just by simply coding it.

    so what would you like me to do for you? keep in mind, the architecture (structure) of your data is also relevant to the situation. e.g. - indexing, relationships, dependency data records all kept together, or can they appear anywhere (e.g. - in any record number).

    it's kind of a garbage-in, garbage-out scenario here.

    you give the necessary information that the tool needs to work with and the tool gives you the output without further hassle.

    if none of that makes sense at all, just indicate how you want to see the data. printed out in the imm. window? shown in a table? thrown to a windows file type of .xyz extension? etc...

  5. #5
    xAkademiks is offline Novice
    Windows XP Access 2010 (version 14.0)
    Join Date
    Oct 2010
    Posts
    8
    lol ...

    The data isn't really all that relevant or the relationships. I need a way to structure a loop and get the following output:

    A list parts that belong to the top level part selected. And a list of parts which belong to the parts inside those parts, and so on and so forth. I also need them labelled 1 for top level into the main part, and 2 for parts that go into those parts, etc.

    If I could just get the looping structure, or a way to do this because the numbenr of parts that goes into any part can be dynamic. I'm going to need to do a call to the database to check the parts which belong to a specific part everytime.

    Please try and help!

  6. #6
    help_me_with_access is offline help_me_with_excel
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    262
    I don't need to try, I'm good enough to just DO. wow...and you thought you had someone here helping you that was actually a NICE guy, didn't you? Come on now, don't lie!

    I hate to say this to you bud, but that information doesn't help at all. but here's what I have to offer with the limited details you've given so far. working code (code that you can just paste into the vb IDE) is in CAPS. the rest is pseudo, which means it's meant to show you the idea of what you should be doing. lowercase code is NOT working syntax:



    Code:
    DIM RECS() AS VARIANT
    DIM I AS LONG
    DIM RS AS DAO.RECORDSET
    
    SET RS = your_database_object.OPENRECORDSET("SELECT DISTINCT your_top_level_field FROM your_table")
    
    RECS = RS.GETROWS()
    
       FOR I = LBOUND(RECS()) TO UBOUND(RECS())
          loop another table or the other data set where your associated data is at
             continue subloops like the top-level example until the bottom level data is reached
       NEXT I
    
    RS.CLOSE
    it's pretty simple, in terms of spelling out the repetitive code that's needed. if of course you're going to write code to do it. without seeing data, that's as far as anyone here can go, in terms of helping you. making any more assumptions without seeing real data, or an example thereof, would be unproductive because it would become guesswork.

  7. #7
    xAkademiks is offline Novice
    Windows XP Access 2010 (version 14.0)
    Join Date
    Oct 2010
    Posts
    8
    Ok, that's not much help. Let me explain it to you like this and see if that helps.

    There's a parts table with a list of all the parts.

    Then there is a table which has a list of all the parts which belong to a certain part. So within those parts that belong to the particular part there can be more parts which belong to those parts.

    I need to be able to loop through the original parts for a particular part. At the first part, I need to find the parts which belong to that part, then the parts which belong to the first part for it's parts and so on and so forth until there isn't any more parts at the end of the chain. Then go onto the second part belonging to the main part and repeat.

  8. #8
    help_me_with_access is offline help_me_with_excel
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    262
    sorry dude. that's just the same re-iteration the 3rd time over. Personally, I can't assist ya anymore unless I see the data. This will be my last post. I tried to help as much as possible. There just isn't enough detail that you can give through words alone.

    good luck to ya!

  9. #9
    xAkademiks is offline Novice
    Windows XP Access 2010 (version 14.0)
    Join Date
    Oct 2010
    Posts
    8
    I don't understand what's so hard to grasp? It's fairly basic.

  10. #10
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Basic? Yes. But mostly it is your inability or unwillingness to provide details.

    This is what you have posted:
    Code:
    Part: F9999
    
    Level    Part Number Trace
       1         B1234 N1234
       2         B1111 N1111
       3         B1999 N1021
       2         B1921 N0001
       1         A1212 N1999
       2         A9999 N9882
       2         A1011 N5555
    That is the results you want?


    How is the data stored? ie What is your table structure?

    It could be a normalized structure:
    Code:
    Table Parts
    ------------
    PartID    PK  Autonumber
    PartNumber    Text
    PartDesc      Text
    
    
    Table Level1
    ------------
    Level1ID  PK  Autonumber
    PartID_FK     Long
    Level1Number  Text
    Level1Desc    Text
    
    Table Level2
    ------------
    Level2ID   PK Autonumber
    Level1ID_FK   Long
    Level2Number  Text
    Level2Desc    Text
    
    Table Level3
    ------------
    Level3ID   PK Autonumber
    Level2ID_FK   Long
    Level3Number  Text
    Level3Desc    Text
    (All you would need here is a query)



    Or it could be in one table:
    Code:
    Table PartsLevels
    ------------
    PartID     PK Autonumber
    PartNumber    Text
    PartDesc      Text
    Level         Integer
    LevelNumber   Text
    LevelDesc     Text
    (This might need to have some VBA.)


    Or your structure could be a combination of the two.
    Or you could have devised a totally different structure.


    So until you can be specific on the tables/fields, you probably won't get much help in getting a solution.

  11. #11
    WyattR is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2012
    Posts
    5
    The sample database provided seems to be what you're looking for. Make sure you know what you're doing before you make alterations though - it's a tough little database.

  12. #12
    WyattR is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2012
    Posts
    5
    Sorry, forgot to provide the link, and then re-visited the thread two months later. If anyone's interested in what I was talking about: http://www.experts-exchange.com/Micr...expansion.html

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Complicated
    By Ganymede in forum Queries
    Replies: 3
    Last Post: 01-22-2012, 06:25 PM
  2. Complicated form
    By secret in forum Access
    Replies: 14
    Last Post: 09-07-2011, 10:16 PM
  3. Complicated IIF?
    By Sweetnuff38 in forum Queries
    Replies: 1
    Last Post: 08-18-2011, 01:13 PM
  4. Help? Complicated Drop-Down
    By Pick9811 in forum Access
    Replies: 7
    Last Post: 06-19-2010, 01:35 PM
  5. Complicated ASP SQL to Access db
    By KLynch0803 in forum Programming
    Replies: 0
    Last Post: 01-31-2010, 08:32 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums