Database Recommendations - Too Many Fields

K

Karl Burrows

I am still a novice with Access and have inherited a database that tracks
home sales for a developer. The database originally had about 50 fields and
were all included in the same table. There are only 2 input forms: one for
all the lot specific data (lot number, section, phase, date lot closed, plat
date, street address, etc.). The other is for architectural review that
overlaps some fields (lot number, street address, home owner and a few more)
and includes info relevant to the homeowner (color, brick/vinyl, #baths,
garage, etc.). In the past year, they have expanded the data collection and
now the fields have grown to over 150. All are in the same table with a
total of about 5000 records covering a period of about 10 years. I haven't
tried to break it out into smaller tables yet since there is a ton of coding
for multiple versions of reports (similar reports by neighborhood, builder,
consolidated, etc.).

My questions are as follows:
1. Are there any longer term issues with having such a large table?
2. If yes, is there a logical way to break them out other than splitting
lot and homeowner data and creating secondary keys to common fields?
3. A lot of this data gets imported into an Excel workbook using an Excel
query. As projects are sold out, they don't need to be exported. I tried
to edit the query in the Excel workbook, but the neighborhood names are so
long and there are so many old ones, there aren't enough characters in the
query field to put all the excludes in. Is there a way to do that within
Access to tell it not to export certain records. Here is the SQL query from
Excel:

<>'BLH ~ Blakeney Heath' And <>'BRK ~ Brookmere' And <>'LDP ~ Lake Davidson
Park' And <>'MDW ~ Meadowmont at Highland Creek' And <>'WGR ~ Withers Grove'
And <>'WGV ~ Withers Grove V' And <>'GLB ~ Glyndebourne'

Ran out of room after that!
4. Instead of importing to Excel, is there a way to create a report and
have it generated in Excel without having to preview the report and analyze
it with Excel?

Any other suggestions would also be greatly appreciated. Thanks!!!
 
G

Guest

Comments inline.

Karl Burrows said:
I am still a novice with Access and have inherited a database that tracks
home sales for a developer. The database originally had about 50 fields and
were all included in the same table.

Ouch. That's twice as many as most large tables I'd prefer to use.
There are only 2 input forms: one for
all the lot specific data (lot number, section, phase, date lot closed, plat
date, street address, etc.). The other is for architectural review that
overlaps some fields (lot number, street address, home owner and a few more)
and includes info relevant to the homeowner (color, brick/vinyl, #baths,
garage, etc.). In the past year, they have expanded the data collection and
now the fields have grown to over 150. All are in the same table with a
total of about 5000 records covering a period of about 10 years. I haven't
tried to break it out into smaller tables yet since there is a ton of coding
for multiple versions of reports (similar reports by neighborhood, builder,
consolidated, etc.).

Well.... a lot of the coding can be salvaged; if you normalize the tables -
as you certainly should!! - you can still create a Query to reconstruct this
"wide-flat" view. You can even give this query the same name as your current
table, and most of the Reports should still work fine. Forms may be another
issue, they may not be updateable.
My questions are as follows:
1. Are there any longer term issues with having such a large table?

There certainly are! For one thing, there's a hard limit of 2000 bytes
*actually occupied* in any one record in a table. You can design a table with
150 20-byte fields - but if you actually fill them all, BANG, an error
message and no new record will be written. In addition, you clearly have a
great deal of redundancy built in; I strongly suspect you have some repeating
fields, and probably some one-to-many relationships embedded in each record.
2. If yes, is there a logical way to break them out other than splitting
lot and homeowner data and creating secondary keys to common fields?

Not knowing the details of the data I cannot say for sure what the breakdown
might be, but I'd guess it would be more extensive than this. You should
identify the "Entities" - real-life persons, things, and events of importance
to your application; each type of Entity should have its own table. Frex. a
Lot is an entity; an Owner is a different kind of entity; you should have
small "lookup" tables for Colors, Materials, etc.
3. A lot of this data gets imported into an Excel workbook using an Excel
query. As projects are sold out, they don't need to be exported. I tried
to edit the query in the Excel workbook, but the neighborhood names are so
long and there are so many old ones, there aren't enough characters in the
query field to put all the excludes in. Is there a way to do that within
Access to tell it not to export certain records. Here is the SQL query from
Excel:

Of course. I'd strongly suggest that if you have a table of Sales, then you
can very simply identify those records which need to be exported (the unsold
ones I'm guessing) and which don't.
<>'BLH ~ Blakeney Heath' And <>'BRK ~ Brookmere' And <>'LDP ~ Lake Davidson
Park' And <>'MDW ~ Meadowmont at Highland Creek' And <>'WGR ~ Withers Grove'
And <>'WGV ~ Withers Grove V' And <>'GLB ~ Glyndebourne'

The criterion for this on an Access Query might be

NOT IN('BLH ~ Blakeney Heath' ,'BRK ~ Brookmere' ,'LDP ~ Lake Davidson
Park','MDW ~ Meadowmont at Highland Creek' ,'WGR ~ Withers Grove'
,'WGV ~ Withers Grove V' ,'GLB ~ Glyndebourne')

or - better - you would have a table of subdivisions with two fields, Code
and SubName ("BLH" and "Blakeney Heath" respectively), and your main table
would contain only the code.
Ran out of room after that!
4. Instead of importing to Excel, is there a way to create a report and
have it generated in Excel without having to preview the report and analyze
it with Excel?

Yes. Access has a very powerful Report generator; and you can export data
directly to Excel spreadsheets.
Any other suggestions would also be greatly appreciated. Thanks!!!

Read up about "Normalization". Excel is a spreadsheet, a good one; Access is
a relational database. They are VERY DIFFERENT though they might look similar
at first glance. You're "committing spreadsheet upon a database", a venial
sin punishable by being required to get a copy of Codd and Date or of Rebecca
Riordan's _Designing Relational Database Systems_ and studying for a while!
<g>

John W. Vinson [MVP]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top