Joins and Unions

[wxkeep]

Have two tables

Sales
-Sold Date
-Salesman
-Amount

Returns
-Return Date
-Salesman
-Refund Amount

Anytime a sale is made - its recorded into the sales table. Likewise anytime a return is made - it's recorded in the return table.

I have a form - that accepts dates- to build a custom query for sales. That way - you can easily design the query to give you all the sales number for a given week, month, year, or open date range. Based on sales date.

I'd like to incorporate into that query the return numbers in two different scopes. Current YTD returns and returns during this period.

So - for example:

Sales
John 1-1-07 500
Mike 1-3-07 500
John 2-8-07 1000
Mark 2-8-07 750
John 2-9-07 200

Returns
John 1-6-07 400
Mike 2-4-07 75
John 2-7-07 100


If the report was ran for the month of february - I'd like a query to give me the following:

Name Sales ReturnsThisPeriod YTDReturns
John 1200 100 500
Mark 750 0 0
Mike 0 75 75

But I'm getting all sorts of lost in how to get all of this in one query - so I can pass it into a report. Any suggestions that you guys could provide - would be fantastic. Thanks!

 

[AlexCuse]

Here is an idea for you:

select a.Name, a.PeriodSales, a.YTDSales
, b.PeriodReturns, b.YTDReturns
from
(
select Name
, Sum(iif(month([SoldDate]) = [Enter Month], [Sales], 0)) as [PeriodSales]
, Sum(iif(year([SoldDate]) = [Enter Year], [Sales], 0)) as [YTDSales]
from Sales
group by Name
) a
left join
(
select Name
, Sum(iif(month([ReturnDate]) = [Enter Month], [Returns], 0)) as [PeriodReturns]
, Sum(iif(year([ReturnDate]) = [Enter Year], [Returns], 0)) as [YTDReturns]
from Sales 
group by Name
) b
on a.Name = b.Name

(not tested at all)

You will need to play around with your IIF statements to get the proper logical test for what you are trying to accomplish, but this should point you in the right direction.

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[wxkeep]

That definitely gets me started - and I can see where you're going with it. One question, since that is left joined - it is going to give me a list of all sales for the selected period - regardless of whether or not that salesman had returns (which is what I wanted) but it doesn't look like it will give me data on returns if the person didn't have any sales...How do I do both a left AND a right join of these two tables?

 

[AlexCuse]

You will want to create a table with all salesman names, and left join from this table to sales and returns in the two subqueries on name. This way all names are present in the two derived tables. To keep this from becoming a maintenance nightmare, I would take use a query like this for your 'name' table (so that it does not need extra maintenance):

select distinct(Name) from sales
union select distinct(Name) from Returns
order by Name

This will return a list of all names that appear in either table.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[jimirvine]

Hi, I would suggest a different table structure if you have the level of control over the db that would allow you to re-structure. Since each return is similar to each sale (it's just that the product and money are 'moving' in different directions) have a single table table to store both returns and sales:

tblTransactions
TransactionID
Transaction_Date
Transaction_Amount
SalesPersonID
Transaction_Type (Sale or return)

tblSalesPerson
SalesPersonID
FName
LName
etc

The queries then become much more straightforward and maintenance is simplified.

HTH
Jim