summing in a query or report

[Tim]

All,

The following is an example:

I have a table that looks as follows:

Appeal_ID 2000 2001 2002
mNLC00 10 100
mNLC01 10 100
eVNT00 200 10
eVNT01 200 10

What I want is something like this:

Appeal 2000 2001 2002
mNLC 10 110 100
eVNT 200 210 10

I can't seem to do this in one query. I can get a sum for a particular
column value but it repeats many times when placed in the detail
section of the report. If I place it in the page footer section than
the sum formula gives an error.

My sum formula looks as follows: =Sum(IIf(Left(total!APPEAL_ID,
4)="mnlc",total![2000],0))

What is the best way to go about doing this?

Thanks,

Tim

 

[M.]

Dear Tim,

The problem is that your table is not sufficiently normalized, since you
have a spreadsheet like setup (years 2000, 2001, etc; categories like mNLC00,
mNLC01, etc.)

What would be helpful for statistical queries (sum, average, max, etc.) is
to use a table designed like this:


AppealCategory (with values like mnNLC, eVNT)
AppealNumber (with values like 0;1;2;etc)
AppealYear (with values like 2000; 2001; etc.)
AppealValue (with values like 10; 200; 100; etc)

In this way, you could 1) group your data by category and year and
sum(value) and
2) produce a cross query to generate an overview as you mentioned in your
original e-mail.

Hope this helps,

M.

 

[Douglas J. Steele]

M. is right that your database isn't properly normalized.

However, while you're waiting to fix it, try the following query:

SELECT Left([Appeal_ID]), Sum([2000]), Sum([2001]), Sum([2002])
FROM MyTable
GROUP BY Left([Appeal_ID])

 

[Tim]

Douglas,

Thanks for your reply. This also helped greatly.

--Tim

M. is right that your database isn't properly normalized.

However, while you're waiting to fix it, try the following query:

SELECT Left([Appeal_ID]), Sum([2000]), Sum([2001]), Sum([2002])
FROM MyTable
GROUP BY Left([Appeal_ID])

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)

All,

The following is an example:

I have a table that looks as follows:

Appeal_ID                   2000    2001    2002
mNLC00                       10      100
mNLC01                                  10       100
eVNT00                        200     10
eVNT01                                   200       10

What I want is something like this:

Appeal                     2000    2001    2002
mNLC                        10       110      100
eVNT                         200      210      10

I can't seem to do this in one query. I can get a sum for a particular
column value but it repeats many times when placed in the detail
section of the report. If I place it in the page footer section than
the sum formula gives an error.

My sum formula looks as follows: =Sum(IIf(Left(total!APPEAL_ID,
4)="mnlc",total![2000],0))

What is the best way to go about doing this?

Tim

 

[Tim]

M,

Thanks for your reply. This helped greatly.

--Tim

Dear Tim,

The problem is that your table is not sufficiently normalized, since you
have a spreadsheet like setup (years 2000, 2001, etc; categories like mNLC00,
mNLC01, etc.)

What would be helpful for statistical queries (sum, average, max, etc.) is
to use a table designed like this:

AppealCategory (with values like mnNLC, eVNT)
AppealNumber (with values like 0;1;2;etc)
AppealYear (with values like 2000; 2001; etc.)
AppealValue (with values like 10; 200; 100; etc)

In this way, you could 1) group your data by category and year and
sum(value) and
2) produce a cross query to generate an overview as you mentioned in your
original e-mail.

Hope this helps,

M.

All,

The following is an example:

I have a table that looks as follows:

Appeal_ID                   2000    2001    2002
mNLC00                       10      100
mNLC01                                  10       100
eVNT00                        200     10
eVNT01                                   200       10

What I want is something like this:

Appeal                     2000    2001    2002
mNLC                        10       110      100
eVNT                         200      210      10

I can't seem to do this in one query. I can get a sum for a particular
column value but it repeats many times when placed in the detail
section of the report. If I place it in the page footer section than
the sum formula gives an error.

My sum formula looks as follows: =Sum(IIf(Left(total!APPEAL_ID,
4)="mnlc",total![2000],0))

What is the best way to go about doing this?

Tim