Filtered Query not staying filtered when brought into another query?

[Maggie Barr]

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

 

[Maggie Barr]

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

 

[xenou]

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.

 

[Maggie Barr]

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

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"))
);

 

[xenou]

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.

 

[xenou]

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

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

select * from Query1
where Field1 = 2

Query3

select * from Table1
where ID in (select q1.ID from Query1 q1)

Query4

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.