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

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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

"John Spencer" wrote:

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
    ... 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
    ... Sum([HH CCY CFAMT]) = 0 ... column.but the response is "Syntax Error in query expresion "((([Edit ... 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 ...
    (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)