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

    Cannot figure out to to write query to get desired results

    Hello all,

    I am trying to write a query to give me the actual PN listed rather than the ID number in a child column. The attached file has 2 tables, a tblPartMain and a tblSubAssemblyInfo.

    The tblSubAssemblyInfo contains aComponentID field as the PK and a PartentID Field and ChildID field. There is also a Qty Field and MyNotes field but I am not doing anything with those yet.

    I have written a query called Child Query that is getting the correct info and populating the datasheet properly for the parent but I cannot figure out how to get the MfrPartNumber to populate the ChildID as I want, I am only getting the ChildID to show.

    Can someone please assist and point me in the right direction?

    I have tried to use a SELECT statement in the criteria in place of ChildID but I don't know enough about SQL to get it correct, I always get an error that the statement ins't correct.



    The File is attached.

    Query Attempt.zip



    Thanks
    Dave

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Its not at all obvious what your query is meant to be showing

    The current one has multiple records for each part number

    The query below gives one record per part number (758 in all) but whether its what you want I've no idea

    Code:
    SELECT tblPartMain.MfrPartNumber, tblSubAssemblyInfo.ChildID
    FROM tblPartMain INNER JOIN tblSubAssemblyInfo ON tblPartMain.ID = tblSubAssemblyInfo.ComponentID;
    Saved as Child Query-CR in the attached

    If as I suspect, that's not correct, I suggest you give some examples of expected output
    Attached Files Attached Files
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

    What I want in the ChildID Column is the MfrPartNumber for that ChildID. If you look at the results of the Child Query, they are correct except the ChildID is actually the ID of that part, what I want is the MfrPartNumber for that ID. Ultimately I want to use this to populate a treeview control.

    If you look at the results of the Child Query, they are correct except that the Actual ChildID is showing and what I want is the MfrPartNumber for that ChildID. If you look at KABRSA-12 there are 8 different parts that go into it which are the correct ChildID's but I would like to have the MfrPartNumber associated with each of the parts from the tblPartMain.

    Line 1 from the Child Query shows the following

    KABRSA-12 870

    What I want is

    KABRSA-12 M23138 (This is the MfrPartNumber for ID 870 in tblPartMain)

    Does that make sense?

    Thanks

    Dave

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    ridders52 beat me.....

    Wasn't sure either, so I modified the dB. (yep... another one)
    This is my take...
    Attached Files Attached Files

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

    You folks are incredible, I like the way you presented that in the form, can I use that?

    You guys are so good at this and so quick too.

    That still doesn't solve my original issue on the query though. Am I trying to do something that cannot be done? I don't think so but maybe, I think it is just a question (or Fact) that I am not not familiar enough with SQL statements yet


    Thanks

    Dave

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Here you are Dave, think this should answer your original question.

    Cheers,
    Vlad
    Attached Files Attached Files

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

    Thats exactly what I was looking for, thank you very much.

    Thanks all

    Dave

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

Similar Threads

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