Unwanted multiplication

[Diogo]

Hello there everyone.
This is my query:

SELECT Count([Tabela Facturas Workflow.Data]) AS Expr1,
Count(Qry_Estatisticas_Workflow_Facturas.Data) AS Expr2
FROM [Tabela Facturas Workflow], Qry_Estatisticas_Workflow_Facturas
WHERE ((([Tabela Facturas Workflow].Data) Between [Forms]![Form Estatisticas
Workflow]![Text0] And [Forms]![Form Estatisticas Workflow]![Text2]));

I thought it would return two values, instead it returns the multiplication
of both values in the two collums.

What I'm I doing wrong?
Could someone help?

 

[pietlinden]

Hello there everyone.
This is my query:

SELECT Count([Tabela Facturas Workflow.Data]) AS Expr1,
Count(Qry_Estatisticas_Workflow_Facturas.Data) AS Expr2
FROM [Tabela Facturas Workflow], Qry_Estatisticas_Workflow_Facturas
WHERE ((([Tabela Facturas Workflow].Data) Between [Forms]![Form Estatisticas
Workflow]![Text0] And [Forms]![Form Estatisticas Workflow]![Text2]));

I thought it would return two values, instead it returns the multiplication
of both values in the two collums.

What I'm I doing wrong?
Could someone help?

Oh, you've got a cross join.

SELECT Count([A].Data) As Count1, Count(.Data As Count2
FROM A INNER JOIN B ON A.Field1=B.Field2 <===You are missing the join
here...
WHERE A.Data BETWEEN Forms![FormA]![Text0] AND Forms![FormA]![Text2]

 

[Michel Walsh]

When you use a cross join, each of the records from one table get logically
associated with each records of the other table, and then, still logically,
the where clause is evaluated for each of these 'associated' mix, and only
those associations which return true for the expression in the where clause
are kept.

In other words, if tablea has

f1 f2 'fields
a 1
b 1
c 3


and table2 has

f3 f4
a 1
b 2
b 3


then:

SELECT *
FROM tablea, table2


yield the result (the order of the record is irrelevant)


f1 f2 f3 f4
a 1 a 1
a 1 b 2
a 1 b 3
b 1 a 1
b 1 b 2
b 1 b 3
c 3 a 1
c 3 b 1
c 3 b 3



where you can see that each record from tablea has been associated to each
record of tableb.

If you COUNT the number of records, at that point, you will get a count of
9, which is **the number of associations**. And again, those associations
are due to the way you wrote the FROM clause.


If you want only the count from the original table, NOT of the possible
associations, use something like:


-----------------------
SELECT Count([Tabela Facturas Workflow.Data]) AS Expr1,
FROM [Tabela Facturas Workflow]
WHERE ((([Tabela Facturas Workflow].Data) Between
[Forms]![Form Estatisticas Workflow]![Text0]
And [Forms]![Form Estatisticas Workflow]![Text2]))
------------------------

ie, there is no more JOIN, since there is just one table

and, in another query, use


--------------------------
SELECT Count(Qry_Estatisticas_Workflow_Facturas.Data) AS Expr2
FROM Qry_Estatisticas_Workflow_Facturas


(since your initial clause was doing nothing to limit the number of record
to be considered coming from Qry_Estatisticas_Workflow_Facturas)


Sure, you now have two queries, instead of one, but what was your goal, in
the first place? Is two queries acceptable?


Vanderghast, Access MVP

 

[Diogo]

SELECT COUNT([Tab1].Data), COUNT([Tab2].Data), COUNT([Tab3].Data)
FROM (SELECT DISTINCT ([Tab1].Data) FROM [Tab1] WHERE ((([Tab1].Data)
Between [Forms]![FormX]![Text0] And [Forms]![FormX]![Text2]))) AS [Nº de
dias Cartas], (SELECT DISTINCT ([Tab2].Data) FROM [Tab2] WHERE
((([Tab2].Data) Between [Forms]![FormX]![Text0] And
[Forms]![FormX]![Text2]))) AS [Nº de dias Facturas], (SELECT DISTINCT
([Tab3].Data) FROM [Tab3] WHERE ((([Tab3].Data) Between
[Forms]![FormX]![Text0] And [Forms]![FormX]![Text2]))) AS [Nº de
Esclarecimentos];

OK why on earth is this still multipling the results??
It was supposed to return 8 13 1 and instead it returns 54 54 54....
Why????

 

[Diogo]

Correction it was suposed to return 3 18 1 and it returns 54 54 54

 

[John Spencer]

SELECT COUNT([Tab1].Data) as Tab1Count
FROM (SELECT DISTINCT [Tab1].Data
FROM [Tab1]
WHERE [Tab1].Data
Between [Forms]![FormX]![Text0] And [Forms]![FormX]![Text2])
AS [Nº de dias Cartas]
UNION ALL
SELECT COUNT([Tab2].Data) as Tab2Count
FROM (SELECT DISTINCT [Tab2].Data
FROM [Tab2]
WHERE [Tab2].Data Between [Forms]![FormX]![Text0] And
[Forms]![FormX]![Text2]) AS [Nº de dias Facturas]
UNION ALL
SELECT COUNT([Tab3].Data) as Tab3Count
FROM (SELECT DISTINCT [Tab3].Data FROM Tab3
WHERE [Tab3].Data) Between
[Forms]![FormX]![Text0] And [Forms]![FormX]![Text2])
AS [Nº de Esclarecimentos];

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Correction it was suposed to return 3 18 1 and it returns 54 54 54

SELECT COUNT([Tab1].Data), COUNT([Tab2].Data), COUNT([Tab3].Data)
FROM (SELECT DISTINCT ([Tab1].Data) FROM [Tab1] WHERE ((([Tab1].Data)
Between [Forms]![FormX]![Text0] And [Forms]![FormX]![Text2]))) AS [Nº de
dias Cartas], (SELECT DISTINCT ([Tab2].Data) FROM [Tab2] WHERE
((([Tab2].Data) Between [Forms]![FormX]![Text0] And
[Forms]![FormX]![Text2]))) AS [Nº de dias Facturas], (SELECT DISTINCT
([Tab3].Data) FROM [Tab3] WHERE ((([Tab3].Data) Between
[Forms]![FormX]![Text0] And [Forms]![FormX]![Text2]))) AS [Nº de
Esclarecimentos];

OK why on earth is this still multipling the results??
It was supposed to return 8 13 1 and instead it returns 54 54 54....
Why????

 

[Diogo]

Dear Spencer

If I do a union all I'll get this as a singel collum with three rows, I want
one row and three collums, because I want to do a union all of another query
that I want to show in the next row.
The final result should be two rows and three collums.
My doubt remains Why is it multipling the three collums???

SELECT COUNT([Tab1].Data) as Tab1Count
FROM (SELECT DISTINCT [Tab1].Data
FROM [Tab1]
WHERE [Tab1].Data
Between [Forms]![FormX]![Text0] And [Forms]![FormX]![Text2])
AS [Nº de dias Cartas]
UNION ALL
SELECT COUNT([Tab2].Data) as Tab2Count
FROM (SELECT DISTINCT [Tab2].Data
FROM [Tab2]
WHERE [Tab2].Data Between [Forms]![FormX]![Text0] And
[Forms]![FormX]![Text2]) AS [Nº de dias Facturas]
UNION ALL
SELECT COUNT([Tab3].Data) as Tab3Count
FROM (SELECT DISTINCT [Tab3].Data FROM Tab3
WHERE [Tab3].Data) Between
[Forms]![FormX]![Text0] And [Forms]![FormX]![Text2])
AS [Nº de Esclarecimentos];

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Correction it was suposed to return 3 18 1 and it returns 54 54 54

SELECT COUNT([Tab1].Data), COUNT([Tab2].Data), COUNT([Tab3].Data)
FROM (SELECT DISTINCT ([Tab1].Data) FROM [Tab1] WHERE ((([Tab1].Data)
Between [Forms]![FormX]![Text0] And [Forms]![FormX]![Text2]))) AS [Nº de
dias Cartas], (SELECT DISTINCT ([Tab2].Data) FROM [Tab2] WHERE
((([Tab2].Data) Between [Forms]![FormX]![Text0] And
[Forms]![FormX]![Text2]))) AS [Nº de dias Facturas], (SELECT DISTINCT
([Tab3].Data) FROM [Tab3] WHERE ((([Tab3].Data) Between
[Forms]![FormX]![Text0] And [Forms]![FormX]![Text2]))) AS [Nº de
Esclarecimentos];

OK why on earth is this still multipling the results??
It was supposed to return 8 13 1 and instead it returns 54 54 54....
Why????