Solved

Day Count

Posted on 2016-09-23
8
31 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
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 47

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 49

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
 
LVL 49

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 49

Expert Comment

by:Gustav Brock
ID: 41812484
You are welcome!

/gustav
0
 
LVL 47

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 49

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now