Access Query to Extract In Month Figures from Monthly YTD Tables

[warriorwoman]

I get medical reports through each month and some are in YTD format when I actually need to report the given months data.

I need to find a way to subtract the M4 YTD report from the M5 YTD report to reveal the M5 in month movement and so on.

I'm looking for ideas on how to achieve this.

I have a single table for each month and each table is in the same format:

MONTH
HOSPITAL
COMMISSIONER
TREATMENT TYPE
RESOURCE GROUP
SPECIALTY
YTD PRICE PLAN
YTD PRICE
YTD ACTIVITY PLAN
YTD ACTIVITY

Each month will have different numbers of rows (approx 20,000) but it is probably safe to assume that the last month will have all the data rows in.

I would like an output in exactly the same format only with the YTD fields replaced by in-month equivalents.

Have I given you enough information to give me a tip on how to construct my query?

Thanks

 

[xenou]

1) Which fields are adequate to match records?
2) Which fields need to be added/subtracted?

For instance, is it enough to match resource group to resource group? Do we also need hospital? Or specialty? And when the records are matched, is it Price that needs to be added/subtracted? Or price plan?

 

[warriorwoman]

I'd have to match across all the fields except month and the data fields:
HOSPITAL
COMMISSIONER
TREATMENT TYPE
RESOURCE GROUP
SPECIALTY

I would be looking for all the data fields as in month:
In Month Plan Price
In Month Price
In Month Plan Activity
In Mnth Activity

So all the data fields would need to be subtracted from the previous months YTD.

Eg:
M5 In Mnth Plan Price = M5 YTD Plan Price - M4 YTD Plan Price
M5 In Mnth Price = M5 YTD Price - M4 YTD Price

and so on.

Thanks

 

[xenou]

Try this to get started. Table1 must be edited to be the latest month, and Table2 the table for the previous month. It would discard any rows where there was something in one table but not the other - a weakness. Also, it is assumed that all data fields have zeros and not NULLs if there is no change in the period (a NULL in either table in a data field would result in NULL in this report).

SELECT
    t1.[HOSPITAL],
    t1.[COMMISSIONER],
    t1.[TREATMENT TYPE],
    t1.[RESOURCE GROUP],
    t1.[SPECIALTY],
    t1.[YTD PRICE PLAN] - t2.[YTD PRICE PLAN] AS [NET CHG YTD PRICE PLAN],
    t1.[YTD PRICE] - t2.[YTD PRICE] AS [NET CHG YTD PRICE],
    t1.[YTD ACTIVITY PLAN] - t2.[YTD ACTIVITY PLAN] AS [NET CHG YTD ACTIVITY PLAN],
    t1.[YTD ACTIVITY] - t2.[YTD ACTIVITY] AS [NET CHG YTD ACTIVITY]

FROM

    [COLOR="Blue"]Table1 t1[/COLOR]

INNER Join

    [COLOR="blue"]Table2 t2[/COLOR]

ON
    t1.[HOSPITAL] = t1.[HOSPITAL]
    AND
    t1.[COMMISSIONER] = t1.[COMMISSIONER]
    AND
    t1.[TREATMENT TYPE] = t1.[TREATMENT TYPE]
    AND
    t1.[RESOURCE GROUP] = t1.[RESOURCE GROUP]
    AND
    t1.[SPECIALTY] = t1.[SPECIALTY]

 

[warriorwoman]

Thankyou very much for your reply. I have now had my first dabble with writing and executing SQL queries!

I have a few problems as you predicted and have attached my actual code below.

What I am finding is that nothing at all is returned until I reduce the number of joins. Sometimes the values will be null eg for spec code and then it doesn't match with its respective partner in the previous month.

Maybe what I am trying to do is a bit too unrealistic when there isn't a consistent matching field or fields between the two tables.

Basically I expect all the code fields to form the link but i want it to match null with null. Does that make sense?

Here's my code:

SELECT
[M2 YTD].TRUST,
[M2 YTD].[PCT CODE],
[M2 YTD].FLAG,
[M2 YTD].[GIVEN POD CODE],
[M2 YTD].[GIVEN POD DESC],
[M2 YTD].[GIVEN SPEC CODE],
[M2 YTD].[GIVEN SPEC DESC],
[M2 YTD].[HRG CODE],
[M2 YTD].[HRG DESC],
[M2 YTD].[ADHOC CODE],
[M2 YTD].[ADHOC DESC],
[M2 YTD].[ANNUAL ACTIVITY PLAN],
[M2 YTD]![YTD ACTIVITY PLAN]-[M1]![YTD ACTIVITY PLAN] AS [IN MNTH ACT PLAN],
[M2 YTD]![YTD ACTIVITY]-[M1]![YTD ACTIVITY] AS [IN MNTH ACT],
[M2 YTD].[ANNUAL PRICE PLAN],
[M2 YTD]![YTD PRICE PLAN]-[M1]![YTD PRICE PLAN] AS [IN MNTH PRICE PLAN],
[M2 YTD]![YTD PRICE]-[M1]![YTD PRICE] AS [IN MNTH PRICE]

FROM

[M2 YTD]

INNER JOIN
[M1]

ON

([M2 YTD].[ADHOC CODE] = M1.[ADHOC CODE])
AND
([M2 YTD].[HRG CODE] = M1.[HRG CODE])
AND
([M2 YTD].[GIVEN SPEC CODE] = M1.[GIVEN SPEC CODE])
AND
([M2 YTD].[GIVEN POD CODE] = M1.[GIVEN POD CODE])
AND
([M2 YTD].FLAG = M1.FLAG)
AND
([M2 YTD].TRUST = M1.TRUST)
AND
([M2 YTD].[PCT CODE] = M1.[PCT CODE]);

 

[warriorwoman]

I've just discovered with my current data (and this won't always be the case) that where I have a spec code recorded I don't have an HRG Code recorded. So by joining on both fields ensures that nothing is returned.

 

[xenou]

I've just discovered with my current data (and this won't always be the case) that where I have a spec code recorded I don't have an HRG Code recorded. So by joining on both fields ensures that nothing is returned.

This sounds like a serious obstacle. It sounds like there's no real relationship on the tables in the ordinary sense. I guess you have to step back and say "how do I know what records this month are the same as last month, except for the dollar figures" - if you can answer that question, then you might have a chance. If the data is changing a lot (things this month that weren't there last month, and so on, then you essentially have to run it in a few steps to get all matches across both tables, even if they are in only one table - we have a current thread on the forum discussing this topic)

Try to do it without matching Nulls - that gets tricky as Null isn't equal to Null (it also isn't not equal to Null either ... it's just Null).