removing duplicates in a sum? 1

[jaw323]

I have a problem in a query where I have Nurse names, Patient Names, # of visits, Year and Month. I am not getting the right totals for each month, I am getting the overall total for all months. Example below

What I get
Nurse Patient Visit Year Month
Jane Doe John Doe 12 2003 5
Jane Doe John Doe 12 2003 7
Jane Doe John Doe 12 2003 8

What I want
Nurse Patient Visit Year Month
Jane Doe John Doe 4 2003 5
Jane Doe John Doe 5 2003 7
Jane Doe John Doe 3 2003 8

Can anyone tell me why this happens and how I might be able to fix it? I'm pretty stuck.

Thanks

 

[jhall01]

how is your table setup?

IE

NURSE PATIENT VISIT YEAR MONTH

and then you sum the visit. And is the visit column always 1 or is it sequential?

What are you grouping by on your SUM? Do you group by NURSE, PATIENT, YEAR, MONTH, and SUM(VISIT)?

Post some code for better help and more details if this does not help!!!

-Jon

 

[jaw323]

The visit is always 1 I think, but I summed to get the totals.

Sorry about not putting code this was my first post to these forums. I'm just an intern

Here is my Group By SQL Code (this is what Access wrote because I am not strong with SQL)

GROUP BY [EMPLOYEE]![LASTNAME] & ", " & [EMPLOYEE]![FIRSTNAME], [PATIENT]![LASTNAME] & ", " & [PATIENT]![FIRSTNAME], SERVICE.DATESERV, ADMIT.PROGRAM, SERVICE.DISCIPLINEID, DatePart("m",[DATESERV]) & "/" & DatePart("yyyy",[DATESERV])
HAVING (((ADMIT.PROGRAM)="1&quot AND ((SERVICE.DISCIPLINEID)="SN&quot);

This looks pretty messed up to me. I probably need to work on this a bit more in design view because I don't think I need that many group bys.

 

[jaw323]

I forgot to tell of my table(s). I am drawing Data from 6 tables

Employee joined to Service by EMPID
Patient joined to ADMIT by PATIENTID
Program joined to Service by PROGRAMID
Admit joined to Service by ADMITID
Episode joined to Service by PATIENTID

I know that there is probably some DB problems here believe me our database could use some work in my opinion. It was in place long before I was around though.

 

[jhall01]

[EMPLOYEE]![LASTNAME] & ", " & [EMPLOYEE]![FIRSTNAME], [PATIENT]![LASTNAME] & ", " & [PATIENT]![FIRSTNAME]???

Is this A Query in SQL view or coming from a MACRO or VBA?

 

[jaw323]

Query in SQL view

 

[jhall01]

Hmmmmm.....

Try this:

GROUP BY [EMPLOYEE]![LASTNAME] & ", " & [EMPLOYEE]![FIRSTNAME], [PATIENT]![LASTNAME] & ", " & [PATIENT]![FIRSTNAME], SERVICE.DATESERV, ADMIT.PROGRAM, SERVICE.DISCIPLINEID, DatePart("m",[DATESERV])
HAVING (((ADMIT.PROGRAM)="1&quot AND ((SERVICE.DISCIPLINEID)="SN&quot);

Try dropping off this:

& "/" & DatePart("yyyy",[DATESERV])

And Seperating it out as another column

& "," & DatePart("yyyy",[DATESERV])

So it should look like this:

GROUP BY [EMPLOYEE]![LASTNAME] & ", " & [EMPLOYEE]![FIRSTNAME], [PATIENT]![LASTNAME] & ", " & [PATIENT]![FIRSTNAME], SERVICE.DATESERV, ADMIT.PROGRAM, SERVICE.DISCIPLINEID, DatePart("m",[DATESERV]) & "," & DatePart("yyyy",[DATESERV])
HAVING (((ADMIT.PROGRAM)="1&quot AND ((SERVICE.DISCIPLINEID)="SN&quot);

 

[jhall01]

If that doesn't work try aliasing the date columns in your SELECT statement and Grouping by the alias name of [month] and [year]

I.E.

SELECT
datePart("yyyy",[DATESERV]) AS [Year], etc...

Also look at other date format functions:

Year([DATESERV]) and MONTH([DATESERV]) which should return the same data as the datePart()

 

[jaw323]

Thanks a ton for being patient with me. I will try this on Monday when I go back to work. I wish I had more experience with SQL.

 

[JonFer]

Two things I see.

1. You are including DATESERV (month,day,year) in your group by and then also trying to group by just the month/year of DATESERV. I think you should remove DATESERV from your group by and that will probably fix the multiple counts. Also, a simpler way to do the month/year is the Format function:

Format([DATESERV],"mm/yyyy&quot

2. You should have the two criteria in a WHERE clause, not the HAVING. It is easy to forget this in the query window. To fix it, add the two fields again to the query. On the previous 2 fields with the criteria, change where it says "Group by" to "Where" (the Total row).