SQL Count From Joined Tables

[DMT Dave]

Hi Guy's i have searched quite a few times on this one



I have all results from this SL, (deliberately not pasted full code) as i beelive this is just an adjustment

So hopefully i can ask teh correct question here: records stored in tblRoutePLan may will have 1 record per (delTo) and (PostCode) there would only be 1 record, in tblEdit, there is 4 items for this Delto / Poscode

So tblRoute Plan will show DelNo, DelTo, Town, PostCode, Qty of 4

In tblEdit there is 2 under a certain status and 2 on another status (strStatus)

what do i need to change here to get the 2 records from 4 because there is a (not in) strStatus

so another way for me to ask, there are 4 records, the SQL will out put 4 because thats is the count but i am looking to change this to out put where strStatus is met which woydl be 2 of 4 records

if i need to post more, i can but hopefully this is just an adjustemt ?

sSQL = "SELECT tblRoutePlan.DelNo, tblRoutePlan.DelTo, tblRoutePlan.Town, tblRoutePlan.PostCode, tblEdit.Qty" _            
    & " FROM tblEdit" _
            & " INNER JOIN tblRoutePlan ON tblEdit.PostCode = tblRoutePlan.PostCode" _
                    & " WHERE (((tblRoutePlan.RouteNo) = " & intRoute & ")" _
                    & " And ((tblRoutePlan.WeekNum) = " & iWeekThis & ")" _
                    & " And ((tblRoutePlan.DelDate) = #" & Format(dtDelDate, "mm/dd/yyyy") & " #)" _
                    & " And ((tblEdit.ShipmentDate) = #" & Format(dtShipDate, "mm/dd/yyyy") & " #)" _
                    & " AND ((tblEdit.Status) Not In ('" & strStatus & "'))" _
                    & " AND ((tblEdit.TotalBox) > 0))" _
                    & " ORDER BY tblRoutePlan.DelNo;"
    Debug.Print sSQL

[June7]

Show sample data and desired output. Build tables in post with Go Advanced editor (or can even copy/paste records) or attach file.

[DMT Dave]

Thanks June 7, i will add some more info, bear with me but thank you

Hipefully I can do this without sensitive data

[madpiet]

Just create a query based on the table with the sensitive columns and leave those columns out of the query. Then turn that into a make table query, and include the table it creates instead of the base table.

[DMT Dave]

Thanks guy's so just quickly wiped out senstive data, hope this will help you to help me, if i need to ask differently then i let me know but you guys will be able to re adjsut my SQL i guess

So here goes

source data x 4 records (note: status for 2 of them is "On Hold"



Listing if deliveries grouped to a route number / the highlighted is the 4 records (2 on hold)



strStatus in the SQL = "On Hold" so now the 4 should only out put 2 to excel, all works apart from the SQL is not ruling out strStatus

excel result



See my post 1 where i am trying to rule out strStatus ("On Hold") so 4 records now shoulld be 2 on the excel sheet

sorry if i have asked this incorrect

[DMT Dave]

so i guess i am asking, is my tblEdit.Qty in the Select incorrect (because ther eis 4 records) however, i am trying to rule out 2 of them ?

[DMT Dave]

SO the strStatus is not ruling out 2 records on hold

sSQL = "SELECT tblRoutePlan.DelNo, tblRoutePlan.DelTo, tblRoutePlan.Town, tblRoutePlan.PostCode, tblEdit.Qty" _
& " FROM tblEdit" _
& " INNER JOIN tblRoutePlan ON tblEdit.PostCode = tblRoutePlan.PostCode" _
& " WHERE (((tblRoutePlan.RouteNo) = " & intRoute & ")" _
& " And ((tblRoutePlan.WeekNum) = " & iWeekThis & ")" _
& " And ((tblRoutePlan.DelDate) = #" & Format(dtDelDate, "mm/dd/yyyy") & " #)" _
& " And ((tblEdit.ShipmentDate) = #" & Format(dtShipDate, "mm/dd/yyyy") & " #)" _
& " AND ((tblEdit.Status) Not In ('" & strStatus & "'))" _
& " AND ((tblEdit.TotalBox) > 0))" _
& " ORDER BY tblRoutePlan.DelNo;"
Debug.Print sSQL

[Welshgasman]

If you only have one value for strstatus, then just use <>

[jojowhite]

what if you directly put the "Delivery" to your query:

sSQL = "SELECT tblRoutePlan.DelNo, tblRoutePlan.DelTo, tblRoutePlan.Town, tblRoutePlan.PostCode, tblEdit.Qty" _
& " FROM tblEdit" _
& " INNER JOIN tblRoutePlan ON tblEdit.PostCode = tblRoutePlan.PostCode" _
& " WHERE (((tblRoutePlan.RouteNo) = " & intRoute & ")" _
& " And ((tblRoutePlan.WeekNum) = " & iWeekThis & ")" _
& " And ((tblRoutePlan.DelDate) = #" & Format(dtDelDate, "mm/dd/yyyy") & " #)" _
& " And ((tblEdit.ShipmentDate) = #" & Format(dtShipDate, "mm/dd/yyyy") & " #)" _
& " AND ((tblEdit.Status) = 'Delivery')" _
& " AND ((tblEdit.TotalBox) > 0))" _
& " ORDER BY tblRoutePlan.DelNo;"
Debug.Print sSQL

if still it includes 4 records, check the status field of tblEdit in Design view,
is it String or Number?

If number then Status field has a LookUp, and you need to put the correct
numeric value that equates to 'Delivery':

[code]

sSQL = "SELECT tblRoutePlan.DelNo, tblRoutePlan.DelTo, tblRoutePlan.Town, tblRoutePlan.PostCode, tblEdit.Qty" _
& " FROM tblEdit" _
& " INNER JOIN tblRoutePlan ON tblEdit.PostCode = tblRoutePlan.PostCode" _
& " WHERE (((tblRoutePlan.RouteNo) = " & intRoute & ")" _
& " And ((tblRoutePlan.WeekNum) = " & iWeekThis & ")" _
& " And ((tblRoutePlan.DelDate) = #" & Format(dtDelDate, "mm/dd/yyyy") & " #)" _
& " And ((tblEdit.ShipmentDate) = #" & Format(dtShipDate, "mm/dd/yyyy") & " #)" _
& " AND ((tblEdit.Status) = IDForDeliveryStatus)" _
& " AND ((tblEdit.TotalBox) > 0))" _
& " ORDER BY tblRoutePlan.DelNo;"
Debug.Print sSQL

[DMT Dave]

Hello jojowhite, yes it was something i considered as i am only looking for 1 status, rather than ruling out what i DONT need, only rule what i DO need.

Going to come back to this very soon but thanks for your input and others

And Yes, Status is a String value

I do have all results apart from the count where status = delivery

[DMT Dave]

So i have quickly adapted to your suggestion, tblEdit.Qty is 4 (total records for postcode etc...

this now outputs 2 record from 4 which is correct but Qty gone to zero, i guess is this just a grouping issue ? OR i am thinking of my 1st question on this Does tblEdit.Qty need to be a self made field Count / Sum of Qty ?

the query of this SQL does have the correct amount of records but Qty Field = 2 of 4 records, so that why i am thinking either a unique count / sum within the criteria's or a group issue ?

I should only have 1 row per delivery, the result in post 5 comes from tblRoutePlan without INNER Join to tblEdit

Attachment 52615

[Minty]

As a tip, where possible always specify what you ARE looking for rather than using NOT IN.
NOT IN won't make use of any indexing and on a large dataset will really slow things down due to inefficiency.

[DMT Dave]

Yes, thanks Minty, i did consider that was the wrong approach as mentioned in #10,

i am going to re create a query and get the results prior to setting as variable, start from scratch and see results first