Qry to sum mult calc fields

[Charles L. Snyder]

Hi-
I have a Access 2003 DB - the main table has fields which include:

Name
Date
Procedure1
Procedure2
Procedure3
Procedure4
Procedure5

Some of the procedure fields may be empty

Another table has a list of procedures and their corresponding values
(RVU)

I am trying to create a query which sums the corresponding values for
Procedures 1 through 5 for all the records, sorted by month and year ie,
result is:

Jan 2,345
Feb 1,456
Mar 2,344
etc....

I can do this easily when each record in the main table has just one
procedure, but can't figure out how to do it when some records have
multiple procedures ??

TIA

CL Snyder

 

[Duane Hookom]

Your table is not normalized which causes issues like this. You can create a
union query that "normalizes" the table:
SELECT [Name] as PtName, [Date] as ProcDate, Procedure1 as Proc, 1 as
ProcNum
FROM tblMain
WHERE Procedure1 is not null
UNION ALL
SELECT [Name], [Date], Procedure2 , 2
FROM tblMain
WHERE Procedure2 is not null
UNION ALL
SELECT [Name], [Date], Procedure3 , 3
FROM tblMain
WHERE Procedure3 is not null
UNION ALL
SELECT [Name], [Date], Procedure4 , 4
FROM tblMain
WHERE Procedure4 is not null
UNION ALL
SELECT [Name], [Date], Procedure5 , 5
FROM tblMain
WHERE Procedure5 is not null;

Save the above a quniProcedures. Then you can query from the union query:

SELECT Format(ProcDate,"yyyy mm") as YrMth, Count(*) as NumProcs
FROM quniProcedures
GROUP BY Format(ProcDate,"yyyy mm");