union sum 1

[yan19454]

module log

Name DOB [Access Module Time] [Module Name]

ABC 1/12/02 1/12/07 Help

ABC 1/12/02 1/12/07 Help2

ACC 1/12/62 11/12/07 Help2

ACC 1/12/62 12/12/07 Help

pratice log

Name DOB [Access Practice Time] [Practice Name]

ABC 1/12/02 1/12/07 P1

ABC 1/12/02 1/12/07 P2

ACC 1/12/62 11/12/07 P1

ACE 1/12/62 12/12/07 P

HREMP

Name DOB [CC]

ABC 1/12/02 A11

ACD 1/12/62 A1D

ACc 1/12/62 A11

I want to create a query when user select A11 from the CC

It will show

Name Count ( sum of the sum count of module log for ABC and sum of count of Practice Log)

ABC 4

ACC 3

so how can I accomplish this ? Thx.

 

[rac2]

SELECT Name, COUNT(*)
FROM (
      SELECT name FROM [module log]
      UNION ALL
      SELECT name FROM [pratice log]
)
JOIN HREMP h ON h.name = a.name
            AND h.[cc] = 'A11'

Assuming name is unique in HREMP.

 

[jedraw]

yan19454,

I think you're looking for a query like this

SELECT name, Count(name) AS CountOfname
FROM
( SELECT name,modname as Fld
FROM moduleLog
UNION
SELECT name,practname as Fld
FROM practicelog)

GROUP BY name;

 

[rac2]

I should have written

SELECT h.Name, COUNT(*)
FROM (
      SELECT name FROM [module log]
      UNION ALL
      SELECT name FROM [pratice log]
) a
JOIN HREMP h ON h.name = a.name
            AND h.[cc] = 'A11'
GROUP BY h.name

The INNER JOIN acts like a filter limiting rows to those with h.cc equal to A11.
The UNION ALL means that every row from either subquery will be in the result.