error selecting duplicate records in Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I tried to make a query for selecting the duplicate records in another query,
based on a calculated field. I got a message (error 3122), saying that my
query does not include the calculated field formula as part of an aggregate
funcion. Anybody can help? Thank you.
 
Dear Riccardo:

Please post the SQL View of the query here, so we can see what is the nature
of the problem.

Generally, it would be unusual to use an aggregate when looking for
duplicates. How did you happen to choose that?

Tom Ellison
 
Dear Tom,

here is the SQL view:

SELECT [All records].[Amount USD], [All records].description
FROM [All records]
WHERE ((([All records].[Amount USD]) In (SELECT [Amount USD] FROM [All
records] As Tmp GROUP BY [Amount USD] HAVING Count(*)>1 )))
ORDER BY [All records].[Amount USD];

and this is the error message:

You tried to execute a query that does not include the specified expression
<name> as part of an aggregate function. (Error 3122)

and finally, here is the SQL of the original query, from which I wish to
make the selection:

SELECT Expenditures.*,
Round((Expenditures!USD+Expenditures!BAM/Expenditures![BAM/USD]),0) AS
[Amount USD]
FROM Expenditures;

Thanks, Riccardo
 
Dear Riccardo:

I have created this situation here for a brief test. A table [All records]
has [Amount USD] (currency) and description (text). I run this version of
your query:

SELECT [Amount USD], description
FROM [All records]
WHERE [All records].[Amount USD] In
(SELECT [Amount USD]
FROM [All records] As Tmp
GROUP BY [Amount USD]
HAVING Count(*)>1 )
ORDER BY [Amount USD];

It works well. Would you try the above on your system?

There is a possibility that your [All records] may be a query, rather than a
table, as in my experiment, and that there is something odd in that. So, if
this doesn't fix it for you, and if [All records] is a query, please post
that query and details of the table(s) on which it functions.

Tom Ellison


Riccardo said:
Dear Tom,

here is the SQL view:

SELECT [All records].[Amount USD], [All records].description
FROM [All records]
WHERE ((([All records].[Amount USD]) In (SELECT [Amount USD] FROM [All
records] As Tmp GROUP BY [Amount USD] HAVING Count(*)>1 )))
ORDER BY [All records].[Amount USD];

and this is the error message:

You tried to execute a query that does not include the specified
expression
<name> as part of an aggregate function. (Error 3122)

and finally, here is the SQL of the original query, from which I wish to
make the selection:

SELECT Expenditures.*,
Round((Expenditures!USD+Expenditures!BAM/Expenditures![BAM/USD]),0) AS
[Amount USD]
FROM Expenditures;

Thanks, Riccardo




Tom Ellison said:
Dear Riccardo:

Please post the SQL View of the query here, so we can see what is the
nature
of the problem.

Generally, it would be unusual to use an aggregate when looking for
duplicates. How did you happen to choose that?

Tom Ellison
Click to expand...
Click to expand...
 
Yes Tom, actually my [All records] is a query, but I have built on your
suggestion and now it works, I needed to add a GROUP BY instruction to it
(the [All records] query).

Thank you very much
Riccardo

Tom Ellison said:
Dear Riccardo:

I have created this situation here for a brief test. A table [All records]
has [Amount USD] (currency) and description (text). I run this version of
your query:

SELECT [Amount USD], description
FROM [All records]
WHERE [All records].[Amount USD] In
(SELECT [Amount USD]
FROM [All records] As Tmp
GROUP BY [Amount USD]
HAVING Count(*)>1 )
ORDER BY [Amount USD];

It works well. Would you try the above on your system?

There is a possibility that your [All records] may be a query, rather than a
table, as in my experiment, and that there is something odd in that. So, if
this doesn't fix it for you, and if [All records] is a query, please post
that query and details of the table(s) on which it functions.

Tom Ellison


Riccardo said:
Dear Tom,

here is the SQL view:

SELECT [All records].[Amount USD], [All records].description
FROM [All records]
WHERE ((([All records].[Amount USD]) In (SELECT [Amount USD] FROM [All
records] As Tmp GROUP BY [Amount USD] HAVING Count(*)>1 )))
ORDER BY [All records].[Amount USD];

and this is the error message:

You tried to execute a query that does not include the specified
expression
<name> as part of an aggregate function. (Error 3122)

and finally, here is the SQL of the original query, from which I wish to
make the selection:

SELECT Expenditures.*,
Round((Expenditures!USD+Expenditures!BAM/Expenditures![BAM/USD]),0) AS
[Amount USD]
FROM Expenditures;

Thanks, Riccardo




Tom Ellison said:
Dear Riccardo:

Please post the SQL View of the query here, so we can see what is the
nature
of the problem.

Generally, it would be unusual to use an aggregate when looking for
duplicates. How did you happen to choose that?

Tom Ellison


I tried to make a query for selecting the duplicate records in another
query,
based on a calculated field. I got a message (error 3122), saying that
my
query does not include the calculated field formula as part of an
aggregate
funcion. Anybody can help? Thank you.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top