SELECT IDSE, TheDay, 1 AS [Set], Es1 AS [Value] FROM [tblMetod]
UNION
SELECT IDSE, TheDay, 1 AS [Set], Es2 AS [Value] FROM [tblMetod]
UNION
.........
SELECT IDSE, TheDay, 2 AS [Set], CO AS [Value] FROM [tblMetod]
UNION
SELECT IDSE, TheDay, 2 AS [Set], G1 AS [Value] FROM [tblMetod]
.......
UNION SELECT IDSE, TheDay, 2 AS [Set], G12 AS [Value] FROM [tblMetod];
_ _ _ _
SELECT Q1.IDSE, Q1.TheDay, Count(*) AS Matches
FROM UnQryMetod AS Q1 INNER JOIN UnQryMetod AS Q2 ON Q1.IDSE = Q2.IDSE
WHERE (((Q1.Set)=1) AND ((Q2.Set)=2) AND ((Q1.Value)=[Q2].[Value]) AND
((Q1.IDSE)>=335))
GROUP BY Q1.IDSE, Q1.TheDay;
is it correct ?
thx
"John Spencer" wrote
You need to go back the Union query and add in the date field to all
parts of the union query. Then the fiedl will be available in the query
based on the union query. Also, I should have prefaced the field name
with the query alias.
SELECT Q1.IDSE
, Q1.TheDay
, Count(*) as Matches
FROM ...
"John Spencer" wrote
Is "theDay" a field in the table?
yes, it's data field in tblMetod
Is it always the same value for each IDSE
every id have a different data ex id 1 10/04/06, 2 11/04/06...
value (a one to one match)?
If it is a one to one match then
the matches don't count data field but other field of table
SELECT Q1.IDSE
, TheDay
, Count(*) AS Matches
FROM UnQryMetod AS Q1 INNER JOIN UnQryMetod AS Q2 ON Q1.IDSE = Q2.IDSE
WHERE (((Q1.Set)=1) AND ((Q2.Set)=2) AND ((Q1.Value)=[Q2].[Value]) AND
((Q1.IDSE)>=335))
GROUP BY Q1.IDSE, TheDay;
this query is based on an other query UnQryMetod ,
SELECT IDSE, 1 AS [Set], Es1 AS [Value] FROM [tblMetod]
UNION
SELECT IDSE, 1 AS [Set], Es2 AS [Value] FROM [tblMetod]
UNION
.........
SELECT IDSE, 2 AS [Set], CO AS [Value] FROM [tblMetod]
UNION
SELECT IDSE, 2 AS [Set], G1 AS [Value] FROM [tblMetod]
.......
UNION SELECT IDSE, 2 AS [Set], G12 AS [Value] FROM [tblMetod];
UnQryMetod on tblMetod
thx
If TheDay is a calculated value, then how is it calculated?
I have a query like this from tblMetod (IDSE, theday,...)and
UnQryMetod
SELECT Q1.IDSE, Count(*) AS Matches
FROM UnQryMetod AS Q1 INNER JOIN UnQryMetod AS Q2 ON Q1.IDSE =
Q2.IDSE
WHERE (((Q1.Set)=1) AND ((Q2.Set)=2) AND ((Q1.Value)=[Q2].[Value])
AND
((Q1.IDSuperE)>=335))
GROUP BY Q1.IDSE;
ex the result are:
ResultMetod IDSE Matches
336 1
337 2
338 2
Is it possible to have a field data too?
IDSE theday Matches
336 10/04/2006 1
337 11/04/2006 2
338 12/04/2006 2
thx
