Filtered Query not staying filtered when brought into another query?

Maggie Barr

Board Regular
Joined
Jan 28, 2014
Messages
188
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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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
 
Upvote 0
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.
 
Upvote 0
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 a moderator:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top