Results 1 to 14 of 14

Pulling different information from 3 seperate fields from on table

  1. #1
    Topflite66 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    67

    Pulling different information from 3 seperate fields from on table

    I have a table, tbl_VehicleOrders, that I use for orders. On the form that I use, Vehicle Order Form, I have 3 separate fields (Pkgs1, Pkgs2, Pkgs3) that I enter different packages or N/A, all coming from the same table, tbl_pkgs. I have a parameter query, Unit List by Model, that I run to see the specifics of each unit based on the vehicle Model which includes the 3 separate fields.



    For the rest of the fields that pull information from junction tables, I can add the field from the primary table so that the query displays the actual text instead of the PK value associated with the junction table. The problem I am running into however is with the 3 separate fields based on one table. I'm not sure how to get the query to display the text instead of the PK value.

    I have included a copy of my db. If you open the Unit List by Model query and then enter Edge in the parameter box the query will run. If you scroll to the right you can see that all the other fields contain the actual text associated with that particular field, except for (Pkgs1, Pkgs2, Pkgs3). If I add the Pkgs field from the tbl_Pkgs table the query will only display what is in Pkgs1. I need the query to display text associated with each of those PK values.

    Thank you.
    Attached Files Attached Files

  2. #2
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,310
    This sounds like a improperly stored data.
    If you have a variable number of dependent child records they should be in a child table, (tblVhclPakages ?)
    You can then store 1, 3 or 40 packages per vehicle without having to redesign everything!
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Topflite66 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    67
    I actually have a table, tbl_Pkgs that stores the data that the three combo boxes pull into the form and then saves to the tbl_VehicleOrders. All three fields pull from the same table. If I create 3 separate tables, so that each field pulls from each table, I will have a lot of duplication. Unless I am misunderstanding??

    I might not be explaining what I am looking for very well, sorry. If you have an opportunity to look at the upload that might help clarify. Thank you.

  4. #4
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,310
    You have stored your packages selections as 3 separate fields in you vehicle orders table.
    They should be a child record(s) of the order. That way you can have as many or few packages per order as you need.

    You would add them in a subform in the order form. You still select them from your packages table.
    So the table would have three fields

    OrdPackID - PK Autonumber - the record number.
    OrderID - FK links it back to the order.
    PkgsID - FK for the package selected.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    Topflite66 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    67
    Quote Originally Posted by Minty View Post
    You would add them in a subform in the order form. You still select them from your packages table.
    So the table would have three fields
    I think I understand now. If you're ok with it, I'll redesign the form and tables and upload it again to make sure I've done it correctly. Thank you.

  6. #6
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,310
    Excellent - Happy to give it a once over when you have made the changes.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #7
    Topflite66 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    67
    Quote Originally Posted by Minty View Post
    Excellent - Happy to give it a once over when you have made the changes.
    So I have the subform added and the new table set up. However, I must have something incorrect as I am having some issues.

    1. I'm not able to access the previously entered orders with the new Form/subform added. I added a record through the form and the combo box worked correctly to only allow me to add certain packages based on my previous selections. I am able to view that record but as I mentioned not the previously entered records. The table, tbl_VehicleOrders, seems to update correctly other than the Pkgs field does not contain any data. The data for the Pkgs combo box is all contained in the jct_VehicleOrders table which I am assuming is correct. Which leads me to my second issue.

    2. If I enter multiple items in the Pkgs combo box, the form does something kind of goofy. I understand that the record selector for the subform would show the number of entries per the viewed record, however the main record selector for the main form is doing the same thing. So it looks like I am browsing to a different record when I am actually viewing the same record. That will be very confusing. The Vehicle Orders table only shows one record however, and the junction table shows the correct number of entries based on the number of entries in the subform.

    Regarding the first issue, I am wondering if I need the PkgsID field in the tbl_VehicleOrders table? As no data is being entered into that field when I create a new record. If you could take a look and see what I have incorrect and give me some guidance that would be great. Once these issues are resolved then I will need to work on my original question, which was how to pull in the packages data into my query, which I'm thinking would be fairly simple to just add the new table as part of the query. Thank you.
    Attached Files Attached Files

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,482
    Open db and immediately get error "Navigation Form does not exist".

    Don't include jct_VehicleOrders table in the main form RecordSource, just tbl_VehicleOrders.

    Don't build a compound key in jct_VehicleOrders. Build a compound index on OrderID and PkgsID to prevent duplicate pairs. Including OrdPackID makes the triple combination unique and duplicate pairs could occur. A primary key in this table is only needed if there is a dependent table. Unique identifiers can have other purpose in some queries, but as is, don't see need at this time, although having the autonumber field doesn't hurt, as long as it is not included in compound key or index.

    Why is tblPkgs linked to both tbl_VehicleOrders and jct_VehicleOrders?

    This appears to be a manufacturing/assembly type of db, IMHO, one of the most difficult to build, along with family tree type. Good luck with project.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Topflite66 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    67
    Thank you for the suggestions June7. The copy I uploaded is a backup that I had to remove a lot of info, i.e. forms, reports, data so that I could upload the file. Once I get everything squared away in the backup I will make the changes to my working copy.

    I removed the jct_VehicleOrders table from the main form and then the rest of the orders were viewable.

    I also removed the keys but left the OrdPakID field for the autonumber option.

    As for why the tblPkgs was linked to both, it was a shot in the dark to get everything to work.

    This is actually a Vehicle Inventory db, and it has been very difficult to build. I've had a lot of help from you and several other people from this forum, which everyone has been great to work with. It's been a labor of love/hate for the last 1 1/2 years, working on it in my spare time at work. Famous last words, how hard can it be to build a db that keeps track of our new vehicle inventory.

    I'm finally at the point where I can start using it everyday. Just working on the reporting that I need, which is what prompted me to move away from using Excel, which I'm much more familiar with. But in this case was no longer serving my purposes.

    Thank you so much for the help.

  10. #10
    Topflite66 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    67
    So I've made all the adjustments but am still having an issue with the query I am working on. I deleted the Pkgs1, Pkgs2, Pkgs3 fields from the tbl_VehicleOrders. I then added a few fields, not including the packages field, to a parameter query I have been working on. When I run the query for say the F-150 parameter, I get the expected results, eight records listed.

    If I add the jct_VehicleOrders table to pull in the package fields and then run the query again for the f-150's, I get one record listed 4 times. This record is the only F-150 of the eight that has packages entered in the subform, 4 separate packages. I tried adding the Pkgs field from the tbl_Pkgs table but that just listed the same record 4 times and listed each package.

    Not sure where to go from here.
    Attached Files Attached Files

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,482
    With INNER JOIN this is correct output. It is not the 'same record'. It is the 4 records from jct_VehicleOrders that associate with the same parent record from tbl_VehicleOrders.

    What do you want to show?

    Advise not to use popup input parameters - can't validate user input - reference control on form for user input.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    Topflite66 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    67
    Quote Originally Posted by June7 View Post
    What do you want to show?
    I would like to be able to show each record, whether there are packages entered or not. If possible to display a dynamic field listing, that I can use for a report, based on the entries of the subform. For example, if an order would have 1 package there would be 1 record with 1 package field listed. If there were 4 packages entered in the subform, there be 1 record with 4 package fields listed.

    Hope this makes sense or is even possible.

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,482
    Change the join type for the link between jct_VehicleOrders and tbl_VehicleOrders.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    Topflite66 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    67
    Perfect. I was able to generate a report that I can tweak later. The main thing was getting the information to pull up. Thank you very much for your help.

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

Similar Threads

  1. Pulling up information
    By Accu-Grind in forum Forms
    Replies: 6
    Last Post: 04-27-2015, 12:08 PM
  2. Replies: 5
    Last Post: 04-26-2013, 09:16 PM
  3. Replies: 11
    Last Post: 04-22-2013, 04:21 PM
  4. Replies: 2
    Last Post: 05-26-2012, 10:39 AM
  5. Replies: 7
    Last Post: 03-30-2012, 09:09 AM

Posting Permissions

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