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.
you need to use the distinct clause on vendor id, and then use the sum function on the expenses.
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.
How many vendors are there? It can also be done pretty easily in excel.
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