How to Tackle Numerous Fields for Calculations?

[Heatshiver]

I am creating a database from an Excel Workbook. Many of the calculated cells are dependent on the values of other calculated cells.

There are more than 255 cells for the one sheet so I can't use one table.



I was forced to use multiple tables and place the calculation fields in their own tables for the use of their fields in a calculating query, although they won't be seen on any form or report.

What is the best way to relate these tables even though they should really be all part of one table? Should I just use a Cross Join? Will a cross join allow me to properly calculate with a field from an unrelated table? What about an Outer Join?

I need to reinforce integrity so that if one set of table data is deleted, so are all the others.

I have already tried to place a "Report Number" autonumber field on each table, but it doesn't seem to allow me to reinforce the integrity and gives me, "invalid field name of definition of index or relationship".

Please help! Thank you.

[Heatshiver]

I realized I had all my other primary keys (Report Number fields) set to autonumber, which would not allow the relationship. But after changing them to Number they would allow the relationship with reinforcement of integrity.

Is this the way I should do it?

[RuralGuy]

It sounds like you are on the right track now.

[Heatshiver]

I realize that without the Excel file I am using it is hard to describe the exact issue (I can upload if wanted). The relationships now work, but it looks like the easiest way to work this is to create calculated fields in a query and go about it that way.

It's just an uphill battle as one cell that is calculated will use many other cells, some of which are also calculated (and those are often based off of other calculated cells!). A nightmare to say the least!

[RuralGuy]

Very common issues when moving from Excel to Access.