Sum of a ROW

[mikesj]

To give you the background, I have a field for every month from Jan 04
to Present with a dollar amount associated with it.

I want to be able to pull up a specific persons record and show the
Total of the row

John Smith | Sum of all entries from Jan 04 - Present |

The current layout is similar to the following

Name | Jan 04 | Feb 04 | Mar 01 | Apt 04 | May 04... Obviously this is
extremly simple in excel, but is there a simple way to do this as there
are 35 columns to be added together.

Thanks

 

[Guest]

Short answer: No, there is no easy way to do this.

Longer answer: Access is a database, not a spreadsheet. You should not
store "data" in this format within a database. If you had the data formatted
in a normalized form (User, Year, Month, Amount) you could do a summation
query across any grouping of year and month, for any user.

Longer answer: Create a function somthing like. This is not the best option

Public Function SumUserRow(UserName) as variant

dim strSQL as string
dim rs as DAO.Recordset
dim intField as integer

strSQL = "SELECT * FROM yourTable WHERE UserName = '" & Username & "'"
set rs = currentdb.openrecordset (strsql)

if not rs.eof then
SumUserRow = 0
For intField = 1 to rs.fields.count -1
SumUserRow = SumUserRow + NZ(rs(intField), 0)
Next
endif

rs.close
set rs = nothing

end function

Hope This Helps.
Dale

*******************************

 

[John Vinson]

To give you the background, I have a field for every month from Jan 04
to Present with a dollar amount associated with it.

Then you have an incorrectly designed table... a spreadsheet, not a
database table!

"Fields are expensive, records are cheap". A much better design would
be a tall-thin table with three fields - an identifier linked to the
primary key of a table describing the meaning of each row in your
current table (account? customer?); a date; and an amount.

I want to be able to pull up a specific persons record and show the
Total of the row

John Smith | Sum of all entries from Jan 04 - Present |

Trivially easy using a Totals query, with the normalized design above.

The current layout is similar to the following

Name | Jan 04 | Feb 04 | Mar 01 | Apt 04 | May 04... Obviously this is
extremly simple in excel, but is there a simple way to do this as there
are 35 columns to be added together.

You can *do* it... in a Query, you can put in a calculated field

Total: NZ([Jan 04]) + NZ([Feb 04]) + NZ([Mar 04]) + <etc etc>

But I'd REALLY REALLY recommend correcting your table structure. As it
is, you'll need to widen your table every month, and change all your
queries, forms, etc. to match; and you'll soon run into the limits on
record size and query complexity.

John W. Vinson[MVP]

 

[mikesj]

I really appreciate both of yours recomendation and views, however let
me give you some background. I would never have designed it this way
either, but due to the fact that I am working off data provided to me
it is the most simplistic way to get the data into access.

I need to use access to give me top producers for groups, as well as
combine information from multiple sheets. For instance I was provided
one spreadsheet which had all of 04, one with all of 05 and one with 06
to present.

I appreciate the feedback, and unfortunatly will still be forced to use
it in this fasion and will resort to the simplistic idea of just adding
the fields together.

Thank you for your ideas, I really do appreciate it

 

[John Vinson]

I really appreciate both of yours recomendation and views, however let
me give you some background. I would never have designed it this way
either, but due to the fact that I am working off data provided to me
it is the most simplistic way to get the data into access.

I need to use access to give me top producers for groups, as well as
combine information from multiple sheets. For instance I was provided
one spreadsheet which had all of 04, one with all of 05 and one with 06
to present.

I appreciate the feedback, and unfortunatly will still be forced to use
it in this fasion and will resort to the simplistic idea of just adding
the fields together.

Thank you for your ideas, I really do appreciate it

You can, if you wish, use a "Normalizing Union Query" to migrate the
data from your imported (or linked) spreadsheets into a properly
normalized table. If you'll be doing more than a little bit of data
mining, it may well be worth the extra step! Post back if you'ld like
help doing so.

John W. Vinson[MVP]

 

[mikesj]

John:

I decided to correct it once and for all. I appreciate your offer for
help, but I was able to get the data in to a correct database format.
I will have to do 2 steps next month to update the data so I receive
the correct output, but everything will be very accessible.

I did not use a "Normalizing Union Query" as I did this prior to
reading your suggestion. I would appreciate any knowledge you can give
me or a link I could goto to learn about this type of query.

Thanks

 

[John Vinson]

I did not use a "Normalizing Union Query" as I did this prior to
reading your suggestion. I would appreciate any knowledge you can give
me or a link I could goto to learn about this type of query.

This is a very common way to get data from a wide-flat table into a
tall-thin table. Say you have a spreadsheet like yours with fields
Jan04, Feb04, Mar04, ... and so on, and a field ID which identifies
each row. You can create a UNION query by going into the SQL view of
the query designer (it's one of the options in the leftmost tool on
the query design toolbar).

Edit a query to

SELECT ID, #1/1/2004# As ItemDate, [Jan04] AS Amount
FROM wideflat
WHERE [Jan04] IS NOT NULL
UNION ALL
SELECT ID, #2/1/2004# As ItemDate, [Feb04] AS Amount
FROM wideflat
WHERE [Feb04] IS NOT NULL
UNION ALL
SELECT ID, #3/1/2004# As ItemDate, [Mar04] AS Amount
FROM wideflat
WHERE [Mar04] IS NOT NULL
<etc through all the fields>

Base an Append query on the UNION query and use it to populate your
predefined normalized table.

John W. Vinson[MVP]

 

[mikesj]

this would have been a more efficient way then I did it.

I appreciate this as it was valueable knowledge gained.

I did not use a "Normalizing Union Query" as I did this prior to
reading your suggestion. I would appreciate any knowledge you can give
me or a link I could goto to learn about this type of query.

This is a very common way to get data from a wide-flat table into a
tall-thin table. Say you have a spreadsheet like yours with fields
Jan04, Feb04, Mar04, ... and so on, and a field ID which identifies
each row. You can create a UNION query by going into the SQL view of
the query designer (it's one of the options in the leftmost tool on
the query design toolbar).

Edit a query to

SELECT ID, #1/1/2004# As ItemDate, [Jan04] AS Amount
FROM wideflat
WHERE [Jan04] IS NOT NULL
UNION ALL
SELECT ID, #2/1/2004# As ItemDate, [Feb04] AS Amount
FROM wideflat
WHERE [Feb04] IS NOT NULL
UNION ALL
SELECT ID, #3/1/2004# As ItemDate, [Mar04] AS Amount
FROM wideflat
WHERE [Mar04] IS NOT NULL
<etc through all the fields>

Base an Append query on the UNION query and use it to populate your
predefined normalized table.

John W. Vinson[MVP]