Results 1 to 9 of 9

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
    63

    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 Hacker
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,030
    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
    63
    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 Hacker
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,030
    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
    63
    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 Hacker
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,030
    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
    63
    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 online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    46,082
    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.
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  9. #9
    Topflite66 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    63
    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.

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, 11:08 AM
  2. Replies: 5
    Last Post: 04-26-2013, 08:16 PM
  3. Replies: 11
    Last Post: 04-22-2013, 03:21 PM
  4. Replies: 2
    Last Post: 05-26-2012, 09:39 AM
  5. Replies: 7
    Last Post: 03-30-2012, 08: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