Results 1 to 7 of 7
  1. #1
    robuisman is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    3

    Question How to select related rows from a producttable in a form

    Good morning,


    I hope someone can help me with this.

    I have an order database. In a form ORDER, I can select products from the table PRODUCTS, which will be placed in a table ORDERCONTENT.

    so far everything works without problems for years. But I have 'stock' problems with products that contain 2 other products. for example:

    Product 1 €100
    product 2 €60
    product 3 €50

    Product 1 contains, both products 2 and 3. If I sell it as product1, it wil be €100,- But when I sell it separately It will be more expensivve when you buy both. Of course this is working, when I just put all these products in the table. But the problem is the stock counting. In my stock, I just have product 2 and 3, and of course not the complete Product 1. So What I would like to do is mark items like product1 as a containeritem, which I can select in my form. But then it should show the containeritem (product1) + the underlaying products 2 and 3 on te next rows automatically. (Of course I count the stock then with the underlaying products. )

    I hope this is a littlebit clear. For me the biggest question is: How can I choose 1 containeritem via a dropdownbox, and then fill automatically the next 2 rows in the table with the depending Products 2 and 3 If I know this I will be helped a lot.

    please see also my attached "explanation"

    Thanks already!!
    Attached Thumbnails Attached Thumbnails pictureexplanation.jpg  

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    the answer depends on how many levels you need to go and whether any 'sub' product will always be at the same level.

    In your example you have two levels and 'sub' products can appear in either level.

    If it remains this simple then create a container table which contains all 'top' level products (your 'container') e.g.products 1, 2 and 3 and link to the sub table (which contains products 2 and 3). The top table does not need to contain all the fields on the sub table, just sufficient to do the job - so for example selling price would be in the top table, cost in the sub table. Because sub table records can appear against more than one top table record, you will also need a many to many table to link the two tables together.

    If you have more levels, you can see it starts to be become more complex and it is generally better to store data in a recursive table. A recursive table links to itself. But these will normally need to be managed using VBA.

    A recursive table will be structured something like this

    PK...Product....Parent
    1.....1.............null (top parent)
    2.....2.............null (top parent)
    3.....3.............null (top parent)
    4.....2.............1
    5.....3.............1
    6.....3.............2

    So in this structure products 1,2 and 3 are sold individually (the null identifies them at the top)
    product 3 is also used in products 2 and 1
    product 2 is also used in product 1

    so product 1 is using 1 product2 and 2 product3's

  3. #3
    robuisman is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    3
    Thanks a lot for your answer Ajax!
    I understand what you mean. But Inmy case I need to have the details in my form. My field ARTNR is a dropdownmenu, that gets the info from the products, and puts this info in the fields in the table. So With your aswer I can make a good query to get the right information to select the parent item. But how can I manage that when I have selected the parent item, that the following 2 rows will also be filled automatically with the subitems? (how do I get this in my screen ? ) I understand that I can make a report with this information easily, but I need to see it also on my screen in the form somehow to understand what items belong to the parent.

    I hope the above is a littlebit clear :-)

    Thanks already!

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    you haven't explained your structure in any more detail so don't see what I can say other than you'll need to some code in your form after update event to create incomplete related records for each of the related children and refresh the form after every entry. I suspect may not be possible and if it is, it will be very messy and may not be updateable (which perhaps defeats the objective).

    Normal way to handle this dynamically on a form is by using subforms with the child data in the subform.

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    robuisman,

    Instead of focusing on forms at the moment can you tell us in simple terms about a typical product and its subproducts.

    Just as you would tell someone who doesn't know your environment nor your business --what are the things involved, and how are they related to each other , and the steps involved--if you were to build me a Product that has subproducts.

  6. #6
    robuisman is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    3
    thanks for your answers. i try to be more clear.

    so i have a table ordercontents. that i fill with information from other tables. this is just a new non related table thay is filled with id, customerid, orderid, date, productid, description prices and current stock.

    with this tablr i calculate my stock in other querys

    the information that i put in this table is coming mainly from the table PRICELIST. in the prixelist there are all separate items like solarpanels, solarregulators, inverters, chargers etc.

    an example of a product that i would like to sell is a solarpanel kit. that will contain a solarpanel and a solarregulator. reason for this is that i also buy them as a kit for a cheaper price. when i buy them i can not sell a regulator without a solarpanel without messing up my stock so therefore i would like to have the kit contents automatically on the following rows so these next rows will contain the stock information when i sell it as a kit and then the kit will only contain pricinginformation. all the above i can manage. the only thing that i dont know how to manage (in a good way) is to add automatically the next rows when i choose the kit article number.

    i was thinkng about the after updating event that AJAX mentioned together with an update query. but as ajax mentioned already it will.maybe be a bit messy. so i am looking for a good way. would it maybe also be possible to put an IFF clausule in the event so it will only do the update job when a KIT article is choosen? and if so how should it look like? or do you maybe have a better suggestion?

    thanks a lot all, for your quick replies and help. I really appreciate that!

    and sorry for the typos. im typing on my phone now with my big fingers :-(

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You sell assembled items (eg solar panel kit) which is/are composed of 1 or more individual "thingys" (solar panel, regulator [and possibly 2 inverters and 5 chargers for illustration/concept]). So it would seem you have to identify the different assembled items you have for sale, and what thingys" and how many of each of these is needed for each of the assembled items. I believe there is more to your set up than 1 table of "pieces" you are calling products. To me, since these are not the same, they do not belong in the same table.

    You have some sort of hierarchy in your "product concept". Here is a link to a hierarchical structure that you may find useful at least in concept.

    Good luck.

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

Similar Threads

  1. Replies: 3
    Last Post: 03-22-2014, 04:32 PM
  2. Replies: 1
    Last Post: 07-01-2013, 08:21 AM
  3. Replies: 5
    Last Post: 12-19-2012, 07:26 PM
  4. Replies: 7
    Last Post: 07-02-2012, 10:50 PM
  5. Replies: 0
    Last Post: 11-30-2010, 12:51 PM

Tags for this Thread

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