Query and Sub Query Help Please (1 Viewer)

GK in the UK

Registered User.
Local time
Today, 08:01
Joined
Dec 20, 2017
Messages
170
Query and Sub Query Help Please

Trying to get an updateable query working which draws totals from a sub query.

I can't get the totals from qrysub_QtyDelivered into the main query

Here's a screenshot of the sub query result set and the main query result with the missing result

sql.jpg



Code:
SELECT tblTransHeaders.TransHeaderID, tblTransLines.TransLineID, tblTransLines.tlCtrlPosting, tblTransLines.tlVatPosting, tblTransLines.tlProductFK, qrysub_QtyDelivered.qDelivered, tblSelectLines.slOrdPicked, tblSelectLines.slOrdCancelled

FROM tblTransHeaders

INNER JOIN (qrysub_QtyDelivered

RIGHT JOIN (tblTransLines

LEFT JOIN tblSelectLines

ON tblTransLines.TransLineID = tblSelectLines.SelectLinesID)

ON (qrysub_QtyDelivered.TransHeaderID = tblTransLines.tlOrderHeaderFK) AND (qrysub_QtyDelivered.tlProductFK = tblTransLines.tlProductFK))

ON tblTransHeaders.TransHeaderID = tblTransLines.tlTransHeaderFK

WHERE (((tblTransHeaders.TransHeaderID)=[HeaderID]) AND ((tblTransLines.tlCtrlPosting)=False) AND ((tblTransLines.tlVatPosting)=False));
 

theDBguy

I’m here to help
Local time
Today, 00:01
Joined
Oct 29, 2018
Messages
8,840
Hi. Even if you could get the result from the subquery into your main query, I am not sure the result will be updateable because of those OUTER JOINs. Just my 2 cents...
 

The_Doc_Man

Happy Retired Curmudgeon
Local time
Today, 02:01
Joined
Feb 28, 2001
Messages
16,350
OK, we can't see one important part of the linkage here. Isolating from that query, you have this:

Code:
...
INNER JOIN (qrysub_QtyDelivered
...
ON (qrysub_QtyDelivered.TransHeaderID = tblTransLines.tlOrderHeaderFK) AND (qrysub_QtyDelivered.tlProductFK = tblTransLines.tlProductFK))
'''
What you showed us is a questionable mess because of the LEFT and RIGHT joins (not to mention an INNER join) in a single query. However, my question is whether there is a chance that the tlOrderHeaderFK and or tlProductFK are linking up properly. I can see that the header and product are in the table but they come through a "middle-man" and that is always a bit "Iffy."

I might have done this as a layered query just to keep the logic in check. The combined query might well work but you are having issues with it. Having a layered query would help you debug the layers so that when you do the final join you know that everything really IS available.
 

GK in the UK

Registered User.
Local time
Today, 08:01
Joined
Dec 20, 2017
Messages
170
Having slept on it I think dbguy is right in saying it’s going to be non-updateable. The sub query that returns a sum will prevent any field from being updateable.

Not sure how I will deal with it but let’s say I drop that requirement. What I want to do sounds straightforward:

For each single record record that the main query returns, along with the quantity saved in that single record, show me the *sum* of the quantities from the same table, with two matching PK/FK pairs.

Doc_Man, the questionable mess was created in the Query Designer. Can you explain a 'layered query'? I thought that by doing the summing in the sub query that would help.

qry2.jpg
 

isladogs

CID Moderator
Staff member
Local time
Today, 08:01
Joined
Jan 14, 2017
Messages
12,601
Have you tried changing SELECT to SELECT DISTINCTROW and using recordset type =inconsistent updates?
Its worth at least trying each in turn then both of them.
 

GK in the UK

Registered User.
Local time
Today, 08:01
Joined
Dec 20, 2017
Messages
170
Thanks Colin. I tried the DistinctRow and the query returns the same result set.

The inconsistent updates - I had to look that up - I think it's a form setting? At the moment I haven't got this query in my form, I'm trying to get the right results just by popping a parameter in (HeaderID) when it runs.

The problem I'm having is with the sub query qrysub_QtyDelivered. It appears to do nothing. My SQL skills aren't great but I *think* it must be to do with the joins and the way the query engine executes the query. It seemingly never finds a match on the sub query. I've tried all combinations of joins and I get nothing returned, by that I mean, I get records returned but the field qDelivered is never populated. Yet running the sub query in isolation gets what I expect as you can see from my first screenshot.

If I can get it to work but it turns out to be non-updateable that gives me a different problem so I think I'm still a little way off getting the functionality I need.

Just for info tblSelectLines is 1:1 on tblTranslines. It's a fe temp table used as a 'holding place' for user input before we commit to the be table.
 

Yev18

New member
Local time
Today, 08:01
Joined
Aug 17, 2018
Messages
4
It looks like you've joined on the wrong column. Both the Query text and the QBE have

Code:
qrysub_QtyDelivered.TransHeaderID = tblTransLines.tlOrderHeaderFK
but it should be

Code:
qrysub_QtyDelivered.TransHeaderID = tblTransLines.tlTransHeaderFK.
 

GK in the UK

Registered User.
Local time
Today, 08:01
Joined
Dec 20, 2017
Messages
170
Well spotted, Yev18. But it is as it is meant to be. I don't know if this counts as a 'novel' solution or a 'wacky' one but it works for my db schema.

User adds an order to the system.
A header record is created in tblTransHeaders.
n line records are created in tblTransLines.
A line record could be an order for (say) for 100 widgets.
The link is as usual, tlTransHeaderFK > TransHeaderID - as you correctly identified.

Only 10 widgets are available and we are ready to despatch.
User opens the order and enters 10 items to be despatched.

Now it gets interesting/unusual/weird

A new header record is created in tblTransHeaders, for the delivery note.
A new line record is created in tblTransLines for the 10 widgets despatched.
The link is tlTransHeaderFK > TransHeaderID - as you would expect.
An additional link is created:
tlOrderHeaderFK > TransHeaderID.

So now we have two documents in the db, the original order, not updated apart from the links, and the delivery note.

Now we could despatch another 10 widgets and we repeat interesting/unusual/weird

So now we have three documents in the db, the original order, not updated apart from the links, and two delivery notes.

They each have their own line records.

Now when we return to the order to make another despatch of widgets, I want to be able to show how many widgets have already been despatched. So I need to lookup the records in tblTransLines which, in the normal way, are linked to the Delivery Note header, but are ALSO linked to the order header. So the lookup is on tlOrderHeaderFK > TransLineID.
 
Last edited:

isladogs

CID Moderator
Staff member
Local time
Today, 08:01
Joined
Jan 14, 2017
Messages
12,601
Inconsistent updates is one of the recordset type options on the query property sheet.
It is rarely used but can be useful in cases where multiple joins make a query read only

Capture.PNG
 
Last edited:

The_Doc_Man

Happy Retired Curmudgeon
Local time
Today, 02:01
Joined
Feb 28, 2001
Messages
16,350
@GK in the UK

I mentioned "layered queries" and you inquired as to what I meant.

A query doesn't have to reference a table. You can build a query that has another query in its FROM clause. You did this yourself by joining to your qrysub_QtyDelivered. I'm just saying that for debug purposes, take the innermost join (of whatever flavor) out and make it a separate named query. Working your way out, continue extracting the innermost join until all you have left is what was your original outermost join.

The reason you would do this is because it is a "divide and conquer" strategy. You can debug each layer to assure that it contains what you want and DOESN'T contain what you didn't want. Stated another way, make each contributor a self-contained part. When you do that, you assure that the things you wanted to see are visible for the next layer. And remember, a query takes up very little space if it is not open. There can be issues with too many layers but I doubt you would have more than three or four layers and Access can go deeper than that.
 

GK in the UK

Registered User.
Local time
Today, 08:01
Joined
Dec 20, 2017
Messages
170
Thanks, Doc_Man. Yes, I've been working on this and I'm pretty sure I can get the result I want by creating two 'base' queries and a third 'joining' query. So it is certainly possible to get the SELECT that I need. Thank you. The updating is a different issue I'll have to deal with that separately.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom