Duplicate rows - Distinct Count

R

Rachel

I have the following Access query, everything is as I want it to be
apart from duplicate PID_No's.
Is it possible (within the same query) to group by PID_No and sum the
CountOfRLC?
For example:
PID_No: 1561251-1 is in the table twice, is it possible to show this
once but with CountOfRLC as 29, instead of two separate entries as 10
and 19?

My Query:
SELECT Table1.PID_No, Table2.RowNo, Count(Table2.RLC) AS CountOfRLC,
Tablel.ID
FROM Table2 LEFT JOIN Table1 ON Table2.RowNo = Table1.RowNo
GROUP BY Table1.PID_No, Table2.RowNo, Table1.ID, Table2.RLC
HAVING (((Table2.RLC) Like "VC*"));

PID_No RowNo CountOfRLC ID
1077604-4 2222 5 3323298
1310694-4 2597 26 3320780
1461552-7 9488 6 3351321
1520369-3 3236 49 3323059
1546061-1 3318 38 3323051
1561251-1 3361 10 3323382
1561251-1 3363 19 3323383
1847406-1 7805 11 3343644

Thank you in advance
R
 
S

Stefan Hoffmann

hi Rachel,
PID_No: 1561251-1 is in the table twice, is it possible to show this
once but with CountOfRLC as 29, instead of two separate entries as 10
and 19?

My Query:
SELECT Table1.PID_No, Table2.RowNo, Count(Table2.RLC) AS CountOfRLC,
Tablel.ID
FROM Table2 LEFT JOIN Table1 ON Table2.RowNo = Table1.RowNo
GROUP BY Table1.PID_No, Table2.RowNo, Table1.ID, Table2.RLC
HAVING (((Table2.RLC) Like "VC*"));

PID_No RowNo CountOfRLC ID
1546061-1 3318 38 3323051
1561251-1 3361 10 3323382
1561251-1 3363 19 3323383
Click to expand...
Yes, it is. You'll get your result if you only GROUP BY [PID_No].

This should work:

SELECT
Table1.PID_No,
Count(Table2.RLC) AS CountOfRLC
FROM Table2
LEFT JOIN Table1 ON Table2.RowNo = Table1.RowNo
GROUP BY Table1.PID_No
WHERE Table2.RLC Like "VC*";


mfG
--> stefan <--
 
R

Rachel

hi Rachel,




PID_No: 1561251-1 is in the table twice, is it possible to show this
once but with CountOfRLC as 29, instead of two separate entries as 10
and 19?
Click to expand...
My Query:
SELECT Table1.PID_No, Table2.RowNo, Count(Table2.RLC) AS CountOfRLC,
Tablel.ID
FROM Table2 LEFT JOIN Table1 ON Table2.RowNo = Table1.RowNo
GROUP BY Table1.PID_No, Table2.RowNo, Table1.ID, Table2.RLC
HAVING (((Table2.RLC) Like "VC*"));
Click to expand...
PID_No      RowNo  CountOfRLC      ID
1546061-1   3318   38             3323051
1561251-1   3361   10             3323382
1561251-1   3363   19             3323383
Click to expand...

Yes, it is. You'll get your result if you only GROUP BY [PID_No].

This should work:

SELECT
   Table1.PID_No,
   Count(Table2.RLC) AS CountOfRLC
FROM Table2
LEFT JOIN Table1 ON Table2.RowNo = Table1.RowNo
GROUP BY Table1.PID_No
WHERE Table2.RLC Like "VC*";

mfG
--> stefan <--- Hide quoted text -

- Show quoted text -
Click to expand...

Thank you very much
R
 
Top