I have a table in a accdb that consists of several columns. They include a social security number, several dates and monetary values. I am trying to query data in here ( there are over 600000 results in the accdb ) .
Social security number can appear once or several times in a database. The dates and the monetary values that are on the same line ( in a different column ) can be different, or not.
So let's say my table looks like this:
Ssn Date1 Date2 moneyvalue PostDate
123455 12-01-20 03-04-20 5.21 (A datettime value )
I am trying to do several things:
First I want to only select the ssn that appear at least twice in the database (or more).
From those results i want to only get the ones where date1 is equal to date2.
From those results i want to get the results where there are different values in moneyvalue per ssn. I want to compare the moneyvalue from the ssn to the money value from the first time this ssn appears in the database ( so the one with the oldest datetime in postDate) and post this ssn if they moneyvalue is different.
Is this possible? How would i go on about this? I have to do this from within ms access sql window, i can't export the database to mssql as it is protected.
So to sum it up:
I want to retrieve all ssn that appear twice or more in the database, where date1 is equal to date2, and where the monetary value in record x does not match the monetary value in the ssn with the oldest postDate.
Your question suggests aggegation and multiple having clauses:
select ssn
from mytable
group by ssn
having
count(*) > 1
and sum(iif(date1 = date2, 1, 0)) > 1
and count(distinct moneyvalue) > 1
Another interpretation is a where clause on condition date1 = date2:
select ssn
from mytable
where date1 = date2
group by ssn
having
count(*) > 1
and count(distinct moneyvalue) > 1
However both queries are not equivalent, and my understanding is that the first one is what you asked for.