3

I have inherited a Access database that has a query that SELECTs over 50 columns. Even in the MS Access graphical query design tool, it's too much information to handle IMO.

A quick disclaimer - I have a programming background, but almost no experience with databases.

For this particular problem, I need data from 50+ columns. Is there a better way to get this information than one huge query?

I am a bit at a loss on how to proceed. The consensus on the web seems to be that SQL queries should be kept relatively small and well formated. I just don't see how to apply that principle when you need so many fields. Can I use lots of simple queries with a UNION or INSERT INTO a temporary table? Any suggestion or RTFMs?

EDIT: More info on the application. The data is spread across 14 tables. I'm grabbing the data to write it out to an external file which has 50+ fields per row (think CSV version of a spreadsheet).

EDIT: Managing and debugging SQL queries in MS Access looks like it contains relevant advice.

Community
  • 1
  • 1
chip
  • 2,262
  • 2
  • 20
  • 24
  • Does the query have a join? or are those 50+ columns from the same table? – Danish Mar 22 '11 at 22:38
  • 2
    Honestly, if you just plain need 50+ columns, I can't think of a "solution" to this that isn't worse than the problem it solves. – Dan J Mar 22 '11 at 22:39
  • 1
    Just curious, what is your programming background? I ask only because I find it curious for someone to state they've got almost no experience with databases but have programming experience. Are you in school? – Mike Atlas Mar 22 '11 at 22:42
  • @MikeAtlas Mostly shell scripting, a lot of Python, and a bit of C - about 5 years working on a C++ product with mostly the python API . Not a programmer by training and not by occupation either. Most of my experience is with creating utilities to patch together SW tools. But yeah, I don't know how I've avoided databases so far either :) – chip Mar 22 '11 at 23:00
  • 1
    @Mike - I did scientific programming for a decade without ever needing to even say "data base". That was a long time ago, but even today there are large swaths of the field of "programming" where data bases have at most a limited use. (Industrial controls, operating systems, real-time signal processing, numerical analysis, etc.) – Ted Hopp Mar 22 '11 at 23:01
  • @Danish It has about a dozen JOIN and pulls data from about 14 tables. – chip Mar 22 '11 at 23:05
  • @chip, @ted thanks for the background. – Mike Atlas Mar 23 '11 at 05:06

5 Answers5

1

I would echo most of the comments about needing 50+ columns every time you run your query.

However, if it's the ugliness of the query that gets you the 50+ columns that is causing your grief, you could create a view for that query. It doesn't get rid of the fact that you're dealing with a bunch of data, but it could encapsulate a large, hairy beast of an SQL statement.

MikeTheReader
  • 4,200
  • 3
  • 24
  • 38
  • The ugliness is definitely causing grief, I just have a hard time believing there isn't a cleaner way to do it. By "view" are you referring to the graphical query design tool in Access? – chip Mar 22 '11 at 22:52
  • A view is essentially a "table" that consists of the results of a given SELECT statement. I'm not terribly familiar with creating them in Access, but here's a page that describes the syntax: http://office.microsoft.com/en-us/access-help/create-view-statement-HA001231443.aspx – MikeTheReader Mar 22 '11 at 22:57
  • Here is the Wikipedia article relating to views in general: http://en.wikipedia.org/wiki/View_(database) – MikeTheReader Mar 22 '11 at 22:57
  • @Dante617 Thanks, I'm still learning the vocabulary. I forgot that was a keyword. – chip Mar 22 '11 at 23:08
  • Okay, so I build a new view using a series of smaller more readable queries. That sounds like a small step I can take in the right direction. – chip Mar 22 '11 at 23:21
  • This is a good solution. It would also allow you to better alias repeated column names (like ID, Name, etc.) – mikesigs Mar 22 '11 at 23:41
  • 1
    Take care that by building the view as several queries you don't compromise performance. If the number of fields is causing issues I'd challenge whether they're all needed, but if they're needed, they're needed. – MatBailie Mar 22 '11 at 23:58
  • 1
    The Access equivalent of a View is a saved Query (one that shows up as a Database Object, in the Database Window/Navigation Pane). – RolandTumble Mar 23 '11 at 17:46
1

I think you're worrying yourself over nothing at all. Pulling 50 fields from 14 tables in order to output to a flat file is nothing at all. The point is that you're intentionally denormalizing data because you need a flat-file output. By definition, that will mean lots of columns.

As others have said, 50 columns in one table would be out of the ordinary (though not necessarily a design error), but in the situation you describe, I don't see an issue at all.

In short, it sounds like a perfectly fine setup and I just have to wonder what makes you think it's a problem to begin with.

David-W-Fenton
  • 22,871
  • 4
  • 45
  • 58
  • This may very well be the case. The examples queries I have seen on the web and printed docs are much shorter and concise. The query, as designed graphically is a bit overwhelming. Honestly, I'm still not sure what I should expect and what I should be aiming for in code that I write. – chip Mar 24 '11 at 04:07
  • I am beginning to realize that a lot of the obfuscation in the query is based on the mixing of code in the ms-access fields and my lack of familiarity with the access dev environment. There are lots of iif() statements, calls to functions in modules, and string cats in the query. Just reading the code in the tiny one-line field was tedious. If it was just grabbing 50 fields I think it would have been more straightforward. – chip Mar 24 '11 at 04:14
  • My gut instinct is to just have the query select the correct columns and do any math/processing in VBA. – chip Mar 24 '11 at 04:21
  • Certainly anything that is presentation-level should be done outside the SQL, seems to me. On the other hand, it depends on how you're writing it out to a file. If you're using typical Access methods, like DoCmd.TransferDatabase, then you need to do it in the SQL itself. But you can make this easier on yourself by using a saved query with the basic data, and then use that as the data source for a further query that does the massaging for the actual output. – David-W-Fenton Mar 27 '11 at 19:34
  • BTW, the reason examples in the documentation are not going to be complicated is because they are generally designed to illustrate a particular point, so they are going to include only the information necessary to make clear what is being demonstrated. I have an ugly query in one of my oldest apps (dates back to 1997, and yes, it's for export) the SQL of which is now 7,253 characters long. It used to be MUCH longer, over 12K. A couple of years ago I got tired of being scared of editing it and replaced a lot of the conditional logic in the SQL with calls to my own UDFs that did the same thing. – David-W-Fenton Mar 27 '11 at 19:37
0

If you're retrieving data from all columns, then you can write SELECT * FROM ....

Ted Hopp
  • 232,168
  • 48
  • 399
  • 521
  • 1
    Of course, if there are hundreds of columns, this may actually be worse than selecting all 50 of the needed columns (performance wise). Furthermore, hundreds of columns (or 50 columns) to me indicates the schema probably has no relational tables... – Mike Atlas Mar 22 '11 at 22:38
  • The data is from lots of tables. I am still deciphering the FROM section which contains about a dozen JOINs from about 14 different tables. – chip Mar 22 '11 at 22:43
  • Don't use SELECT * unless you absolutely need every single column, otherwise specify! – mikesigs Mar 22 '11 at 23:39
  • 1
    @whatispunk - That's why I wrote, "If you're retrieving data from all columns." Why does this rate a downvote? – Ted Hopp Mar 23 '11 at 00:42
  • @Ted - The OP has said the query is spread across 14 tables, so doing a SELECT * would be disastrous to performance. I think that SELECT * has very limited use and should not be encouraged. Also, your post was appearing at the top of the list and I'd hate to see future answer-seekers wind up here and think that was the default/optimal solution. Absolutely nothing personal. – mikesigs Mar 23 '11 at 15:04
  • @whatispunk - OP also said that over 50 columns were being retrieved. It's not unreasonable at all that this might be _all_ the columns. I was just pointing out that if this was the case, using `SELECT *` would work just fine. I never thought the downvote was personal, just uncalled for. – Ted Hopp Mar 25 '11 at 04:23
  • Using `SELECT *` would return duplicate data, since the fields on which the tables are joined don't need to be repeated for both sides of the JOIN. – David-W-Fenton Mar 27 '11 at 19:38
  • @David - That depends on the data base design and how the joins are done, doesn't it? MS Access (and SQL in general) returns only one copy of the joined columns in any equi-join (see [here](http://support.microsoft.com/kb/136699)). – Ted Hopp Mar 27 '11 at 21:05
0

If there are 50 columns in a single table, the database design is questionable at best. That might practically mean that you'll just have to cope with not-so-elegant solutions.

Do you really need all that data at once? If not, it's probably better to only SELECT the data you need at a given time. You should provide more information on the actual task.

StackExchange saddens dancek
  • 3,318
  • 1
  • 27
  • 37
  • The data is spread across 14 tables. I'm grabbing the data to write it out to an external file. So each line in the external file has the 50+ fields. – chip Mar 22 '11 at 22:47
0

If your data is coming from multiple tables, you could use Common Table Expressions to divide the query into groupings.

Mike Atlas
  • 8,193
  • 4
  • 46
  • 62
BStruthers
  • 958
  • 6
  • 8
  • We are using Access JET on the backend and not SQL Server. I'm not sure if CTEs are supported by JET. But I think I get the concept. – chip Mar 22 '11 at 23:31