Sub query syntax for FROM clause (1 Viewer)

[GK in the UK]

Help please on sub query syntax. I've been tweaking this for ages, read the Allen Browne stuff etc. etc. but still can't get it right.
I am trying to insert the sub query into the FROM clause. I need the summed value qValueAllocated. The main query is also a group sum of n records from a different table.

Here's the main query:

SELECT tblCustsSupps.CustSuppID, tblTransHeaders.TransHeaderID, tblTransHeaders.thDocReference, tblTransHeaders.thDate, tblTransHeaders.thDueDate, tblTransHeaders.thYourRef, tblTransHeaders.thPpDiscount, tblTransHeaders.thPpdAutoAdjust, tblTransHeaders.thPpdTaken, Sum(Nz([tblTransLines].[tlNetValue],0)+Nz([tblTransLines].[tlVatValue],0)) AS qGrossValue


FROM tblCustsSupps INNER JOIN

(tblTransHeaders INNER JOIN tblTransLines ON tblTransHeaders.TransHeaderID = tblTransLines.tlTransHeaderFK)

ON tblCustsSupps.CustSuppID = tblTransHeaders.thCustSuppFk


GROUP BY tblCustsSupps.CustSuppID, tblTransHeaders.TransHeaderID, tblTransHeaders.thDocReference, tblTransHeaders.thDate, tblTransHeaders.thDueDate, tblTransHeaders.thYourRef, tblTransHeaders.thPpDiscount, tblTransHeaders.thPpdAutoAdjust, tblTransHeaders.thPpdTaken
HAVING (((tblCustsSupps.CustSuppID)=900))
ORDER BY tblTransHeaders.TransHeaderID;

Here is the sub query that I want to insert in the FROM clause. It's tested and working gets the summed value I need per the criteria.
I would replace "PIN000103" with tblTransHeaders.thDocReference (from the main query)

SELECT tblAllocations.allocTransHeaderFK, tblAllocations.allocDocReference, tblAllocations.allocDate, Sum(tblAllocations.allocValueAllocated) AS qValueAllocated
FROM tblAllocations
GROUP BY tblAllocations.allocTransHeaderFK, tblAllocations.allocDocReference, tblAllocations.allocDate
HAVING (((tblAllocations.allocDocReference)="PIN000103"));

 

[vba_php]

GK,

I don't write many subqueries and I've produced quite a few large pieces of architecture in access. but since no one else has said anything here, I would ask you this:

Your queries seem overly complicated. Isn't there anyway you can simplify this stuff so you either:

=> don't need any subqueries at all, or:
=> can accomplish the goal with less complication, either in the main query itself or the subquery

In this regard, I would be interested in seeing a snapshot of your relationships window to see how this whole structure of yours looks. sorry, I don't have an answer regarding the SQL-based solution. but my first guess would be: "Are you sure you can insert a sub that has a GROUP BY in it, when the main has a GROUP BY in it?". or simply: "can you nest these things in the manner you are doing when there is so much aggregation involved"?

 

[GK in the UK]

Adam, I would love to simplify it.
I've had various incarnations of this query but none produces the correct result.

What I want is:
For a single record in tblCustsSupps
Show me all related records in tblTransHeaders, with the total of all related records in tblTransLines, and the total of all related records in tblAllocations.

I tried a version (without a sub query) which summed the 3 records in tblAllocations, so I thought I had it working, but then discovered that the totals for the single document PIN000103 had been summed 3 times.
So the query was doing the selection from tblTransHeaders the same number of times as there are records in tblAllocations and giving a wrong result.

Allan Browne mentions this multiple summing on his page and it seems like I need a sub query.

Basically, my proposed sub query does the summing of the 3 lines. Then the main query must do its work with no opportunity to make the round trip to tblTransHeaders more than once. If only I could figure out how to insert the sub query. I keep getting 'Syntax Error in FROM clause'

 

[June7]

Inner nested query doesn't 'know' the outer query therefore cannot pull parameter from it. Outer query 'knows' inner query. Both datasets need a field with DocReference values they can be joined on. Which query name is designation for the inner query? I see only table names. Build the full outer query that references the other saved query object so syntax is correct then copy/paste inner query SQL statement into the main query. If you want to provide db for analysis, follow instructions at bottom of my post.

 

[vba_php]

What I want is:
For a single record in tblCustsSupps
Show me all related records in tblTransHeaders, with the total of all related records in tblTransLines, and the total of all related records in tblAllocations.

what I was asking for is that you show us the relationships of your *tables* in the relationships window, not the relationships in the query you are running. The reason I asked that is because I was thinking your architecture could be changed, if you have the time and will, to make things easier on yourself so you don't have to get involved in nesting activities like this. Honestly, I've seen *very* few situations where a simple architecture can't solve problems. And another honest note....I'm doing a few other things today, so reading through your explanation is a bit tough right now, but I think June is right in saying that, you uploading a replica of your file would help a great deal, because that way someone can simulate the problem. That's what I always do when helping someone.

and another thing....your datasheet fields don't show the entire field names (even though your other photo does show everything)! To give the helpers a bit of a hand, make sure you make all the relevant details available, otherwise people are left to *guess* what is what.

 

[GK in the UK]

Thanks June7.
I've got a working inner query with 'input' criteria of DocReference.
I've got a working outer query with no reference to the inner query or tblAllocations (shown in post #1 Main Query)

The working outer query is related on the PK/FK.
However I do save DocReference in tblTransLines as well (to be honest I thought this was a normalisation violation but it's in there)
DocReference is also saved in tblAllocations.

Are you saying I should relate on DocReference ? I could relate tblTransHeaders and tblTransLines on DocReference if necessary.

My inner query is currently named 'qryFrmPayment_Allocated by DocReference' but I was going to embed the entire query in the SQL and have it in the module code.

Can you expand a bit on " Build the full outer query that references the other saved query object so syntax is correct then copy/paste inner query SQL statement into the main query."

Really that's my question. How do I insert my inner query (also in post #1) into the main query ?

 

[June7]

Don't know how to be more descriptive. Query can use a query like table. Build query that includes tables/queries properly joined on key fields. Reference to a query can be replaced with the saved query's SQL statement via copy/paste then delete the query object if you want. Example:

2 queries
(this first query object is named GamesSorted and referenced in second query)
SELECT Games.*, Teams.Division, Teams.PlayLevel FROM Teams RIGHT JOIN Games ON Teams.ID = Games.HomeTeam;

SELECT GameID, GameDate, Division, PlayLevel, "Base", UmpID FROM Umpires RIGHT JOIN GamesSorted ON Umpires.UmpID = GamesSorted.Base

1 query
(combines above two queries into 1 statement)
SELECT GameID, GameDate, Division, PlayLevel, "Base", UmpID FROM Umpires RIGHT JOIN (SELECT Games.*, Teams.Division, Teams.PlayLevel FROM Teams RIGHT JOIN Games ON Teams.ID = Games.HomeTeam) AS GamesSorted ON Umpires.UmpID = GamesSorted.Base

 

[GK in the UK]

All working now thanks, last post was a big help