Simple Query Design Puzzle 1

[dominicdunmow]

I have 2 tables, with the fields below:-

Table1:

ID Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8
-- -- -- -- -- -- -- -- --
1 4 8 6 1 2 7 5 7
2 5 9 8 4 2 5 4 4
3 4 6 8 7 4 4 2 2

Table2:

ID Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8
-- -- -- -- -- -- -- -- --
1 2005 2005 2006 2006 2006 2007 2007 2007
2 2005 2006 2006 2006 2006 2007 2007 2007
3 2005 2005 2005 2006 2007 2007 2007 2007

table1 represents revenue payment values per quarter (not calendar quarters), Table2 represents the years that the revenue payments will be received. I need to build a query where for each ID the total revenue value per year is displayed. This has to be dynamic as the payment years can change.

Something along the lines of: ID1 Q1 and Q2 payments are in 2005 so the 2005 total will be 4 + 8. I don't know how to work this into a query though.

Thanks

 

[PHV]

Either play with code (VBA and Recordset) or normalize your database schema.

http://www.abcdataworks.com/fundamentals.htm

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[r937]

if you prefer not to open Word docs, you can read the exact same article on a web page here --

http://r937.com/relational.html

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[lespaul]

and one other option, create a query that normalizes your data and then use that query as the source for what you are trying to do:

table1

SELECT ID As "ID", 'Q1' As "Quarter", Q1 As "Amount" From Table1
UNION
SELECT ID, 'Q2', Q2 From Table1
UNION
SELECT ID, 'Q3', Q3 From Table1

etc...

You will end up with:

1 Q1 4
1 Q2 8
1 Q3 6

do the same thing for table two. Using ID, Quarter and Year for the As field names.

Use those two queries as the source for your original problem. then your final query will be something like:

SELECT A.ID, A.Quarter, Sum(A.AMount) FROM query1 A INNER JOIN query2 B on A.ID = B.ID and A.Quarter = B.Quarter WHERE B.Year = 2005
GROUP BY A.ID, A.Quarter

Leslie

 

[dominicdunmow]

Leslie,

many thanks for your post. I've carried through most of it, but I'm having trouble with the final query.

I've substituted "A" for the name of the query derived from table 1, and "B" the name of the query derived from table 2, but I'm unsure about where you mention Query1 and query2.

Thanks

 

[lespaul]

so you created two queries, and named them A and B? Then your final query will be:

SELECT A.ID, A.Quarter, Sum(A.AMount) FROM A INNER JOIN B on A.ID = B.ID and A.Quarter = B.Quarter WHERE B.Year = 2005
GROUP BY A.ID, A.Quarter

Leslie