I can't figure this out! SQL Statement

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

I can't figure this out! SQL Statement

Author

Topic

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2003-03-18 : 08:54:56

I have 6 tables:

ProjectInformation
ProjectID | ProjectName | City | Province

ShippingLoadInfo
ShippingDate | ProjectID | LoadNumber

StructuralSchedule
ProjectID | ComponentID | ShippingDate | LoadNumber

StructuralComponent
ProjectID | ComponentID | Weight ......

HCSchedule
ProjectID | ComponentID | ShippingDate | LoadNumber

HCComponentInfo
ProjectID | ComponentID | Weight .......

What I need to do, is get a recordset Grouping each ProjectID and LoadNumber, where ShippingDate is BETWEEN DATE1 AND DATE2. The fields I need are the City, Province, TotalStructuralWeight, TotalHCWeight for each load.

I tried setting it up in Access and when I execute, I get the wrong values. My question is this, can someone help me set up the relation ships and create the correct SQL Statement?

I tried the following relationships

ProjectInformation.ProjectID -------- ShippingLoadInfo.ProjectID

ShippingLoadInfo.ProjectID ----- StructuralSchedule.ProjectID
ShippingLoadInfo.ShippingDate----- StructuralSchedule.ShippingDate
ShippingLoadInfo.LoadNumber ---- StructuralSchedule.LoadNumber

StructuralSchedule.ProjectID ---- StructuralComponent.ProjectID
StructuralSchedule.ComponenID ----- StrucrualComponent.ComoponentID

ShippingLoadInfo.ProjectID ----- HCSchedule.ProjectID
ShippingLoadInfo.ShippingDate----- HCSchedule.ShippingDate
ShippingLoadInfo.LoadNumber ---- HCSchedule.LoadNumber

HCSchedule.ProjectID ---- HCComponent.ProjectID
HCSchedule.ComponenID ----- HCComponent.ComoponentID

Is there anyone that can help me with this? I worked on it all afternoon yesturday and I cannot seem to get it right!

MikeB

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2003-03-18 : 10:10:48

The following 3 SQL statements do what I want but I would like to combine them into one SQL statement. Is it possible. Geez, I wish I was smarter

.

To get all the Load information
csSelect.Format("SELECT tblShippingLoadInformation.ShippingDate, tblShippingLoadInformation.ProjectID, tblShippingLoadInformation.LoadNumber, tblProjectInformation.City, tblProjectInformation.Province, tblShippingLoadInformation.Carrier, tblShippingLoadInformation.Trailer, tblShippingLoadInformation.BillOfLading, tblShippingLoadInformation.Rate FROM tblProjectInformation INNER JOIN tblShippingLoadInformation ON tblProjectInformation.ProjectID = tblShippingLoadInformation.ProjectID WHERE tblShippingLoadInformation.ShippingDate BETWEEN #%s# AND #%s# ORDER BY tblShippingLoadInformation.ShippingDate DESC, tblShippingLoadInformation.ProjectID, tblShippingLoadInformation.LoadNumber",
dtBefore.Format("%Y/%m/%d"),
dt.Format("%Y/%m/%d"));

//********************************************************************
To get the weight from the structural table
csQuery1.Format("SELECT SUM(tblStructuralComponents.Weight) AS [SumOfWeight] FROM tblStructuralComponents INNER JOIN tblStructuralSchedule ON (tblStructuralComponents.ProjectID = tblStructuralSchedule.ProjectID) AND (tblStructuralComponents.ComponentID = tblStructuralSchedule.ComponentID) WHERE tblStructuralSchedule.ShippingDate = #%s# AND tblStructuralSchedule.ProjectID = '%s' AND tblStructuralSchedule.LoadNumber = %d", date.Format(_T("%Y/%m/%d")), csProject, nLoadNumber);

//********************************************************************
To get the weight from the Hollowcore table
csQuery2.Format("SELECT SUM(tblHollowcoreComponents.Weight) AS [SumOfWeight] FROM tblHollowcoreComponents INNER JOIN tblHollowcoreSchedule ON (tblHollowcoreComponents.ProjectID = tblHollowcoreSchedule.ProjectID) AND (tblHollowcoreComponents.ComponentID = tblHollowcoreSchedule.ComponentID) WHERE tblHollowcoreSchedule.ShippingDate = #%s# AND tblHollowcoreSchedule.ProjectID = '%s' AND tblHollowcoreSchedule.LoadNumber = %d", date.Format(_T("%Y/%m/%d")), csProject, nLoadNumber);

//********************************************************************
Any help for me?

Mike B

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources