Results 1 to 4 of 4

Thread: Anyone good with SQL/Access?

  1. #1
    Super Moderator RBB's Avatar
    Join Date
    Jul 2002
    Location
    NOVA
    Posts
    0

    Anyone good with SQL/Access?

    I suck at this stuff, but if I could figure out a query it would prevent me from going through about 30K lines of garbage and doing this manually. I've got a table in Access where I would like to look at a column of Vendor ID's. When the vendor ID changes I would like to total up all the Expenses for that Vendor, then move on to the next Vendor ID. Anyone know how would i go about this?

    For example, i could have 50 rows with the same vendor ID. When the ID changes I want my query to produce one row with that Vendor ID, the total of the expenses, and the name, address, etc.
    Last edited by RBB; 01-26-2010 at 10:20 AM.


    AtLarge Nutrition Supplements Get the best supplements and help support Wannabebig!

  2. #2
    Senior Member
    Join Date
    May 2003
    Location
    Ireland
    Posts
    408
    you need to use the distinct clause on vendor id, and then use the sum function on the expenses.

    For example
    select distinct vendor_id, name, address, sum(expenses) from vendortable group by vendor_id,name,address

    The group by clause needs to include all fields in your select statement excluding the columns the sum funcion is on.

    this will add up all the expenses for each individual vendor. so if vendor A has 50 lines with an expense of $1 per line. this query will show one line for Vendor A with a value of 50$

    is that what you were looking for?
    Last edited by ftotti10; 01-26-2010 at 02:41 PM.

  3. #3
    Lifting junkie. AKMass's Avatar
    Join Date
    Sep 2007
    Location
    MASSachusetts
    Posts
    969
    How many vendors are there? It can also be done pretty easily in excel.
    5'10"
    180

    Ultra Nasty Hypertrophy journal (HCT-12)-http://www.wannabebig.com/forums/sho...rnal!-(HCT-12)

    Old My 5-3-1 Journal:http://www.wannabebig.com/forums/sho...d.php?t=132576

  4. #4
    Super Moderator RBB's Avatar
    Join Date
    Jul 2002
    Location
    NOVA
    Posts
    0
    Quote Originally Posted by ftotti10 View Post
    you need to use the distinct clause on vendor id, and then use the sum function on the expenses.

    For example
    select distinct vendor_id, name, address, sum(expenses) from vendortable group by vendor_id,name,address

    The group by clause needs to include all fields in your select statement excluding the columns the sum funcion is on.

    this will add up all the expenses for each individual vendor. so if vendor A has 50 lines with an expense of $1 per line. this query will show one line for Vendor A with a value of 50$

    is that what you were looking for?
    yes! very much appreciated, bro!


    AtLarge Nutrition Supplements Get the best supplements and help support Wannabebig!

Similar Threads

  1. Westside Primer/FAQ by Kethnaab
    By Sentinel in forum Powerlifting and Strength Training
    Replies: 0
    Last Post: 09-14-2006, 04:39 PM
  2. MonStar's Journal: A New Beginning
    By MonStar in forum Member Online Journals
    Replies: 612
    Last Post: 12-18-2002, 04:01 PM
  3. MonStar's HST Journal
    By MonStar in forum Member Online Journals
    Replies: 210
    Last Post: 06-02-2002, 03:18 PM

Bookmarks

Posting Permissions

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