Summing over values in a related table

[j_horbach]

Hello.

This is my first post. I am making an attempt at learning Access and have run into a wall. Just wondering if anyone can give me hand.

Let me set up the problem.

I have 2 tables:

tblCategories
---> CategoryID, name, totalSpent

tblItem
--> ItemID, name, price, tax(yes/no), CategoryID

They share a one-to-many relationship through CategoryID(1)->ItemID(MANY)

I wish to have the totalSpent field in tblCategories display the the sum over all items in the category of ( price * ( 1.0 + tax_percentage ) ). I have tried using the expression builder in default value, but I couldn't get it to work. Please help.

 

[Pat Hartman]

Defaults in a table definition cannot refer to other columns. Create a query that joins the two tables and sum the quantity and calculate the total. You don't have tax_percentage in either table so I don't know where it is comming from.

BTW, name is a poor choice as a column name since it is the name of a property.

 

[j_horbach]

Thanks for the reply.

I saw that about my choosing name as field properties....thanks....CategoryName and ItemName are better.

tax_percentage would be a constant. If tax is to be applied to an item (as indicated by the tax(yes/no) field), then the formula calculating totals would reflect that.

I never have used a query to link two tables....but ill give it a try....thanks

If you have any other advice please let me know.

J

 

[Pat Hartman]

Creating the query is simple enough.
1. Select new from the query window
2. Add both tables from the chooser menu
3. To join the tables, click on the primary key in the 1-side table and drag to the foreign key in the many-side table. If you have already created a relationship between these tables in the relationship window, Access will automatically draw the join lines. You won't have to.
4. Select the fields you need from each table.
5. Save the query and test it.
6. Switch to SQL view and add the calculated field to the end of the select clause:

Select ..... , price * (1.0 + GetTaxRate()) As TotalSpent
From ....;

Since you cannot refer to a VBA variable from within SQL, I used a function - GetTaxRate() - but you'll need to create it. Substitute the correct tax rate. You can use this function any time you need the tax rate rather than using a VBA constant or public variable.

Public Function GetTaxRate() As Single
    GetTaxRate = .065
End Function

 

[j_horbach]

Hello.

Thanks for the help above.

Suppose I have the following tables:

tblCategory
--> CatID, CatName, totalSpent
tblItem
--> ItemID, ItemName, ItemCost, ItemCat, RecID, CatID
tblReceipt
--> RecID, RecStore, totalCost

These tables have the following relationships:

CatID<(1)--(Many)>ItemID
RecID<(1)--(Many)>ItemID

What I want to be able to do, is enter items via the Receipt table (I can do this) and while entering an item, I want to choose which CatID it belongs to by having a drop down list box containing all available CatName values, or by entering a new value for CatName thereby creating a new CatID. Once this has been done, I want the CatID field in the items table that pops up when the plus is clicked to reflect the proper CatID for that items CatName field, and I wish it to be locked.

The other part I wish to have is when looking at tblCategory, I want to have the CatName field in the items table produced when I click the plus sign, locked.

Any advice is great!

Thanks.