Problem with sql query

I have a sql Query with Joins.. I need to join information from another table but in this case I need to sum a total
Here is my existing query
Select tblProject.KEYField, tblProject.Project_Code, tblProject.GL_Code,tblProject.Plan_Year,tblProject_Type.Name, tblProject.Name, Acres, Est_Total, c_Total, tblEmployee.Last_Name  + ', ' + tblEmployee.First_Name as FullName, Load_Start_Date, Load_End_Date, tblProject.Evac_Form_Authorized,tblProject.Is_Complete
From tblProject
Join tblProject_Type
On tblProject.FKEYProjectType = tblProject_Type.KeyField
Join tblEmployee
On tblProject.Project_Owner = tblEmployee.KeyField            
Order by tblProject.Name

Open in new window


the new table looks like this (table is called tblProject_Volume)
KEYField, FKeyProject (this is the project number from the main project table), Volume
in that table you will find several entries of Volume for each project

I need to know how to add sum(volume) to the query above
Results would show 1 line for each project and the last field would be the sum of all the volums for the that project from the tblProject_Volume table

I hope that makes since
Thanks in advance experts
David ModugnoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
Assuming that "the project number from the main project table" is also called FKeyProject then something like this should do it.

SELECT  p.KEYField,
        p.Project_Code,
        p.GL_Code,
        p.Plan_Year,
        t.Name,
        p.Name,
        Acres,
        Est_Total,
        c_Total,
        e.Last_Name + ', ' + e.First_Name AS FullName,
        Load_Start_Date,
        Load_End_Date,
        p.Evac_Form_Authorized,
        p.Is_Complete,
	v.TotalVolume
FROM    tblProject p
        JOIN tblProject_Type t ON p.FKEYProjectType = t.KeyField
        JOIN tblEmployee e ON p.Project_Owner = e.KeyField
		JOIN (
			SELECT	KEYField, FKeyProject, SUM(Volume) TotalVolume
			FROM	tblProject_Volume
			GROUP BY
				KeYField, FKeyProject) v ON p.FKeyProject = v.FKeyProject and p.KEYField = v.KEYField
ORDER BY p.Name;

Open in new window

0
David ModugnoAuthor Commented:
thanks for the answer.. I get an error when I run it

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "v.FKeyProject" could not be bound.
Msg 207, Level 16, State 1, Line 23
Invalid column name 'FKeyProject'.
0
David ModugnoAuthor Commented:
and yes  KEYField in the project table is FKEYProject in the tblProject_Volume table
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Anthony PerkinsCommented:
I suggest you post the schema (CREATE TABLE) for all the tables involved and their relationships as clearly something is getting lost in translation.
0
David ModugnoAuthor Commented:
The project table is a monster.... but here they are
tblProject_Volume
USE [TimberOps]
GO
/****** Object:  Table [dbo].[tblProject_Volume]    Script Date: 08/31/2015 09:51:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblProject_Volume](
	[KEYField] [int] IDENTITY(1,1) NOT NULL,
	[FKEYProject] [int] NOT NULL,
	[volDate] [datetime] NULL,
	[Volume] [int] NULL,
	[FKEYContractor] [int] NULL,
	[Date_Entered] [datetime] NULL CONSTRAINT [DF_tblProject_Volume_Date_Entered]  DEFAULT (getdate()),
	[FKEYVolume_Type] [int] NULL CONSTRAINT [DF_tblProject_Volume_FKEYVolume_Type]  DEFAULT ((1)),
 CONSTRAINT [PK_tblProject_Volume] PRIMARY KEY CLUSTERED 
(
	[KEYField] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
EXEC sys.sp_addextendedproperty @name=N'MS_AggregateType', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'KEYField'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=False , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'KEYField'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'KEYField'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=1275 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'KEYField'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_TextAlign', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'KEYField'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_AggregateType', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'FKEYProject'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=False , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'FKEYProject'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'FKEYProject'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=1665 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'FKEYProject'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_TextAlign', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'FKEYProject'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_AggregateType', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'volDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=False , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'volDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'volDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=1350 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'volDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DisplayControl', @value=N'' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'volDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Format', @value=N'' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'volDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_IMEMode', @value=N'0' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'volDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_TextAlign', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'volDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_AggregateType', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'Volume'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=False , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'Volume'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'Volume'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'Volume'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_TextAlign', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'Volume'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_AggregateType', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'FKEYContractor'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=False , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'FKEYContractor'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'FKEYContractor'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'FKEYContractor'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_TextAlign', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'FKEYContractor'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_AggregateType', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'Date_Entered'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=False , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'Date_Entered'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'Date_Entered'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=2580 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'Date_Entered'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DisplayControl', @value=N'' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'Date_Entered'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Format', @value=N'' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'Date_Entered'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_IMEMode', @value=N'0' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'Date_Entered'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_TextAlign', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'Date_Entered'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_AggregateType', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'FKEYVolume_Type'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=False , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'FKEYVolume_Type'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'FKEYVolume_Type'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=2340 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'FKEYVolume_Type'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'1=Budget, 2 = Actual, 3 = ??? Cutting' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'FKEYVolume_Type'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DisplayControl', @value=N'109' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'FKEYVolume_Type'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Format', @value=N'' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'FKEYVolume_Type'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_IMEMode', @value=N'0' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'FKEYVolume_Type'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_TextAlign', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'FKEYVolume_Type'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DefaultView', @value=0x02 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Filter', @value=NULL , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_FilterOnLoad', @value=False , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_HideNewField', @value=False , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_OrderBy', @value=N'[tblProject_Volume].[FKEYProject] DESC' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_OrderByOn', @value=True , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_OrderByOnLoad', @value=True , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Orientation', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_TableMaxRecords', @value=10000 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_TotalsRow', @value=False , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume'

Open in new window


tblProject .... attached file

thanks again for the help
tblProject.sql
0
David ModugnoAuthor Commented:
any thoughts experts
0
Anthony PerkinsCommented:
I suggest you post the schema (CREATE TABLE) for all the tables involved and their relationships
You included the schema for two tables tblProject and tblProject_Volume with no mention of tblProject_Type or tblEmployee.  Even without these tables I could probable guess (I just would not be able to test) if I knew what was the relationship between these two tables (tblProject and blProject_Volume) .  Is it perhaps:
1. tblProject.KEYField = tblProject_Volume.FKEYProject
2. Something else?
0
David ModugnoAuthor Commented:
I am no sql expert by any means... I grabbed where I would expect to see the relationships (see pic) but I don't see any...
It looks like when the tables were created they did not create them

but

From a use perspective
if I take a keyfield from tblProject  (418 as example is Daugherty 2) - see pic for the result
and run this query I get all volumes for that project number against the tblProject_Volume
SELECT     KEYField, FKEYProject, volDate, Volume, FKEYContractor, Date_Entered, FKEYVolume_Type
FROM         tblProject_Volume
WHERE     (FKEYProject = 418)

Open in new window


I need the sum of all the volume for that project in the other query

Sorry I don't know this stuff better.. I hop this makes since
tables.jpg
volumequery.jpg
0
David ModugnoAuthor Commented:
just to double check... I ran this query (found it online) against both tblProject and tblProject_volume
and it returned no  relationships

select name 'ForeignKeyName', 
    OBJECT_NAME(referenced_object_id) 'RefrencedTable',
    OBJECT_NAME(parent_object_id) 'ParentTable'
from sys.foreign_keys
where referenced_object_id = OBJECT_ID('tblProject_volume') or 
    parent_object_id = OBJECT_ID('tblProject_volume')

Open in new window

0
Anthony PerkinsCommented:
if I take a keyfield from tblProject  (418 as example is Daugherty 2) - see pic for the result
I think that confirms my assumption that tblProject.KEYField = tblProject_Volume.FKEYProject, so try it this way:
SELECT  p.KEYField,
        p.Project_Code,
        p.GL_Code,
        p.Plan_Year,
        t.Name,
        p.Name,
        Acres,
        Est_Total,
        c_Total,
        e.Last_Name + ', ' + e.First_Name AS FullName,
        Load_Start_Date,
        Load_End_Date,
        p.Evac_Form_Authorized,
        p.Is_Complete,
        v.TotalVolume
FROM    tblProject p
        JOIN tblProject_Type t ON p.FKEYProjectType = t.KeyField
        JOIN tblEmployee e ON p.Project_Owner = e.KeyField
        JOIN (SELECT    FKeyProject,
                        SUM(Volume) TotalVolume
              FROM      tblProject_Volume
              GROUP BY  FKeyProject
             ) v ON p.KEYField = v.FKeyProject
ORDER BY p.Name;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David ModugnoAuthor Commented:
Perfect Thank you
0
David ModugnoAuthor Commented:
Thanks for all your help... worked perfectly
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.