Matching Multiple Records

[Dave M]

I have a table that contains an account number, year end, version number,
dollar value one and dollar value two. The table contains about 10,000
account numbers and three different year ends. Each account/year end
generally has 2-3 versions, but can have over 10.

I need to find out:

For each account number and year end:

In each version number, I need to find out if there has been a change in
dollar value one. I also need a seperate result to find out dollar value two.

I.E. account 123, year end 2007.

# Acct YE Version $1 $2
1 Acct 123 YE2007 Version 4 10 15
2 Acct 123 YE2007 Version 3 11 15
3 Acct 123 YE2007 Version 2 11 14
4 Acct 123 YE2007 Version 1 11 14

I would need to flag #2 for the change in $1 and #3 for the change in $2. I
think that what I need the flag to do is update a table with the acct number
and year end and a check box to mark the checkbox.

Stumped, looking forward to some insight.

 

[Michel Walsh]

Compare the MIN and the MAX, for each group:


SELECT acct, ye, version,
MIN([$1]) = MAX([$1]) AS changeInDollarOne,
MIN([$2]) = MAX([$2]) AS changeInDollarTwo
FROM table
GROUP BY acct, ye, version




Vanderghast, Access MVP

 

[Dave M]

Thanks! I think that I came to a similar conclusion while I was waiting for
the response. What I ended up doing was a query with a group by acct and ye
and $1 (repeated for $2). I then did a seperate query with the count of the
numbers for each acct and ye. Same idea, one extra step.

Compare the MIN and the MAX, for each group:


SELECT acct, ye, version,
MIN([$1]) = MAX([$1]) AS changeInDollarOne,
MIN([$2]) = MAX([$2]) AS changeInDollarTwo
FROM table
GROUP BY acct, ye, version




Vanderghast, Access MVP

I have a table that contains an account number, year end, version number,
dollar value one and dollar value two. The table contains about 10,000
account numbers and three different year ends. Each account/year end
generally has 2-3 versions, but can have over 10.

I need to find out:

For each account number and year end:

In each version number, I need to find out if there has been a change in
dollar value one. I also need a seperate result to find out dollar value
two.

I.E. account 123, year end 2007.

# Acct YE Version $1 $2
1 Acct 123 YE2007 Version 4 10 15
2 Acct 123 YE2007 Version 3 11 15
3 Acct 123 YE2007 Version 2 11 14
4 Acct 123 YE2007 Version 1 11 14

I would need to flag #2 for the change in $1 and #3 for the change in $2.
I
think that what I need the flag to do is update a table with the acct
number
and year end and a check box to mark the checkbox.

Stumped, looking forward to some insight.