Loop VBA for Dmax records from subtable

[kstevens]

Lets say i have 2 tables (ok i really do). One is tblShipping, the other is tblShippingDeta ils. I need to run a query to find all of the maximum values of a qty backordered, so that i can find out what is left to so that i can multiply it by its price and determine how much is still left to ship, but for all open orders. We do partial shipments, and i am not recording the ship values on another table, it is appending the original table, because so many of the values change.

So I decided that i would find the maximum backordered qty, subtract it from the original qty, then multiply what is left by the unitprice. I know how to write a temp table, use dmax("QTYBackOr der","tblShippi ngDetails","Job Number ='" & [this is the problem part]) to get the value from the details table, how do i then move to the next job number to look for the max "qtybackord er". I am not sure how to look for the next job number to write to a temp table so that i am just reading a table with all of the max quantities.

Should i first sort the table, if i do, how do i move to the next different JobNumber. I hope i have given enough information. If i knew how to find the max for each jobnumber with a query(wizard of course, sql scares me, lol) then i would just use the qry as the record source, but i couldnt figure it out. I couldnt figure out what criteria to use for job number..... or how to word it.

Any help is greatly appreciated. If i havent given enough information, feel free to tell me.

[NeoPa]

I suspect the info is all (mostly) there K, but the problem is that the order it's presented in is so random that it's very difficult to work with.

I don't understand why you're looking for a maximum value rather than a sum, if you're trying to determine what has been shipped.

Your line of code however, should be constructed differently depending on what type of field [JobNumber] is.
You don't say how you have access to the required JobNumber value, so I shall assume, for the sake of clarity, that you have a variable with it stored in. If it is a string value then this will be strJobNo :

... = DMax("[QTYBackOrder]","[tblShippingDetails]","[JobNumber]='" & strJobNo & "'")

If it is numeric, then it will be lngJobNo :

... = DMax("[QTYBackOrder]","[tblShippingDetails]","[JobNumber]=" & lngJobNo)

[kstevens]

Sorry,

I missed something very easy there, that you pointed out. I have (with a query)taken the first jobnumber record (there are multiples, due to the fact that we sometimes ship partial orders) and the sum of the qty shipped, i dont know why i was trying to do this the hard way.....Thanks NeoPa.

Btw - I could have inserted the criteria for jobnumber, but i needed to run the query on all records.

[NeoPa]

I don't pretend to understand you clearly K, but if I've helped then I'm pleased.

I do get the impression that you're now processing with a query rather than a single (or even multiple) call(s) to a Domain Aggregate function (DMax). If so, that certainly seems a more sensible approach.

Success with your project.

[kstevens]

Just for a little clarification: I have reports for the president and multiple vice presidents that let them know what the $ value of what we have shipped for the month is. Then they wanted the value of what was still in house, for all open orders that have either not shipped or have not completely shipped. I know i was a little random, but i was having trouble, and beleive it or not just by you saying the word "SUM" helped me tremendously.

My original method was query, i was having trouble with it and decided i had more options if i used code, with dlookups and create table, and append queries, while looping.....(no t ever thinking about summing the actual shipped qty's..... brain fart). But i have the query finished and it all seems very simple now.

Sometimes i just need a person that understands access to bounce questions off of, and make me think for myself. Need a job? lol

Thanks again.

[NeoPa]

@kstevens
I'm absolutely not kidding when I say that's exactly the same for me.

Sometimes, just talking to someone who has the intelligence to nod wisely as I stream my thought processes out, can help to clarify things enormously. If they actually know something (even just a little) about what I'm saying as well, that's a bonus.

PS. Thankfully, I'm not looking for alternative employment at this (very difficult) time.

[OldBirdman]

I agree with you. If I think the person I am talking to MAY (even with low probability) be able to answer the question, I can answer half of them myself. But if I ask my Teddy Bear, it doesn't work.

I get half my questions answered by creating the questions for this forum. I never actually have to submit them, 'cause I know the answer.

Part of it is that if I am going to ask someone else, I have to actually formulate the question, and get rid of all those partial/fuzzy ideas running around in my head. I never post in haste.

[NeoPa]

No you don't. I always know if a thread has your name on it then it will tax me. Always the question is fully explained too.