Solved

Day Count

Posted on 2016-09-23
8
57 Views
Last Modified: 2016-09-23
Experts,

I need to show a Day Count between [ValueDate] for Where T.FacilityType=Q_Disbursement.FacilityType.
I have attached a db with a query Q_DisbursementSum (as shown in the pic below)

Let me know if you need additional information.
Grateful for your help.

DayCountPicBalanceEE.accdb
0
Comment
Question by:pdvsa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 22

Expert Comment

by:Ferruccio Accalai
ID: 41812266
My suggestion:
SELECT TABLE1.FacilityType, TABLE1.ValueDate, Table1.[Cumulative Drawn], TABLE1.Available, DATEDIFF("D", TABLE1.valueDate, MIN(Table2.valueDate)) AS DayCount FROM (SELECT Q_Disbursement.FacilityType, Q_Disbursement.ValueDate, (Select Sum(T.Amount) From Q_Disbursement As T Where T.FacilityType=Q_Disbursement.FacilityType And T.ValueDate <= Q_Disbursement.ValueDate) AS [Cumulative Drawn], [FacilityAmount]-[Cumulative Drawn] AS Available
FROM Q_Disbursement
GROUP BY Q_Disbursement.FacilityType, Q_Disbursement.ValueDate, "?", Q_Disbursement.FacilityAmount, Q_Disbursement.Amount
ORDER BY Q_Disbursement.FacilityType, Q_Disbursement.ValueDate)  TABLE1
LEFT JOIN (SELECT Q_Disbursement.FacilityType, Q_Disbursement.ValueDate, (Select Sum(T.Amount) From Q_Disbursement As T Where T.FacilityType=Q_Disbursement.FacilityType And T.ValueDate <= Q_Disbursement.ValueDate) AS [Cumulative Drawn], [FacilityAmount]-[Cumulative Drawn] AS Available
FROM Q_Disbursement
GROUP BY Q_Disbursement.FacilityType, Q_Disbursement.ValueDate, "?", Q_Disbursement.FacilityAmount, Q_Disbursement.Amount
ORDER BY Q_Disbursement.FacilityType, Q_Disbursement.ValueDate) TABLE2 on TABLE1.FacilityType = TABLE2.FacilityType AND TABLE2.ValueDate > TABLE1.ValueDate
Group by TABLE1.FacilityType, TABLE1.ValueDate,  TABLE1.[Cumulative Drawn], TABLE1.Available

Open in new window

0
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 41812283
Gotta say I like the magnitude of those values.

So, without downloading your database, I use something like:

SELECT T.FacilityType
, T.DrawDate
, Min(T2.DrawDate) as NextDraw
, DateDiff("d", T.DrawDate, Min(T2.DrawDate)) as DayGap
FROM yourTable as T
LEFT JOIN yourTable as T1
ON T.FacilityType = T1.FacilityType
AND T.DrawDate < T1.DrawDate
GROUP BY T.FacilityType, T.DrawDate
1
 
LVL 50

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 41812364
In your query, use this simple expression (not a Group By):

DayCountx: DateDiff("d",[ValueDate],(Select Min(Q.ValueDate) From Q_Disbursement As Q Where Q.FacilityType = Q_Disbursement.FacilityType And Q.ValueDate > Q_Disbursement.ValueDate))

Demo
/gustav
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50

Expert Comment

by:Gustav Brock
ID: 41812375
I noticed the double entry for PIF 2015-06-23.
You can get rid of that by changing your last column from Group By to Sum:

eesum.PNG
/gustav
0
 

Author Closing Comment

by:pdvsa
ID: 41812481
Perfect.  

Dale's and Ferrucios method did give the correct count however I needed it to follow my query I had.  I am not too familiar with the T method so its difficult for me to modify it.

thank you experts!
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 41812484
You are welcome!

/gustav
0
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 41812491
pdvsa,

The "T method" is just a matter of aliasing table or query names so that your query is more readable.

I usually use an alias that is similar to the name of the table I'm working with, for example:

SELECT P.*
FROM tbl_People as P

or

SELECT P.*, F.Family_ID
FROM tbl_People as P
LEFT JOIN tbl_Families as F
ON P.Family_ID = F.Family_ID
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 41812506
Also, alias can be mandatory - as Q (or T or whatever) in my Select .. above - to distinguish between two instances of the same table/query.

/gustav
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question