Join tables Count problems

Help,

I have 2 tables one called ProjectHeader and the other called ProjectSuppliers. There is a one to many relationships. I’m trying to get a count of the total number of projects for a certain supplier where the Date Entered is less then 5/1/2206 with the Bid Date greater than 3/31/2206.

When I run the query below the count comes back wrong. I know it’s because the ProjectSupplier table can have multiply entries of the supplier under the same Projectid.

For example, the query below gives me a count of 206 projects for Miller in Tampa.

Where he really only has 169 projects entered.

I know you can run two queries to get the results.

But because the data is being display on a Web page, I really would like the one SQL statement to handle this.

ProjectHeader Table	ProjectSuppliers Table
ProjectID	ProjectID
Bid Date	SupplierName
	Date Entered
	District
	Modifed By

SELECT Count(ProjectHeader.ProjectID) AS CountOfProjectID, ProjectHeader.District, ProjectSuppliers.[Modifed By]

FROM ProjectSuppliers INNER JOIN ProjectHeader ON ProjectSuppliers.ProjectID = ProjectHeader.ProjectID

WHERE (((ProjectSuppliers.[Bid Date])>#3/31/2006#) AND ((ProjectSuppliers.SupplierName)='Some Supplier') AND ((ProjectSuppliers.[Date Entered])<#5/1/2006#))

GROUP BY ProjectHeader.District, ProjectSuppliers.[Modifed By]

HAVING (((Count(ProjectHeader.ProjectID))>0))

ORDER BY Count(ProjectHeader.ProjectID) DESC , ProjectHeader.District, ProjectSuppliers.[Modifed By];

Hi,

I tried to simplify the syntax you have used in the script

And I end up with the below one (I changed column names for simplicity)

SELECT
Count(PH.ProjectID) AS CountOfProjectID,
PH.District,
PS.ModifedBy
FROM ProjectSuppliers PS
INNER JOIN ProjectHeader PH ON PS.ProjectID = PH.ProjectID
WHERE
PS.BidDate > #3/31/2006#
AND PS.SupplierName ='Some Supplier'
AND PS.DateEntered < #5/1/2006#
GROUP BY PH.District, PS.ModifedBy
HAVING Count(PH.ProjectID) > 0
ORDER BY Count(PH.ProjectID) DESC, PH.District, PS.ModifedBy

The first problem I see is that there is not a "Bid Date" column in Project Suppliers, so this query will not work.

Am I missing something?

|||

I believe all you have to do is add the distinct keyword to make what you want to work.

Select count(distinct ProjectHeader.ProjectID)AS CountOfProjectID, ProjectHeader.District, ProjectSuppliers.[Modifed By]
FROM ProjectSuppliers INNER JOIN ProjectHeader ON ProjectSuppliers.ProjectID = ProjectHeader.ProjectID
WHERE (((ProjectSuppliers.[Bid Date])>#3/31/2006#) AND ((ProjectSuppliers.SupplierName)='Some Supplier') AND ((ProjectSuppliers.[Date Entered])<#5/1/2006#))
GROUP BY ProjectHeader.District, ProjectSuppliers.[Modifed By]
HAVING (((Count(ProjectHeader.ProjectID))>0))
ORDER BY Count(distinct ProjectHeader.ProjectID) DESC , ProjectHeader.District, ProjectSuppliers.[Modifed By];

I haven't bothered checking if my assumption is right...but I think this should solve your problem of duplicate bids being counted twice.

|||I think he juxtaposed biddate and dateentered on the table. In that case he'd need to switch it in the query as well.|||

Try this query.

SELECT Count(ProjectSuppliers.SupplierName) AS CountOfProjectID, ProjectSuppliers.District, ProjectSuppliers.ModifiedBy
FROM ProjectSuppliers INNER JOIN ProjectHeader ON ProjectSuppliers.ProjectID=ProjectHeader.ProjectID
WHERE (((ProjectHeader.BidDate)>#3/31/2006#) And ((ProjectSuppliers.SupplierName)='Some Supplier') And ((ProjectSuppliers.DateEntered)<#5/1/2006#))
GROUP BY ProjectSuppliers.District, ProjectSuppliers.ModifiedBy
HAVING (((Count(ProjectSuppliers.SupplierName))>0));

I used these tables and filled with some dummy data.

ProjectHeader ProjectID BidDate 1 3/30/2006 10 4/1/2006 2 3/29/2006 3 2/25/2006 4 5/5/2006 5 4/15/2006 6 4/8/2006 7 5/1/2006 8 4/10/2006 9 5/2/2006

ProjectSuppliers ProjectID SupplierName DateEntered District ModifiedBy 4 Other Supplier 4/25/2009 Miami 4/20/2006 3 Other Supplier 4/27/2006 Tampa 4/29/2006 2 Other Supplier 4/29/2006 Miami 4/30/2006 1 Other Supplier 5/11/2006 Tamp 4/16/2006 9 Other Supplier 4/19/2006 Miami 4/15/2006 8 Some Supplier 4/19/2006 Tampa 4/15/2006 7 Some Supplier 4/18/2006 Tampa 4/8/2006 6 Some Supplier 4/20/2006 Tampa 4/8/2006 5 Some Supplier 4/20/2006 Tampa 4/11/2006 4 Some Supplier 4/25/2006 Tampa 4/20/2006 3 Some Supplier 4/27/2006 Tampa 4/29/2006 2 Some Supplier 4/29/2006 Tampa 4/30/2006 1 Some Supplier 5/11/2006 Tampa 4/16/2006

Please let me know if it worked.

-Ujj

|||

Try this query.

SELECT Count(ProjectSuppliers.SupplierName) AS CountOfProjectID, ProjectSuppliers.District, ProjectSuppliers.ModifiedBy
FROM ProjectSuppliers INNER JOIN ProjectHeader ON ProjectSuppliers.ProjectID=ProjectHeader.ProjectID
WHERE (((ProjectHeader.BidDate)>#3/31/2006#) And ((ProjectSuppliers.SupplierName)='Some Supplier') And ((ProjectSuppliers.DateEntered)<#5/1/2006#))
GROUP BY ProjectSuppliers.District, ProjectSuppliers.ModifiedBy
HAVING (((Count(ProjectSuppliers.SupplierName))>0));

I used these tables and filled with some dummy data.

ProjectHeader

ProjectID	BidDate
1	3/30/2006
10	4/1/2006
2	3/29/2006
3	2/25/2006
4	5/5/2006
5	4/15/2006
6	4/8/2006
7	5/1/2006
8	4/10/2006
9	5/2/2006

ProjectSuppliers

ProjectID	SupplierName	DateEntered	District	ModifiedBy
4	Other Supplier	4/25/2009	Miami	4/20/2006
3	Other Supplier	4/27/2006	Tampa	4/29/2006
2	Other Supplier	4/29/2006	Miami	4/30/2006
1	Other Supplier	5/11/2006	Tamp	4/16/2006
9	Other Supplier	4/19/2006	Miami	4/15/2006
8	Some Supplier	4/19/2006	Tampa	4/15/2006
7	Some Supplier	4/18/2006	Tampa	4/8/2006
6	Some Supplier	4/20/2006	Tampa	4/8/2006
5	Some Supplier	4/20/2006	Tampa	4/11/2006
4	Some Supplier	4/25/2006	Tampa	4/20/2006
3	Some Supplier	4/27/2006	Tampa	4/29/2006
2	Some Supplier	4/29/2006	Tampa	4/30/2006
1	Some Supplier	5/11/2006	Tampa	4/16/2006

This is the result I got.

CountOfProjectID	District	ModifiedBy
1	Tampa	4/11/2006
1	Tampa	4/15/2006
1	Tampa	4/20/2006
2	Tampa	4/8/2006

Please let me know if it worked.

-Ujjwal Kaji Shrestha