Find Duplicates Query Question

[djkc]

I have a duplicates query counting accts for each of my branch locations. I
need for the query to say zero if the branch location has no accts to count.
Here is a copy of the SQL view of my query

SELECT First([Recency 180 days1].[Correct BranchNumber]) AS [Correct
BranchNumber Field], Count([Recency 180 days1].AcctNumber) AS NumberOfDups
FROM [Recency 180 days1]
GROUP BY [Recency 180 days1].[Correct BranchNumber];

 

[KARL DEWEY]

Unless you have a separate table that list all branches and left join it in
your query you can not get a count of zero.

Post your SQL.

 

[djkc]

What do you mean left join? There is a seperate table with the branches in it
and I joined the two tables together by that field.

Unless you have a separate table that list all branches and left join it in
your query you can not get a count of zero.

Post your SQL.

--
KARL DEWEY
Build a little - Test a little

I have a duplicates query counting accts for each of my branch locations. I
need for the query to say zero if the branch location has no accts to count.
Here is a copy of the SQL view of my query

SELECT First([Recency 180 days1].[Correct BranchNumber]) AS [Correct
BranchNumber Field], Count([Recency 180 days1].AcctNumber) AS NumberOfDups
FROM [Recency 180 days1]
GROUP BY [Recency 180 days1].[Correct BranchNumber];

 

[djkc]

Nevermind Karl, I googled left join and tried it.

Thanks,
DJ

What do you mean left join? There is a seperate table with the branches in it
and I joined the two tables together by that field.

Unless you have a separate table that list all branches and left join it in
your query you can not get a count of zero.

Post your SQL.

--
KARL DEWEY
Build a little - Test a little

I have a duplicates query counting accts for each of my branch locations. I
need for the query to say zero if the branch location has no accts to count.
Here is a copy of the SQL view of my query

SELECT First([Recency 180 days1].[Correct BranchNumber]) AS [Correct
BranchNumber Field], Count([Recency 180 days1].AcctNumber) AS NumberOfDups
FROM [Recency 180 days1]
GROUP BY [Recency 180 days1].[Correct BranchNumber];

 

[KARL DEWEY]

In query design view, above the grid the tables are shown. Click on the line
connecting the table fields. Select the option that says something like
'Include all records from "Branch_List_Table" and only those from "Recency
180 days1" that are equal.'

Something like this --
SELECT [Recency 180 days1].[Correct BranchNumber], Count([Recency 180
days1].AcctNumber) AS NumberOfDups
FROM [Branch_List_Table] LEFT JOIN [Recency 180 days1] ON
[Branch_List_Table].[BranchNumber] = [Recency 180 days1].[Correct
BranchNumber]
GROUP BY [Recency 180 days1].[Correct BranchNumber];

--
KARL DEWEY
Build a little - Test a little

What do you mean left join? There is a seperate table with the branches in it
and I joined the two tables together by that field.

Unless you have a separate table that list all branches and left join it in
your query you can not get a count of zero.

Post your SQL.

--
KARL DEWEY
Build a little - Test a little

I have a duplicates query counting accts for each of my branch locations. I
need for the query to say zero if the branch location has no accts to count.
Here is a copy of the SQL view of my query

SELECT First([Recency 180 days1].[Correct BranchNumber]) AS [Correct
BranchNumber Field], Count([Recency 180 days1].AcctNumber) AS NumberOfDups
FROM [Recency 180 days1]
GROUP BY [Recency 180 days1].[Correct BranchNumber];