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
> Distinct Query, Access 2016    
 
   
Adsens
post Dec 29 2017, 08:19 AM
Post#1



Posts: 13
Joined: 29-November 17



Hello everybody
I have a data table :


and I want to create a query to calculate the Sum of [Amount], the minimum value of [Date], the maximum value of [Date] and count the values distinct from [N_Facture].
I tried several times but I can not do it.

I tried with this :
CODE
SELECT DISTINCT Sum(Paiements_17_18.Amount) AS SommeDeAmount, Min(Paiements_17_18.Date_Operation) AS MinDeDate_Operation, Max(Paiements_17_18.Date_Operation) AS MaxDeDate_Operation, Count(Paiements_17_18.N_Facture) AS CompteDeN_Facture
FROM Paiements_17_18;



Count of DISTINCT [N_Facture] should be 6 not 9.

Thank you for your help.

The database is in attached file




Attached File(s)
Attached File  TEST.zip ( 19.84K )Number of downloads: 3
 
Go to the top of the page
 
MadPiet
post Dec 29 2017, 08:37 AM
Post#2



Posts: 2,421
Joined: 27-February 09



Here's an article on it...
http://www.geeksengine.com/article/access-...inct-count.html

Apparently, Access doesn't support COUNT(DISTINCT([columnName]))
Go to the top of the page
 
GroverParkGeorge
post Dec 29 2017, 09:14 AM
Post#3


UA Admin
Posts: 32,821
Joined: 20-June 02
From: Newcastle, WA


Here's a possible solution. It feels a bit messy, but it ought to work.

Attached File  TEST_Modified.zip ( 23.51K )Number of downloads: 4

--------------------
Go to the top of the page
 
Adsens
post Dec 29 2017, 09:58 AM
Post#4



Posts: 13
Joined: 29-November 17



@GroverParkGeorge
Thanks for your effort, but unfortunately I need to do it in one query, and then take the SQL code and put it in a VBA recordset.
Go to the top of the page
 
RJD
post Dec 29 2017, 10:07 AM
Post#5


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


Hi: PMFJI, but here is slightly different approach (in one query with subqueries) ...

SELECT
Sum(Amount) AS SommeDeAmount,
Min(Date_Operation) AS MinDeDate_Operation,
Max(Date_Operation) AS MaxDeDate_Operation,
(SELECT Count(N_Facture) FROM (SELECT DISTINCT N_Facture FROM Paiements_17_18)) AS N_FactureCount
FROM Paiements_17_18;

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
 
GroverParkGeorge
post Dec 29 2017, 10:24 AM
Post#6


UA Admin
Posts: 32,821
Joined: 20-June 02
From: Newcastle, WA


Cool. Thanks, Joe.

--------------------
Go to the top of the page
 
Adsens
post Dec 29 2017, 10:26 AM
Post#7



Posts: 13
Joined: 29-November 17



@RJD
IT WORKS PERFECTLY, Thank you very Much.
notworthy.gif notworthy.gif notworthy.gif
Go to the top of the page
 
RJD
post Dec 29 2017, 03:21 PM
Post#8


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


You are very welcome. Glad that works for you. thumbup.gif

(And thanks, George.)

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
 


Custom Search
RSSSearch   Top   Lo-Fi    22nd June 2018 - 12:42 AM