Find Duplicates Query Question

  • Thread starter Thread starter djkc
  • Start date Start date
D

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];
 
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.
 
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.

KARL DEWEY said:
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


djkc said:
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];
Click to expand...
Click to expand...
 
Nevermind Karl, I googled left join and tried it.

Thanks,
DJ

djkc said:
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.

KARL DEWEY said:
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


djkc said:
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];
Click to expand...
Click to expand...
Click to expand...
 
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


djkc said:
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.

KARL DEWEY said:
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


djkc said:
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];
Click to expand...
Click to expand...
Click to expand...
 
Back
Top