UtterAccess.com
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    
 
   
ClaudioHenriques
post Feb 20 2019, 07:48 AM
Post#1



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: 9
 
Go to the top of the page
 
nvogel
post Feb 20 2019, 08:17 AM
Post#2



Posts: 1,011
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(ResultDR IS NOT NULL, ResultDR,
IIF(movesDR IS NOT NULL, movesDR,
IIF(deliveryDR IS NOT NULL, deliveryDR,
IIF(PublDR IS NOT NULL, PublDR,
IIF(AssinDR IS NOT NULL, AssinDR,
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
Post#3


UtterAccess VIP
Posts: 10,305
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...

--------------------
Regards

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
 
BruceM
post Feb 20 2019, 08:52 AM
Post#4


UtterAccess VIP
Posts: 7,971
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
 
GroverParkGeorge
post Feb 20 2019, 10:02 AM
Post#5


UA Admin
Posts: 35,887
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 do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
ClaudioHenriques
post Feb 21 2019, 10:26 AM
Post#6



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    19th October 2019 - 07:36 PM