Results 1 to 6 of 6
  1. #1
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376

    Query doesn't give desired results- cannot get more than 1 piece of data from a liked table

    Hello,



    The attached database contains a portion of a larger one. the tables are the ones giving me the issue, forms are also included to show how I am entering the data. The table represents various components that go into a product. Originally I had 6 fields of 1 of the components because up to 6 could be used. I took those out and put them into their own table to eliminate redundant fields, then created a link table to tie all 3 tables together.

    After placing a subform in the original form and checking the content of the new table, everything looks correct as far as the data goes.

    I cannot build a query that gives me the desired results. I have attached a spreadsheet of what I would like the results to look like. I want to be able to create a report based on the query.

    All other parts of the original database are working great. This is just the first time I have had to break out a table like this and create a link table and I am not sure how to set up the query correctly.

    The tblM22368-2 is the main one tblM22368LinkM13472 is the link table and tblM13472 is another table that I am pulling the M13472 Lot number ID from that gets placed in the tblM22368LinkM13472.

    Can someone please help me out with ths

    Thanks

    Dave

    Query Issue Spreadsheet.zip

    Query issue-Progress.zip

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why don't you give tables and fields more meaningful names? What does MXXXXX signify?

    There is no data in M13472ID field of the link table. It has been entered into M13472LotNum field. Combobox ControlSource is set to the wrong field. Why is the M13472LotNum field even in that table?
    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.

  3. #3
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    June7,

    The field names represent what goes into that field, each being a Name of a component. Each different component has a lot number which has to be traced

    M13472LotNum is in that table because I thought it needed to be, that may be part of my problem. I am not an expert at this by any means, just a novice trying to learn and create a database that serves a needed purpose. I want it to be done correctly which is why I rely one the good folks in these forums to try to get me off on the right foot and keep me on the straight and narrow.

    Each Table in the original database represents a single component, Each field in that table represents various other components that go into making the new Component. Some components are chemical in nature, some aren't.

    Thanks

    Dave

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Rats! Manufacturing/Assembly type of database. IMHO, one of the more difficult to construct. Very common topic in forum. Suggest you do a search and review some discussions.
    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.

  5. #5
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    June7,

    Oh fine, I'm in the wrong field of work so your just going to let me swing in the breeze. That may explain why I am having issues. I guess this is a good one to work with because when I get done, I will have learned a lot, already have. Thanks for trying my friend. It's great there is such a place for us novices to get help and guidance from those of you who have far more experience.

    Thanks

    Dave

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Hate to leave you swinging, but I have never built that type of database and from what little I know of would not want to. The complexity is probably why there are quite a few threads on the topic, mostly from novices trying to just get started on the design. Why rehash it all here?

    Sorry, I should have caught on from reading your first paragraph but was skimming.
    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.

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

Similar Threads

  1. Replies: 6
    Last Post: 03-24-2016, 12:56 PM
  2. Query not producing the desired data
    By WAVP375 in forum Access
    Replies: 7
    Last Post: 01-14-2016, 02:18 PM
  3. LIKE query does not give desired results
    By MarksinMO in forum Queries
    Replies: 3
    Last Post: 09-03-2015, 06:58 AM
  4. Replies: 1
    Last Post: 10-23-2014, 04:40 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