My table isn't big enough to contain all my information

[Leo]

Hello everyone. I'm trying to create a job costing
form. However I have too many values and not all of them
fit into the table I would like to use as my field list.
What do I do? I decided to split the fields that I would
like to use into two tables however in my form I can only
use one of the tables as a field list. Any help would be
appriciated.

 

[Allen Browne]

It sounds like you are trying to create a table like an expense journal,
that has a column for each or your expense categories.

That's not the way to do it in a relational database. Instead, create a
small table to hold the expense categories. The fields will be something
like this:
ExpenseCatID AutoNumber primary key
ExpenseCat Text name of this category.

Now create your expense table with these fields:
ExpenseID AutoNumber primary key
ExpenseCatID Number (Long) foreign key to above table.
ExpenseDate Date/Time when the expense occurred.
ExpenseAmount Currency how much

With this approach, you simply choose the expense category from a drop-down
list. The amount goes into the same column regardless of the category.

This makes it very easy to do things like:
- summing the expenses (it's just one colunn);

- summing the expenses for one category, e.g.:
=DSum("ExpenseAmount", "tblExpense", "ExpenseCatID = 2");

- creating a report that summarizes by each category, and totals at the end;

- doing all of the above for any date period (month, quarter, ...), just by
filtering on the ExpenseDate.

It is also possible to generate the journal look with a crosstab query,
though it sounds like you have too many categories for this approach to be
useful.

 

[John Vinson]

Hello everyone. I'm trying to create a job costing
form. However I have too many values and not all of them
fit into the table I would like to use as my field list.
What do I do? I decided to split the fields that I would
like to use into two tables however in my form I can only
use one of the tables as a field list. Any help would be
appriciated.

Access limits you to 255 fields. This limit is ABSURDLY high for any
properly normalized set of tables! I've had to use as many as 60
fields, a couple of times in the past twenty years.

Think vertically, not horizontally. I very strongly suspect that you
are storing data in fieldnames, and would do much better to implement
your tables as a many-to-many relationship using three tables, or some
other such multitable, tall-thin arrangement.

Please post a description of the data you're trying to store, I'm sure
someone will be able to suggest a solution which will not require
enormously wide tables.