Solved

Day Count

Posted on 2016-09-23
8
36 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

920 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

15 Experts available now in Live!

Get 1:1 Help Now