X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Get Maximum Date: Of A Multiple Date Records, Access 2010    
post Feb 20 2019, 07:48 AM

Posts: 101
Joined: 9-August 16

Dear, I would like to ask for help on a process

I have a table where the expected dates and actual dates of steps of a service are inserted.
I need to determine the highest date in the 'DR' end date set in the field

An example: I have document STC.00006.2018

AcceptReqDR - 28/06/2018
MAnalysisDR - 06/08/2018
RecebOpinionsDR - 30/08/2018
AssinDR - 17/09/2018
PublDR - 24/09/2018
deliveryDR -
movesDR -
ResultDR -
forwardDR - 20/11/2018
approvalDR -
approvalendDR -

An example: I have document STC.00013.2018

AcceptReqDR - 09/07/2018
MAnalysisDR - 03/09/2018
RecebOpinionsDR - 04/10/2018
AssinDR - 04/10/2018
PublDR - 28/12/2018
deliveryDR - 08/02/2019
movesDR -
ResultDR -
forwardDR -
approvalDR -
approvalendDR -

In the case of document STC.00006.2018 it would have to have the the stage is in" forwardDR- 20/11/2018 ",because it is the maximum from "AcceptReqDR" a "approvalendDR".
In the case of document STC.00013.2018 it would have to have the the stage is in" deliveryDR- 08/02/2019 ",because it is the maximum from "AcceptReqDR" a "approvalendDR".

I tried to use the function dmax but without success.

Thank you all

Attached File(s)
Attached File  Database.zip ( 25.04K )Number of downloads: 10
Go to the top of the page
post Feb 20 2019, 08:17 AM

Posts: 1,042
Joined: 26-January 14
From: London, UK

This seems like a very inconvenient design for your table. Have you considered creating a table with one row for each step?

Are the dates always in the same sequence as the fields in your table? If so then try the following:

SELECT numdocument,
IIF(approvalendDR IS NOT NULL,approvalendDR,
IIF(approvalDR IS NOT NULL, approvalDR,
IIF(forwardDR IS NOT NULL, forwardDR,
IIF(movesDR IS NOT NULL, movesDR,
IIF(deliveryDR IS NOT NULL, deliveryDR,
IIF(RecebOpinionsDR IS NOT NULL, RecebOpinionsDR,
IIF(MAnalysisDR IS NOT NULL, MAnalysisDR,
IIF(AcceptReqDR IS NOT NULL, AcceptReqDR))))))))))) AS lastdate
FROM tbEtapas;

The DR and DP suffixes look to me like information that would be better represented as values in a column rather than as column names. Putting information into column names generally makes it harder to get useful results out of data.

Go to the top of the page
Jeff B.
post Feb 20 2019, 08:36 AM

UtterAccess VIP
Posts: 10,337
Joined: 30-April 10
From: Pacific NorthWet

I'll echo <nvogel>'s comments -- searching across columns requires you and Access to work harder than searching within a column.

"How" depends on "what" … it would help us help you to have a clear picture of your data/data structure.

The examples you posted seemed to suggest that you have multiple columns that can hold a date, and, as <nvogel> points out, putting data (i.e., what type of date) in the column names is neither necessary nor useful in a relational database like Access.

More info, please...


Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
post Feb 20 2019, 08:52 AM

UtterAccess VIP
Posts: 7,993
Joined: 24-May 10
From: Downeast Maine

I will add my voice to the suggestions that it seems not to be the best design. A more appropriate design may be to have a main table for the document information, with a related table for the various dates. The latest date is much simpler to find in that case.

If it is always the same list of items you can insert the items as related records, with the dates blank. If it is not always the same list of items, even better, since you don't need to include those items. Best of all is that if you wish to add an item you can do so without redesigning your tables, queries, forms, and reports.
Go to the top of the page
post Feb 20 2019, 10:02 AM

UA Admin
Posts: 36,206
Joined: 20-June 02
From: Newcastle, WA

Here's a link to a few blog posts describing the problem of Repeated Columns, as previously posters have warned against.

My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
post Feb 21 2019, 10:26 AM

Posts: 101
Joined: 9-August 16

thank you all for the answers
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    14th December 2019 - 09:45 PM