Number of lines in a table

[CraigR]

Good evening and thanks for the help in advance



I believe you can have about 255 items/lines in a table... but is there a way to increase this?

I am developing a database that will assess houses against building codes. So far I have "about" 255 fields with specific data but have now run out of fields is it possible to increase the number of fields in a table

thanks
craig

[ranman256]

your design may be wrong if you have >255 fields. What ARE these field names?

[orange]

Tell us in simple, plain English --no jargon -- what "business processes" exactly is this database intended to support?
Databases contain tables, tables contain records. I'm not sure what you mean by lines. 255 fields in a relational table is unheard of.

Sounds like a minimum of 3 tables (and probably more)

House ---->AssessmentVSBldgCode<----BldgCode

[ArviLaanemets]

It looks like classic try to put a bad Excel table design into Access!

An example: You want to register movements of parts in store. Often in Excel this is done as
MovementDate, Part1, Part2, Part2, ...
, and the quantity for a part is stored in according column. (Very inefficient even for Excel)

At least in Access you have to structure the same table like
MovementID, MovementDate, PartID, MovementQty

Only 4 fields are needed, and the number of different parts is practically unlimited (a smart way for Excel is same).

[Bulzie]

Until we can see what you are trying to do as Orange suggested, hard to suggest options. As you noticed you have a 255 limit. What you might be able to do is break up your data into multiple tables each with a foreign key field that will link back to main table. Then you can create multiple subforms or subreports to display the fields/data.

[June7]

Thread title is misnamed. Issue is not lines but fields. Table is basically unlimited in how many lines (records) are possible.

Queries are also limited to 255 fields. Could never get all the data into one report without subreport structure.

Something is probably very wrong with db structure.

[orange]

Something is probably very wrong with db structure.

agree totally, or the OP's description is extremely unclear.

[CraigR]

I am trying to develop a database to assess dwelling houses (initially) against the building code of aus & the aus standards.
there 12 sections of the code a house needs to be assessed against.
some sections of the housing code may have 4 fields that need to be assessed, other sections may have 15 fields to assess.
I also have a number of other tables in the database, some of these tables include council specific data about the planning scheme, others climate zones etc and others land developments with their own development requirements.
I can break sections down to specific tables, but the house assessment will have roughly 400 elements that need to be assess and I would like to keep them together.
not trying to sound like a dick but its hard to explain.

craig

[ArviLaanemets]

So to give some recommendations, we need to know what type of data these 400 elements contain (numeric, text, etc.), which is diversity grade for data type (how many integers, how many short integers, how many short texts (p.e. up tp 10 characters), how many long texts, etc). When the diversity is low, then you can consider a separate table(s) for building characteristics. (like BuildingCharacteristicID, BiuldingID, CharacteristicSection, CharacteristicID, CharacteristicValue)

[orange]

It would be helpful to you and readers if you could provide a description of the business that you are trying to support in plain English--no jargon. Start at the 30000 ft level and gradually add detail. This description will represent your business facts. Typically, preparing a draft model (showing proposed tables and relationships) with pencil and paper and refining it, is more efficient than trying to jump into physical Access with a trial and error sort of pproach.

A table with 400 fields is beyond Access' specifications (and extremely rare - if one actually exists).

So you have Aus BldgCode and Standards -we, non Aus, don't know exactly what that means so an overview would be helpful.
House assessments are made against 12 sections of the code.
Different sections have different points required for an assessment.

I also have a number of other tables in the database, some of these tables include council specific data about the planning scheme, others climate zones etc and others land developments with their own development requirements.

Where do these fit in the overall "business" you are trying to automate/support with this database?

Do you have some sample data showing what an assessment is?

From experience--if you can't describe it, no one can build it.

Good luck with your project.

[Bulzie]

You could break the 400 into 2 tables of 200 with a key field that links them and the main table together. Again you would have to keep them as separate subforms or subreports but when they are displayed they should all be pointing to the same record.