Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2005
    Posts
    29

    Exclamation Unanswered: Newbie question in suming and filtering/deleting records.

    Hi guys, i'm here again posting more problem ..hope u guys don't mind to help. The question as follows:-

    i have the following field in the table

    Name Money Given
    Alvin 2
    ben 3
    john 5
    alvin 5
    kenneth 6
    ben 5
    alvin 3

    what i want is to add up all the money from the particular person and create a new table like below

    Name money given
    alvin 10
    ben 8
    john 5
    kenneth 6

    how should i go about doing it? Thanks in advance for any help...

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    use a query

    Code:
    SELECT Name, SUM([Money Given])
    FROM tblmoney
    GROUP BY Name;

  3. #3
    Join Date
    Feb 2005
    Posts
    29
    Hi David, what if the number contain in that field "Money Given" is a text instead of number....and there are other field other then this two. Ask this because the system prompt me that there is and aggregate error ...something about data type.....is there a way to change text to numbers?........

  4. #4
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    either change your table, or use a conversion function
    Cint() changes things into integers

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    dumb question:-
    if the column name is [money given] why would it be defined as text?
    either its money given or its not. I would suggest you chaneg the data type of the underlying column in the table to a numeric value - currency for preference.

    David C might be being a little 'frisky' in suggesting CINT, you would probably be better off using the currency conversion CCUR, unless of course you donations are always integer.

    incidentally I'd also suggest that you dont use a space in column names, ie
    "money given" should become "moneygiven" or "MoneyGiven", or give it an even clear name eg "Donation"

    the reason? it gets rid of the need to enclise column names with square brackets eg [Money Given]

    You can separate words with the underscore character but that cause problems with other SQL back ends such as SQL Server. AS a consequcne I'd suggest get in some good habits early.

  6. #6
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    ah come on it was before 8 in the morning when i answered,

    and i got it almost right
    and its displayed as an integer and for all i know it could be a field of donnations in pence,

    ok im making excuses now
    Last edited by DavidCoutts; 02-18-05 at 07:05. Reason: my brain seems to have stopped working today

  7. #7
    Join Date
    Feb 2005
    Posts
    29
    hey hey sorry for the trouble...dudes...take it easy..the example above is not the actual thing i'm doing...just an illustration. Basically i'm transfering all these datas from excel and those idiots mess up all the datatype and fields in the excel sheet. My job didn't have much a choice but to except those mess and convert them into a working database. Anyway both of you i right...i just need some advice because i quite new to access and are draining all my brain cell to start learning. Thanks for all your advice i value them very much.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •