Solved

Day Count

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

763 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