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
> Select All Records With The Same Max Date, Access 2013    
 
   
SomekindaVB
post Aug 13 2019, 11:40 PM
Post#1



Posts: 312
Joined: 15-December 16



Hi all,

How do I select all records in a table with the same max date?

Using Max date in Access seems to return either all records with the max date, or just the first one - usually dependant on what other fields i include in my query.

I need to show all fields in the table where the date field is max date.

I hope that's understandable.

Cheers
Go to the top of the page
 
June7
post Aug 14 2019, 01:00 AM
Post#2



Posts: 788
Joined: 25-January 16



Do you mean the maximum date in table?

SELECT * FROM table WHERE datefield = DMax("datefield", "tablename");

If not, then show sample raw data and desired output.

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
BruceM
post Aug 14 2019, 07:00 AM
Post#3


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


Is there a time component to the date/time value? If so, it will need to be taken into account, which is simple enough but involves a little more code.
Go to the top of the page
 
SomekindaVB
post Aug 14 2019, 08:28 PM
Post#4



Posts: 312
Joined: 15-December 16



June7

DMAX looks like a great idea.

I'm trying to use it as a method to open a form with the criteria For example

CODE
strFormCriteria = "[ChildID] =" & lngID & " AND [FieldDate] =#" & Format(LngFieldDate, "MM/DD/YYYY") & "#"
DoCmd.OpenForm "PopupMyForm", acNormal, , strFormCriteria, , acHidden



So I need that criteria, but I need to find the MAX date records that are all the same for the ChildID

I tried

CODE
DMax("FieldDate", "Tbl_MyTable", "[ChildID] =" & lngID)


But that returned all records in the table. That confused me somewhat.
Go to the top of the page
 
RJD
post Aug 14 2019, 09:01 PM
Post#5


UtterAccess VIP
Posts: 10,017
Joined: 25-October 10
From: Gulf South USA


Hi: If you need all the records with the same max date, by ChildID, then take a look at the demo attached. See if it does what you want.

The demo first uses a query to get the max date by ChildID (using a Totals query, not DMax). It then uses that query against the table, with INNER JOINS between the dates and the ChildIDs to limit the results.

HTH
Joe
Attached File(s)
Attached File  MaxDateByChildID.zip ( 18.72K )Number of downloads: 5
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
BruceM
post Aug 15 2019, 06:36 AM
Post#6


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


I'm curious as to why you are opening the form hidden.
Go to the top of the page
 
SomekindaVB
post Aug 15 2019, 06:48 PM
Post#7



Posts: 312
Joined: 15-December 16



QUOTE
I'm curious as to why you are opening the form hidden.


This is a legacy strategy giving an appearance of speed in the event that I need to do a lot of work before the form is opened.

Typically, an hourglass is running and when the task is complete, the form is unhidden.



Go to the top of the page
 
RJD
post Aug 16 2019, 10:09 AM
Post#8


UtterAccess VIP
Posts: 10,017
Joined: 25-October 10
From: Gulf South USA


Hi: I noticed you have not downloaded the demo in Post #5. Do you have the solution you need already, or is the original issue still open?

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
projecttoday
post Aug 16 2019, 11:15 AM
Post#9


UtterAccess VIP
Posts: 10,955
Joined: 10-February 04
From: South Charleston, WV


I'm guessing the OP figured it out as something like

CODE
strFormCriteria = "[ChildID] =" & lngID & " AND [FieldDate] =" & DMax("datefield", "tablename")
DoCmd.OpenForm "PopupMyForm", acNormal, , strFormCriteria, , acHidden



--------------------
Robert Crouser
Go to the top of the page
 
RJD
post Aug 16 2019, 12:27 PM
Post#10


UtterAccess VIP
Posts: 10,017
Joined: 25-October 10
From: Gulf South USA


Hi Robert: Yeah, not sure where this is right now. The OP also said ...

QUOTE
So I need that criteria, but I need to find the MAX date records that are all the same for the ChildID

I tried

CODE
DMax("FieldDate", "Tbl_MyTable", "[ChildID] =" & lngID)

But that returned all records in the table. That confused me somewhat.

My demo had a way to return the set of records with the same Max date for each ChildID, I thought overcoming the last issue in the quote above.

I guess we need to hear from the OP to clear up my confusion ...

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
jklinephd
post Aug 20 2019, 09:10 AM
Post#11



Posts: 78
Joined: 11-September 07
From: Missouri


Joe,

I am hoping that I can latch onto this thread since it is on point to an issue I am having in my database. If this is considered a Thread Jack then i will repost. :-)

Joe, I have attached your sample database with a few minor changes.

1. Modified the table data so that none of the records had the same fieldDate for the same ChildId. I wanted each ChildId to have multiple dates, but only one "Max Date." that matches my DB's structure.

2. I added some records to the table with a ChildId, but with an empty FieldDate field.

3. I added a status field to the table. 2 statuses - Completed and Pending. Pending corresponds to those ChildIDs without a FieldDate

I adapted your original structure in my database and was able to easily create a query which showed a list of clients and displayed their max date for "fieldDate" Great.

I now want to create a query that will display each childID with a MaxDate and display the ChildID records if the FieldDate is empty. Any ideas on how to do this? I played with creating a 3rd query that just captures those with a Null FieldDate, but I do not know how to join that in such away as to make it work. I don't know if the Status field will help or not.

Thanks

Jeff
Attached File(s)
Attached File  MaxDateByChildID_rev2.zip ( 27.05K )Number of downloads: 1
 
Go to the top of the page
 
RJD
post Aug 20 2019, 10:59 AM
Post#12


UtterAccess VIP
Posts: 10,017
Joined: 25-October 10
From: Gulf South USA


Hi Jeff: If I understand your situation, this might do what you want ...

CODE
SELECT tbl_MyTable.ID, tbl_MyTable.ChildID, tbl_MyTable.FieldDate
FROM qryMaxDateByChildID INNER JOIN tbl_MyTable ON (qryMaxDateByChildID.ChildID = tbl_MyTable.ChildID) AND (qryMaxDateByChildID.MaxOfFieldDate = tbl_MyTable.FieldDate)
UNION ALL SELECT ID, ChildID, FieldDate
FROM tbl_MyTable
WHERE FieldDate Is Null
ORDER BY ChildID;

And, yes, you should start a new thread in this case - and more folks will see it and respond. In your first post in the new thread you can refer to the old thread with a link to it.

HTH
Joe
Attached File(s)
Attached File  MaxDateByChildID_Rev3.zip ( 19.1K )Number of downloads: 1
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
jklinephd
post Aug 20 2019, 11:19 AM
Post#13



Posts: 78
Joined: 11-September 07
From: Missouri


Thanks.

I created another thread here:

New Thread
Go to the top of the page
 
RJD
post Aug 20 2019, 11:20 AM
Post#14


UtterAccess VIP
Posts: 10,017
Joined: 25-October 10
From: Gulf South USA


thumbup.gif

I think we can terminate this here Jeff, and go with your other thread now.

Thanks,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th September 2019 - 09:20 AM