Query two criteria on one field, or something else..

[dreid1011]

Good afternoon,

I'm trying to find out if I can set up a query to pull data based on two criteria on a single field. I have a table containing all the properties in the county and taxes owing back to the mid 90's.

What I would like to do is query all the properties where they have taxes owing for the most current 4 years and only show those 4 years.

Properties with taxes owing for 3 or less years should be ignored, and properties with taxes owing for 5 or more should also be ignored.

I could just query on the year, but I would like to try to avoid pulling everything with 3 or less years owing.

Thank you.

 

[alansidman]

Assuming that there is a record for each year taxes owed, then in the criteria for the year field use the Between function.ie.

Between #1/1/2015# and #1/1/2011#

 

[dreid1011]

Assuming that there is a record for each year taxes owed, then in the criteria for the year field use the Between function.ie.

Between #1/1/2015# and #1/1/2011#

Yes, there are records for each year. Being that the data is stored as year only as a number, not a date, I adjusted the criteria to:

Between 2014 and 2019

The problem I am having with the year criteria is it will still pull records with only taxes owing for 1, 2 or 3 years and will pull records with taxes owing for 5, 6 or more years, but it only shows the years I have specified. That leaves me to double check all the account numbers and look it up in another program to verify the data. Which is what I would like to avoid.

The ideal criteria would be something like this:

Pull only accounts with exactly 4 years of taxes owing for the years 2015, 2016, 2017 and 2018.

 

[Joe4]

It is kind of hard to envision without seeing how your data is structured and what it looks like.
Can you post a sample?

 

[xenou]

I would probably resolve this information into a temp table as a first step.

1) get distinct {account, year} into a temp table A
2) get {account, count of year} into a temp table B (where year between x and y)

your result is now to simply select from temp table B where count = 4



* note that when I say temp table in MSAcess I just mean create a regular table to be used for temporary processing. Create such tables the normal way. Clear them (delete all records) as the very first thing you do each time you want to reevaluate your results.


** an alternative that might work (albeit a little ugly and possibly slow) would be a correlated exists clause at the end of whatever query you write:

select * from table T
where 
(
exists (select * from table T1 where T1.Account = T.Account and year(T1.OwedDate) = 2015)
and exists (select * from table T2 where T2.Account = T.Account and year(T2.OwedDate) = 2016)
and exists (select * from table T3 where T3.Account = T.Account and year(T3.OwedDate) = 2017)
and exists (select * from table T4 where T4.Account = T.Account and year(T4.OwedDate) = 2018)
)

 

[dreid1011]

It is kind of hard to envision without seeing how your data is structured and what it looks like.
Can you post a sample?

There is at least 1 record per account for each year since the account was created. Some go back as far as 1983.
Below:
Account A has taxes owing for only 2018. 2017 indicates taxes have been paid for that year. I want to ignore it.
Account B has taxes owing for only 2018, 2017, 2016, 2015. (not showing paid years where owing = 0)
Account C has taxes owing for more than 4 years and I want to ignore it.

Results for this sample should be all 4 records of account B where owing for 2015-2018 > 0.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Account
[/TD]
[TD]Tax Year
[/TD]
[TD]Taxes Owing
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]2018
[/TD]
[TD]100
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]2017
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]2018
[/TD]
[TD]100
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]2017
[/TD]
[TD]95
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]2016
[/TD]
[TD]90
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]2015
[/TD]
[TD]85
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]2018
[/TD]
[TD]100
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]2017
[/TD]
[TD]95
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]2016
[/TD]
[TD]90
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]2015
[/TD]
[TD]85
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]2014
[/TD]
[TD]80
[/TD]
[/TR]
</tbody>[/TABLE]

I would probably resolve this information into a temp table as a first step.

1) get distinct {account, year} into a temp table A
2) get {account, count of year} into a temp table B (where year between x and y)

your result is now to simply select from temp table B where count = 4



note that when I say temp table in MSAcess I just mean create a regular table to be used for temporary processing. Create such tables the normal way. Clear them (delete all records) as the very first thing you do each time you want to reevaluate your results.

Hrm, I think I did something similar when I exported the list to Excel. The table is already distinct by account/year, unless I misunderstand you. However, I think I now know what I should have been doing in Excel to gather the data easier. I've been shooting myself in the foot by limiting my query to those specific 4 years. I should have pulled ALL years, then counted where the amount owing is greater than 0.

I will go play and see what happens!

 

[james_lankford]

select 
  Account, 
  sum( [Taxes Owing] ) as [Total Owed] 
from 
  tbl 
where 
  Account in 
  (
    select 
      Account 
    from 
      tbl 
    where 
    (
      [Taxes Owing] <> 0
      or 
      [Taxes Owing] is not null
    ) 
    group by 
      Account
    having 
      count(*) = 4 
  )
group by 
  Account