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!!!
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!!!