Results 1 to 6 of 6

Thread: Filtered Query not staying filtered when brought into another query?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jan 2014
    Location
    Maine
    Posts
    161
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Filtered Query not staying filtered when brought into another query?

    Hello and thank you in advance if you can help,
    I am using Excel 2019 on a PC. I am still quite new to Access, so I would appreciate any guidance to resolve this issue I am having, and I will apologize in advance if any of my explanation is confusing. I have a database that is 1,115,1867 records and I use that as my base for my queries for analyses (Base Data Output). After applying a series of criteria in one query (All valid records), I get the file down to all the "valid" records (100,873 records). I then would like to bring those records over as a filter to another query on the same base file so that I can exclude the records that were valid and have a base file to investigate multiple tests on "Invalid" data. When I bring the valid query in, and bring in a field I created "Valid", it applies that to all of the records (1,115,1867), not just the ones that were there in the final query for valid records (100,873).
    I hope this is making some sense.
    Thank you for your help,
    Maggie

  2. #2
    Board Regular
    Join Date
    Jan 2014
    Location
    Maine
    Posts
    161
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filtered Query not staying filtered when brought into another query?

    Solved.
    Well, for some reason when I brought in the field I had created in the valid records query to point to "VALID" records in hopes to filter on that, it would apply "VALID" to the whole record set instead of just the valid records, but if I brought in the Global Unique Identifier, the ID for the records, it would populate the cells according to only those that remained after the filter in that query. Then I could filter in the criteria for that column using Is Null, and it removed the records from my "Invalid" query. Not sure why it was doing what it was using the field I created, but I found a work around that worked.
    Just thought I would update my post, as I can't delete it, and I didn't want anyone wasting time helping. Though if you have any idea why it was doing what it was doing using my populated field, I would love to know.
    Best,
    Maggie

  3. #3
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,341
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Filtered Query not staying filtered when brought into another query?

    Your sql might be too long to analzye but if not you could post your original query for a look at it. Normally you do want to use an ID field as the way to keep track of "previously found" records. You probably made some rookie error in how you were treating your "Valid" field.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  4. #4
    Board Regular
    Join Date
    Jan 2014
    Location
    Maine
    Posts
    161
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filtered Query not staying filtered when brought into another query?

    xenou,
    Thanks for the response. Below is the SQL for the query I was trying to use the "valid" field from. I am sure it is a rookie maneuver, but I have no idea what. If I could learn how to do this better, it would help me with bringing the results of different tests in multiple queries more easily into the larger data. How would you create a field that applies only the the records left in a query after filters have been applied?
    Thanks,
    Maggie
    Code:
    SELECT 
    t.[GLOBAL UNIQUE IDENTIFIER],
    "VALID" AS [RECORD VALID],
    t.Flag_Results,
    t.Archive_Record_Condition,
    t.[Data Source],
    t.Species_Type,
    t.Invalid_Code_4_Species,
    t.Breeding_Type,
    t.[OBS_DAY-MONTH],
    t.[DAY-MONTH_VALUE],
    t.START_Date_VALUE,
    t.END_Date_VALUE,
    IIf([DAY-MONTH_VALUE]<[START_Date_VALUE] Or [DAY-MONTH_VALUE]>[END_Date_VALUE],"OUTSIDE","GOOD RECORD") AS OBS_DATE_ACCEPTANCE,
    t.Buffer_START_Date_VALUE,
    t.Buffer_END_Date_VALUE,
    t.CATEGORY,
    t.NEW_COMMON_NAME,
    t.[OBSERVATION DATE],
    t.[Species; BreedingCode],
    t.[BREEDING BIRD ATLAS CODE],
    t.[Breeding_Code_Value ],
    t.[Breeding_Category_Value_1-3],
    t.[BREEDING BIRD ATLAS CATEGORY],
    t.CLOBlockNa,
    t.CoordRegio,
    t.Modified_Block_Type,
    t.COUNTY,
    t.[OBSERVER ID],
    t.[SAMPLING EVENT IDENTIFIER],
    t.[GROUP IDENTIFIER],
    t.LOCALITY,
    t.[TRIP COMMENTS],
    t.APPROVED,
    t.REVIEWED,
    t.REASON,
    t.[PROTOCOL TYPE],
    t.[PROJECT CODE],
    t.[DURATION MINUTES],
    t.[NUMBER OBSERVERS],
    t.[SPECIES COMMENTS]
    FROM [eBird_ALL_DATA Query2 Base Data Output] t
    WHERE 
    (
    	((t.[Data Source]) Not In ("Small File")) 
    	AND 
    	((t.Species_Type) In ("BreedingBird_Maine")) 
    	AND 
    	(
    		(t.Breeding_Type) Is Not Null 
    		And (t.Breeding_Type)<>""
    	) 
    	AND ((IIf([DAY-MONTH_VALUE]<[START_Date_VALUE] Or [DAY-MONTH_VALUE]>[END_Date_VALUE],"OUTSIDE","GOOD RECORD")) In ("GOOD RECORD")) 
    	AND ((t.[BREEDING BIRD ATLAS CODE]) Is Not Null And (t.[BREEDING BIRD ATLAS CODE])<>"" And (t.[BREEDING BIRD ATLAS CODE])<>"F") 
    	AND ((t.[PROJECT CODE]) In ("EBIRD_ATL_ME"))
    );
    Last edited by xenou; May 13th, 2019 at 05:11 PM. Reason: Format SQL

  5. #5
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,341
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Filtered Query not staying filtered when brought into another query?

    The query looks fine as such. I'm not sure how exactly you'd be wanting to use it. Let me see if I can drum up an example.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  6. #6
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,341
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Filtered Query not staying filtered when brought into another query?

    Well, here's some thoughts,

    We assume a table called Table1 as such:
    --------------------------------------------
    | ID | Field1 | Field2 | Field3 |   Field4 |
    --------------------------------------------
    |  1 |      1 | a      |      0 | 1/1/2019 |
    |  2 |      2 | b      |        | 1/1/2019 |
    |  3 |      3 | c      |      0 | 1/1/2019 |
    |  4 |      9 | a      |      0 | 1/1/2019 |
    |  5 |      1 | a      |      0 | 1/1/2018 |
    |  6 |      2 | b      |      0 | 1/1/2019 |
    |  7 |      3 | x      |        | 1/1/2019 |
    |  8 |      9 | a      |      0 | 1/1/2019 |
    |  9 |      1 | b      |      0 | 1/1/2019 |
    | 10 |      2 | x      |      0 | 1/1/2019 |
    --------------------------------------------



    We can write a query similar to yours (with a lot less detail in the query but basically the same query structure-wise):
    Query1
    Code:
    select "Valid" as RecordType, t.ID, t.Field1, t.Field2, t.Field3
    from
    Table1 t
    where
    	Field1 in (1,2,3)
    	and Field2 in ('a', 'b', 'c')
    	and Field3 is not null
    	and Field4 >= dateserial(2019, 1, 1)

    And then we can use the filtered data for more queries:
    Query2
    Code:
    select * from Query1
    where Field1 = 2
    Query3
    Code:
    select * from Table1
    where ID in (select q1.ID from Query1 q1)
    Query4
    Code:
    select t1.* 
    from 
    	Table1 t1
    	inner join Query1 q1
    	on t1.ID = q1.ID
    where 
    	q1.Field2 = 'b'
    However, as you can see from the above, we generally need the ID field (which corresponds to your GUID field) in order to refer back to the original table. So that's what we see in Query3 and Query4 above. But if we only want fields directly from Query1 itself, then we can select without using the ID field (as in Query2 above) - a simple query on a query.

    We actually hardly ever need to use the "Valid" field from Query1. That's because every row in Query1 is marked "Valid". So there's no real advantage in it - you can just use Query1 the same without that field it and it's still all "Valid" rows. This would be different if Query1 stored "Valid" or "Invalid" results - you might use that field to get one type or the other in that case.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •