If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Producing combined table grouped by categories
Hi!
I have two tables: Categories and Expenses. Categories table has 3 fields: CategoryID CategoryName CategoryDescription 1 Stationaries Office materials, pens, paper, etc. 2 Petrol Petrol for company vehicles 3 Vegetables Expenses related to vegetables 4 Fruits Expenses related to fruits 5 Utilities Gas, electricity, water, etc Expenses table has 4 fields: ExpenseID CategoryID ExpenseName AmmountSpent 4 1 Pens$50.00 5 1 Pencils$30.00 6 1 Rulers$75.00 7 1 Glue$20.00 8 2 Diesel for Car A $120.00 9 2 Diesel for Car B $80.00 10 2 Benzine for Car C$70.00 11 2 Benzine for Car D$90.00 12 3 Carrots$40.00 13 3 Pumpkins $20.00 14 3 Cabbage$24.00 15 3 Onions$33.00 16 3 Radish$30.00 17 4 Apples$100.00 18 4 Wine grapes $120.00 19 4 Peaches$70.00 20 4 Bananas$150.00 21 5 September 2009 utility bill $120.00 22 5 October 2009 utility bill $145.00 23 5 November 2009 utility bill $137.00 24 5 Decemper 2009 utility bill $155.00 My task is to make a query that will produce the following result: Stationaries4 1 Pens $50.00 5 1 Pencils $30.00 6 1 Rulers $75.00 7 1 Glue $20.00 Petrol8 2 Diesel for Car A $120.00 9 2 Diesel for Car B $80.00 10 2 Benzine for Car C $70.00 11 2 Benzine for Car D $90.00 Vegetables12 3 Carrots $40.00 13 3 Pumpkins $20.00 14 3 Cabbage $24.00 15 3 Onions $33.00 16 3 Radish $30.00 Fruits17 4 Apples $100.00 18 4 Wine grapes $120.00 19 4 Peaches $70.00 20 4 Bananas $150.00 Utilities21 5 September 2009 utility bill $120.00 22 5 October 2009 utility bill $145.00 23 5 November 2009 utility bill $137.00 24 5 Decemper 2009 utility bill $155.00 Is it possible to do so? How? Thank you! |
#2
|
|||
|
|||
Producing combined table grouped by categories
A query returns the same number of columns so will have to have the query
return CategoryName for every row. You can print a report that looks like your requested output. And now that I think about it, you might be able to return BLANK/Null for a calculated column for category name SELECT IIF([ExpenseID] = DMin("ExpenseID","Expenses","CategoryID=" & Expenses.[CategoryID], [CategoryName],Null) as ShowCategory , ExpenseID, Expenses.CategoryID, ExpenseName, AmountSpent FROM Expenses INNER JOIN Categories ON Expenses.CategoryID = Categories.CategoryId ORDER BY Expenses.CategoryID, Expenses.ExpenseID That would still return 5 columns but only the first row of each category would DISPLAY a value for CategoryName. In query design view == add both tables == Drag from categoryID to CategoryId to set the join of the two tables == Select the fields you want to see from the Expenses table == Enter the following expression in a blank field "cell" IIF([ExpenseID] = DMin("ExpenseID","Expenses","CategoryID=" & Expenses.[CategoryID], [CategoryName],Null) == Set the sort to be by expenses category id and expenseId. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Zaur Bahramov wrote: Hi! I have two tables: Categories and Expenses. Categories table has 3 fields: CategoryID CategoryName CategoryDescription 1 Stationaries Office materials, pens, paper, etc. 2 Petrol Petrol for company vehicles 3 Vegetables Expenses related to vegetables 4 Fruits Expenses related to fruits 5 Utilities Gas, electricity, water, etc Expenses table has 4 fields: ExpenseID CategoryID ExpenseName AmmountSpent 4 1 Pens$50.00 5 1 Pencils$30.00 6 1 Rulers$75.00 7 1 Glue$20.00 8 2 Diesel for Car A $120.00 9 2 Diesel for Car B $80.00 10 2 Benzine for Car C$70.00 11 2 Benzine for Car D$90.00 12 3 Carrots$40.00 13 3 Pumpkins $20.00 14 3 Cabbage$24.00 15 3 Onions$33.00 16 3 Radish$30.00 17 4 Apples$100.00 18 4 Wine grapes $120.00 19 4 Peaches$70.00 20 4 Bananas$150.00 21 5 September 2009 utility bill $120.00 22 5 October 2009 utility bill $145.00 23 5 November 2009 utility bill $137.00 24 5 Decemper 2009 utility bill $155.00 My task is to make a query that will produce the following result: Stationaries4 1 Pens $50.00 5 1 Pencils $30.00 6 1 Rulers $75.00 7 1 Glue $20.00 Petrol8 2 Diesel for Car A $120.00 9 2 Diesel for Car B $80.00 10 2 Benzine for Car C $70.00 11 2 Benzine for Car D $90.00 Vegetables12 3 Carrots $40.00 13 3 Pumpkins $20.00 14 3 Cabbage $24.00 15 3 Onions $33.00 16 3 Radish $30.00 Fruits17 4 Apples $100.00 18 4 Wine grapes $120.00 19 4 Peaches $70.00 20 4 Bananas $150.00 Utilities21 5 September 2009 utility bill $120.00 22 5 October 2009 utility bill $145.00 23 5 November 2009 utility bill $137.00 24 5 Decemper 2009 utility bill $155.00 Is it possible to do so? How? Thank you! |
#3
|
|||
|
|||
Producing combined table grouped by categories
When I write the expression:
IIF([ExpenseID] = DMin("ExpenseID","Expenses","CategoryID=" & Expenses.[CategoryID], [CategoryName],Null) I get the following error: "The expression you entered has a function containing the wrong number of arguments". "John Spencer" wrote in message ... A query returns the same number of columns so will have to have the query return CategoryName for every row. You can print a report that looks like your requested output. And now that I think about it, you might be able to return BLANK/Null for a calculated column for category name SELECT IIF([ExpenseID] = DMin("ExpenseID","Expenses","CategoryID=" & Expenses.[CategoryID], [CategoryName],Null) as ShowCategory , ExpenseID, Expenses.CategoryID, ExpenseName, AmountSpent FROM Expenses INNER JOIN Categories ON Expenses.CategoryID = Categories.CategoryId ORDER BY Expenses.CategoryID, Expenses.ExpenseID That would still return 5 columns but only the first row of each category would DISPLAY a value for CategoryName. In query design view == add both tables == Drag from categoryID to CategoryId to set the join of the two tables == Select the fields you want to see from the Expenses table == Enter the following expression in a blank field "cell" IIF([ExpenseID] = DMin("ExpenseID","Expenses","CategoryID=" & Expenses.[CategoryID], [CategoryName],Null) == Set the sort to be by expenses category id and expenseId. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Zaur Bahramov wrote: Hi! I have two tables: Categories and Expenses. Categories table has 3 fields: CategoryID CategoryName CategoryDescription 1 Stationaries Office materials, pens, paper, etc. 2 Petrol Petrol for company vehicles 3 Vegetables Expenses related to vegetables 4 Fruits Expenses related to fruits 5 Utilities Gas, electricity, water, etc Expenses table has 4 fields: ExpenseID CategoryID ExpenseName AmmountSpent 4 1 Pens$50.00 5 1 Pencils$30.00 6 1 Rulers$75.00 7 1 Glue$20.00 8 2 Diesel for Car A $120.00 9 2 Diesel for Car B $80.00 10 2 Benzine for Car C$70.00 11 2 Benzine for Car D$90.00 12 3 Carrots$40.00 13 3 Pumpkins $20.00 14 3 Cabbage$24.00 15 3 Onions$33.00 16 3 Radish$30.00 17 4 Apples$100.00 18 4 Wine grapes $120.00 19 4 Peaches$70.00 20 4 Bananas$150.00 21 5 September 2009 utility bill $120.00 22 5 October 2009 utility bill $145.00 23 5 November 2009 utility bill $137.00 24 5 Decemper 2009 utility bill $155.00 My task is to make a query that will produce the following result: Stationaries4 1 Pens $50.00 5 1 Pencils $30.00 6 1 Rulers $75.00 7 1 Glue $20.00 Petrol8 2 Diesel for Car A $120.00 9 2 Diesel for Car B $80.00 10 2 Benzine for Car C $70.00 11 2 Benzine for Car D $90.00 Vegetables12 3 Carrots $40.00 13 3 Pumpkins $20.00 14 3 Cabbage $24.00 15 3 Onions $33.00 16 3 Radish $30.00 Fruits17 4 Apples $100.00 18 4 Wine grapes $120.00 19 4 Peaches $70.00 20 4 Bananas $150.00 Utilities21 5 September 2009 utility bill $120.00 22 5 October 2009 utility bill $145.00 23 5 November 2009 utility bill $137.00 24 5 Decemper 2009 utility bill $155.00 Is it possible to do so? How? Thank you! |
#4
|
|||
|
|||
Producing combined table grouped by categories
Sorry, missed a closing parenthesis. Try this revised expression.
IIF([ExpenseID] = DMin("ExpenseID","Expenses","CategoryID=" & Expenses.[CategoryID]), [CategoryName],Null) John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Zaur Bahramov wrote: When I write the expression: IIF([ExpenseID] = DMin("ExpenseID","Expenses","CategoryID=" & Expenses.[CategoryID], [CategoryName],Null) I get the following error: "The expression you entered has a function containing the wrong number of arguments". "John Spencer" wrote in message ... A query returns the same number of columns so will have to have the query return CategoryName for every row. You can print a report that looks like your requested output. And now that I think about it, you might be able to return BLANK/Null for a calculated column for category name SELECT IIF([ExpenseID] = DMin("ExpenseID","Expenses","CategoryID=" & Expenses.[CategoryID], [CategoryName],Null) as ShowCategory , ExpenseID, Expenses.CategoryID, ExpenseName, AmountSpent FROM Expenses INNER JOIN Categories ON Expenses.CategoryID = Categories.CategoryId ORDER BY Expenses.CategoryID, Expenses.ExpenseID That would still return 5 columns but only the first row of each category would DISPLAY a value for CategoryName. In query design view == add both tables == Drag from categoryID to CategoryId to set the join of the two tables == Select the fields you want to see from the Expenses table == Enter the following expression in a blank field "cell" IIF([ExpenseID] = DMin("ExpenseID","Expenses","CategoryID=" & Expenses.[CategoryID], [CategoryName],Null) == Set the sort to be by expenses category id and expenseId. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Zaur Bahramov wrote: Hi! I have two tables: Categories and Expenses. Categories table has 3 fields: CategoryID CategoryName CategoryDescription 1 Stationaries Office materials, pens, paper, etc. 2 Petrol Petrol for company vehicles 3 Vegetables Expenses related to vegetables 4 Fruits Expenses related to fruits 5 Utilities Gas, electricity, water, etc Expenses table has 4 fields: ExpenseID CategoryID ExpenseName AmmountSpent 4 1 Pens$50.00 5 1 Pencils$30.00 6 1 Rulers$75.00 7 1 Glue$20.00 8 2 Diesel for Car A $120.00 9 2 Diesel for Car B $80.00 10 2 Benzine for Car C$70.00 11 2 Benzine for Car D$90.00 12 3 Carrots$40.00 13 3 Pumpkins $20.00 14 3 Cabbage$24.00 15 3 Onions$33.00 16 3 Radish$30.00 17 4 Apples$100.00 18 4 Wine grapes $120.00 19 4 Peaches$70.00 20 4 Bananas$150.00 21 5 September 2009 utility bill $120.00 22 5 October 2009 utility bill $145.00 23 5 November 2009 utility bill $137.00 24 5 Decemper 2009 utility bill $155.00 My task is to make a query that will produce the following result: Stationaries4 1 Pens $50.00 5 1 Pencils $30.00 6 1 Rulers $75.00 7 1 Glue $20.00 Petrol8 2 Diesel for Car A $120.00 9 2 Diesel for Car B $80.00 10 2 Benzine for Car C $70.00 11 2 Benzine for Car D $90.00 Vegetables12 3 Carrots $40.00 13 3 Pumpkins $20.00 14 3 Cabbage $24.00 15 3 Onions $33.00 16 3 Radish $30.00 Fruits17 4 Apples $100.00 18 4 Wine grapes $120.00 19 4 Peaches $70.00 20 4 Bananas $150.00 Utilities21 5 September 2009 utility bill $120.00 22 5 October 2009 utility bill $145.00 23 5 November 2009 utility bill $137.00 24 5 Decemper 2009 utility bill $155.00 Is it possible to do so? How? Thank you! |
Thread Tools | |
Display Modes | |
|
|