How can I use DMax function and apply it to a single customer ID?

[DanicaDear]

I have a report named "rptHOTSTICKS_EXPIRING" based on a query named "HOSTICKS_SHIPPING_REPORT Query". The query contains these fields:
ORDER_NUM (text)
CUST_NUM (text)
Name, address, contact info, I think not important to this thread
DATE_SHIP (date)
Then I have a field

STICKS_EXPIRE: DateAdd("yyyy",2,[DATE_SHIP])

with criteria

Between [From Date] And [To Date]

where [FromDate] and [To Date] are parameters.

My report is to let me know when sticks are coming up that are 2 years old or more. However, if I have already shipped a new order (say like last week) I want to exclude it from my report. For example, I have a customer with sticks expiring 12/10/09, but I shipped him a new order 11/4/09. Currently he appears on my report but I do not want him to. (The report is to alert me of upcoming orders to fill, and his won't be one because it's already filled!) I thought I could use DMax to help me out here, but I don't know how to limit it to each customer number in the report. Right now the result gives me the max ship date in the entire table and reports that date for every result of the query. I thought I could use criteria on the DMax result, perhaps if LAST_SHIPMENT and DATE_SHIP are equal, then put them on the report.
Here is how I'm trying to use the DMax command in my query. (I'm not saying it's correct, but I tried.) (HOTSTICKS_ORDERS is a table name. I didn't learn good naming practices in time for this one.)

LAST_SHIPMENT: DMax("[DATE_SHIP]","HOTSTICK_ORDERS")

I hoped since the query was based on CUST_NUM it would do DMax for each customer. I also tried putting [CUST_NUM] on the end of that DMax argument but that didn't work either. Could anyone offer me any pointers?

As always, if I'm not clear, please let me know. Thanks in advance!!!!!!

[ChipR]

I just can't figure out what you mean by "sticks that are coming up that are 2 years old or more". Those terms don't appear to relate to anything in your post.
From your example, why don't you just do a select from customers who haven't ordered within the last 2 years?

[DanicaDear]

ChipR, thanks for the fast reply. The reason why I don't want to just look at orders over 2 years is because (trying to make long story short) these sticks are safety devices and we actually have to ship a new order before the old order becomes two years old. The customer ships back the old order *after* receiving the new order. They can never be without sticks.

I think the real reason I have this arranged the way I do is because we test the sticks they return and put them back into inventory. To ensure we have enough sticks to fill all upcoming orders, we may want to look at *just* next month's orders, or *just* what we need the month after that, and not everything over two years old. I hope this answers your question and sheds light on my efforts.

If you still think I'm going about this the wrong way I'm willing to listen. I think what you are saying has merit and usually I have to let these things settle in for a little while before I totally get it. Thanks!!

[ChipR]

Can you define exactly what you want to see in the report based on the fields and information you've given?

[topher23]

Danica likes to talk about her sticks, as anyone who has helped her knows. It might be helpful to know what a "hotstick" is, for those who have nothing to do with the pwer business.

A hotstick an electrically insulated pole, usually made of fiberglass, that high-voltage electrical workers use to shut off power switches from a safe distance, or to connect tools to when they have to do work without cutting off the power. This is why they are very important safety items. OSHA in the U.S. requires that they be tested and replaced if defective every two years, so businesses like Danica's ship out known good replacements every two years and take back the old ones for testing.

Hope that's helpful. My father in law's a substation tech.

[DanicaDear]

Right on, topher!!!!!!!!!!!!!!!!!!!!!

[DanicaDear]

ChipR,
If I can't, I'm in trouble. LOL. So here goes:
I want to see CUST_NUM whose STICKS_EXPIRE (as defined above) are between the two parameters defined above, *and* there are no DATE_SHIP occuring later than the one causing the STICKS_EXPIRE to be within my parameters. (note STICKS_EXPIRE is based off of DATE_SHIP).
For instance, if STICKS_EXPIRE 12/10/09, then the DATE_SHIP would be 12/10/08. If 12/10/08 is the last DATE_SHIP, I want it in the report. If that same CUST_NAME has a DATE_SHIP entry of 11/4/09, I don't want it in the report. I'm including SQL of my current query; it might help you. Be aware, I don't really understand how to work in SQL--I still consider myself a beginner.

PARAMETERS [From Date] DateTime, [To Date] DateTime;
SELECT HOTSTICK_ORDERS.ORDER_NUM,
       HOTSTICK_ORDERS.CUST_NUM,
       HOTSTICK_CUSTOMERS.[First_Name] & " " & [Last_Name] & " " & [Suffix] AS Whole_Name,
       HOTSTICK_CUSTOMERS.ADDR1,
       HOTSTICK_CUSTOMERS.ADDR2,
       HOTSTICK_CUSTOMERS.ADDR3,
       [Addr1] & (Chr(13) & Chr(10)+[Addr2]) & (Chr(13) & Chr(10)+[Addr3]) & (Chr(13) & Chr(10)+[city_ST_zip]) AS Addr,
       [HOTSTICK_CUSTOMERS].[CITY] & ", " & [ST] & "  " & [ZIP] AS City_ST_Zip,
       HOTSTICK_CUSTOMERS.INTERCOMPANY_PHONE,
       HOTSTICK_CUSTOMERS.EXTERNAL_PHONE,
       HOTSTICK_CUSTOMERS.INTERCOMPANY_PHONE,
       HOTSTICK_CUSTOMERS.[RADIO/LINC],
       HOTSTICK_CUSTOMERS.CELL,
       HOTSTICK_CUSTOMERS.COMPANY_EMAIL,
       HOTSTICK_CUSTOMERS.DIV,
       HOTSTICK_ORDERS.DATE_SHIP,
       HOTSTICK_ORDERS.BOX_NUM,
       HOTSTICK_ORDERS.DATE_RET,
       HOTSTICK_ORDERS.REMARKS,
       DateAdd("yyyy",2,[DATE_SHIP]) AS STICKS_EXPIRE,
       DMax("[DATE_SHIP]","HOTSTICK_ORDERS") AS LAST_SHIPMENT
 
FROM   HOTSTICK_CUSTOMERS INNER JOIN
       HOTSTICK_ORDERS
  ON   HOTSTICK_CUSTOMERS.CUST_NUM=HOTSTICK_ORDERS.CUST_NUM
 
WHERE  (DateAdd("yyyy",2,[DATE_SHIP]) Between [From Date] And [To Date]);

[DanicaDear]

I'm sorry about that long line in the CODE. I don't know how to make it not do that.

[topher23]

I see what you're trying to do now, anyway. Try something like this:

Your SQL statement...
WHERE (((DateAdd("yyyy",2,[DATE_SHIP])) Between [From Date] And [To Date])) 
AND [DATE_SHIP] = DMax("[DATE_SHIP]","HOTSTICK_ORDERS","[CUST_NUM] = " & HOTSTICK_ORDERS.CUST_NUM) ; 

That feels like it will work.

[ChipR]

What if the same customer has multiple shipping dates which will be expiring, and none more recent? Would you want to show them all, or only the latest?

[topher23]

And here's an alternative if it doesn't.

Make a second query with only the [CUST_NUM] and [DATE_SHIP] fields in Query Designer. Now, right-click and activate the Totals. Set [DATE_SHIP]'s total to Max and [CUST_NUM] to Group By.

Now, do a Join from your original query's [DATE_SHIP] to this new query's [DATE_SHIP]. Have it show all values from your original query and only those from the new one that match (I think that's a right join, but I can never remember those directions). Now, pull either of the fields from the new query into your original query and do a filter on it for Is Not Null.

EDIT: You can actually just do a join where all fields match for this (inner join), and skip the filter for Is Not Null. Sorry, I was thinking originally about filtering out the matches, not only allowing matches.

Now you will only get records in which the record listed is the newest record for that customer.

Viva la SQL! :)

[topher23]

Dangit, Chip, why do you want to ruin things with questions?

Chip has a point. Would such an occasion ever exist?

[NeoPa]

Performance-wise, using DMax() (or any of the Domain Aggregate functions) within a SQL query is bad news. I would consider using GROUP BY in your query. Maybe as a linked sub-query.

PS. Carriage Retun/Line Feeds can be done using the predefined vbCrLf in your code (or in the SQL).

[DanicaDear]

@ChipR
ChipR, now that I've had time for this to sink in, I think you're right on. If there was a *new* order, then there should be a *new* expiration date, so it wouldn't fall in my range. To continue the same example....if the 11/4/09 order was made, then the new expiration date would be 11/4/11 and it wouldn't come up when I enter my parameter from 12/1/09 to 12/31/09. However, that's *not* what is happening in my report...it's pulling up all orders that *ever* had an expiration date during that time. I need the report to look at just the *latest* expiration date. Perhaps I could use my DMax there.

To All, I haven't read all your replies yet. You may have already provided me with the info I seek. I'm caring for a sick child tonight. Will study up on what you wrote and get this worked out tomorrow. Thanks everyone!

[DanicaDear]

topher23,
The SQL you provided didn't quite work and plus NeoPa suggested not using DMax. So I'm going to try your second approach. Why must I have 2 queries? Why can't you do the Max total on the main query? (I already know it won't work because I tried it.) :-)

ChipR,
It is possible to have a customer having two orders expiring at the same time. It's very unlikely but still possible. However, they would have separate order numbers (I haven't discussed order number in this thread...it's a subreport, so I don't think it's an issue.) I would want to see all that customer's orders expiring between the parameter dates specified.

[DanicaDear]

topher23,
It worked!!!!!!!!!!!!!!!!!! Thanks so much! You people are so slick it makes me sick. Hahahahaha.

[NeoPa]

@DanicaDear
Generally you just need to remember two things :

1. Use the CODE tags (You already know this).
2. Use only spaces for formatting. Never TABs.

[DanicaDear]

@NeoPa
NeoPa,
I copied the SQL and pasted it in. I didn't use spaces or tabs. LOL. I just used the query wizard and clipboard. :-) So now what?

[NeoPa]

I would always recommend people reformat SQL before posting. Each field on a separate line; Use the same position on the line as much as possible so they display as a column. These things make reading it much easier, and spotting problems (yourself before even posting it) much more likely.

Remember that Access creates SQL exclusively for its Jet SQL Engine. Access isn't failing in that sense, as it is quite fit for purpose. Posting on a forum however, is quite a different thing and has different requirements. I would never dream of posting SQL straight from a QueryDef into a post (although we do often ask members to do just that - anything more complicated can prove a step too far). Most of our experts can work with that, but as it gets ore complicated, so it gets exponentially more difficult to work with. On the other hand, having a post with clearly structured SQL makes it easier to answer, and therefore gives you a better chance of an appropriate reply.

Formatting the SQL in a text editor (They typically use non-proportional fonts so columns can be easily aligned correctly) prior to posting is a good idea and a technique I always use.

[topher23]

@DanicaDear
With the Totals option, you really have to think about what you're trying to do. When you use totals, everything in your query has to total somehow (unless it's an expression, which is an entirely different issue) or be a Group On field. When a query isn't too complicated, you can use just one query, but as it gets more complex the variables involved when you do totals can create undesired results. So, if all you're looking for in the totals is the Max of a certain value (like the date), but you don't want to hassle with all of the other issues that come up on a query that uses totals, you do it with 2 queries.

And, as NeoPa noted, using those 2 queries is much better, performance-wise, than using DMax. With 2 queries, both queries are only run once, for a total of just 2 queries. The DMax function is sort of a query in itself, along with some extra processing, so what you end up with is a second query running for every record in the query. The total number of queries run for the set is then the number of records in the query plus one for the original query. And once you've got several thousand records, that's a lotta queries!

[NeoPa]

As he said...

That explains perfectly why I don't recommend using Domain Aggregate functions (DMax(), DCount(), DSum(), etc) within queries.

[DanicaDear]

Great explanations. Thanks to you all.