Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Unique values for one column, and sum the other column

Discussion in 'Information Technology' started by nokternal7, Jul 28, 2006.

  1. nokternal7

    nokternal7
    Expand Collapse
    Guest

    Hi,

    I have a large database. I want to show only unique values for one column,
    and I want the values of another column summed. For example:

    I have this:

    Column X Column Y
    ----------------------
    1 5
    1 6
    2 7
    2 6
    3 8
    3 9

    and I want it to be like this:

    Column X Column Y
    -------------------------
    1 11
    2 13
    3 17

    I'm fairly new to access. Sorry about the simple question.
    Thanks in advance
     
  2. Loading...

    Similar Threads Forum Date
    India Gwalior: A Unique Link to India's History Breaking News Nov 16, 2013
    Heritage Sikh Art: Reflections Of A Unique Spiritual, Secular Sikhism Identity History of Sikhism Jan 9, 2013
    General Great Sindhi Folk, wonderfully unique music style: 1 Videos Feb 23, 2012
    Mother Hope: Prakash Kaur and her Unique Home for Girls Inspirational Stories Feb 1, 2011
    In US, Sikhs succeed in push for understanding their unique culture Interfaith Dialogues Jun 18, 2010

  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    1. Create a query that uses this table.

    2. Depress the Total button on the toolbar (upper sigma icon.)
    Access adds a Total row to the grid.

    3. Drag the 2 fields into the query grid.
    Accept Group By under X.
    Under Y, choose: Sum

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "nokternal7" <nokternal7@discussions.microsoft.com> wrote in message
    news:F58EA03C-5F33-4DB5-8F12-D649FEB804E9@microsoft.com...
    > Hi,
    >
    > I have a large database. I want to show only unique values for one
    > column,
    > and I want the values of another column summed. For example:
    >
    > I have this:
    >
    > Column X Column Y
    > ----------------------
    > 1 5
    > 1 6
    > 2 7
    > 2 6
    > 3 8
    > 3 9
    >
    > and I want it to be like this:
    >
    > Column X Column Y
    > -------------------------
    > 1 11
    > 2 13
    > 3 17
    >
    > I'm fairly new to access. Sorry about the simple question.
    > Thanks in advance
    >
    >
     
  4. Jerry Whittle

    Jerry Whittle
    Expand Collapse
    Guest

    You need a Totals query. Create a query the includes both columns. Run it to
    make sure it gets the raw data you want. Back in design view, go up to View,
    Totals. (Personally I think this option should be under Query, but I
    digress.) You'll notice a new Total: row in the QBE grid. Leave Column X as
    Group By. Change Column Y to sum. For a nice touch, sort on Column X.
    --
    Jerry Whittle
    Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


    "nokternal7" wrote:

    > Hi,
    >
    > I have a large database. I want to show only unique values for one column,
    > and I want the values of another column summed. For example:
    >
    > I have this:
    >
    > Column X Column Y
    > ----------------------
    > 1 5
    > 1 6
    > 2 7
    > 2 6
    > 3 8
    > 3 9
    >
    > and I want it to be like this:
    >
    > Column X Column Y
    > -------------------------
    > 1 11
    > 2 13
    > 3 17
    >
    > I'm fairly new to access. Sorry about the simple question.
    > Thanks in advance
    >
    >
     
  5. nokternal7

    nokternal7
    Expand Collapse
    Guest

    Thank you for the quick replies.

    I did what you said and got the error, "data type mismatch in criteria
    expression." I failed to mention that I have a number of columns in this
    dataset, not just 2. I have 8 columns. But I still want to do esentially
    the same thing: Collapse all the results based only on the unique
    (non-duplicate) values of one column, and then sum the corresponding values
    of another column.

    Thanks again.




    "Jerry Whittle" wrote:

    > You need a Totals query. Create a query the includes both columns. Run it to
    > make sure it gets the raw data you want. Back in design view, go up to View,
    > Totals. (Personally I think this option should be under Query, but I
    > digress.) You'll notice a new Total: row in the QBE grid. Leave Column X as
    > Group By. Change Column Y to sum. For a nice touch, sort on Column X.
    > --
    > Jerry Whittle
    > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
    >
    >
    > "nokternal7" wrote:
    >
    > > Hi,
    > >
    > > I have a large database. I want to show only unique values for one column,
    > > and I want the values of another column summed. For example:
    > >
    > > I have this:
    > >
    > > Column X Column Y
    > > ----------------------
    > > 1 5
    > > 1 6
    > > 2 7
    > > 2 6
    > > 3 8
    > > 3 9
    > >
    > > and I want it to be like this:
    > >
    > > Column X Column Y
    > > -------------------------
    > > 1 11
    > > 2 13
    > > 3 17
    > >
    > > I'm fairly new to access. Sorry about the simple question.
    > > Thanks in advance
    > >
    > >
     
  6. Jerry Whittle

    Jerry Whittle
    Expand Collapse
    Guest

    Ouch. In that case you have a serious normalization problem with that table.
    When you need to sum up multiple columns of data into on figure, it's almost
    a lock that your table needs to be split out into at least one more table.
    --
    Jerry Whittle
    Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


    "nokternal7" wrote:

    > Thank you for the quick replies.
    >
    > I did what you said and got the error, "data type mismatch in criteria
    > expression." I failed to mention that I have a number of columns in this
    > dataset, not just 2. I have 8 columns. But I still want to do esentially
    > the same thing: Collapse all the results based only on the unique
    > (non-duplicate) values of one column, and then sum the corresponding values
    > of another column.
    >
    > Thanks again.
    >
    >
    >
    >
    > "Jerry Whittle" wrote:
    >
    > > You need a Totals query. Create a query the includes both columns. Run it to
    > > make sure it gets the raw data you want. Back in design view, go up to View,
    > > Totals. (Personally I think this option should be under Query, but I
    > > digress.) You'll notice a new Total: row in the QBE grid. Leave Column X as
    > > Group By. Change Column Y to sum. For a nice touch, sort on Column X.
    > > --
    > > Jerry Whittle
    > > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
    > >
    > >
    > > "nokternal7" wrote:
    > >
    > > > Hi,
    > > >
    > > > I have a large database. I want to show only unique values for one column,
    > > > and I want the values of another column summed. For example:
    > > >
    > > > I have this:
    > > >
    > > > Column X Column Y
    > > > ----------------------
    > > > 1 5
    > > > 1 6
    > > > 2 7
    > > > 2 6
    > > > 3 8
    > > > 3 9
    > > >
    > > > and I want it to be like this:
    > > >
    > > > Column X Column Y
    > > > -------------------------
    > > > 1 11
    > > > 2 13
    > > > 3 17
    > > >
    > > > I'm fairly new to access. Sorry about the simple question.
    > > > Thanks in advance
    > > >
    > > >
     

Share This Page