Access "Expression Builder" I am baffled! 2

[wbishopjr]

I'm new at this Access thing, I have built 2 databases before and I have been assigned to another one to track failure costs of our optical connectors. I thought it was "simple" in the beginnig.
I have tried various expressions in the builder, and they turn up many odd results...#Name?, 0, "blank",...Arrgggh!
I can't get it. I'll show a simplified example of my database below. The red part is what I can't figure out...

|Table1|
(Connector)(Price)

|Table2|
(Connector)(Failure Amount)(Total Cost of failures)

|Form|
(<Combo Box> - Choose Connector) Store it in |Table2| (Connector)
(<Text Box> - Failure Amount) Store it in |Table2| (Failure Amount)
(<?????> - <<expression>>=(Failure Amount) * |Table1|(Price) store it in |Table2|(Total cost of failures)

The reason for the external connector price table, is the prices change and Table2 would keep historical data on old prices.
I would LOVE any help ...
Thanks a million in advance,
W.Bishop

 

[johnbrugman]

I don't really understand what you do. Some suggestions:
* Total cost of failures is an attribute which don't have to be stored in the database. It is the total of Failure Amount of a specific Connector. If you do this anyway you shoudl store this in the Connector table (table 1)
* Table 2 keeps a history. Maybe you already do but it is usefull to add a datetimestamp in this table.
* The form should contain a subform with the contents of table 2. The total cost of failures can then be a unbinded filed which expresse the total of failure amount of table 2. You can use a group by query
* Otherwise i need a saple of the db

 

[TresVinos]

If you're saying what I think you're saying, then this may be more simple than you thought. I believe what you are looking for is to keep an historical track of costs related to connector failures.

If this is so, then I believe you only need one table:

|Table1|
(Connector)(Price)(FailureAmt)

a query:

|Query1|
(Connector)(Price)(FailureAmt)(FailureCost: [Price]*[FailureAmt])

and a form which puts all this into a nice display format.

Now that I think of it, you may want the Connectors in their own table and simply use a lookup function in the table that lists the price and failure amount.

Hope this solves your problem,

Rafael

 

[wbishopjr]

Sorry it took so long to reply to this post, both are great suggestions I finally worked out this problem with your help, THANKS!!

Waymond