Solved Weird Dictionary Issue (1 Viewer)

[LGDGlen]

Hi All

As an example I have 2 master records with 6 and 2 child records respectively selected from a recordset and want to get the counts of master and child into a dictionary to use in calculations later:

    Set rs = Form.Recordset.Clone
    With rs
        .MoveFirst
        Do While Not .EOF
            delProdID = !del_prod_id
            If Me.txtSelected Like "*," & delProdID & ",*" Then
                consID = !cons_id
                If Dict_consDeliveryNumbers.Exists(consID) = False Then
                    Dict_consDeliveryNumbers.Add Key:=consID, Item:=1
                Else
                    Dict_consDeliveryNumbers(consID) = Dict_consDeliveryNumbers(consID) + 1
                End If
            End If
            .MoveNext
        Loop
        .MoveFirst
    End With
    
    Dim consSplitAmount As Double
    consSplitAmount = Me.txtTotalAmount / Dict_consDeliveryNumbers.Count

    For Each iKey In Dict_consDeliveryNumbers.Keys
        Debug.Print "!!!" & iKey & "!!!", Dict_consDeliveryNumbers(iKey)
        'Debug.Print "For Consignment: " & iKey & " the amount for each of the " & CStr(Dict_consDeliveryNumbers(iKey)) & " deliveries would be:  " & CStr(consSplitAmount / CInt(Dict_consDeliveryNumbers(iKey)))
    Next iKey

Weirdly i get:

!!!34283!!!
!!!34283!!!  6
!!!34285!!!  2

as the debug output, I have no idea where that first dictionary key is coming from, it shouldn't exist as its the same as the second 1.

Anyone got any ideas what i'm doing wrong, never seen this before and i've used dictionaries before and not had an issue so i'm sure i'm doing something stupidly wrong

Thanks in advance

Glen

 

[LGDGlen]

IGNORE ME, i'm an idiot, i had a bit of debug code further up that i hadn't removed, sorry, it was there for me to use to remember the format of the adding to a dictionary and forgot to delete it ffs

 

[Pat Hartman]

There is no reason to do this with code or to store the results. Use a totals query.

 

[LGDGlen]

@Pat Hartman thanks for the feedback, i'm not sure "no reason" would be accurate, the above code was using an example, there would be potentially 10s of combinations of the consignment parents to delivered products children i'd have to requery many times getting totals for the deliveries under each consignment, unless (and this is totally probably) i don't quite understand how total queries would work. the user can select a number of parents from a list which is stored in a hidden form variable, this would need to be the IN for a query to return ALL the consignments (parents) then i'd need to get the totals of the number of deliveries for each of the consignments so i could

- firstly divide the total invoice amount by the number of consignments
- and then each portion is portioned further by the number of deliveries under each consignment

if that is possible in 1 query without having to do any VBA work to update the query could you give an example as i'd honestly REALLY like to be able to do this in a better way and want to learn but i am not experienced enough in queries to even know where to start with that

 

[Pat Hartman]

Using an In() clause to select multiple records is easy enough to do. You just can't do it with an argument. You have to use VBA to construct the In() and append it to the rest of the query.

I can't make much sense out of the rest of the statement. Although you might need two queries that you later join to do the calculations since the domains for each seem to be different and queries operate on sets of data rather than using procedural logic and loops as does VBA. You are telling me what you cannot do rather than describing what you want to do. Queries are optimized to handle related data efficiently and are always much faster than using a recordset that you read/create with VBA. Saving the results from a query just bloats your database regardless of whether you create the temp table in the FE or BE and therefore you have to compact much more frequently than you would otherwise. Besides, you should NEVER have to compact the FE if you distribute it properly.

I've attached a sample db that has three examples of using the In() clause.

You seem to be happy enough with your current solution. I was trying to point out that there might be a better way. If you want to take the journey, we'll be happy to help. The world isn't going to come crashing down because you are using a less efficient method although if your database ever holds enough data, you might wonder why it is slowing down over time and why you have to compact every day. When you are dealing with only a few thousand records, almost nothing you do makes a difference. I frequently work with multi-million row data sets so EVERYTHING I do matters. Therefore, over the years I have been able to train myself to automatically do things the most efficient way even when it won't actually matter. It gives me fewer decisions to make during development. Just in summary, queries are always faster than VBA code loops once you get more than a few thousand records. The point at which you will see significant differences in time will vary with what you are trying to do and the amount of data you are pushing around. If you have 50 columns in your table but you only need 3 of them, specifically select those three in a query rather than working directly with the table or a Select * query. This is even more important if you ever use a non-ACE RDBMS as the BE.

 

[LGDGlen]

@Pat Hartman thanks for responding, it is much appreciated. i want to learn better ways to do things as i come from a development background in things like c++ etc so my go to is "code it" and i have no sql/query/database experience so this is all new to me. i usually know what i want to achieve and can see ways to do it, but usually the ways are not the most efficient.

i was pretty sure i would need some VBA to do this particular functionality due to the nature of capturing the data and manipulating it to enable me to perform this specific task.

i'll take a look at your examples and see if there are things that i can adapt and improve for my specific situation, if i have questions i'll raise em (so probably )

once again thanks for the detailed response it is much appreciated.