Query help please

[brtny82]

Hi,
I have a table in Access called Aftercare Report Data and the headers are as follows: ID, MM/YYYY, Discharging Provider Name, Level of Care, Type, Total Late or No Appointment, and % of Late or No Aftercare Appointment.
I want to run a query that only shows Discharging Providers, who are in the spreadsheet 3+ consecutive months (from most recent date entered) with the same Level of Care and Type.
Can someone please help me with writing this expression? :banghead:
Thank you in advance

 

[plog]

Can someone please help me with writing this expression

Its not an expression you want, its sub-queries. Also, you're MM/YYYY field isn't helping you any--since you want to do date caclulations, you need a date value and MM/YYYY isn't it.

What you need to do is create a query to prepare your data for what you want to achieve. So, build a query that includes all the fields you will need to use along with a calculated field that converts your MM/YYYY value into an actual date (Hint: you will be using a few functions from this page http://www.techonthenet.com/access/functions/).

Post back your SQL.

 

[brtny82]

SELECT Format([MM/YYYY],"dd/mm/yyyy") AS [Date], [Aftercare Report data].[Discharging Provider Name], [Aftercare Report data].[Level of Care], [Aftercare Report data].[Type], [Aftercare Report data].[Total Late or No Appointment], [Aftercare Report data].[% of Late or No Aftercare Appointment]
FROM [Aftercare Report data];


Correct? Now my dates are dd/mm/yyyy and only the columns I need are present.

 

[plog]

First, "Date" is a poor choice for a field name, You should rename it by prefixing it with what that date represents (e.g. DischargeDate, VisitDate, etc.). Second,

I want to run a query that only shows Discharging Providers, who are in the spreadsheet 3+ consecutive months (from most recent date entered) with the same Level of Care and Type.

Why do you have [Total Late or No Appointment] and [% of Late...etc] fields in there? Are they relevant to what you need?

Lastly, will this data be unique? Can any rows of data in this query be an exact match of another row?

 

[brtny82]

Okay, I changed some of the Field Names
SELECT [Aftercare Report data].[Discharging Provider Name] AS [Discharging Provider], [Aftercare Report data].[Level of Care], [Aftercare Report data].[Type], Format([MM/YYYY],"mm/dd/yyyy") AS [Trending Date], [Aftercare Report data].[Total Late or No Appointment] AS Total, [Aftercare Report data].[% of Late or No Aftercare Appointment] AS [%]
FROM [Aftercare Report data]
ORDER BY [Aftercare Report data].[Discharging Provider Name], [Aftercare Report data].[Level of Care], [Aftercare Report data].[Type], Format([MM/YYYY],"mm/dd/yyyy");

I do need the total and % because I'm looking for trends to report out and those are the numbers I need to relay. So everymonth the database is appended with data. And I need to see if a provider trended in the last 3 or more months (based on the most recent month of data entered) for the same Type and Level of Care. All rows will be unique.

 

[plog]

Now I'm confused as to what you want. So please post sample starting data from [Aftercare Report data] (include field names) and then, based on that starting sample data what you expect as the results. Be sure to include enough data to cover all cases.

 

[brtny82]

Okay, I pull the data from Aftercare Report data and the field names are Discharging Provider, Level of Care, Type, Trending Date, Total, %

One provider may have 3 levels of care and 2 types but I need to know which providers are in the database 3+ recent months consecutively with the same Level of Care and Type.

 

[plog]

I need to know which providers are in the database 3+ recent months consecutively with the same Level of Care and Type.

Now you've gone back to your initial requirements where those 2 fields aren't part of it.

Please provide me 2 sets of data:

A. Starting sample data from your table, include table and field names.

B. What you expect your query to return based on the data in A.

 

[brtny82]

You mean like this?
Starting sample:
Discharging ProviderLevel of CareTypeTrending DateTotal%Provider C3LA02/01/20151312%Provider A3NA01/01/2015820%Provider B2LA02/01/2015823%Provider C1NA11/01/20141050%Provider C1NA12/01/2014565%Provider B2LA01/01/2015568%Provider A2NA10/01/20146100%Provider B2LA12/01/20149100%

And I want my results to spit out this:
Discharging ProviderLevel of CareTypeTrending DateTotal%Provider B2LA12/01/20149100%Provider B2LA01/01/2015568%Provider B2LA02/01/2015823%

Same Provider, Same Level of Care, Same Type, 3 consecutive months from the most recent input, and the corresponding Totals and %s

 

[brtny82]

Lol, that doesn't look like it was supposed to

 

[brtny82]

Okay, I tried to attach what the samples would look like

 

[brtny82]

And here is a sample database

 

[plog]

Ok, got it now. Its not going to be a sub-query, but a function. Below is the code:

Public Function get_ConsecutiveMonths(in_Provider, in_Level, in_Type, in_Date) As Integer

ret = 1
    ' return value, default of 1 for itself
    
counter_i = 0
    ' counter variable to search through next/previous months
    
tmp_Found = 1
    ' trigger to stop searching for consecutive months
    
    
While tmp_Found > 0
    ' searches for consecutive months after in_Date until finds month without one
    tmp_Found = DCount("[Discharging Provider]", "[Aftercare Report data]", "[Level of Care]=" & in_Level & " AND [Type]='" & in_Type & "' AND [Trending Date]=#" & DateAdd("m", counter_i + 1, in_Date) & "#")
    If tmp_Found > 0 Then counter_i = counter_i + 1
    Wend
    
ret = ret + counter_i
    ' adds however many months found after in_Date to ret

tmp_Found = 1
counter_i = 0
    ' resets search variables so it can search for months occuring before in_Date
    
While tmp_Found > 0
    ' searches for consecutive months after in_Date until finds month without one
    tmp_Found = DCount("[Discharging Provider]", "[Aftercare Report data]", "[Level of Care]=" & in_Level & " AND [Type]='" & in_Type & "' AND [Trending Date]=#" & DateAdd("m", counter_i - 1, in_Date) & "#")
    If tmp_Found > 0 Then counter_i = counter_i - 1
    Wend

ret = ret - counter_i
    ' adds however months found before in_Date to ret (counter_i will be negative)
    
get_ConsecutiveMonths = ret


End Function

Paste that into a module and save it. Then to use it in a query, you would do this:

ConsecutiveMonths: get_ConsecutiveMonths([Discharging Provider],[Level of Care],[Type],[Trending Date])

It will return how many consecutive months of data that record has. Let me know if you have any questions.

 

[brtny82]

Okay, I got the module and I input

ConsecutiveMonths: get_ConsecutiveMonths([Discharging Provider],[Level of Care],[Type],[Trending Date])
in the query but it keeps asking me to Enter Parameter Value for Discharging Provider​

 

[brtny82]

Here is the SQL, instead of me entering the parameters, I want it to show me all that it applies to or give be a count of how many consecutive months of data that record has so I can then do 3 or more
SELECT [Aftercare Report data].[Discharging Provider Name] AS DischargingProvider, [Aftercare Report data].[Level of Care] AS LOC, [Aftercare Report data].Type AS Type, Format([MM/YYYY],"mm/dd/yyyy") AS TrendingDate, get_ConsecutiveMonths([Discharging Provider],[Level of Care],[Type],[Trending Date]) AS ConsecutiveMonths, [Aftercare Report data].[Total Late or No Appointment] AS Total, [Aftercare Report data].[% of Late or No Aftercare Appointment] AS [Percent]
FROM [Aftercare Report data]
ORDER BY [Aftercare Report data].[Discharging Provider Name], [Aftercare Report data].[Level of Care], [Aftercare Report data].Type, Format([MM/YYYY],"mm/dd/yyyy");

 

[plog]

You need to change that in the query and the VBA code to "Discharging Provider Name"

 

[brtny82]

Thanks! When you say VBA code, do you mean the code I put in for the module?

 

[brtny82]

This is the VBA Code I put in the module, can you tell me exactly what I change?



Public Function get_ConsecutiveMonths(in_Provider, in_Level, in_Type, in_Date) As Integer
ret = 1
' return value, default of 1 for itself

counter_i = 0
' counter variable to search through next/previous months

tmp_Found = 1
' trigger to stop searching for consecutive months


While tmp_Found > 0
' searches for consecutive months after in_Date until finds month without one
tmp_Found = DCount("[Discharging Provider]", "[Aftercare Report data]", "[Level of Care]=" & in_Level & " AND [Type]='" & in_Type & "' AND [Trending Date]=#" & DateAdd("m", counter_i + 1, in_Date) & "#")
If tmp_Found > 0 Then counter_i = counter_i + 1
Wend

ret = ret + counter_i
' adds however many months found after in_Date to ret
tmp_Found = 1
counter_i = 0
' resets search variables so it can search for months occuring before in_Date

While tmp_Found > 0
' searches for consecutive months after in_Date until finds month without one
tmp_Found = DCount("[Discharging Provider]", "[Aftercare Report data]", "[Level of Care]=" & in_Level & " AND [Type]='" & in_Type & "' AND [Trending Date]=#" & DateAdd("m", counter_i - 1, in_Date) & "#")
If tmp_Found > 0 Then counter_i = counter_i - 1
Wend
ret = ret - counter_i
' adds however months found before in_Date to ret (counter_i will be negative)

get_ConsecutiveMonths = ret

End Function

 

[plog]

When you say VBA code, do you mean the code I put in for the module?

Yes that is what I mean.

 

[brtny82]

In the VBA above, I changed this part to "Discharging Provider Name"
tmp_Found = DCount("[Discharging Provider]", "[Aftercare Report data]", "[Level of Care]=" & in_Level & " AND [Type]='" & in_Type & "' AND [Trending Date]=#" & DateAdd("m", counter_i - 1, in_Date) & "#")

And I changed my query to

Now I'm getting an error message that says "Run-time error '3075': Syntax error (missing operator) in query expression '[Level of Care]=Family based Team Member w/Conusmer AND [Type}='No Appointment' AND [Trending Date]=##'

Is this because I have a Level of Care that has a / in it?
If I hit Debug, it highlights this:
tmp_Found = DCount("[Discharging Provider Name]", "[Aftercare Report data]", "[Level of Care]=" & in_Level & " AND [Type]='" & in_Type & "' AND [Trending Date]=#" & DateAdd("m", counter_i + 1, in_Date) & "#")