Results 1 to 6 of 6
  1. #1
    cbayless is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2014
    Location
    Tinley Park, IL
    Posts
    3

    Query database field and return a yes or no if criteria is met

    Often times I need to query data from 2 different tables. One table contains a serial number of a part with it's attributes and another table that holds test data of that same part at multiple stages of testing. Because test data can have the same serial number multiple time it returns the data on multiple lines.


    Post Thermal Test: IIf([dbo_SourcesBuilt]![SerialNoSource]=[dbo_SourceTests]![SerialNoSource] And [dbo_SourceTests]![SourceTestStage]="POST_THERMAL","Tested","No")

    What is returned is shown below. What need is to show is that is POST_THERMAL test exists return Tested if not return No in same field in the query.

    Thanks

    Serial GyroSerial Burn In Source Type FailureTypeID Post Thermal Test
    452700 454036 KVH Burn-in Standard 1750
    No
    452700 454036 KVH Burn-in Standard 1750
    Tested
    452712
    KVH Burn-in Standard 1750
    No
    452712
    KVH Burn-in Standard 1750
    Tested


  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What differentiates these records? Why are both results showing up? It sounds like the query is missing a key field, or perhaps there is a Date associated with the values.

    How could an item have a Post Thermal Test result of both No and Tested --- doesn't make any sense.

  3. #3
    cbayless is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2014
    Location
    Tinley Park, IL
    Posts
    3
    There are 2 test types "PIGTAIL" and "POST_THERMAL" so in both cases in sample shown both test were completed so they show twice the one that says No is the Pigtail test the one that shows Tested is the is the other. What I really need is what serial numbers have had the post thermal to show tested but if that test is not done yet to show not tested all on the same line.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What produced the sample you showed in post#1?
    What should have appeared in order to meet your requirements?

    It would be helpful if we could see your tables and relationships ---a jpg will do.
    Also could we see the full SQL of the query you are working with?

  5. #5
    cbayless is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2014
    Location
    Tinley Park, IL
    Posts
    3
    This is the problem that keeps popping up for me because different components have the same tests run to determine pass or fail after they are put through
    Stresses like Thermal, Shock or Vibe testing.

    SELECT dbo_ComponentsBuilt.Serial, dbo_GyroComponents.GyroSerial, IIf([dbo_SourcesBuilt]![SourceComment] Like "*Exalos Burned-in*","Exalos Burn-in","KVH Burn-in") AS [Burn In], IIf([SourceTypeCode] Like "*1%*", "1% 1750","Standard 1750") AS [Source Type], dbo_SourcesBuilt.FailureTypeID, IIf([dbo_SourcesBuilt]![SerialNoSource]=[dbo_SourceTests]![SerialNoSource] And [dbo_SourceTests]![SourceTestStage]="POST_THERMAL","Tested","No") AS [Post Thermal Test]
    FROM ((dbo_Link_Jobs_Rigs_ComponentsBuilt INNER JOIN dbo_ComponentsBuilt ON dbo_Link_Jobs_Rigs_ComponentsBuilt.Serial = dbo_ComponentsBuilt.Serial) INNER JOIN (dbo_SourcesBuilt LEFT JOIN dbo_GyroComponents ON dbo_SourcesBuilt.SerialNoSource = dbo_GyroComponents.SerialNoSource) ON dbo_ComponentsBuilt.Serial = dbo_SourcesBuilt.SerialNoSource) LEFT JOIN dbo_SourceTests ON dbo_SourcesBuilt.SerialNoSource = dbo_SourceTests.SerialNoSource
    WHERE (((dbo_SourcesBuilt.SourceComment) Like "*Burned-in*") AND ((dbo_Link_Jobs_Rigs_ComponentsBuilt.RigNo)=5 Or (dbo_Link_Jobs_Rigs_ComponentsBuilt.RigNo)=2) AND ((dbo_Link_Jobs_Rigs_ComponentsBuilt.Job_RigTypCod e)="SRCBUILDPT"));


    Click image for larger version. 

Name:	ScreenShot.jpg 
Views:	8 
Size:	258.1 KB 
ID:	15765

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Criteria depending on data in other records of same table is tricky. Requires subquery or domain aggregate function. The function approach can be slow.

    Still not clear about data structure. Are Pigtail and Post_Thermal 2 fields in table? Can't see the expression in query for [Post Thermal Test] constructed field. An image of the tables from Relationship Builder might have been more helpful.

    Query using DLookup something like:

    SELECT Serial, Nz(DLookup("[Post Thermal Test]", "table/queryname", "Serial=" & [Serial] & " AND [Post Thermal Test]='Tested'"), "No") AS TestStatus FROM table/query GROUP BY Serial;
    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: 01-12-2014, 03:11 AM
  2. Replies: 5
    Last Post: 05-01-2013, 11:39 AM
  3. Replies: 4
    Last Post: 03-23-2012, 01:18 PM
  4. Replies: 9
    Last Post: 03-09-2012, 02:55 PM
  5. Replies: 3
    Last Post: 08-15-2011, 10:06 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