Splitting big table to smaller ones (1 Viewer)

[UserError]

Hello,
I have inherited a database with in excess of 78 fields and I gather I need break this down as it may be causing the "record is too large" error. There are sections which lend themselves to this: there are field prefixed "susc", "val" and "vis" but I can't see how to select these in a query.
I could add all the fields and choose only the ones I want but that seems a but primitive. There must be a more efficient way?
Many thanks

 

[Uncle Gizmo]

The usual issue is with a table(s) imported from Excel, often having Fields which should be Rows. For example it is common to construct a spreadsheet with columns for the months, January through December. You may have columns which represent check marks/checkboxes offering the choice of yes/no answers. In MS Access these can placed as horizontal entries in a row instead of a column. I've done a comprehensive blog "Excel in Access" on the issue here:-

Excel in Access​

Excel in Access - Nifty Access

Excel in Access - Problem & Solution! One of the most frequent problems I see is people confusing MS Access with Microsoft Excel. This leads to fundamental design errors. Now, the problem is, this error "A Major Error" does not become apparent straight away. Unfortunately your database will work...

www.niftyaccess.com

I also provide a tool for extracting the offending columns into the correct row structure.

There are videos and text, and I take you through an example based on students and the courses they are taking. I also link to other blogs throughout Access World Forums (AWF) which provide you with more information. I am also available to help you if you get stuck.

The tool is available for free, all you need do is contact me in a private message and I will explain how you can get a free copy.

 

[Uncle Gizmo]

In this 2008 video I made on the subject, you can get a idea of the issue and the solution:-

How to Move Data from Excel to Access​

 

[UserError]

Hi UncleGizmo, Thanks, this is a large landscape assessment database with many justifiable/valid fields (assessment of many characteristics of the landscape). It may once have originated in Excel and possibly outgrew it, but I know as a database it has been used like this on previous occasions. There are a number of H/M/L fields that (I put) in combo boxes/tables but in this version there are no Y/N (although the version I was sent has loads of these, so I would be interested in reconfiguring those so will PM you about your offer, thank you.
In the meantime, I copied the SQL into Notepad++ & edited the text with find/replace then pasted it back in as a workaround