query shows the same rows

[radek225]

Have some query and would like to write a code which count and shows only records where [tblGoraZleceniaNowaWyceny].[NumerArkusza] are the same. how could i do that? Below query COUNT the same [numerarkusza] from all table, not where [id_wycena_pre]=Forms!frmWycenyObszarroboczy!ID_wycena_pre and SHOWS only [id_wycena_pre]=Forms!frmWycenyObszarroboczy!ID_wycena_pre so it's wrong:/

SELECT [tblGoraZleceniaNowaWyceny].[NumerArkusza], [tblGoraZleceniaNowaWyceny].[nazwa], [tblGoraZleceniaNowaWyceny].[id_wycena_pre] 
FROM tblGoraZleceniaNowaWyceny 
WHERE ((([tblGoraZleceniaNowaWyceny].[numerarkusza]) In (SELECT [numerarkusza] FROM [tblGoraZleceniaNowaWyceny] As Tmp and [id_wycena_pre]=Forms!frmWycenyObszarroboczy!ID_wycena_pre 
GROUP BY [numerarkusza] 
HAVING Count(*)>1 ))) and [id_wycena_pre]=Forms!frmWycenyObszarroboczy!ID_wycena_pre

 

[CJ_London]

use the query wizard 'find duplicates'.

there are two possible results, one provides a duplicate count as you require, the other will report the lines that are duplicated. To get the former do not show additional fields.

 

[CJ_London]

meant to say

and [id_wycena_pre]=Forms!frmWycenyObszarroboczy!ID_wycena_pre

is limiting your query

 

[radek225]

meant to say

and [id_wycena_pre]=Forms!frmWycenyObszarroboczy!ID_wycena_pre

is limiting your query

Exactly!

e.g. my query now shows two records
1) - 1
2) - 2

shows only two because [id_wycena_pre]=Forms!frmWycenyObszarroboczy!ID_wycena_pre BUT, the problem is that, query count replications from all table but I want to count replcations only from this area of my table where [id_wycena_pre]=Forms!frmWycenyObszarroboczy!ID_wycena_pre

So in my example query should shows nothing because I don't have replications in my form

In query wizard I can't include my condition [id_wycena_pre]=Forms!frmWycenyObszarroboczy!ID_wycena_pre

 

[CJ_London]

you need to do this filter first.

create a query

 SELECT * FROM tblGoraZleceniaNowaWyceny WHERE [id_wycena_pre]=Forms!frmWycenyObszarroboczy!ID_wycena_pre

then base your duplicates query on that query

 

[radek225]

you need to do this filter first.

create a query

 SELECT * FROM tblGoraZleceniaNowaWyceny WHERE [id_wycena_pre]=Forms!frmWycenyObszarroboczy!ID_wycena_pre

then base your duplicates query on that query

Yes, but the problem is, I want to use this query to work with recordset, so I can't refer to another query in my string, right?


ReplikacjeDoSprDanych:

SELECT numerarkusza
FROM tblGoraZleceniaNowaWyceny
WHERE [id_wycena_pre]=Forms!frmWycenyObszarroboczy!ID_wycena_pre;

strSQL = "SELECT [ReplikacjeDoSprPopDanych].[NumerArkusza], [ReplikacjeDoSprPopDanych].[nazwa], [ReplikacjeDoSprPopDanych].[id_wycena_pre] 
FROM ReplikacjeDoSprPopDanych 
WHERE ((([ReplikacjeDoSprPopDanych].[numerarkusza]) In (SELECT [numerarkusza] FROM [ReplikacjeDoSprPopDanych] As Tmp 
GROUP BY [numerarkusza] 
HAVING Count(*)>1 )))"
Set rst = CurrentDb.OpenRecordset(strSQL)

Get error, so doesn't work:/

 

[CJ_London]

Get error, so doesn't work

very unhelpful, what is the error description?

only think I can see is I said
SELECT *

and you used
SELECT numerarkusza

 

[radek225]

very unhelpful, what is the error description?

only think I can see is I said
SELECT *

and you used
SELECT numerarkusza

Right, Ofc should be *, but still get an error "Too few parameters, expected 1"

ReplikacjeDoSprDanych:

SELECT *
FROM tblGoraZleceniaNowaWyceny
WHERE [id_wycena_pre]=Forms!frmWycenyObszarroboczy!ID_wycena_pre;

strSQL = "SELECT [ReplikacjeDoSprPopDanych].[NumerArkusza], [ReplikacjeDoSprPopDanych].[nazwa], [ReplikacjeDoSprPopDanych].[id_wycena_pre] 
FROM ReplikacjeDoSprPopDanych 
WHERE ((([ReplikacjeDoSprPopDanych].[numerarkusza]) In (SELECT [numerarkusza] FROM [ReplikacjeDoSprPopDanych] As Tmp 
GROUP BY [numerarkusza] 
HAVING Count(*)>1 )))"
Set rst = CurrentDb.OpenRecordset(strSQL)

 

[CJ_London]

suggest you create your duplicates query using the query wizard and check it works, then compare with your sqlstr

only other thought - but you would get a compile error - is that you don't have any string continuations e.g.

 strSQL = "SELECT ....[ReplikacjeDoSprPopDanych].[id_wycena_pre][COLOR=red]" & _
[/COLOR][COLOR=red]"[/COLOR] FROM ReplikacjeDoSprPopDanych" [COLOR=red]& _[/COLOR]
 ...

or perhaps you have edited them out - but maybe you are missing a space

 

[radek225]

suggest you create your duplicates query using the query wizard and check it works, then compare with your sqlstr

only other thought - but you would get a compile error - is that you don't have any string continuations e.g.

 strSQL = "SELECT ....[ReplikacjeDoSprPopDanych].[id_wycena_pre][COLOR=red]" & _
[/COLOR][COLOR=red]"[/COLOR] FROM ReplikacjeDoSprPopDanych" [COLOR=red]& _[/COLOR]
 ...

or perhaps you have edited them out - but maybe you are missing a space

I have everything in one line in my strSQL, yes I created it and checked it in query wizard before I post about my problem. I think the problem is, related qeury "ReplikacjeDoSprPopDanych" from my strSQL , because when I replace ReplikacjeDoSprPopDanych to tblGoraZleceniaNowaWyceny - table, then there's no error in strSQL.

 

[CJ_London]

so your form frmWycenyObszarroboczy is open and the ID_wycena_pre control is populated?

 

[radek225]

so your form frmWycenyObszarroboczy is open and the ID_wycena_pre control is populated?

yes, always when I'm using this code, ID_wycena_pre control is populated
e.g.
id_wycena_pre = 68