Please help me - Combining Result Set - How To?
-
Hello all - first time poster here
I have an access db, with a table named "picks". It contains information on a stock picking group and what person picked what stock for a given day for everyday the market is open and someone submits a pick. In a query, what I do is summarize the average daily gain for each person that picks a stock based on a week's time.
For example:
------------
ID, EntryDate, Name, Stock, Open, Close, Gain
1, 1/3/2005, Scott, ABC, $1.00, $1.01, 1.00%
2, 1/3/2005, Bob, DEF, $4.05, $4.14, 2.22%
3, 1/4/2005, Scott, JKL, $2.00, $2.05, 2.50%
4, 1/4/2005, Bob, MNO, $0.55, $0.75, 36.36%
The data in the table continues, and I make report based on weeks (week 1, week 2, week 3, etc) for the month.
A sample query that I use is:
---------------------------
SELECT picks.name AS Name, Count(picks.name) AS [Days Picked], Avg(picks.gain) AS [Week 1 Avg Gain]
FROM picks
WHERE (((picks.entrydate)>=#1/3/2005#) AND ((picks.entrydate)<=#1/7/2005#))
GROUP BY picks.name
ORDER BY Avg(picks.gain) DESC;
So this would return:
--------------------
Name | Days Picked | Week 1 Avg Gain
Scott 2 1.75%
Bob 2 19.29%
The QUESTION is, How can I get Week 1, Week 2, Week 3's Average Gain together in 1 result set like this?
So this would return:
--------------------
Name | Days Picked | Week 1 Avg Gain | Week 2 Avg Gain | Week 3 Avg Gain
Scott 2 1.75% 0.45% -1.23%
Bob 2 19.29% 7.23% -4.54%
Thanks!
Scott
-
you can use a left join / inner join
if
SELECT picks.name AS Name, Count(picks.name) AS [Days Picked], Avg(picks.gain) AS [Week 1 Avg Gain]
FROM picks
WHERE (((picks.entrydate)>=#1/3/2005#) AND ((picks.entrydate)<=#1/7/2005#))
GROUP BY picks.name
ORDER BY Avg(picks.gain) DESC;
is query week 1
than you can make almost the same one for week 2
and left/inner join them on picks.name
-
So would it look like this?
SELECT picks.name AS Name, Count(picks.name) AS [Days Picked], Avg(picks.gain) AS [Week 1 Avg Gain]
FROM picks
WHERE (((picks.entrydate)>=#1/3/2005#) AND ((picks.entrydate)=#1/10/2005#) AND ((picks.entrydate)<=#1/14/2005#))
GROUP BY picks.name
ORDER BY Avg(picks.gain) DESC;
Thnaks -
Scott
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply