Results 1 to 9 of 9
  1. #1
    sukhjinder's Avatar
    sukhjinder is offline Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    43

    Query to identify new record

    I have a two table which stores data of main assembly and part numbers respectively and having 1:M relationship. All the original part numbers are serially associated with the main assembly. If one part of the main assembly is swapped with new one. how to create a query to get all the parts associated with the main assembly.
    Further i have include a date and fitment status in the part number table also to maintain a history of changed records.
    Moreover i also attached an image pertaining to the output of the query. In the attachment main assambly (1001) is related to various part num fitted 10 jan 15. if a worksman change the partnum 51290 (fitted on 10-1-15 and removed on 15-2-17) with 51297 (fitted on 15-2-17). How the query will display all the associated records of mainassm (1001) without REMOVED part.
    Thank you.



    Click image for larger version. 

Name:	fitment.PNG 
Views:	24 
Size:	6.9 KB 
ID:	26936

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    make a query,qsRemoved, that uses the query above,qsAll, and only pulls the REMOVED partnums.
    mainAssm, PartNum, REMOVED

    make a query,qsFittedOnly, that uses your query above,qsAll, AND join together with qsRemoved,
    join on MainAssm, partnum.
    set the joins to OUTER JOIN, show all records in qsAll
    bring down qsAll.MainAssm, qsAll.partnum, qsRemoved.PartNum
    set qsAll.PartNum to filter IS NULL.

    this should show your parts in qsAll, but NOT parts that were removed.

  3. #3
    sukhjinder's Avatar
    sukhjinder is offline Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    43
    partnum only fiited parts.zip

    sir i have tried by not able to fetch all the recent fitted records. Attachment is for your reference.

  4. #4
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    In your query, can't you just add <> "REMOVED" to the criteria line of fitmentsstatus column? Or am I not reading right what you are trying to do.

  5. #5
    sukhjinder's Avatar
    sukhjinder is offline Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    43
    Quote Originally Posted by Bulzie View Post
    In your query, can't you just add <> "REMOVED" to the criteria line of fitmentsstatus column? Or am I not reading right what you are trying to do.
    sir i have related main assembly from one table with the part number of other table. As i have shown in the picture that all part number are associated with the main assembly (1001). Due to malfunctioning the part number(51290) is removed on 15/2/17 with part number 51297 on the same date. how to create a query which will identify all the fitted only without part number 51290 (As 51290 has two entry 1. fitted on 10-1-15 2. removed on 15/2/17). Please suggest me if i need any modification in the table itself in order to run the query. Any idea???

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I tried what Bulzie suggested.

    If this what you are trying to do:
    Click image for larger version. 

Name:	Presentation1.jpg 
Views:	17 
Size:	12.5 KB 
ID:	26996

    I used this query - "qsall":
    Code:
    SELECT tblmainassy.mainassy_PK, tblpartnum.partnum, tblpartnum.fdate, tblfitment.status
    FROM tblmainassy INNER JOIN (tblfitment INNER JOIN tblpartnum ON tblfitment.fitmentstatus_PK = tblpartnum.fitmentsatus_FK) ON tblmainassy.mainassy_PK = tblpartnum.mainassy_FK
    WHERE (((tblmainassy.mainassy_PK)=1001) AND ((tblfitment.status)<>"removed"))
    ORDER BY tblpartnum.partnum, tblpartnum.fdate;

  7. #7
    sukhjinder's Avatar
    sukhjinder is offline Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    43
    This is fine but i want the output without 51290 part num ie all the record from tablemainassy but without removed parts. Ie this part 51290 is fitted on 10/1/15 but it is removed in the later stage. with yours query it is showing fitted on 10/1/15.

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You want part num 51290 gone?

    Maybe this? (see qsallWithoutMatching_qsremoved)
    Attached Files Attached Files

  9. #9
    sukhjinder's Avatar
    sukhjinder is offline Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    43
    Thanks a lot this is the actual result that i am finding. Many many

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

Similar Threads

  1. Identify new record and discard...
    By Joakim N in forum Programming
    Replies: 4
    Last Post: 10-26-2016, 07:09 AM
  2. Replies: 4
    Last Post: 01-25-2016, 01:53 PM
  3. Replies: 13
    Last Post: 11-08-2011, 11:57 AM
  4. Query to identify added dependents
    By brickballer in forum Queries
    Replies: 2
    Last Post: 03-17-2011, 01:22 PM
  5. Query to identify sequences of data
    By TheWolfster in forum Queries
    Replies: 13
    Last Post: 05-25-2010, 12:55 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
  •  
Other Forums: Microsoft Office Forums