[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 550
  • Last Modified:

Complex access query

Hello Experts,
     I need assistance on a query that will give totals and percentages.  I will list what is needed with the table and field.

Total count of referred (Referral.Referral Dt)
Total count of Enrolled (Referral.Enrolled)
Total count of refused the program (Referral.Declined)
Total count of Inelegible (Referral.Eligible=no)
Total count of Transferred (Referral.Skilled_Nursing_Facility)
Total count of Readmitted (Referral.Rehospitalized)

If home visit was done within 72 hours, that would be (tblhomevisits.HomeDate) 72 hours from (Referral.Hosp_Discharge_Date)

Percentage of enrolled who had a home visit (enrolled is Referral.Enrolled and home visit is based on tblhomevisits.HomeDate)

Percentage of enrolled who  received Phone Call 1 (enrolled is Referral.Enrolled and phone call is based on tblPhonePillars (1).Ph2Date)

Percentage of enrolled who received Phone Call 2 (enrolled is Referral.Enrolled and phone call is based on tblPhonePillars (2).Ph7Date)

Percentage of enrolled who received Phone Call 3 (enrolled is Referral.Enrolled and phone call is based on tblPhonePillars (3).Ph14Date)

Percentage of referred who enrolled in the program (referred is Referral.Referral Dt and enrolled is based on Referral.Enrolled)

Percentage of referred who completed the program (referred is Referral.Referral Dt and completed is based on tblPhonePillars (3).Ph14Date)

Percentage of enrolled who were readmitted (enrolled is Referral.Enrolled Dt and readmitted is based on Referral.Rehospitalized)
 
Percentage of referred who were readmitted (referred is Referral.Referral Dt and readmitted is based on Referral.Rehospitalized)

Percentage of referred who were ineligible (referred is Referral.Referral Dt and ineligible is based on Referral.eligible=no)

This needs to be based on a between dates for Referral.Enrolled Dt
0
aplona
Asked:
aplona
  • 10
  • 5
  • 2
1 Solution
 
aikimarkCommented:
please post a sample database with some mocked up data in your tables
0
 
aplonaAuthor Commented:
Here it is.
sample.zip
0
 
aikimarkCommented:
I don't have Access2010.  Hopefully, one of the other Access experts will be able to help you with this.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
aplonaAuthor Commented:
Here is a 2007 version.  Let me know if you need an older version, thanks.
Sample-2007.zip
0
 
als315Commented:
It is first part of query (some totals). If idea will be clear, you can add columns with other totals. Percentage could be calculated in next query.
SELECT Count(Referral.StudyNo) AS CountOfStudyNo, Sum(IIf([Enrolled],1,0)) AS [Count of Enrolled], Sum(IIf(IsNull([Referral Dt]),0,1)) AS [Count Of Referred], Sum(IIf([Enrolled] And Not IsNull([HomeDate]),1,0)) AS [Count Of Home Visited], Sum(IIf([Declined],1,0)) AS [Count of Declined], Sum(IIf(Not [Eligible],1,0)) AS [Count of Inelegible], Sum(IIf([Skilled_Nursing_Facility],1,0)) AS [count of Transferred], Sum(IIf([Rehospitalized],1,0)) AS [count of Readmitted], Sum(IIf([Enrolled] And Not IsNull([HomeDate]),IIf(DateDiff("h",[HomeDate],[Hosp_Discharge_Date])<=72,1,0),0)) AS [Count of Home Visited 72]
FROM (Referral INNER JOIN [tblPhonePillars (1)] ON Referral.StudyNo = [tblPhonePillars (1)].StudyNo) INNER JOIN tblHomeVisits ON Referral.StudyNo = tblHomeVisits.StudyNo;

Open in new window


If you have questions, I will be able to answer 10 hours later.
0
 
aplonaAuthor Commented:
als315,
     Those counts look good, i do need to enter a date range based on referral.referral dt.  But how about the percentages?  Those are the ones i really dont understand how to do and seemed more complex.
0
 
als315Commented:
Are you using any form with this date range?
Percentages can be calculated in next query (Quantity from this query/Total)
0
 
aplonaAuthor Commented:
No, but i can,  I will name it frmreferral
0
 
als315Commented:
Add date filter. In th query designer add to this referral.referral dt field condition:
Between Datevalue(Forms!frmreferral.Date1) And  Datevlaue(Forms!frmreferral.Date2).
Use Browse button for proper form's fields names
0
 
aplonaAuthor Commented:
got it, how do i get the percentages to come out though?
0
 
aplonaAuthor Commented:
Also, I created the form with the to and from date fields.  How do i link that form to the query?
0
 
als315Commented:
Create query, add this query and divide one column to other (Use builder)
0
 
aplonaAuthor Commented:
I've tried it with just trying to get one of the percentages i need.  What is wrong:

SELECT Totals_Percentages.[Count of Enrolled], Totals_Percentages.[Count Of Referred], Totals_Percentages.[Count Of Home Visited], Totals_Percentages.[Count of Declined], Totals_Percentages.[Count of Inelegible], Totals_Percentages.[count of Transferred], Totals_Percentages.[count of Readmitted], Totals_Percentages.[Count of Home Visited 72]
FROM Totals_Percentages
WHERE (((Totals_Percentages.[Count of Enrolled])=[Totals_Percentages]![Count of Enrolled]/[Totals_Percentages]![Count Of Home Visited]));
0
 
als315Commented:
This is possible query:
SELECT Totals_Percentages.[Count of Enrolled], Totals_Percentages.[Count Of Referred], Totals_Percentages.[Count Of Home Visited], Totals_Percentages.[Count of Declined], Totals_Percentages.[Count of Inelegible], Totals_Percentages.[count of Transferred], Totals_Percentages.[count of Readmitted], Totals_Percentages.[Count of Home Visited 72], [Totals_Percentages]![Count Of Home Visited]/[Totals_Percentages]![Count of Enrolled] AS PercHomeVisited
FROM Totals_Percentages;

Open in new window

If you like to see percents:
percentsyou can open this calculated field's properties and select Percent form list of formats:
field's formatDates also could be selected with builder:
select dates from formAll is in sample. As you can see you can not fill all tables with unnesessary records. Use left join with main table.
Sample27838349.zip
0
 
aplonaAuthor Commented:
Thanks, i wont be able to get to this for a little while but i will test all of this and get back to you , thanks again.
0
 
aplonaAuthor Commented:
In order to move onto the percentage query i wanted to add phone call totals that i will need for the percentage query bit i receive and error:

SELECT
 Count(Referral.StudyNo) AS CountOfStudyNo,
 Sum(IIf(IsNull([Referral Dt]),0,1)) AS [Count Of Referred],
 Sum(IIf([Enrolled],1,0)) AS [Count of Enrolled],
 Sum(IIf([Declined],1,0)) AS [Count of Refused],
 Sum(IIf(Not [Eligible],1,0)) AS [Count of Inelegible],
 Sum(IIf([Skilled_Nursing_Facility],1,0)) AS [count of Transferred],
 Sum(IIf([Rehospitalized],1,0)) AS [count of Rehospitalized],
 Sum(IIf([Enrolled] And Not IsNull([HomeDate]),
 Sum(IIf(DateDiff("h",[HomeDate], [Hosp_Discharge_Date])<=72,1,0),0)) AS [Count of Home Visited 72],
 Sum(IIf([Enrolled] And Not IsNull([HomeDate]),
 Sum(IIf([tblPhonePillars (1).Ph2Date],1,0)) AS [count of Phone Call 1],
 Sum(IIf([tblPhonePillars (2).Ph7Date],1,0)) AS [count of Phone Call 2],
 Sum(IIf([tblPhonePillars (3).Ph14Date],1,0)) AS [count of Phone Call 3]
FROM (Referral INNER JOIN [tblPhonePillars (1)] ON Referral.StudyNo = [tblPhonePillars (1)].StudyNo) INNER JOIN tblHomeVisits ON Referral.StudyNo = tblHomeVisits.StudyNo;
0
 
aplonaAuthor Commented:
forget my last post, i figured that out.  I am working on the dates
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 10
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now