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
> How To Use The Totals Parameter, Access 2016    
 
   
techexpressinc
post Oct 21 2019, 02:07 PM
Post#1



Posts: 446
Joined: 6-October 08
From: indiana, usa


The query needs to select the first row of a set for a Item-Id. For example where there are two rows for item-id = 1264. We need only first row on the output of the query.
I am trying to use the query Totals option without success. There should be a option to pick the first row of the set, i have not been able to figure it out. Attached is a screen shot of the query.
Any ideas would help?

Input:
Item-ID WorkType EffectiveDate
1264 SVCAGRMT_Restate_1 5/1/2018
1264 SVCAGRMT 1/1/2015
1266 SVCAGRMT 8/1/2015
1268 SVCAGRMT 9/2/2019
1268 SVCAGRMT 9/2/2018
Desired output:
Item-ID WorkType EffectiveDate
1264 SVCAGRMT_Restate_1 5/1/2018
1266 SVCAGRMT 8/1/2015
1268 SVCAGRMT 9/2/2019

Attached File(s)
Attached File  QUERY_DESIGN_10_21_19.jpg ( 46.96K )Number of downloads: 0
Attached File  SAMPLE_10_21_19_QUERY_INPUT.zip ( 7.89K )Number of downloads: 4
 

--------------------
Thanks,
Russ Neuman, Insurance designations = FLMI, ASC, ALMI
Certified MS-Office Specialist
Go to the top of the page
 
June7
post Oct 21 2019, 02:15 PM
Post#2



Posts: 1,008
Joined: 25-January 16



Review http://allenbrowne.com/subquery-01.html#TopN

--------------------
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
 
Jeff B.
post Oct 21 2019, 02:17 PM
Post#3


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


Your definition of "the first row" and Access' definition of "the first row" may not match.

Are there any other identifying features you could tell Access to check for besides position in the table?

--------------------
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
 
techexpressinc
post Oct 21 2019, 02:33 PM
Post#4



Posts: 446
Joined: 6-October 08
From: indiana, usa


The query needs to only select one row, similar to the example at the URL "Select just the TOP (or most recent) 5 scores per client."

Select just the TOP row, i.e. newest Date per client.

So, if two row were the input:
1264 SVCAGRMT 5/1/2018
1264 SVCAGRMT 1/1/2015

We need only one row out:
1264 SVCAGRMT 5/1/2018
This post has been edited by techexpressinc: Oct 21 2019, 02:34 PM

--------------------
Thanks,
Russ Neuman, Insurance designations = FLMI, ASC, ALMI
Certified MS-Office Specialist
Go to the top of the page
 
June7
post Oct 21 2019, 02:46 PM
Post#5



Posts: 1,008
Joined: 25-January 16



Did you review tutorial link in post 2?

That method requires a unique record identifier - autonumber field should serve.

SELECT Table1.*
FROM Table1
WHERE ID IN (SELECT TOP 1 ID FROM Table1 AS Dupe WHERE Dupe.ItemID=Table1.ItemID ORDER BY ItemID, EffectiveDate DESC);

Or try:

SELECT Table1.*
FROM Table1
WHERE (((Table1.EffectiveDate)=DMax("EffectiveDate","Table1","ItemID=" & [ItemID])));

This version will likely perform slower in large dataset.

Advise not to use punctuation/special characters in naming convention.

This post has been edited by June7: Oct 21 2019, 03:21 PM

--------------------
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
 
Jeff B.
post Oct 21 2019, 06:14 PM
Post#6


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


Would your data ever contain more than one row for an [ItemID] that has the same [EffectiveDate]?

--------------------
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
 
techexpressinc
post Oct 22 2019, 04:43 AM
Post#7



Posts: 446
Joined: 6-October 08
From: indiana, usa


Yes - the objective is to get the newest date for the “Item-Id”.

--------------------
Thanks,
Russ Neuman, Insurance designations = FLMI, ASC, ALMI
Certified MS-Office Specialist
Go to the top of the page
 
June7
post Oct 22 2019, 05:55 AM
Post#8



Posts: 1,008
Joined: 25-January 16



Understand the objective. Jeff asked if an item can have multiple records with the same date. So could there be 2 records like:

1264 SVCAGRMT 5/1/2018
1264 SVCAGRMT 5/1/2018

Are there other fields that make each record unique?

Did you try suggestions?

This post has been edited by June7: Oct 22 2019, 05:56 AM

--------------------
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
 
techexpressinc
post Oct 22 2019, 07:19 AM
Post#9



Posts: 446
Joined: 6-October 08
From: indiana, usa


Understand the objective. Jeff asked if an item can have multiple records with the same date. So could there be 2 records like:
1264 SVCAGRMT 5/1/2018
1264 SVCAGRMT 5/1/2018

Yes

Are there other fields that make each record unique?


No, but could add autonumber field


Did you try suggestions?

No, looks like SQL coding the query, was trying to do without doing SQL.



--------------------
Thanks,
Russ Neuman, Insurance designations = FLMI, ASC, ALMI
Certified MS-Office Specialist
Go to the top of the page
 
Jeff B.
post Oct 22 2019, 08:28 AM
Post#10


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


It sounds like you're saying there could be "duplicate" rows. Does that mean that whatever you're recording can happen more than once (on any particular day), or that there are 'spurious' duplications? ?Any chance you could use both date & time instead of only date?

And my original question/comment (and June7's comment) is that if YOU would have trouble telling two records apart, there's likely no way you could explain to Access how to tell them apart. Yes, I'd probably use an Autonumber primary key for the table, but the question still stands … how do you (and Access) decide which record is the 'right' one?

--------------------
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
 
techexpressinc
post Oct 22 2019, 08:47 AM
Post#11



Posts: 446
Joined: 6-October 08
From: indiana, usa


Solved - The fix was a bit tricky but "And so it goes". Attached are Snapshots of the queries. MS-Access Max pick process appeared to want only two fields in the select to pick the highest value process.

Thank you all for your support I appreciate it.

Russ



Attached File(s)
Attached File  QUERY_GET_MAX_DT_10_22_19.pdf ( 79.28K )Number of downloads: 2
 

--------------------
Thanks,
Russ Neuman, Insurance designations = FLMI, ASC, ALMI
Certified MS-Office Specialist
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    8th December 2019 - 01:15 PM