255 Column Limit (1 Viewer)

[naa123]

Hi,

I am a beginner with Access, so apologies if this question has a very obvious answer/has been answered before.

I am hoping to use Microsoft Access to store a large master database which will consist of 5 individual databases combined. The individual databases have around 350 columns and my hope was to import these individually and then create a query to combine them into one. I realised very quickly that Access has a 255 column limit when importing data, so then I decided to split each individual database and import into tables that way. However, I realise this doesn't solve my problem as after importing data into smaller tables that Access will handle, I cannot then combine the data into a master database with 350 columns - too many fields are defined in the query.

What I would like to know is, is there any way to run a query in Access which will allow me to create a master database of more than 255 columns?

To give a bit of context around the individual databases - they are databases of cases seen in a health care setting. Cases have completed measures which have a large number of items.

Thank you in advance for any advice.

 

[The_Doc_Man]

First, this is your first post, so welcome to the forum!

Without knowing the detailed structure of your data, I can only guess. However, it SOUNDS like you have a normalization problem. Having 350 columns is not impossible in real life because Excel can do that. But it lacks other abilities that would make life easier for data analysis.

I am going to suggest that you look into the topic of database normalization. This would tell you how to break down data in different ways than jamming them together side-by-side. Search this forum for "Normalization" because it IS, after all, a database forum. But if you search on the web for help, you must use "Database Normalization" because by itself, "normalization" can apply to not less than six different major disciplines. Start with a few articles from .EDU domains, only because the .COM articles USUALLY have something to sell. But often the .COM articles are helpful, too.

In a relational database like Access, you can build hierarchies, pyramids of data to help you narrow the tables. When I was with the U.S. Navy, I ran across impossibly wide spreadsheets, so I know that it can happen. But we have MANY health-care projects represented in this forum that somehow overcame the issue.

 

[theDBguy]

Hi. Welcome to AWF!

Unfortunately, there is no way around the 255 columns limitation other than to use Access properly as already mentioned.

 

[ebs17]

is there any way to run a query in Access which will allow me to create a master database of more than 255 columns?

A clear NO.
The limit in Jet-SQL is 255 for both tables and queries.

You should turn to a database management system that offers higher limits.

In addition, 350 columns are extremely large for both tables and queries. Who can meaningfully watch something like that?

 

[MajP]

When you have lots of "measures" a database may be properly normalized, but that still does not mean it is manageable or useable.
In this case a Entity Value module can make working with this easier and easier to maintain.

record value in table when one of many buttons are clicked

That's exactly what I did with my tblParts, tblFinalProducts, and tblAssemblyComponents. Exactly that.....and it works great. I get that. Thank you for the effort of showing me a "map." Works well for me. If that is what we're all talking about then it may be conceivable to incorporate that...

www.access-programmers.co.uk

I will not say this is a panacea because it makes certain things much easier and other things more complicated.

The other solution is to potentially break the table up into multiple tables linked by the PK. You will never be able to bring it all together, but it is highly unlikely any need to do that. What is someone going to do with 350 columns.

 

[ebs17]

The individual databases have around 350 columns

What kind of databases are these that have 350 columns? Why don't you stick with it?

Could it be that the many columns result from a pivot table with the days of a year?
In a real database using SQL, you work in a row-oriented manner. In Power Query in Excel there is a very simple UNPIVOT to convert columns to rows.

 

[LarryE]

Maybe if you described the overall system you are attempting to build the application for, we could help you design it properly. For example:

1. I have multiple health care providers (hospitals, clinics et.)
2. Each provider has multiple associated departments
3. Each department has multiple associated doctors
4. Each doctor has multiple patients
5. Each patient has multiple visit dates
6. Each visit date may have multiple recommendations or medical actions taken

This is how you think about relational database designs. As The Doc Man said, they are hierarcies by nature. In the case I described, you would construct a separate table for each layer and connect those tables together with Primary and Foreign Keys. So you would have the following tables:

1. Providers (Hospital or clinic names, addresses etc.)
2. Departments
3. Doctors
4. Patients
5. Visit Dates
6. Medical actions or recommendations

I hope this helps you to understand how to think about these types of systems.

 

[mike60smart]

Hi,

I am a beginner with Access, so apologies if this question has a very obvious answer/has been answered before.

I am hoping to use Microsoft Access to store a large master database which will consist of 5 individual databases combined. The individual databases have around 350 columns and my hope was to import these individually and then create a query to combine them into one. I realised very quickly that Access has a 255 column limit when importing data, so then I decided to split each individual database and import into tables that way. However, I realise this doesn't solve my problem as after importing data into smaller tables that Access will handle, I cannot then combine the data into a master database with 350 columns - too many fields are defined in the query.

What I would like to know is, is there any way to run a query in Access which will allow me to create a master database of more than 255 columns?

To give a bit of context around the individual databases - they are databases of cases seen in a health care setting. Cases have completed measures which have a large number of items.

Thank you in advance for any advice.

I take it that your 5 Databases with 350 Columns are actually Excel Sheets?

I fthat is the case can you upload a sample Excel sheet with no confidential data?

 

[naa123]

Hi everyone, thank you very much for all your help and advice so far. It is clear that I need to normalize the database before considering importing the data into Access. I have been reading up on this and am currently trying to get my head around it for my specific database.

I currently have 5 service databases in Excel and they each have 2 tabs: a case database (patient, referral info, discharge info and pre-post scores if the case is discharged) and appointments database (appointment info and routine outcome measure scores collected at each appointment). I originally wanted to combine individual service case and appointment databases in Access to create a master database but realise normalisation is required first, so I have put a rough table plan together in Word (attached). I wonder if anyone would be able to give any advice on my current design plan? Any comments would be very welcome and please do let me know if I have missed any key information around what I am trying to do here.

In the document, you will see that there is a table for measures 1 and 2, we have around 10 measures so my plan was to create a new table for each of the measures.

 

[gemma-the-husky]

If you have medical appointments, you would have a patient. If it's not medical it's still similar. A consulting system is a consulting system, whatever industry it relates to, pretty much.

You then need to decide whether every patient complaint is treated separately, in which case you have a complaints table. If some complaints are treated together, you need a modified version of this to store multiple complaints for the one consultation, but you still need a separate table(s).

The complaints table(s) need to be linked to the patient.

Maybe you have a separate list (table) of acceptable complaints, with standard "industry" codes for example.

You then need an appointments table linked to the patient. The appointments table should include an indication of which complaint it relates to, and any other information. One table for all appointments, not one table per patient, or per complaint.

And so on.

This way you get a system with a larger number of tables each one having a relatively small number of columns.

Tables in general should be long and thin, not short and wide.

You will get practical design issues, such as marking a complaint closed. It's not really an issue affecting the system analysis, but if you mark a case closed on #31/10/2023#, the system should not then allow you to enter an appointment after that date. It probably should warn you if you are entering an appointment before the last recorded appointment, but that's stuff to provide a good interface and a robust system, and not something that affects the database design.

 

[Gasman]

In the document, you will see that there is a table for measures 1 and 2, we have around 10 measures so my plan was to create a new table for each of the measures.

I would have a table for measures and a field to indicate which numeric measure it is.

 

[MajP]

As @Gasman says you probably only want one table to store values, but you might need to do this like an entity value model. Because some measures may be numeric and others text and they have different units.

Have a table that holds all the types of things you measure. I assume not everyone gets all measures.
tblGenericMeasures
- measureID
- measureName
- measureDataType (text, numeric, date, boolean)
- Units (ppm, mm hg, ml, mg, etc.)

then in your patient measures.
tblPatientMeasures
- PatientMeasureID
- measureID_FK
- appointMentID_FK
- measureValue (text field)

In this way you can select a generic measure and input the value.
assume generic measures

1	Systolic Blood Pressure	Numeric	mm hg
2	Smoker	Boolean
3	Blood Type	Text

(now some of those like blood type and smoker are probably in a patient data table, but wanted different data types for example)

	MeasureID_FK	appointmentID_FK	measureValue
	1	1	120
	2	1	Yes
	3	1	O+

In a query you could see all the details with Measure Name, value, units.
The measuredatatype might not be important if all measure are numeric. Then make measurevalue a numeric field. If you plan to store non-numeric values it may be important to do validation of the inputs, sorting, querying.
Systolic Blood Pressure: 120 mm hg

 

[naa123]

As @Gasman says you probably only want one table to store values, but you might need to do this like an entity value model. Because some measures may be numeric and others text and they have different units.

Have a table that holds all the types of things you measure. I assume not everyone gets all measures.
tblGenericMeasures
- measureID
- measureName
- measureDataType (text, numeric, date, boolean)
- Units (ppm, mm hg, ml, mg, etc.)

then in your patient measures.
tblPatientMeasures
- PatientMeasureID
- measureID_FK
- appointMentID_FK
- measureValue (text field)

In this way you can select a generic measure and input the value.
assume generic measures

1	Systolic Blood Pressure	Numeric	mm hg
2	Smoker	Boolean
3	Blood Type	Text

(now some of those like blood type and smoker are probably in a patient data table, but wanted different data types for example)

	MeasureID_FK	appointmentID_FK	measureValue
	1	1	120
	2	1	Yes
	3	1	O+

In a query you could see all the details with Measure Name, value, units.
The measuredatatype might not be important if all measure are numeric. Then make measurevalue a numeric field. If you plan to store non-numeric values it may be important to do validation of the inputs, sorting, querying.
Systolic Blood Pressure: 120 mm hg

Thank you @Gasman and @MajP. The detailed explanation here is very helpful and makes a lot of sense.

In the case with my data, each measure has more than one value (with some having up to 50 values!). If I give an example of three measures used:

1. RCADS: This is a 47-item self-reported questionnaire with 6 anxiety and depression subscales. This measure would have 6 values in our database – a numeric score for each subscale.

2. Current View: This is a 50 item/value measure assessing presenting problems, and each item would have a text response of either None, Mild, Moderate, Severe or No, Yes, Not known (depending on the item).

3. ORS: This is a measure assessing self-reported functioning and 4 items/values which can have a numeric score out of 10.

For the last 2 measures, we also need to know who the measure was completed by (client, practitioner/employee or carer).

In this case, it seems difficult to have all values in one table without having an extra table to describe each individual measure. Would a good design be to have a measures table so:

tblGenericMeasures:
- measureID
- measureName
- measureDataType (text, numeric, date, boolean)

1	RCADS	Numeric
2	Current View	Text
3	ORS	Numeric

And then a table for each measure which captures the item information, for example:

tblCurrentView

• MeasureID_FK
• Item name
• Item number
• Item ID (Item name - Item number)

2	Separation anxiety	1	2-1
2	Social anxiety	2	2-2
2	Generalised anxiety	3	2-3
2	OCD	4	2-4

So then my values table would capture information from both and look something like this?

MeasureID_FK	ItemID_FK	appointmentID_FK	measureValue	CompletedBy
1	1-1	1	16
2	2-1	1	Mild	Carer
2	2-2	1	Moderate	Carer

Doing it this way would obviously make my values table very long, and I’m not sure if this would pose any issues in terms of file size/amount of time it takes to load/run queries? We will have more than 10,000 patient records within the database, each with 6-8 appointments each. We previously used Excel for our database but it has now become too large which is why I am looking to design this relational database and move the data into Acccess.

 

[mike60smart]

Hi
Sound like you need a Survey/Questionnaire type structure.

 

[MajP]

Doing it this way would obviously make my values table very long, and I’m not sure if this would pose any issues in terms of file size/amount of time it takes to load/run queries? We will have more than 10,000 patient records within the database, each with 6-8 appointments each

Databases are usually more efficient and take up less size if the tables are "tall and Narrow" vs "Short and wide". In other words less columns and more rows is usually a better more compact design.

We will have more than 10,000 patient records within the database, each with 6-8 appointments each. We previously used Excel for our database but it has now become too large which is why I am looking to design this relational database and move the data into Acccess.

You may want to consider putting the backend on Sql Server in that case. Even the free version will give you greater size limits than access.

 

[The_Doc_Man]

There has been a lot of discussion regarding very wide tables (due to too many fields). I want to also note that there is a limit on the physical width of tables regardless of the number of fields. There is a hard limit of 4000 bytes per record (not counting Long Text and OLE Objects). The more fields you have that are text oriented, the easier it will be to stumble into the byte limit. If you have 250 fields, you run into the limit if all of the fields are 16 bytes long. If you have 250-byte fields, you can only have 16 of them - and hardly anything else.

Therefore, when we say that Access likes tables and recordsets that are tall and narrow, it should be clear that we say that because of the record-width limits.

 

[gemma-the-husky]

Not only what @The_Doc_Man said, but in general databases work best with long thin tables. Access can handle tens of thousands of rows of data very quickly.

The process of normalisation ought to produce tables with lots of rows, and few columns.

A questionnaire with 50 questions doesn't need 50 columns. It needs about 3 columns basically, storing question_number, and question_response, together with a response_identifier of some sort. The question response though, might be text, yes no, a number or something else, so designing an application to manipulate and analyse the responses is the tricky part.

A 50 question database with 1000 responders, will have 50000 rows.

 

[GPGeorge]

You should download and study this classic sample survey database to get a better idea of how it should be approached.

 

[LarryE]

1. Is each of the 5 EXCEL spreadsheets a service provider (hospital, clinic etc.)
2. Does each service provider have their own RCAD, Current View and ORS questionnaire?
3. Does each patient fill out these questionnaires at each appointment?

 

[naa123]

Hi, thank you again for all of your very helpful responses. Unfortunately I am unable to share the spreadsheets online, apologies. However to answer your questions @LarryE:

1. yes the 5 spreadsheets are each from an individual service provider (different clinics), so 1 spreadsheet per provider.
2. The RCADS, Current View and ORS questionnaire are the same for each service provider. The columns in each of the spreadsheets are exactly the same.
3. With the Current View, this is usually only filled out once during the first appointment, unless circumstances significantly change. The other 2 measures should be completed at each appointment, however this is not always the case.