Joining Two related queries
Dear all;
I would like to find out whether it is possible to join two related queries to come up with summary data before create a report , example:
Query 1
Has the following fields
ProductID SalesDate SalesMixValue
Query 2
ProductID SalesDate SalesMixvalue
The idea here is to find the cumulative movement in terms of SalesMixValue , the thing is that each customer is given a discount on daily basis $200.00 any unused discounts is carried forward on a daily basis for future use , hence the need to determine the sales value on cumulative basis by product ID and Sales Date:
The mechanism here is that Query 2 must be always behind by one day lets say instead of pulling the current data as on 24/08/2017 it should just pull as on 23/08/2017
Query 1 must always pull the current data that is as on 24/08/2017
Now what is required here is to subtract Query 2 (salesMixvalue) from Query1 (salesMixvalue) so that the accurate sales value netted with discount is reported as daily sales value.
Kindly see how you can help, I have heard of net query but frankly I do not understand it .
Regards
Chris
I would like to find out whether it is possible to join two related queries to come up with summary data before create a report , example:
Query 1
Has the following fields
ProductID SalesDate SalesMixValue
Query 2
ProductID SalesDate SalesMixvalue
The idea here is to find the cumulative movement in terms of SalesMixValue , the thing is that each customer is given a discount on daily basis $200.00 any unused discounts is carried forward on a daily basis for future use , hence the need to determine the sales value on cumulative basis by product ID and Sales Date:
The mechanism here is that Query 2 must be always behind by one day lets say instead of pulling the current data as on 24/08/2017 it should just pull as on 23/08/2017
Query 1 must always pull the current data that is as on 24/08/2017
Now what is required here is to subtract Query 2 (salesMixvalue) from Query1 (salesMixvalue) so that the accurate sales value netted with discount is reported as daily sales value.
Kindly see how you can help, I have heard of net query but frankly I do not understand it .
Regards
Chris
Pawan Kumar
Can you pls provide some sample data and the expected output?
AS at now I'm not at HO I'm in another city so it will be very difficult to do that , but all what I wanted to find out is that is it possible to design the queries like that?
Yes possible... we just need to design the logic..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thank you so much
You are welcome!
/gustav
/gustav
or possibly:
Also, the use of a non-equi Join (Q1.SalesDate = Q2.SalesDate + 1) will not be visible in the query designer, only in the SQL view.
I also added the NZ( ) function to your computation to ensure that if there is a gap in dates or if either of the SalesMixValues is NULL, you will still get a value in the SalesMixDifference computation.
Select
Q1.ProductID,
Q1.SalesDate,
Q2.SalesDate as Q2SalesDate,
Q1.SalesMixValue,
Q2.SalesMixValue as Q2SalesMixValue,
NZ(Q1.SalesMixValue,0) - NZ(Q2.SalesMixValue, 0) As SalesMixDifference
From
Query1 as Q1 LEFT JOIN Query2 as Q2
ON Q1.ProductID = Q2.ProductID
AND Q1.SalesDate = Q2.SalesDate + 1
Order By Q1.ProductID, Q1.SalesDateAlso, the use of a non-equi Join (Q1.SalesDate = Q2.SalesDate + 1) will not be visible in the query designer, only in the SQL view.
I also added the NZ( ) function to your computation to ensure that if there is a gap in dates or if either of the SalesMixValues is NULL, you will still get a value in the SalesMixDifference computation.
Gustav,
I disagree with your solution. use of the syntax:
Query1.SalesDate = Date()
And
Query2.SalesDate = DateAdd("d", -1, Date())
Will only get records where there are records in Query1 for Date, and when there are records in Query2 for date-1. But what if there is a gap in dates. Using the left outer join in my query results in still getting the Query1 records even when there is no record in Query2
Dale
I disagree with your solution. use of the syntax:
Query1.SalesDate = Date()
And
Query2.SalesDate = DateAdd("d", -1, Date())
Will only get records where there are records in Query1 for Date, and when there are records in Query2 for date-1. But what if there is a gap in dates. Using the left outer join in my query results in still getting the Query1 records even when there is no record in Query2
Dale
But what if there is a gap in dates.
I see what you mean, but the question does state, that data exist for both days.
/gustav
Call it professional skepticism.
Plus the Join syntax is more efficient than the cartesian join that your query uses
Dale
Plus the Join syntax is more efficient than the cartesian join that your query uses
Dale
Approved answer.