No idea how to approach query 1

[shoff]

I am currently working on my first real database project. Since this is my first, I am bit confused on the best way to approach my problem.

I currently have 3 tables that should store and total production annually. The format is:

(Table Capital) Paid_To | sYear | sJanuary | sFebruary | sMarch...
(Table Advisors) Paid_To | sYear | sJanuary | sFebruary | sMarch...
(Table Corporation) Paid_To | sYear | sJanuary | sFebruary | sMarch...
*year field is the year and not the actual total
*actual total is being genereated using SQL

I want to create a query that adds up total production (Capital production + Corporation Production + advisor Production) monthly and annually based on the existing tables. It should be availabe for the given time period for each Paid_to field. (List how much was paid to each person for the given months)

I imagine I could do this by using various arrays (I often end up doing that) but I think that an actual SQL solution would probably be much better. I don't know if I should use a join, or what join to use.

The closest think I could think about using was an outer join and that I couldn't get that to work. I realized that I was following the SQL advice for SQL Server and I am using access. That might have been part of the problem.

I'm pretty stumped and looking for solutions!

Thanks,
Scott

 

[Vancouverite]

Personally, I would start with a view:

Create View vwPayments AS
SELECT Capital.Paid_to, Capital.sYear, Capital.sJanuary, Capital.sFebruary....
UNION ALL
SELECT Advisors.Paid_to, Advisors.sYear, Advisors.sJanuary, Advisors.sFebruary....
UNION ALL
SELECT Corporation.Paid_to, Corporation.sYear, Corporation.sJanuary, Corporation.sFebruary....

Then, I would select from it like this (sample - you may need more):

SELECT Paid_to, sYear, Sum(sJanuary), Sum(sFebruary), ...
FROM vwPayments
GROUP BY Paid_to, sYear

 

[shoff]

I haven't tried it yet... but thank you very much. I really appreciate your help.

 

[shoff]

It states that I neeed at least on valid table or query...

do you know what that means?

the syntax seems different than what listed on devguru:

query1
UNION [ALL]

query2
[UNION [ALL]

queryn [ ... ]]
[GROUP BY grouplist, [...]]

any ideas?

 

[Vancouverite]

I probably should have included the FROM line in the example. You did add those, yes?

Create View vwPayments AS
SELECT Capital.Paid_to, Capital.sYear, Capital.sJanuary, Capital.sFebruary....
FROM Capital
UNION ALL
SELECT Advisors.Paid_to, Advisors.sYear, Advisors.sJanuary, Advisors.sFebruary....
FROM Advisors
UNION ALL
SELECT Corporation.Paid_to, Corporation.sYear, Corporation.sJanuary, Corporation.sFebruary....
FROM Corporation

 

[lespaul]

in reviewing your thread, the best piece of advice is NORMALIZE your data. Not sure what your database is designed to do, but at first glance it doesn't appear to be normalized. Trust me, you will be happier in the long run if you take care of this at the beginning.

Leslie

 

[shoff]

That fixed my problem for now.... Thanks again for your help.

 

[shoff]

I'm out of practice... You suggest that I go back in and must make a feild for capital, corp, advisor?

 

[lespaul]

Here's a link to the first of several articles that discuss what normalization is and how to accomplish it:

http://databases.about.com/library/weekly/aa080501a.htm

 

[mnk82o]

If you want to avoid creation of view and running yearly totals for each Paid_to, as specified in your original problem, then use this:

SELECT 'Capital ' TblId, Paid_to, sYear, sJanuary, sFebruary....
INTO #Temp
FROM Capital
UNION ALL
SELECT 'Advisors ' TblId, Paid_to, sYear, sJanuary, sFebruary....
FROM Advisors
UNION ALL
SELECT 'Corporate' TblId, Paid_to, sYear, sJanuary, sFebruary....
FROM Corporation
GO
SELECT 'Total ' TblId, Paid_to, sYear, sum(sJanuary) sJanuary, sum(sFebruary) sFebruary....
FROM #Temp
GROUP BY Paid_to, sYear
union
SELECT TblId, Paid_to, sYear, sJanuary, sFebruary....
FROM #Temp
ORDER BY sYear, Paid_to, TblId


MNK