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
 SQL Help again of course!

Author  Topic 

MikeB
Constraint Violating Yak Guru

387 Posts
Posted - 2003-10-28 : 10:20:51
I have three tables:

tblOne
Load | Date | Project

tblTwo
Project | Component | Load | Date | Weight

tblThree
Project | Component | Load | Date | Weight

I want to list all the Loads in tblOne and sum the weight / Load from tblTwo and tblThree. Any ideas?

SELECT SUM (tblTwo.Weight) AS [W1],
SUM (tblThree.Weight) AS [W2],
tblOne.*
FROM ((tblOne INNER JOIN tblTwo
ON (tblOne.Project = tblTwo.Project)
AND (tblOne.Date = tblTwo.Date)
AND (tblOne.Load = tblTwo.Load))
INNER JOIN tblThree
ON (tblOne.Project = tblThree.Project)
AND (tblOne.Date = tblThree.Date)
AND (tblOne.Load = tblThree.Date))
ORDER BY tblOne.Date DESC, tblOne.Project DESC, tblOne.Load ASC

Man I wish I was better with this SQL stuff.
Mike B

AjarnMark
SQL Slashing Gunting Master

3246 Posts
Posted - 2003-10-28 : 11:39:08
OK, you know this is coming.... WHY do you have two apparently identical tables?

And what do you get when you run that? I would suspect an error telling you that if you are using a aggregate function (SUM) that you have to have a GROUP BY clause. And do you want the two weights themselves added together? Such as SUM(tblTwo.Weight) + SUM(tblThree.Weight)...

But, let's cut to the chase. Since Tables 2 and 3 have the same info, why don't you UNION them together (either in-line or in a separate query) and then join to that?

--------------------------------------------------------
Visit the SQLTeam Weblogs at [url]http://weblogs.sqlteam.com[/url]
Go to Top of Page

MikeB
Constraint Violating Yak Guru

387 Posts
Posted - 2003-10-28 : 12:46:05
quote:
Originally posted by AjarnMark

OK, you know this is coming.... WHY do you have two apparently identical tables?


:) Yes I did kno that was coming. The are not identical at all. Just similar fields shown as is for clarity.

quote:

And what do you get when you run that? I would suspect an error telling you that if you are using a aggregate function (SUM) that you have to have a GROUP BY clause.

Correct.

quote:

And do you want the two weights themselves added together?


Yes

quote:

But, let's cut to the chase. Since Tables 2 and 3 have the same info, why don't you UNION them together (either in-line or in a separate query) and then join to that?


I am using access, how do I do what you suggest? Any hints?

Mike B
Go to Top of Page

MikeB
Constraint Violating Yak Guru

387 Posts
Posted - 2003-10-28 : 13:26:12
This is now more difficult then the original idea. There are now 5 tables involved.

tblOne (Load Information)
Date | Project | Load

tblTwo (Product 1 Information)
Project | Component | Weight | ............

tblThree (Product 1 production/shipping schedule)
Component | ..... | ShippingDate | Load

tblFour ( Product 2 information )
Project | Component | Weight |.......

tblFive ( Product 2 production / shipping schedule)
Component | .... | ShippingDate | Load

The following is my feable attempt. The inner selects work, as a whole I get a "Syntax error in from clause".

SELECT tblOne.*, SumWeight FROM tblOne INNER JOIN 
         (SELECT tblTwo.*, Sum([tblThree].[Weight]) AS [SumWeight]
             FROM tblTwo INNER JOIN tblThree 
                ON (tblTow.Component = tblThree.Component) 
                AND (tblTwo.Project = tblThree.Project)
                GROUP BY tblTwo.ShippingDate, tblTwo.Project, tblTwo.Load
          UNION 
          SELECT tblFour.*, Sum([tblFive].[Weight]) AS [SumWeight]
              FROM tblFour INNER JOIN tblFive
                 ON (tblFour.Component = tblFive.Component)
                 AND (tblFour.Project = tblFive.Project)
                 GROUP BY tblFour.ShippingDate, tblFour.Project, tblFour.Load) AS WeigthRecordset

ON (tblOne.Project = WeigthRecordset.Project)
AND tblOne.Date = WeigthRecordset.ShippingDate)
AND tblOne.Load = WeigthRecordset.Load)
ORDER BY tblOne.Date DESC, tblOne.Project DESC, tblOne.Load

Sometimes I think I make everything hard on myself.

Mike B
Go to Top of Page

Stoad
Freaky Yak Linguist

1983 Posts
Posted - 2003-10-28 : 14:06:22
Being extremely surprized by your kinky reqs of the day
can propose for your consideration the DSum() function
instead of sql aggregate SUM().
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

3246 Posts
Posted - 2003-10-28 : 14:46:40
Mike, I assume you are building this in an Access query and not in the middle of some VBA code. To simplify for testing, you might try removing the inner union selects and put them in a query of their own. Test it to make sure you're getting the right resultset in it. Then come back to the original query and join to the new one just like you would if it were a table.

--------------------------------------------------------
Visit the SQLTeam Weblogs at [url]http://weblogs.sqlteam.com[/url]
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2003-10-28 : 15:18:49
Can I ask what is this data and why are they in seperate tables? What are you modelling in your database? what do these tables represent and how do they relate to each other?

- Jeff
Go to Top of Page

MikeB
Constraint Violating Yak Guru

387 Posts
Posted - 2003-10-28 : 16:10:57
quote:
Originally posted by AjarnMark

Mike, I assume you are building this in an Access query and not in the middle of some VBA code. To simplify for testing, you might try removing the inner union selects and put them in a query of their own. Test it to make sure you're getting the right resultset in it. Then come back to the original query and join to the new one just like you would if it were a table.


Yes, I am building it in the Access query, I did test the inner selects and they work as expected. Can I join a table to a recordset as I have shown? This seems to be the problem since it says there is a syntax error in the from clause.

quote:

Can I ask what is this data and why are they in seperate tables?

They are in separate tables because they are a different product with entirely different attributes. They only have "Project", "Shipping Date" and "Load" in common because some of each product can be shipped to the same location on the same load.

quote:

What are you modelling in your database?

I am designing "Load Scheduling" software.

quote:

what do these tables represent and how do they relate to each other?

There are 2 tables for each product.

A product definition table (properties table). When a certain component is designed it is entered into this table to store its design properties: Length, Width, Weight.....

A production table. When a component is scheduled for production, it is stored in this table......

There is a Many (Produced) to One (Component) relationship.

Mike B
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

3246 Posts
Posted - 2003-10-28 : 19:43:52
Mike, I don't see any reason Access wouldn't let you join to the derived table. I just thought it might be easier to troubleshoot if you removed it to its own query at least temporarily and narrowed the amount of code to debug.

--------------------------------------------------------
Visit the SQLTeam Weblogs at [url]http://weblogs.sqlteam.com[/url]
Go to Top of Page

MikeB
Constraint Violating Yak Guru

387 Posts
Posted - 2003-10-28 : 21:37:09
quote:
Originally posted by AjarnMark

Mike, I don't see any reason Access wouldn't let you join to the derived table. I just thought it might be easier to troubleshoot if you removed it to its own query at least temporarily and narrowed the amount of code to debug.

--------------------------------------------------------
Visit the SQLTeam Weblogs at [url]http://weblogs.sqlteam.com[/url]

Thanks for your input AjarnMark. I thought what I have is pretty logical but something has to be wrong. Is it the UNION maybe?

I am at a total loss. I have exhausted everything with this.

Mike B
Go to Top of Page

Stoad
Freaky Yak Linguist

1983 Posts
Posted - 2003-10-29 : 03:11:16
You just missed a couple of round brackets (here in red bold):

SELECT tblOne.*, SumWeight FROM tblOne INNER JOIN

(... ...) AS WeigthRecordset

ON (tblOne.Project = WeigthRecordset.Project)
AND (tblOne.Date = WeigthRecordset.ShippingDate)
AND (tblOne.Load = WeigthRecordset.Load)
ORDER BY tblOne.Date DESC, tblOne.Project DESC, tblOne.Load

Also, hope you wrote in the inner SELECT tblTwo with asterisk just for
the query's text better readability by the forum's readers as long as it
is not allowed along with GROUP BY clause.
Go to Top of Page

MikeB
Constraint Violating Yak Guru

387 Posts
Posted - 2003-10-29 : 06:58:24
quote:
Originally posted by Stoad

You just missed a couple of round brackets (here in red bold):

Also, hope you wrote in the inner SELECT tblTwo with asterisk just for
the query's text better readability by the forum's readers as long as it
is not allowed along with GROUP BY clause.

Yes I did. I actually have a pretty long field list there. :)

Thanks for your responce. I will try it as soon as I get to work!

Mike B
Go to Top of Page

MikeB
Constraint Violating Yak Guru

387 Posts
Posted - 2003-10-29 : 09:28:10
I have a solution.... sortof see
[urlhttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30157[/url]

Thanks to all for your help with this thread!!!!!!

Mike B
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site