Producing combined table grouped by categories

Z

Zaur Bahramov

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!
 
J

John Spencer

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
 
Z

Zaur Bahramov

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 said:
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 said:
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!
Click to expand...
Click to expand...
 
J

John Spencer

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 said:
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 said:
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 said:
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!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

find values from column and place it in individual 3
Group By - SQL Question? 4
Need subtotals 12
Pivot Table Calculated Fields 4
Vertical lookup conundrum 4
how to add subtotals to a report? 2
Group By Question 1
how can I have a formula result based on multiple criteria/columns 1

Top