Joining Two related queries

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
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?
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Yes possible... we just need to design the logic..
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Yes:

Select 
    Query1.ProductID,
    Query1.SalesDate,
    Query1.SalesMixValue - Query2.SalesMixValue As SalesMixDifference
From
    Query1,
    Query2
Where
    Query1.ProductID = Query2.ProductID
    And
    Query1.SalesDate = Date()
    And
    Query2.SalesDate = DateAdd("d", -1, Date())

Open in new window

/gustav
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!

/gustav
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
or possibly:
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.SalesDate

Open in new window

I've added the Q2 sales date as well as each of the SalesMixValues so that you can see them and compare them to the final value.
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.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
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
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Call it professional skepticism.

Plus the Join syntax is more efficient than the cartesian join that your query uses

Dale
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Approved answer.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial