Max Date on report

[1koppp]

Hi, I am trying to sort a table of work requests with several columns of dates to find the latest date of those listed. I have tried ascending order in the query, but it doesn't really do what I want it to. Ideally this 'maxdate' would then be stored back in the table in another column. The date is only needed for display in a report, so its not crucial to save it, though. Sorry if this seems like a rookie question, Pete

 

[dhookom]

Sorting is only reliably done with the Sorting and Grouping dialog in the report. Don't waste your time attempting to sort the query.

If you still have issues, please take the time to type in a few records into a reply and explain the issues you are experiencing.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[1koppp]

OK. Thanks for your response. For each work request generated, I have several dates in columns. These dates are delivery dates for pieces necessary to start making the requested item. [work request]. For easier viewing, I am trying to create a 'maxdate' or 'lastdate' category for each work request which is equal to the latest of the listed dates. WorkRequest# ADelivery Bdelivery CDelivery DDelivery

I tried several 'MAX' equations, but I am sure I had the wrong syntax. I know what we need is simple enough because the data is there, but am clueless as to where to start.(BTW: not all dates will have a value)

 

[dhookom]

The problem might be that your table structure is not normalized. Having repeating date columns is not generally good practice. Each date should be in its own record where Max() will work as advertised.

If you can't change your table structure, you can create a union query of your table:
SELECT WorkRequestNum, "A" as Delivery, ADelivery as DelivDate
FROM tblWR
WHERE ADelivery is not null
UNION ALL
SELECT WorkRequestNum, "B", BDelivery
FROM tblWR
WHERE BDelivery is not null
UNION ALL
SELECT WorkRequestNum, "C", CDelivery
FROM tblWR
WHERE CDelivery is not null
...etc...
You can then create a totals query that groups by WorkRequestNum and finds the max of DelivDate.


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[1koppp]

I'm afraid I don't understand.
This data is in a select query combined with 2 other tables already. The tables are combined in the query with the relationship being WorkRequest#.
Everything I need from the query works for the forms and reports, except my 'maxdate' problem. I have made a column in the table where delivery dates are for this number, but it is not being used; yet.
Is your response the code i need write in the report, or just used in a query? I usually get the basics wrong, so feel free to straighten me out.
Thanks Again,
Pete

 

[dhookom]

You need to provide more information about your table and fields " WorkRequest# ADelivery Bdelivery CDelivery DDelivery ". Why do you have four (or more) similar fields and what are you storing in them? If there are four fields, what happens if you need a fifth (not a fifth of Vodka)?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[1koppp]

hello again,
The actual number of fields is a lot greater than four. The table they are in indicates any special ordered components of a product that is being made from the work request. There are 3 tables tied to the work request number. One is basic work request info which includes type and style of product, quantity, order date, and when needed.
The second table is used in a query to automatically load standard part numbers for components. Upon making a selection in a subform this table is updated with standard part #'s from a part number table and appear in the subform. Both tables in the query where this occurs are essentially the same, except one (the one related to work request #) is for recording the other table's values when a work request is created.
The third table, (the one with dates), records delivery dates for NON standard components (NOT listed in table2). There are other columns in this table with text describing what is "special" about this component,& if it has been delivered (yes/no). There are actually a,b,c,d,e,f.....through k number of component categories. I only listed a-d because I figured the premise was the same.
SO, what the engineer that is scheduling wants to know is: when will he receive the last component for a work request #? That is equal to the 'lastdate' category I have been trying to describe.
Does this background information help?

 

[dhookom]

Your explanation confirms my assumption that your table of dates is not normalized which causes your Max() issue. You can create a union query as I suggested the creates a record for the date of each component. Then create a totals query based on the union query that finds the maximum date for a work request.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[1koppp]

OK. I think I understand. BUT I know I will need clarification.
Create a query where the delivery dates are listed when they are NOT NULL. (criteria)
Where do I put the 'union all' command or code, or whatever we call it?
My understanding is we are making a master date value which will then be sorted for MAX and linked with Work request # in yet another query.
BTW, what does normalized mean? (if you still have time to explain, that is)
Thanks Much for Your Time,
Pete

 

[dhookom]

You can do a search in google on "Database Normalization" and get tons of hits.

If you want a query defined for the last/max date, please provide names of the primary key field and the date fields from your third table.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[1koppp]

Ok.
Primary field is WorkRequest#
BalnDelivery
PrxInDelivery
DxInDelivery
MBDelivery
DXOutDelivery
TpDelivery
MdshftDelivery
MManifoldDelivery
B-TubeDelivery
OManifoldDelivery
StrRelDelivery
All (delivery)have input mask:99/99/00;0;_
K. Thanks Again. I guess I need to buy you a beer. Pete

 

[dhookom]

Create a union query "quniWRDelivery":
SELECT WorkRequest#, "BalnDelivery" as Deliv, BalnDelivery as DelivDate
FROM tbl3rdTable
WHERE BalnDelivery Is Not Null
UNION ALL
SELECT WorkRequest#, "PrxInDelivery", PrxInDelivery
FROM tbl3rdTable
WHERE PrxInDelivery Is Not Null
UNION ALL
SELECT WorkRequest#, "DxInDelivery", DxInDelivery
FROM tbl3rdTable
WHERE DxInDelivery Is Not Null
UNION ALL
SELECT WorkRequest#, "MBDelivery", MBDelivery
FROM tbl3rdTable
WHERE MBDelivery Is Not Null
UNION ALL
...etc for all delivery fields...

Then create a query
SELECT WorkRequest#, Max(DelivDate) as MaxDelivDate
FROM quniWRDelivery;

This should give you the most recent delivery date.


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[1koppp]

Allright! Excellent!
My problem lies in the fact that I have never normalized data, nor created a union query, NOR ever tried to use SQL.
Having read your post, which is most informative, I am now exposed to a whole other section of Access that I have not used .
I appreciate your patience and information, which has been invaluable in getting my motor kick-started on this.
As I said before, I usually get some of the basics wrong, which mucks me up in later development.
Thanks for your patience and assistance,
Pete
P.S. I will let you know how it turns out.

 

[1koppp]

K. I wasn't able to get to this until late today. I got a syntax error. Incomplete statement, I think. (Couldn't save it either).
I think it has to do with the # symbol after WorkRequest.
Any Ideas?

 

[dhookom]

Good reason to never use spaces or punctuation marks in object names. Try use [WorkRequest#]. If that doesn't work, post your entire SQL view back here.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[1koppp]

OK. Same thing: Incomplete Statement.
I tried actually adding another line that said END, which gave me other faults.
Here's the SQL:
SELECT [WorkRequest#], "BalloonDelivery" as Deliv, BalloonDelivery as DelivDate
FROM tblWRSpecialComponents
WHERE BalloonDelivery Is Not Null
UNION ALL
SELECT [WorkRequest#], "ProxInnerDelivery", ProxInnerDelivery
FROM tblWRSpecialComponents
WHERE ProxInnerDelivery Is Not Null
UNION ALL
SELECT [WorkRequest#], "DxInnerDelivery", DxInnerDelivery
FROM tblWRSpecialComponents
WHERE DxInnerDelivery Is Not Null
UNION ALL
SELECT [WorkRequest#], "MBDelivery", MBDelivery
FROM tblWRSpecialComponents
WHERE MBDelivery Is Not Null
UNION ALL
SELECT [WorkRequest#], "DXOuterDelivery", DXOuterDelivery
FROM tblWRSpecialComponents
WHERE DXOuterDelivery Is Not Null
UNION ALL
SELECT [WorkRequest#], "TipDelivery", TipDelivery
FROM tblWRSpecialComponents
WHERE TipDelivery Is Not Null
UNION ALL
SELECT [WorkRequest#], "MidshaftDelivery", MidshaftDelivery
FROM tblWRSpecialComponents
WHERE MidshaftDelivery Is Not Null
UNION ALL
SELECT [WorkRequest#], "MRManifoldDelivery", MRManifoldDelivery
FROM tblWRSpecialComponents
WHERE MRManifoldDelivery Is Not Null
UNION ALL
SELECT [WorkRequest#], "Bi-TubeDelivery", Bi-TubeDelivery
FROM tblWRSpecialComponents
WHERE Bi-TubeDelivery Is Not Null
UNION ALL
SELECT [WorkRequest#], "OTWManifoldDelivery", OTWManifoldDelivery
FROM tblWRSpecialComponents
WHERE OTWManifoldDelivery Is Not Null
UNION ALL
SELECT [WorkRequest#], "StrainReliefDelivery", StrainReliefDelivery
FROM tblWRSpecialComponents
WHERE StrainReliefDelivery Is Not Null
UNION ALL
The entire SELECT statement IS on one line, just too long for this format.
Awaiting your reply,
Pete

 

[dhookom]

Remember what I stated about "spaces or punctuation marks in object names"? Try place []s around Bi-TubeDelivery. Also, the sql should end with a ";" following an "Is Not Null". There shouldn't be a dangling "UNION ALL" at the end.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[1koppp]

Does that mean to remove the last "union all" and place the ";" after strainreliefdelivery is not null?

 

[dhookom]

Yep. "UNION ALL" suggests there is a select statement following it.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[1koppp]

It is giving me a problem I had before now, (when I tried and END statement): It says it cannot find the table, and to check that it exists and is spelled correctly. It does and it is.