table structure advice

I do not use the SQL server and know little about it--I use Access to pull from it.

I have just started a new position and have inherited a survey database that from my perspective is mess. It's a nightmare to query and is very difficult to find anything in. Part of that is simply the structure of the Access program that was built by the person before me. But part of it is because the SQL server table is just one long table (almost maxed out--around 320 fields!) and according to one of the IT people who works with it, "is not a proper table structure." The internet version of the survey is obviously built to dump it's info. directly into the SQL server.

Whatever I choose to do will require a lot of work for both me and IT--but my question to you guys is--is there value to keeping data in a single table and simply working in Access with multiple queries? There would be many advantages to retructuring it into multiple table, but I also have no idea how much work it will be for IT to redo the survey in SQL.

It is possible for it to function the way it is--they've done it for several years--but my first instinct is to make it more effecient and flexible with a better table structure.

I know this doesn't give a lot of info--I'm just looking for "off the top of your head" thoughts. Thanks!

It really depends on what you need to do with the data.

Hmm..lots of stuff. We compare the data to previous years (the biggest problem with changing anything, though I think I can work around that. . .), we create reports based on pieces of the data, and frequently I'm asked to analyze differently than the original intent--for instance, we ask a question about which DB product organizations use, although it's set up to easily say which products are used by and organization, and how many organizations use each product, it's unwieldy to determine which organizations use each product.

In the table it's a spreadsheet with 1 as yes and 0 as no: (although in Access the standard is -1 as yes and 0 as no, so I don't get that either. . . . I was told it was so the user could "add up" the number of responses--I don't know why they just didn't "count" instead of "sum". . . but oh well.)

Organization SQL Access Oracle Filepro etc.
(Name) 0 0 1 0 etc.

I would do it like this:

Organi DB Product
(Name) SQL
(NAME) Access

etc.

My way requires two more tables--an organization table and a DB product table--and with multiple questions like this, I'm talking about a LOT more tables for the IT people. But a lot fewer Union queries for me!

Of course, I do need to be able to know how many responses there were to a particular question--but I believe I could do that easily my way with a SELECT DISTINCT for the organization and the date of the answer.

I may just be convincing myself that it has to be changed. . . but if anything I've said makes you think the one table is better, please let me know! Thanks!

I would go with making it a more relational database and separate the columns into their related tables.