Can you do an Inner Join on the results of 2 queries? (1 Viewer)

[LambtonWorm]

Hi,

I have a form with many slow and cumbersome dLookup formulae in textboxes. I want to replace it with a neat and dynamic query subform.

For this I need to add the numerical results of two queries in something analagous to an inner join. But inner joins only exist for tables, and you can't establish relationships between queries.

The two queries are similar, but have to be separate because the filter conditions on the 'Time Sent' field are mutually exclusive, i.e. no overlap.
Both queries return a 'Strategy' field. I would want to add the respective 'xyz_USDPNL' results of the two queries whereever 'Strategy' is identical, or assume zero when not present.

Here is the SQL for the two queries. They work fine so there's nothing to troubleshoot. 'dCutoff()' just grabs a constant.

Query 1:

SELECT CLng([Time Sent]-0.5+dCutoff()) AS TradeDate, [Security] & " " & [Family] & "_" & [TrigInterval] AS Strategy, Sum([Contracts]*([Spot]-[TradedPrice])*[tblStatic]![PointValue]-Abs([Contracts])*[tblStatic]![ClearingCosts])*[FXSpot] AS NEW_USDPNL
FROM tblFX INNER JOIN (tblStatic INNER JOIN tblTrades ON tblStatic.Asset = tblTrades.Security) ON tblFX.KeyCcy = tblStatic.CCY
GROUP BY CLng([Time Sent]-0.5+dCutoff()), [Security] & " " & [Family] & "_" & [TrigInterval], tblTrades.Confirmed, tblFX.FXSpot
HAVING (((CLng([Time Sent]-0.5+dCutoff()))=Date()) AND ((tblTrades.Confirmed)=Yes));

Query 2:

SELECT [Security] & " " & [Family] & "_" & [TrigInterval] AS Strategy, Sum([StartDayPosn]*([Spot]-[PrevClose])*[tblStatic]![PointValue]-Abs([StartDayPosn])*[tblStatic]![ClearingCosts])*[FXSpot] AS OLD_USDPNL
FROM tblFX INNER JOIN (tblStatic INNER JOIN tblTrades ON tblStatic.Asset = tblTrades.Security) ON tblFX.KeyCcy = tblStatic.CCY
GROUP BY CLng([Time Sent]-0.5+dCutoff())<Date(), [Security] & " " & [Family] & "_" & [TrigInterval], tblTrades.Confirmed, tblFX.FXSpot
HAVING (((CLng([Time Sent]-0.5+dCutoff())<Date())=Yes) AND ((tblTrades.Confirmed)=Yes));

Please could someone point me in the right direction?
Is there a way of rewriting and combining the queries? (There's more to add, this is just the start..) Or do i need to create a temporary table every time this is refreshed? Or is it impossible?

Many Thanks!

 

[Guus2005]

Save the first query as Query1 and the second query as Query2
Use both queries as they where tables.

select * from Query1 inner join Query2 on Query1.Id = Query2.id

Ofcourse you need a common field (id) on which you can join the two queries.
That doesn't have to be an Id.


HTH

 

[LambtonWorm]

Dank u wel Guus!

That really put me in the right direction, so simple really but I didn't realise that was allowed with queries in the place of tables.
By the way your example gives the inner join, whereas I in fact need a FULL OUTER JOIN.

It turns out that a full outer join is not supported by Access, but i found this helpful solution for anyone who is interested:

Originally Posted by databasejournal.com
Access does not support the FULL OUTER join clause. Therefore, to simulate the FULL OUTER join functionality you need perform three different joins, and then UNION together the three different result sets. Where Set 1 would be created using an INNER JOIN, Set 2 would be created with a LEFT OUTER JOIN and Set 3 would be created using a RIGHT OUTER JOIN.

 

[Guus2005]

Are you sure you need a full outer join?
Can't think of a reason where you might want to need one. But hey, that's me.

If you're absolutely sure then the way you describe is the way to go by creating three queries.

HTH

 

[LambtonWorm]

Hi Guus,
Not sure because I'm no expert, but it seeems right to me. I will explain.

Query 1 might return numerical results for Strategies: A, B and C.
Query 2 might return results for Strategies: C, D and E.

I need a final query that gives me the sum across queries 1 & 2 for ALL strategies that have a result in EITHER or BOTH.
A & B don't appear in Query 2, and D & F don't appear in results of Query 1, but I do not want to omit them. I need everything! If I don't use FULL OUTER JOIN i can not get all 5 A to E.
That's why I want a FULL OUTER JOIN. If that sounds wrong then let me know!

Thanks again to you.

 

[Guus2005]

I need more information because why wouldn't Query1 return results for strategies C, D en E?

Can you post a sample database describing the problem?

 

[vega]

Hello,

I think I have a similar problem and I think post # 2 above relates to the solution.
I have 2 queries, one calculating picking cost per (strawberry) crate per day, 2nd query calculating hourly cost per crate per day. Essentially there are 2 dimensions that I am measuring, picking and hourly.

When I run these 2 queries separate they give me the calculate cost per unit per day, however, when I create a third query to add qry 1 and qry 2 (to derive TOTAL cost per unit) the query quadruples the results.

I think I am missing something obvious

 

[LambtonWorm]

Hi Guus,
Query 1 would return no results for strategies C D & E if there are no records in the DB which satisfy Query1's condition on the 'Time Sent' field.
Specifically, Query 1 looks for TimeSent in the last 24 hours, for which a different calculation is required, hence the different query.
It is entirely possible for there to exist records which satisfy Query2, but none that satisfy Query1 (they are mutually exclusive). In that case you would get results for Q2 and null from Q1. My Full Outer Join idea seems to be working ok.

If you still think the thread would benefit from a sample DB let me know and I'll create one. Thanks again for your interest.

Vega: Not sure, but have you established the Join between the 2 queries appropriately? Otherwise could you be getting a cartesian product and hence quadrupling? Am not an expert, it's just an idea.