Re: identify semi-duplicate records, and amend elements of those r

Tech-Archive recommends: Speed Up your PC by fixing your registry

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.


"Tom MacKay" <TomMacKay@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:400424F3-E83B-4D40-9D32-3723AD289BD7@xxxxxxxxxxxxxxxx
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])





"John Spencer" wrote:

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])

"Tom MacKay" <TomMacKay@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3C5A3FC5-6F99-402C-8D19-77CB55B6D095@xxxxxxxxxxxxxxxx
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





.

Relevant Pages

  • Re: identify semi-duplicate records, and amend elements of those r
    ... Your idea was my first thought...so I relaxed that rule by allowing the Sum ... called OR how its used specifically within the query may have an impact? ... It could be that the Sum(HH CCY CFAMT) is not returning exactly ZERO. ... the select statement, and filled in specific criterion by criterion, to see ...
    (microsoft.public.access.queries)
  • Re: identify semi-duplicate records, and amend elements of those r
    ... There's a new error mssg upon trying to execute the query. ... Sum(HH CCY CFAMT) = 0 ... match if the Sum of EEE is zero for the group. ... I am trying to learn how to build part by using a Find Duplicates ...
    (microsoft.public.access.queries)
  • Re: identify semi-duplicate records, and amend elements of those r
    ... There's a new error mssg upon trying to execute the query. ... Sum(HH CCY CFAMT) = 0 ... match if the Sum of EEE is zero for the group. ... I am trying to learn how to build part by using a Find Duplicates ...
    (microsoft.public.access.queries)
  • Re: identify semi-duplicate records, and amend elements of those r
    ... 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 ... Sum(HH CCY CFAMT) = 0 ... I am trying to learn how to build part by using a Find Duplicates ...
    (microsoft.public.access.queries)
  • Re: identify semi-duplicate records, and amend elements of those r
    ... I have inserted this statement into the Criteria for a Query, ... where 4 particular fields match and the sum of another field is zero. ... I am trying to learn how to build part by using a Find Duplicates ... The table id Edit Transactions. ...
    (microsoft.public.access.queries)