Calculation back to Table

[RufussMcGee]

What I have is one table called Total with ID (Autonumber), Item, Bgn Inv (Beginning Inventory) and Purch (Purchase) and Tbl ID (Table ID) for fields.

What I am trying to do is create a summary of the total pieces for each type of Item...

For Example
Item, Bgn Inv, Purch, Tbl ID
Baseballs, 2, 10, T1
Bats, 0, 2, T1
Football, 5, 1, T2
Helmet, 10, 0, T2

my Textbox control source equals this...

=IIf([Total]![Tbl ID]="T1",[Total]![Bgn Inv]+[Total]![Purch],0)

In Stead of getting an error would like to see this on my report page...
Baseball Items 14
Football Items 16.

I like the one total table for the items for other reasons. Would like to resolve this problem but would like to know if it would be better to seperate the items to individual tables.

Thanks

 

[redwoodly]

- Do not separate into different tables.
(not good db design).

- why don't you try to do it in a query first and then create the report based on the query. -

so create query based on table TOTAL.

click the TOTALS button on the menu bar (the greek symbol) (this will add the TOTAL row down in the grid)

then add the field TableID TO THE GRID as the first column with total row (3RD ROW) = GROUPBY

THEN ADD THIS AS THE first line of the 2nd COLUMN: [Total]![Bgn Inv]+[Total]![Purch]
on the TOTALS row for this column - select SUM

THEN RUN THE QUERY. SHOULD DO IT except for the names of the "TYPES"

do you have a table that explains what the TableID means?
e.g., T1 = baseball, T2 = Football

if so include that table also in the query with the SPORTNAME in the first column instead of TableID (and a relationship line between the two TableIDs - if that makes sense). Then it will show TYPE and SUM for each type.

 

[RufussMcGee]

The 3rd row of the first column "sort" allows only three choices ascending, descending or unsorted. So where should the groupby go?

 

[RufussMcGee]

Ignore this question missed a step.