count & group duplicate records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I list duplicate tax ids in groups for type of account and cost center?
I need to know what tax ids are duplicated for what accounts in what cost
center.

My date table has these fields:
tax id, type of account, cost center
 
SELECT YourDataTable.[cost center], YourDataTable.[type of account],
YourDataTable.[tax id], Count(YourDataTable.[tax id]) AS [CountOftax id]
FROM YourDataTable
GROUP BY YourDataTable.[cost center], YourDataTable.[type of account],
YourDataTable.[tax id]
HAVING (((Count(YourDataTable.[tax id]))>1));
 
Your code is good. But I have a 2nd ?. What would have to chg if I need a
list of tax ids that have 1 kind of type of account and any 1 of the other
types of the accountunts. So each tax id would have a min of 2 accounts.

KARL DEWEY said:
SELECT YourDataTable.[cost center], YourDataTable.[type of account],
YourDataTable.[tax id], Count(YourDataTable.[tax id]) AS [CountOftax id]
FROM YourDataTable
GROUP BY YourDataTable.[cost center], YourDataTable.[type of account],
YourDataTable.[tax id]
HAVING (((Count(YourDataTable.[tax id]))>1));


Chaz said:
How do I list duplicate tax ids in groups for type of account and cost center?
I need to know what tax ids are duplicated for what accounts in what cost
center.

My date table has these fields:
tax id, type of account, cost center
Click to expand...
Click to expand...
 
Do not know what you are asking for beyond the SQL below.

SELECT YourDataTable.[tax id], YourDataTable.[cost center],
YourDataTable.[type of account]
FROM YourDataTable
WHERE (((YourDataTable.[type of account])=[Enter type of account]))
ORDER BY YourDataTable.[tax id];


Chaz said:
Your code is good. But I have a 2nd ?. What would have to chg if I need a
list of tax ids that have 1 kind of type of account and any 1 of the other
types of the accountunts. So each tax id would have a min of 2 accounts.

KARL DEWEY said:
SELECT YourDataTable.[cost center], YourDataTable.[type of account],
YourDataTable.[tax id], Count(YourDataTable.[tax id]) AS [CountOftax id]
FROM YourDataTable
GROUP BY YourDataTable.[cost center], YourDataTable.[type of account],
YourDataTable.[tax id]
HAVING (((Count(YourDataTable.[tax id]))>1));


Chaz said:
How do I list duplicate tax ids in groups for type of account and cost center?
I need to know what tax ids are duplicated for what accounts in what cost
center.

My date table has these fields:
tax id, type of account, cost center
Click to expand...
Click to expand...
Click to expand...
 
Back
Top