identify semi-duplicate records, and amend elements of those recor

[Guest]

I am trying to (i) take an existing table, scan it, and identify records
where 4 particular fields match and the sum of another field is zero. and
then (ii) update a different field on both/any records that qualify

I am trying to learn how to build part (i) by using a Find Duplicates query,
hoping I could then take the expression, amend it and insert it into the
field to be used in an IIF statement within an Update Table query...but
that's getting ahead of myself.

The table id Edit Transactions.
The four fields to match are AAA, BBB, CCC and DDD
The field whose sum should be 0 is EEE

Anyways, this is what the Find Duplicates query produced:

In (SELECT [AAA] FROM [Edit Transactions] As Tmp GROUP BY
[AAA],[BaBB],[CCC],[DDD],[EEE] HAVING Count(*)>1 And [BBB] = [Edit
Transactions].[BBB] And [CCC] = [Edit Transactions].[CCC] And [DDD] = [Edit
Transactions].[DDD] And [EEE] = [Edit Transactions].[EEE])

Doesn't work obviously...it matches on all 5 fields instead, not bad, but I
cannot amend the last statement to

[EEE] = -1*[Edit Transactions].[EEE]
or
[EEE] + [Edit Transactions].[EEE] = 0

or anything to make it work...and reading the other posts on Duplicates
querying is difficult so far...

Any ideas, please let me know.

Txs,
Tom

 

[Guest]

FYI...I also tried another intuitive idea...created a new field called FFF
which is equal to -EEE...and then tried to make the last statmente

[EEE] = [Edit Transactions].[FFF]

but it doesn't like that either. Obviously I don't understand the syntax
well enough to tinker with this...

 

[John Spencer]

I think what you want is the following. This should only return a possible
match if the Sum of EEE is zero for the group. Then the match will be
determined on the first four fields

In (SELECT [AAA] FROM [Edit Transactions] As Tmp
GROUP BY [AAA],[BBB],[CCC],[DDD]
HAVING Count(*)>1 And
Sum(EEE) = 0 And
[BBB] = [Edit Transactions].[BBB] And
[CCC] = [Edit Transactions].[CCC] And
[DDD] = [Edit Transactions].[DDD])

 

[Guest]

I have inserted this statement into the Criteria for a Query, under the AAA
column…but the response is “Syntax Error in query expresion "((([Edit
Transactions].[AAA] IN...â€.

Maybe the problem is where I have used it?

Here is exactly what I used...and sorry, but the simplified field names are
now gone....

In (SELECT [PORTFOLIO NAME] FROM [Edit Transactions] As Tmp
GROUP BY [PORTFOLIO NAME],[BACKOFFICE PROCESS DATE],[BACKOFFICE ENTRY
CODE],[INSTRUMENT DESCRIPTION]
HAVING Count(*)>1 And
Sum(HH CCY CFAMT) = 0 And
[BACKOFFICE PROCESS DATE] = [Edit Transactions].[BACKOFFICE PROCESS DATE] And
[BACKOFFICE ENTRY CODE] = [Edit Transactions].[BACKOFFICE ENTRY CODE] And
[INSTRUMENT DESCRIPTION] = [Edit Transactions].[INSTRUMENT DESCRIPTION])

I think what you want is the following. This should only return a possible
match if the Sum of EEE is zero for the group. Then the match will be
determined on the first four fields

In (SELECT [AAA] FROM [Edit Transactions] As Tmp
GROUP BY [AAA],[BBB],[CCC],[DDD]
HAVING Count(*)>1 And
Sum(EEE) = 0 And
[BBB] = [Edit Transactions].[BBB] And
[CCC] = [Edit Transactions].[CCC] And
[DDD] = [Edit Transactions].[DDD])

I am trying to (i) take an existing table, scan it, and identify records
where 4 particular fields match and the sum of another field is zero. and
then (ii) update a different field on both/any records that qualify

I am trying to learn how to build part (i) by using a Find Duplicates
query,
hoping I could then take the expression, amend it and insert it into the
field to be used in an IIF statement within an Update Table query...but
that's getting ahead of myself.

The table id Edit Transactions.
The four fields to match are AAA, BBB, CCC and DDD
The field whose sum should be 0 is EEE

Anyways, this is what the Find Duplicates query produced:

In (SELECT [AAA] FROM [Edit Transactions] As Tmp GROUP BY
[AAA],[BaBB],[CCC],[DDD],[EEE] HAVING Count(*)>1 And [BBB] = [Edit
Transactions].[BBB] And [CCC] = [Edit Transactions].[CCC] And [DDD] =
[Edit
Transactions].[DDD] And [EEE] = [Edit Transactions].[EEE])

Doesn't work obviously...it matches on all 5 fields instead, not bad, but
I
cannot amend the last statement to

[EEE] = -1*[Edit Transactions].[EEE]
or
[EEE] + [Edit Transactions].[EEE] = 0

or anything to make it work...and reading the other posts on Duplicates
querying is difficult so far...

Any ideas, please let me know.

Txs,
Tom

 

[John Spencer]

AUUUGH!!!! Names with spaces AUUGGHH!!!!

Ok, that's out of my system.

Anytime you have spaces in a field name or table name you HAVE to surround
the name with [] or you will get an error. You are missing the [] in the
following
Sum(HH CCY CFAMT) = 0
which should be
Sum([HH CCY CFAMT]) = 0

In (SELECT [PORTFOLIO NAME] FROM [Edit Transactions] As Tmp
GROUP BY [PORTFOLIO NAME],[BACKOFFICE PROCESS DATE],[BACKOFFICE ENTRY
CODE],[INSTRUMENT DESCRIPTION]
HAVING Count(*)>1 And
Sum([HH CCY CFAMT]) = 0 And
[BACKOFFICE PROCESS DATE] = [Edit Transactions].[BACKOFFICE PROCESS DATE]
And
[BACKOFFICE ENTRY CODE] = [Edit Transactions].[BACKOFFICE ENTRY CODE] And
[INSTRUMENT DESCRIPTION] = [Edit Transactions].[INSTRUMENT DESCRIPTION])

Hope this works.

I have inserted this statement into the Criteria for a Query, under the AAA
column.but the response is "Syntax Error in query expresion "((([Edit
Transactions].[AAA] IN...".

Maybe the problem is where I have used it?

Here is exactly what I used...and sorry, but the simplified field names
are
now gone....

In (SELECT [PORTFOLIO NAME] FROM [Edit Transactions] As Tmp
GROUP BY [PORTFOLIO NAME],[BACKOFFICE PROCESS DATE],[BACKOFFICE ENTRY
CODE],[INSTRUMENT DESCRIPTION]
HAVING Count(*)>1 And
Sum(HH CCY CFAMT) = 0 And
[BACKOFFICE PROCESS DATE] = [Edit Transactions].[BACKOFFICE PROCESS DATE]
And
[BACKOFFICE ENTRY CODE] = [Edit Transactions].[BACKOFFICE ENTRY CODE] And
[INSTRUMENT DESCRIPTION] = [Edit Transactions].[INSTRUMENT DESCRIPTION])

I think what you want is the following. This should only return a
possible
match if the Sum of EEE is zero for the group. Then the match will be
determined on the first four fields

In (SELECT [AAA] FROM [Edit Transactions] As Tmp
GROUP BY [AAA],[BBB],[CCC],[DDD]
HAVING Count(*)>1 And
Sum(EEE) = 0 And
[BBB] = [Edit Transactions].[BBB] And
[CCC] = [Edit Transactions].[CCC] And
[DDD] = [Edit Transactions].[DDD])

I am trying to (i) take an existing table, scan it, and identify records
where 4 particular fields match and the sum of another field is zero.
and
then (ii) update a different field on both/any records that qualify

I am trying to learn how to build part (i) by using a Find Duplicates
query,
hoping I could then take the expression, amend it and insert it into
the
field to be used in an IIF statement within an Update Table query...but
that's getting ahead of myself.

The table id Edit Transactions.
The four fields to match are AAA, BBB, CCC and DDD
The field whose sum should be 0 is EEE

Anyways, this is what the Find Duplicates query produced:

In (SELECT [AAA] FROM [Edit Transactions] As Tmp GROUP BY
[AAA],[BaBB],[CCC],[DDD],[EEE] HAVING Count(*)>1 And [BBB] = [Edit
Transactions].[BBB] And [CCC] = [Edit Transactions].[CCC] And [DDD] =
[Edit
Transactions].[DDD] And [EEE] = [Edit Transactions].[EEE])

Doesn't work obviously...it matches on all 5 fields instead, not bad,
but
I
cannot amend the last statement to

[EEE] = -1*[Edit Transactions].[EEE]
or
[EEE] + [Edit Transactions].[EEE] = 0

or anything to make it work...and reading the other posts on Duplicates
querying is difficult so far...

Any ideas, please let me know.

Txs,
Tom

 

[Guest]

sorry about that...I haven't done enough Access work to have this pounded
into my skull, as you obviously have.

So, I added the []

The full expression is:

In (SELECT [PORTFOLIO NAME] FROM [Edit Transactions] As Tmp
GROUP BY [PORTFOLIO NAME],[BACKOFFICE PROCESS DATE],[BACKOFFICE ENTRY
CODE],[INSTRUMENT DESCRIPTION]
HAVING Count(*)>1 And
Sum([HH CCY CFAMT]) = 0 And
[BACKOFFICE PROCESS DATE] = [Edit Transactions].[BACKOFFICE PROCESS DATE]
And
[BACKOFFICE ENTRY CODE] = [Edit Transactions].[BACKOFFICE ENTRY CODE] And
[INSTRUMENT DESCRIPTION] = [Edit Transactions].[INSTRUMENT DESCRIPTION])

There's a new error mssg upon trying to execute the query.

You tried to execute a query that does not include the specified expression
'Count(*)>1 and Sum([HH CCy CFAmt])=0 And...<<remainder of expression>>' as
part of an aggregate function.

If it is a factor...I am typing this expression inside the Criteria for the
column Portfolio Name, which is taken from Edit transactions table, and part
of a Find Duplicates wizarded query...

One other thing I wonder about...each field that is being compared for dupes
uses the format [AAA] = [Edit transactions].[AAA]...this seems like a method
of taking an individual record's value of AAA and then comparing it to every
other record in that table. But when you look at the SUM criterion, it
doesn't utilise that same structure.

Thanks very much for the assistance,
Tom

AUUUGH!!!! Names with spaces AUUGGHH!!!!

Ok, that's out of my system.

Anytime you have spaces in a field name or table name you HAVE to surround
the name with [] or you will get an error. You are missing the [] in the
following
Sum(HH CCY CFAMT) = 0
which should be
Sum([HH CCY CFAMT]) = 0

In (SELECT [PORTFOLIO NAME] FROM [Edit Transactions] As Tmp
GROUP BY [PORTFOLIO NAME],[BACKOFFICE PROCESS DATE],[BACKOFFICE ENTRY
CODE],[INSTRUMENT DESCRIPTION]
HAVING Count(*)>1 And
Sum([HH CCY CFAMT]) = 0 And
[BACKOFFICE PROCESS DATE] = [Edit Transactions].[BACKOFFICE PROCESS DATE]
And
[BACKOFFICE ENTRY CODE] = [Edit Transactions].[BACKOFFICE ENTRY CODE] And
[INSTRUMENT DESCRIPTION] = [Edit Transactions].[INSTRUMENT DESCRIPTION])

Hope this works.

I have inserted this statement into the Criteria for a Query, under the AAA
column.but the response is "Syntax Error in query expresion "((([Edit
Transactions].[AAA] IN...".

Maybe the problem is where I have used it?

Here is exactly what I used...and sorry, but the simplified field names
are
now gone....

In (SELECT [PORTFOLIO NAME] FROM [Edit Transactions] As Tmp
GROUP BY [PORTFOLIO NAME],[BACKOFFICE PROCESS DATE],[BACKOFFICE ENTRY
CODE],[INSTRUMENT DESCRIPTION]
HAVING Count(*)>1 And
Sum(HH CCY CFAMT) = 0 And
[BACKOFFICE PROCESS DATE] = [Edit Transactions].[BACKOFFICE PROCESS DATE]
And
[BACKOFFICE ENTRY CODE] = [Edit Transactions].[BACKOFFICE ENTRY CODE] And
[INSTRUMENT DESCRIPTION] = [Edit Transactions].[INSTRUMENT DESCRIPTION])

I think what you want is the following. This should only return a
possible
match if the Sum of EEE is zero for the group. Then the match will be
determined on the first four fields

In (SELECT [AAA] FROM [Edit Transactions] As Tmp
GROUP BY [AAA],[BBB],[CCC],[DDD]
HAVING Count(*)>1 And
Sum(EEE) = 0 And
[BBB] = [Edit Transactions].[BBB] And
[CCC] = [Edit Transactions].[CCC] And
[DDD] = [Edit Transactions].[DDD])

I am trying to (i) take an existing table, scan it, and identify records
where 4 particular fields match and the sum of another field is zero.
and
then (ii) update a different field on both/any records that qualify

I am trying to learn how to build part (i) by using a Find Duplicates
query,
hoping I could then take the expression, amend it and insert it into
the
field to be used in an IIF statement within an Update Table query...but
that's getting ahead of myself.

The table id Edit Transactions.
The four fields to match are AAA, BBB, CCC and DDD
The field whose sum should be 0 is EEE

Anyways, this is what the Find Duplicates query produced:

In (SELECT [AAA] FROM [Edit Transactions] As Tmp GROUP BY
[AAA],[BaBB],[CCC],[DDD],[EEE] HAVING Count(*)>1 And [BBB] = [Edit
Transactions].[BBB] And [CCC] = [Edit Transactions].[CCC] And [DDD] =
[Edit
Transactions].[DDD] And [EEE] = [Edit Transactions].[EEE])

Doesn't work obviously...it matches on all 5 fields instead, not bad,
but
I
cannot amend the last statement to

[EEE] = -1*[Edit Transactions].[EEE]
or
[EEE] + [Edit Transactions].[EEE] = 0

or anything to make it work...and reading the other posts on Duplicates
querying is difficult so far...

Any ideas, please let me know.

Txs,
Tom

 

[Guest]

Wait a minute...it does actually work. I cannot get the error mssg to come
up, and the query is working properly. John, thank you once again.

sorry about that...I haven't done enough Access work to have this pounded
into my skull, as you obviously have.

So, I added the []

The full expression is:

In (SELECT [PORTFOLIO NAME] FROM [Edit Transactions] As Tmp
GROUP BY [PORTFOLIO NAME],[BACKOFFICE PROCESS DATE],[BACKOFFICE ENTRY
CODE],[INSTRUMENT DESCRIPTION]
HAVING Count(*)>1 And
Sum([HH CCY CFAMT]) = 0 And
[BACKOFFICE PROCESS DATE] = [Edit Transactions].[BACKOFFICE PROCESS DATE]
And
[BACKOFFICE ENTRY CODE] = [Edit Transactions].[BACKOFFICE ENTRY CODE] And
[INSTRUMENT DESCRIPTION] = [Edit Transactions].[INSTRUMENT DESCRIPTION])

There's a new error mssg upon trying to execute the query.

You tried to execute a query that does not include the specified expression
'Count(*)>1 and Sum([HH CCy CFAmt])=0 And...<<remainder of expression>>' as
part of an aggregate function.

If it is a factor...I am typing this expression inside the Criteria for the
column Portfolio Name, which is taken from Edit transactions table, and part
of a Find Duplicates wizarded query...

One other thing I wonder about...each field that is being compared for dupes
uses the format [AAA] = [Edit transactions].[AAA]...this seems like a method
of taking an individual record's value of AAA and then comparing it to every
other record in that table. But when you look at the SUM criterion, it
doesn't utilise that same structure.

Thanks very much for the assistance,
Tom

AUUUGH!!!! Names with spaces AUUGGHH!!!!

Ok, that's out of my system.

Anytime you have spaces in a field name or table name you HAVE to surround
the name with [] or you will get an error. You are missing the [] in the
following
Sum(HH CCY CFAMT) = 0
which should be
Sum([HH CCY CFAMT]) = 0

In (SELECT [PORTFOLIO NAME] FROM [Edit Transactions] As Tmp
GROUP BY [PORTFOLIO NAME],[BACKOFFICE PROCESS DATE],[BACKOFFICE ENTRY
CODE],[INSTRUMENT DESCRIPTION]
HAVING Count(*)>1 And
Sum([HH CCY CFAMT]) = 0 And
[BACKOFFICE PROCESS DATE] = [Edit Transactions].[BACKOFFICE PROCESS DATE]
And
[BACKOFFICE ENTRY CODE] = [Edit Transactions].[BACKOFFICE ENTRY CODE] And
[INSTRUMENT DESCRIPTION] = [Edit Transactions].[INSTRUMENT DESCRIPTION])

Hope this works.

I have inserted this statement into the Criteria for a Query, under the AAA
column.but the response is "Syntax Error in query expresion "((([Edit
Transactions].[AAA] IN...".

Maybe the problem is where I have used it?

Here is exactly what I used...and sorry, but the simplified field names
are
now gone....

In (SELECT [PORTFOLIO NAME] FROM [Edit Transactions] As Tmp
GROUP BY [PORTFOLIO NAME],[BACKOFFICE PROCESS DATE],[BACKOFFICE ENTRY
CODE],[INSTRUMENT DESCRIPTION]
HAVING Count(*)>1 And
Sum(HH CCY CFAMT) = 0 And
[BACKOFFICE PROCESS DATE] = [Edit Transactions].[BACKOFFICE PROCESS DATE]
And
[BACKOFFICE ENTRY CODE] = [Edit Transactions].[BACKOFFICE ENTRY CODE] And
[INSTRUMENT DESCRIPTION] = [Edit Transactions].[INSTRUMENT DESCRIPTION])





:

I think what you want is the following. This should only return a
possible
match if the Sum of EEE is zero for the group. Then the match will be
determined on the first four fields

In (SELECT [AAA] FROM [Edit Transactions] As Tmp
GROUP BY [AAA],[BBB],[CCC],[DDD]
HAVING Count(*)>1 And
Sum(EEE) = 0 And
[BBB] = [Edit Transactions].[BBB] And
[CCC] = [Edit Transactions].[CCC] And
[DDD] = [Edit Transactions].[DDD])

I am trying to (i) take an existing table, scan it, and identify records
where 4 particular fields match and the sum of another field is zero.
and
then (ii) update a different field on both/any records that qualify

I am trying to learn how to build part (i) by using a Find Duplicates
query,
hoping I could then take the expression, amend it and insert it into
the
field to be used in an IIF statement within an Update Table query...but
that's getting ahead of myself.

The table id Edit Transactions.
The four fields to match are AAA, BBB, CCC and DDD
The field whose sum should be 0 is EEE

Anyways, this is what the Find Duplicates query produced:

In (SELECT [AAA] FROM [Edit Transactions] As Tmp GROUP BY
[AAA],[BaBB],[CCC],[DDD],[EEE] HAVING Count(*)>1 And [BBB] = [Edit
Transactions].[BBB] And [CCC] = [Edit Transactions].[CCC] And [DDD] =
[Edit
Transactions].[DDD] And [EEE] = [Edit Transactions].[EEE])

Doesn't work obviously...it matches on all 5 fields instead, not bad,
but
I
cannot amend the last statement to

[EEE] = -1*[Edit Transactions].[EEE]
or
[EEE] + [Edit Transactions].[EEE] = 0

or anything to make it work...and reading the other posts on Duplicates
querying is difficult so far...

Any ideas, please let me know.

Txs,
Tom

 

[John Spencer]

Good, I was sitting here trying to figure out what I had wrong. I was giving
up when I saw this message. You are very welcome.

Wait a minute...it does actually work. I cannot get the error mssg to come
up, and the query is working properly. John, thank you once again.

sorry about that...I haven't done enough Access work to have this pounded
into my skull, as you obviously have.

So, I added the []

The full expression is:

In (SELECT [PORTFOLIO NAME] FROM [Edit Transactions] As Tmp
GROUP BY [PORTFOLIO NAME],[BACKOFFICE PROCESS DATE],[BACKOFFICE ENTRY
CODE],[INSTRUMENT DESCRIPTION]
HAVING Count(*)>1 And
Sum([HH CCY CFAMT]) = 0 And
[BACKOFFICE PROCESS DATE] = [Edit Transactions].[BACKOFFICE PROCESS DATE]
And
[BACKOFFICE ENTRY CODE] = [Edit Transactions].[BACKOFFICE ENTRY CODE] And
[INSTRUMENT DESCRIPTION] = [Edit Transactions].[INSTRUMENT DESCRIPTION])

There's a new error mssg upon trying to execute the query.

You tried to execute a query that does not include the specified expression
'Count(*)>1 and Sum([HH CCy CFAmt])=0 And...<<remainder of expression>>' as
part of an aggregate function.

If it is a factor...I am typing this expression inside the Criteria for the
column Portfolio Name, which is taken from Edit transactions table, and part
of a Find Duplicates wizarded query...

One other thing I wonder about...each field that is being compared for dupes
uses the format [AAA] = [Edit transactions].[AAA]...this seems like a method
of taking an individual record's value of AAA and then comparing it to every
other record in that table. But when you look at the SUM criterion, it
doesn't utilise that same structure.

Thanks very much for the assistance,
Tom

AUUUGH!!!! Names with spaces AUUGGHH!!!!

Ok, that's out of my system.

Anytime you have spaces in a field name or table name you HAVE to surround
the name with [] or you will get an error. You are missing the [] in the
following
Sum(HH CCY CFAMT) = 0
which should be
Sum([HH CCY CFAMT]) = 0

In (SELECT [PORTFOLIO NAME] FROM [Edit Transactions] As Tmp
GROUP BY [PORTFOLIO NAME],[BACKOFFICE PROCESS DATE],[BACKOFFICE ENTRY
CODE],[INSTRUMENT DESCRIPTION]
HAVING Count(*)>1 And
Sum([HH CCY CFAMT]) = 0 And
[BACKOFFICE PROCESS DATE] = [Edit Transactions].[BACKOFFICE PROCESS DATE]
And
[BACKOFFICE ENTRY CODE] = [Edit Transactions].[BACKOFFICE ENTRY CODE] And
[INSTRUMENT DESCRIPTION] = [Edit Transactions].[INSTRUMENT DESCRIPTION])

Hope this works.


I have inserted this statement into the Criteria for a Query, under the AAA
column.but the response is "Syntax Error in query expresion "((([Edit
Transactions].[AAA] IN...".

Maybe the problem is where I have used it?

Here is exactly what I used...and sorry, but the simplified field names
are
now gone....

In (SELECT [PORTFOLIO NAME] FROM [Edit Transactions] As Tmp
GROUP BY [PORTFOLIO NAME],[BACKOFFICE PROCESS DATE],[BACKOFFICE ENTRY
CODE],[INSTRUMENT DESCRIPTION]
HAVING Count(*)>1 And
Sum(HH CCY CFAMT) = 0 And
[BACKOFFICE PROCESS DATE] = [Edit Transactions].[BACKOFFICE PROCESS DATE]
And
[BACKOFFICE ENTRY CODE] = [Edit Transactions].[BACKOFFICE ENTRY CODE] And
[INSTRUMENT DESCRIPTION] = [Edit Transactions].[INSTRUMENT DESCRIPTION])





:

I think what you want is the following. This should only return a
possible
match if the Sum of EEE is zero for the group. Then the match will be
determined on the first four fields

In (SELECT [AAA] FROM [Edit Transactions] As Tmp
GROUP BY [AAA],[BBB],[CCC],[DDD]
HAVING Count(*)>1 And
Sum(EEE) = 0 And
[BBB] = [Edit Transactions].[BBB] And
[CCC] = [Edit Transactions].[CCC] And
[DDD] = [Edit Transactions].[DDD])

I am trying to (i) take an existing table, scan it, and identify records
where 4 particular fields match and the sum of another field is zero.
and
then (ii) update a different field on both/any records that qualify

I am trying to learn how to build part (i) by using a Find Duplicates
query,
hoping I could then take the expression, amend it and insert it into
the
field to be used in an IIF statement within an Update Table query...but
that's getting ahead of myself.

The table id Edit Transactions.
The four fields to match are AAA, BBB, CCC and DDD
The field whose sum should be 0 is EEE

Anyways, this is what the Find Duplicates query produced:

In (SELECT [AAA] FROM [Edit Transactions] As Tmp GROUP BY
[AAA],[BaBB],[CCC],[DDD],[EEE] HAVING Count(*)>1 And [BBB] = [Edit
Transactions].[BBB] And [CCC] = [Edit Transactions].[CCC] And [DDD] =
[Edit
Transactions].[DDD] And [EEE] = [Edit Transactions].[EEE])

Doesn't work obviously...it matches on all 5 fields instead, not bad,
but
I
cannot amend the last statement to

[EEE] = -1*[Edit Transactions].[EEE]
or
[EEE] + [Edit Transactions].[EEE] = 0

or anything to make it work...and reading the other posts on Duplicates
querying is difficult so far...

Any ideas, please let me know.

Txs,
Tom

 

[Guest]

There may be a problem...

The query seems to missing some of the duplicate records...or at least the
dupes according to me. ;-)

A review of the resulting reports clearly shows that some records which that
select should identify as dupes...and apply a "washed" attribute...are not
being updated.

Within the exact same family of records, other pairs of records have been
properly washed.

I have not been able to find the reason why...I have built a query to mimic
the select statement, and filled in specific criterion by criterion, to see
if the records I expect to match do indeed have matching values on all the
Select statement criterion. And they seem to...but when it gets fired
up...they don't get identified.

Any suggestions on examining "hidden" attributes or other areas to
investigate?

Tks,
Tom

Good, I was sitting here trying to figure out what I had wrong. I was giving
up when I saw this message. You are very welcome.

Wait a minute...it does actually work. I cannot get the error mssg to come
up, and the query is working properly. John, thank you once again.

sorry about that...I haven't done enough Access work to have this pounded
into my skull, as you obviously have.

So, I added the []

The full expression is:

In (SELECT [PORTFOLIO NAME] FROM [Edit Transactions] As Tmp
GROUP BY [PORTFOLIO NAME],[BACKOFFICE PROCESS DATE],[BACKOFFICE ENTRY
CODE],[INSTRUMENT DESCRIPTION]
HAVING Count(*)>1 And
Sum([HH CCY CFAMT]) = 0 And
[BACKOFFICE PROCESS DATE] = [Edit Transactions].[BACKOFFICE PROCESS DATE]
And
[BACKOFFICE ENTRY CODE] = [Edit Transactions].[BACKOFFICE ENTRY CODE] And
[INSTRUMENT DESCRIPTION] = [Edit Transactions].[INSTRUMENT DESCRIPTION])

There's a new error mssg upon trying to execute the query.

You tried to execute a query that does not include the specified expression
'Count(*)>1 and Sum([HH CCy CFAmt])=0 And...<<remainder of expression>>' as
part of an aggregate function.

If it is a factor...I am typing this expression inside the Criteria for the
column Portfolio Name, which is taken from Edit transactions table, and part
of a Find Duplicates wizarded query...

One other thing I wonder about...each field that is being compared for dupes
uses the format [AAA] = [Edit transactions].[AAA]...this seems like a method
of taking an individual record's value of AAA and then comparing it to every
other record in that table. But when you look at the SUM criterion, it
doesn't utilise that same structure.

Thanks very much for the assistance,
Tom

:

AUUUGH!!!! Names with spaces AUUGGHH!!!!

Ok, that's out of my system.

Anytime you have spaces in a field name or table name you HAVE to surround
the name with [] or you will get an error. You are missing the [] in the
following
Sum(HH CCY CFAMT) = 0
which should be
Sum([HH CCY CFAMT]) = 0

In (SELECT [PORTFOLIO NAME] FROM [Edit Transactions] As Tmp
GROUP BY [PORTFOLIO NAME],[BACKOFFICE PROCESS DATE],[BACKOFFICE ENTRY
CODE],[INSTRUMENT DESCRIPTION]
HAVING Count(*)>1 And
Sum([HH CCY CFAMT]) = 0 And
[BACKOFFICE PROCESS DATE] = [Edit Transactions].[BACKOFFICE PROCESS DATE]
And
[BACKOFFICE ENTRY CODE] = [Edit Transactions].[BACKOFFICE ENTRY CODE] And
[INSTRUMENT DESCRIPTION] = [Edit Transactions].[INSTRUMENT DESCRIPTION])

Hope this works.


I have inserted this statement into the Criteria for a Query, under the AAA
column.but the response is "Syntax Error in query expresion "((([Edit
Transactions].[AAA] IN...".

Maybe the problem is where I have used it?

Here is exactly what I used...and sorry, but the simplified field names
are
now gone....

In (SELECT [PORTFOLIO NAME] FROM [Edit Transactions] As Tmp
GROUP BY [PORTFOLIO NAME],[BACKOFFICE PROCESS DATE],[BACKOFFICE ENTRY
CODE],[INSTRUMENT DESCRIPTION]
HAVING Count(*)>1 And
Sum(HH CCY CFAMT) = 0 And
[BACKOFFICE PROCESS DATE] = [Edit Transactions].[BACKOFFICE PROCESS DATE]
And
[BACKOFFICE ENTRY CODE] = [Edit Transactions].[BACKOFFICE ENTRY CODE] And
[INSTRUMENT DESCRIPTION] = [Edit Transactions].[INSTRUMENT DESCRIPTION])





:

I think what you want is the following. This should only return a
possible
match if the Sum of EEE is zero for the group. Then the match will be
determined on the first four fields

In (SELECT [AAA] FROM [Edit Transactions] As Tmp
GROUP BY [AAA],[BBB],[CCC],[DDD]
HAVING Count(*)>1 And
Sum(EEE) = 0 And
[BBB] = [Edit Transactions].[BBB] And
[CCC] = [Edit Transactions].[CCC] And
[DDD] = [Edit Transactions].[DDD])

I am trying to (i) take an existing table, scan it, and identify records
where 4 particular fields match and the sum of another field is zero.
and
then (ii) update a different field on both/any records that qualify

I am trying to learn how to build part (i) by using a Find Duplicates
query,
hoping I could then take the expression, amend it and insert it into
the
field to be used in an IIF statement within an Update Table query...but
that's getting ahead of myself.

The table id Edit Transactions.
The four fields to match are AAA, BBB, CCC and DDD
The field whose sum should be 0 is EEE

Anyways, this is what the Find Duplicates query produced:

In (SELECT [AAA] FROM [Edit Transactions] As Tmp GROUP BY
[AAA],[BaBB],[CCC],[DDD],[EEE] HAVING Count(*)>1 And [BBB] = [Edit
Transactions].[BBB] And [CCC] = [Edit Transactions].[CCC] And [DDD] =
[Edit
Transactions].[DDD] And [EEE] = [Edit Transactions].[EEE])

Doesn't work obviously...it matches on all 5 fields instead, not bad,
but
I
cannot amend the last statement to

[EEE] = -1*[Edit Transactions].[EEE]
or
[EEE] + [Edit Transactions].[EEE] = 0

or anything to make it work...and reading the other posts on Duplicates
querying is difficult so far...

Any ideas, please let me know.

Txs,
Tom

 

[John Spencer]

Guessing,

It could be that the Sum(HH CCY CFAMT) is not returning exactly ZERO. If the
field - HH CCY CFAMT - is not an integer or currency field then you could be
getting a very slight rounding error.
You might need to put a tolerance level in that checks that the amount is very
near zero (as well as exactly zero).

Sum([HH CCY CFAMT]) Between -0.0000001 and 0.0000001

There may be a problem...

The query seems to missing some of the duplicate records...or at least the
dupes according to me. ;-)

A review of the resulting reports clearly shows that some records which that
select should identify as dupes...and apply a "washed" attribute...are not
being updated.

Within the exact same family of records, other pairs of records have been
properly washed.

I have not been able to find the reason why...I have built a query to mimic
the select statement, and filled in specific criterion by criterion, to see
if the records I expect to match do indeed have matching values on all the
Select statement criterion. And they seem to...but when it gets fired
up...they don't get identified.

Any suggestions on examining "hidden" attributes or other areas to
investigate?

Tks,
Tom

Good, I was sitting here trying to figure out what I had wrong. I was giving
up when I saw this message. You are very welcome.

Wait a minute...it does actually work. I cannot get the error mssg to come
up, and the query is working properly. John, thank you once again.

:

sorry about that...I haven't done enough Access work to have this pounded
into my skull, as you obviously have.

So, I added the []

The full expression is:

In (SELECT [PORTFOLIO NAME] FROM [Edit Transactions] As Tmp
GROUP BY [PORTFOLIO NAME],[BACKOFFICE PROCESS DATE],[BACKOFFICE ENTRY
CODE],[INSTRUMENT DESCRIPTION]
HAVING Count(*)>1 And
Sum([HH CCY CFAMT]) = 0 And
[BACKOFFICE PROCESS DATE] = [Edit Transactions].[BACKOFFICE PROCESS DATE]
And
[BACKOFFICE ENTRY CODE] = [Edit Transactions].[BACKOFFICE ENTRY CODE] And
[INSTRUMENT DESCRIPTION] = [Edit Transactions].[INSTRUMENT DESCRIPTION])

There's a new error mssg upon trying to execute the query.

You tried to execute a query that does not include the specified expression
'Count(*)>1 and Sum([HH CCy CFAmt])=0 And...<<remainder of expression>>' as
part of an aggregate function.

If it is a factor...I am typing this expression inside the Criteria for the
column Portfolio Name, which is taken from Edit transactions table, and part
of a Find Duplicates wizarded query...

One other thing I wonder about...each field that is being compared for dupes
uses the format [AAA] = [Edit transactions].[AAA]...this seems like a method
of taking an individual record's value of AAA and then comparing it to every
other record in that table. But when you look at the SUM criterion, it
doesn't utilise that same structure.

Thanks very much for the assistance,
Tom

:

AUUUGH!!!! Names with spaces AUUGGHH!!!!

Ok, that's out of my system.

Anytime you have spaces in a field name or table name you HAVE to surround
the name with [] or you will get an error. You are missing the [] in the
following
Sum(HH CCY CFAMT) = 0
which should be
Sum([HH CCY CFAMT]) = 0

In (SELECT [PORTFOLIO NAME] FROM [Edit Transactions] As Tmp
GROUP BY [PORTFOLIO NAME],[BACKOFFICE PROCESS DATE],[BACKOFFICE ENTRY
CODE],[INSTRUMENT DESCRIPTION]
HAVING Count(*)>1 And
Sum([HH CCY CFAMT]) = 0 And
[BACKOFFICE PROCESS DATE] = [Edit Transactions].[BACKOFFICE PROCESS DATE]
And
[BACKOFFICE ENTRY CODE] = [Edit Transactions].[BACKOFFICE ENTRY CODE] And
[INSTRUMENT DESCRIPTION] = [Edit Transactions].[INSTRUMENT DESCRIPTION])

Hope this works.


I have inserted this statement into the Criteria for a Query, under the AAA
column.but the response is "Syntax Error in query expresion "((([Edit
Transactions].[AAA] IN...".

Maybe the problem is where I have used it?

Here is exactly what I used...and sorry, but the simplified field names
are
now gone....

In (SELECT [PORTFOLIO NAME] FROM [Edit Transactions] As Tmp
GROUP BY [PORTFOLIO NAME],[BACKOFFICE PROCESS DATE],[BACKOFFICE ENTRY
CODE],[INSTRUMENT DESCRIPTION]
HAVING Count(*)>1 And
Sum(HH CCY CFAMT) = 0 And
[BACKOFFICE PROCESS DATE] = [Edit Transactions].[BACKOFFICE PROCESS DATE]
And
[BACKOFFICE ENTRY CODE] = [Edit Transactions].[BACKOFFICE ENTRY CODE] And
[INSTRUMENT DESCRIPTION] = [Edit Transactions].[INSTRUMENT DESCRIPTION])





:

I think what you want is the following. This should only return a
possible
match if the Sum of EEE is zero for the group. Then the match will be
determined on the first four fields

In (SELECT [AAA] FROM [Edit Transactions] As Tmp
GROUP BY [AAA],[BBB],[CCC],[DDD]
HAVING Count(*)>1 And
Sum(EEE) = 0 And
[BBB] = [Edit Transactions].[BBB] And
[CCC] = [Edit Transactions].[CCC] And
[DDD] = [Edit Transactions].[DDD])

I am trying to (i) take an existing table, scan it, and identify records
where 4 particular fields match and the sum of another field is zero.
and
then (ii) update a different field on both/any records that qualify

I am trying to learn how to build part (i) by using a Find Duplicates
query,
hoping I could then take the expression, amend it and insert it into
the
field to be used in an IIF statement within an Update Table query...but
that's getting ahead of myself.

The table id Edit Transactions.
The four fields to match are AAA, BBB, CCC and DDD
The field whose sum should be 0 is EEE

Anyways, this is what the Find Duplicates query produced:

In (SELECT [AAA] FROM [Edit Transactions] As Tmp GROUP BY
[AAA],[BaBB],[CCC],[DDD],[EEE] HAVING Count(*)>1 And [BBB] = [Edit
Transactions].[BBB] And [CCC] = [Edit Transactions].[CCC] And [DDD] =
[Edit
Transactions].[DDD] And [EEE] = [Edit Transactions].[EEE])

Doesn't work obviously...it matches on all 5 fields instead, not bad,
but
I
cannot amend the last statement to

[EEE] = -1*[Edit Transactions].[EEE]
or
[EEE] + [Edit Transactions].[EEE] = 0

or anything to make it work...and reading the other posts on Duplicates
querying is difficult so far...

Any ideas, please let me know.

Txs,
Tom

 

[Guest]

Now thats great response time. Tks for picking this up again.

Your idea was my first thought...so I relaxed that rule by allowing the Sum
to return any value with ABS(value) < 1...this did not deliver any
differences. As expected...its a currency field with no transformations or
rounding...

Since we are thinking along similar lines, here's some stuff that I am
wondering if it has an impact. Once again, inexperience with Access leaves
me grasping at straws.

1) Its possible that there are 4 records might pair off...or any
even-numbered amount. It is not common, and has not been the cause of the
cases I am looking at specifically.

2) The column [HH CCY CFAMT] is related to other columns, most notably
something called [HH Ccy NET Amt]. NET vs. CF. And whenever the NET = CF,
the trap dupes query seems to work...but whenever NET <> CF, it does not
work. I say seem because it is impossible to review all cases quite yet. I
know the NET fields is not particpating in the Select...

3) MOST LIKELY CULPRIT. For the three cases I am investigating...in each
case, pairs of records WERE washed successfully, and these pairs matched the
records from the same family that SHOULD have been washed in every way save
one...the [INSTRUMENT DESCRIPTION] was different.

It just so hapens that this different instrument description is
characteristic of how these two classes of very similar records differ...it's
wrapped up in 2) as well.

So I wonder if the Select statement, either by how its built OR when its
called OR how its used specifically within the query may have an impact?
That is...could the matched pairs have somehow been found "first'...and
because they shared so many common attributes with the second unmatched pair,
ruined it for them?

I really wonder about this last one...because other variations of this
problem exist within this data. I'm just focussing on this one aspect of the
problem to try and learn something about what's going on.

If you like...you may have the entire database to take a look at any
details...

Regards,
Tom

Guessing,

It could be that the Sum(HH CCY CFAMT) is not returning exactly ZERO. If the
field - HH CCY CFAMT - is not an integer or currency field then you could be
getting a very slight rounding error.
You might need to put a tolerance level in that checks that the amount is very
near zero (as well as exactly zero).

Sum([HH CCY CFAMT]) Between -0.0000001 and 0.0000001

There may be a problem...

The query seems to missing some of the duplicate records...or at least the
dupes according to me. ;-)

A review of the resulting reports clearly shows that some records which that
select should identify as dupes...and apply a "washed" attribute...are not
being updated.

Within the exact same family of records, other pairs of records have been
properly washed.

I have not been able to find the reason why...I have built a query to mimic
the select statement, and filled in specific criterion by criterion, to see
if the records I expect to match do indeed have matching values on all the
Select statement criterion. And they seem to...but when it gets fired
up...they don't get identified.

Any suggestions on examining "hidden" attributes or other areas to
investigate?

Tks,
Tom

Good, I was sitting here trying to figure out what I had wrong. I was giving
up when I saw this message. You are very welcome.

Tom MacKay wrote:

Wait a minute...it does actually work. I cannot get the error mssg to come
up, and the query is working properly. John, thank you once again.

:

sorry about that...I haven't done enough Access work to have this pounded
into my skull, as you obviously have.

So, I added the []

The full expression is:

In (SELECT [PORTFOLIO NAME] FROM [Edit Transactions] As Tmp
GROUP BY [PORTFOLIO NAME],[BACKOFFICE PROCESS DATE],[BACKOFFICE ENTRY
CODE],[INSTRUMENT DESCRIPTION]
HAVING Count(*)>1 And
Sum([HH CCY CFAMT]) = 0 And
[BACKOFFICE PROCESS DATE] = [Edit Transactions].[BACKOFFICE PROCESS DATE]
And
[BACKOFFICE ENTRY CODE] = [Edit Transactions].[BACKOFFICE ENTRY CODE] And
[INSTRUMENT DESCRIPTION] = [Edit Transactions].[INSTRUMENT DESCRIPTION])

There's a new error mssg upon trying to execute the query.

You tried to execute a query that does not include the specified expression
'Count(*)>1 and Sum([HH CCy CFAmt])=0 And...<<remainder of expression>>' as
part of an aggregate function.

If it is a factor...I am typing this expression inside the Criteria for the
column Portfolio Name, which is taken from Edit transactions table, and part
of a Find Duplicates wizarded query...

One other thing I wonder about...each field that is being compared for dupes
uses the format [AAA] = [Edit transactions].[AAA]...this seems like a method
of taking an individual record's value of AAA and then comparing it to every
other record in that table. But when you look at the SUM criterion, it
doesn't utilise that same structure.

Thanks very much for the assistance,
Tom

:

AUUUGH!!!! Names with spaces AUUGGHH!!!!

Ok, that's out of my system.

Anytime you have spaces in a field name or table name you HAVE to surround
the name with [] or you will get an error. You are missing the [] in the
following
Sum(HH CCY CFAMT) = 0
which should be
Sum([HH CCY CFAMT]) = 0

In (SELECT [PORTFOLIO NAME] FROM [Edit Transactions] As Tmp
GROUP BY [PORTFOLIO NAME],[BACKOFFICE PROCESS DATE],[BACKOFFICE ENTRY
CODE],[INSTRUMENT DESCRIPTION]
HAVING Count(*)>1 And
Sum([HH CCY CFAMT]) = 0 And
[BACKOFFICE PROCESS DATE] = [Edit Transactions].[BACKOFFICE PROCESS DATE]
And
[BACKOFFICE ENTRY CODE] = [Edit Transactions].[BACKOFFICE ENTRY CODE] And
[INSTRUMENT DESCRIPTION] = [Edit Transactions].[INSTRUMENT DESCRIPTION])

Hope this works.


I have inserted this statement into the Criteria for a Query, under the AAA
column.but the response is "Syntax Error in query expresion "((([Edit
Transactions].[AAA] IN...".

Maybe the problem is where I have used it?

Here is exactly what I used...and sorry, but the simplified field names
are
now gone....

In (SELECT [PORTFOLIO NAME] FROM [Edit Transactions] As Tmp
GROUP BY [PORTFOLIO NAME],[BACKOFFICE PROCESS DATE],[BACKOFFICE ENTRY
CODE],[INSTRUMENT DESCRIPTION]
HAVING Count(*)>1 And
Sum(HH CCY CFAMT) = 0 And
[BACKOFFICE PROCESS DATE] = [Edit Transactions].[BACKOFFICE PROCESS DATE]
And
[BACKOFFICE ENTRY CODE] = [Edit Transactions].[BACKOFFICE ENTRY CODE] And
[INSTRUMENT DESCRIPTION] = [Edit Transactions].[INSTRUMENT DESCRIPTION])





:

I think what you want is the following. This should only return a
possible
match if the Sum of EEE is zero for the group. Then the match will be
determined on the first four fields

In (SELECT [AAA] FROM [Edit Transactions] As Tmp
GROUP BY [AAA],[BBB],[CCC],[DDD]
HAVING Count(*)>1 And
Sum(EEE) = 0 And
[BBB] = [Edit Transactions].[BBB] And
[CCC] = [Edit Transactions].[CCC] And
[DDD] = [Edit Transactions].[DDD])

I am trying to (i) take an existing table, scan it, and identify records
where 4 particular fields match and the sum of another field is zero.
and
then (ii) update a different field on both/any records that qualify

I am trying to learn how to build part (i) by using a Find Duplicates
query,
hoping I could then take the expression, amend it and insert it into
the
field to be used in an IIF statement within an Update Table query...but
that's getting ahead of myself.

The table id Edit Transactions.
The four fields to match are AAA, BBB, CCC and DDD
The field whose sum should be 0 is EEE

Anyways, this is what the Find Duplicates query produced:

In (SELECT [AAA] FROM [Edit Transactions] As Tmp GROUP BY
[AAA],[BaBB],[CCC],[DDD],[EEE] HAVING Count(*)>1 And [BBB] = [Edit
Transactions].[BBB] And [CCC] = [Edit Transactions].[CCC] And [DDD] =
[Edit
Transactions].[DDD] And [EEE] = [Edit Transactions].[EEE])

Doesn't work obviously...it matches on all 5 fields instead, not bad,
but
I
cannot amend the last statement to

[EEE] = -1*[Edit Transactions].[EEE]
or
[EEE] + [Edit Transactions].[EEE] = 0

or anything to make it work...and reading the other posts on Duplicates
querying is difficult so far...

Any ideas, please let me know.

Txs,
Tom