Need Microsoft Access help

[NakaNaka]

Hey. I just started an internship and one of the things we need to know how to do is use Microsoft Access. It wasn't a requirement so I'm learning as I go and I understand queries and all that. What I need to do is in the table, create a new column and have that new column be the sum of three other colums. I asked my supervisor and he quickly told me but then when I went back to put it into the program, it didn't work because I don't think I truly got what he meant. So any help would be greatly appreciated. Thanks.

 

[ActuaryTm]

Originally posted by: NakaNaka
What I need to do is in the table, create a new column and have that new column be the sum of three other colums.

For a sum by record, simply use a query. Use the "Build..." function to create a summation expression. The syntax should follow somehing along these lines:

• Sum(tblData!tblvalue1+tblData!tblvalue2+tblData!tblvalue3)

 

[KB]

Creating a column that is the sum of three other columns breaks 2nd normal database form. Don't do it unless you really know what your doing. Just show the sum value by using a query to sum up the columns as ActuaryTm mentioned.

SELECT Sum(tblData!tblvalue1+tblData!tblvalue2+tblData!tblvalue3) As SumeofThreeColumns

Read about normal forms: http://www.bkent.net/Doc/simple5.htm

 

[NakaNaka]

Thanks for the help, but I'm not sure where to type this Sum (Name of column a + name of column b, etc etc) into the query. At the very top? Thanks.

Edit: I'm so good with Access, I tried it and I crahsed the program

 

[ActuaryTm]

Originally posted by: NakaNaka
Thanks for the help, but I'm not sure where to type this Sum (Name of column a + name of column b, etc etc) into the query. At the very top? Thanks.

In the "Design View" query builder, select a blank column, right click on the blank "Field" value and select "Build...". This will launch the Expression Builder. The rest should be relatively easy to follow from the previous instructions.

Alternatively, you can use the SELECT statement above, but as a beginning user I would suggest insead opting for the "Build..." function until you are confortable with SQL statements.

 

[NakaNaka]

Originally posted by: ActuaryTm

Originally posted by: NakaNaka
Thanks for the help, but I'm not sure where to type this Sum (Name of column a + name of column b, etc etc) into the query. At the very top? Thanks.

In the "Design View" query builder, select a blank column, right click on the blank "Field" value and select "Build...". This will launch the Expression Builder. The rest should be relatively easy to follow from the previous instructions.

Alternatively, you can use the SELECT statement above, but as a beginning user I would suggest insead opting for the "Build..." function until you are confortable with SQL statements.

I must sound like such a newb but I just can't get it. I did as you said and I wanted a query with user_id and then the sum of these colums for each user. So if user a has 3 2 and 4 then I want his to read 9. I don't want a sum for all the users. And the error message I got when I tried to run this query: You tried to execute a query that does not include the specified expression 'UserID' as part of an aggregate function.

Am I just a complete idiot? Thanks guys for all the help.

 

[NakaNaka]

I think I got it. I'll let you know if I have any more questions. Thanks.

 

[ActuaryTm]

Hopefully this example will help.

Beyond that, would actually have to see the database design to offer further aid.

 

[NakaNaka]

Hey. I have another quick access question. Say I have two queries. One puts out 400 records, the other 1000. These two queries show that 400/1000 customers have purchased our product 5 times or more. I need to put these in some type of report. How do I make it so that I can either turn this into another query or a report or export into excel with a nice 400/1000 number coming up, and not 400 and 1000 customer listings. Thanks

-Phil

 

[EagleKeeper]

There is a summation operation that will tell you how many records within the output.