Help with SQL Query? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Help with SQL Query?

Author

Topic

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2003-03-25 : 11:41:54

I have 2 tables:

StructuralComponent
ProjectID (pk)| ComponentID (pk) | Length | Width | etc.....

StructrualSchedule
ProjectID | ComponentID | BarcodeID (pk) | IssuedDate

I need to get all the structural componet ComponentID's from structural components and the count of ComponentID from the StructuralSchedule where issued date != NULL and the issued date == NULL.

Is there anyone that can help me with this? I really don't know where to start!

Mike B

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2003-03-25 : 11:49:17

I tried the following in access

SELECT DISTINCT tblStructuralComponents.ComponentID,
COUNT (tblStructuralSchedule.ComponentID) AS [Total]
FROM tblStructuralComponents INNER JOIN tblStructuralSchedule
ON (tblStructuralComponents.ProjectID = tblStructuralSchedule.ProjectID)
AND (tblStructuralComponents.ComponentID = tblStructuralSchedule.ComponentID)
WHERE (((tblStructuralComponents.Type)='Solids')
AND ((tblStructuralComponents.ProjectID)='11200'));

I keep getting the following error:
"You tried to execute a query that does not include the specified expression 'ComponentID' as part of an aggregate function."

Any ideas?
Mike B

Edited by - MikeB on 03/25/2003 12:13:23

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2003-03-25 : 13:27:09

I think I got it. Is there any other way, or this it?

SELECT tblStructuralSchedule.ComponentID,
COUNT (tblStructuralSchedule.ComponentID)
FROM tblStructuralSchedule
INNER JOIN tblStructuralComponents
ON ((tblStructuralSchedule.ProjectID = tblStructuralComponents.ProjectID)
AND (tblStructuralSchedule.ComponentID = tblStructuralComponents.ComponentID))
WHERE tblStructuralSchedule.ProjectID = '11200'
AND tblStructuralComponents.Type = 'Solids'
GROUP BY tblStructuralSchedule.ComponentID;

Thanks for your interest!
Mike B

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources