| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 59263 | 2005-06-26 10:27:00 | MS Acess query | Helplesss (272) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 367173 | 2005-06-26 10:27:00 | I have a simple database with three fields, (Name, year, cost). I want to create a query that will output (eg) Jims total costs for 2005 and 2004 (seperate totals on same query). Any ideas on how i can do this? | Helplesss (272) | ||
| 367174 | 2005-06-26 22:00:00 | Read up on a Crosstab query. This will total by year. You will have to create a query to total the costs first. | berryb (99) | ||
| 367175 | 2005-06-27 01:13:00 | sql for want you want is SELECT Table1.Year, Sum(Table1.cost) AS SumOfcost FROM Table1 GROUP BY Table1.Year HAVING (((Table1.Year)=[year_total])); |
beama (111) | ||
| 367176 | 2005-06-27 02:22:00 | sorry that will only output one years total, I have another go when I get time. | beama (111) | ||
| 367177 | 2005-06-27 02:43:00 | Berry is correct Crosstab query you do it by QBE or the fun way SQL | beama (111) | ||
| 367178 | 2005-06-27 11:27:00 | Following on from the posts from berryb and beama, yes you need to do a crosstab query. Assuming your data is held in just the one table (I have called it 'YourTableName'), the SQL for the crosstab query would look like this: TRANSFORM Sum(YourTableName.Cost) AS SumOfCost SELECT YourTableName.Name FROM YourTableName GROUP BY YourTableName.Name ORDER BY YourTableName.Name, YourTableName.Year PIVOT YourTableName.Year WITH OWNERACCESS OPTION; If you create a new query, click View -> SQL View and you can copy and paste the sample SQL - be sure to use your actual table and field names otherwise it won't work. HTH, Andrew :) |
andrew93 (249) | ||
| 1 | |||||