Table design.

[Guest]

I am converting some mainframe data to an access database.
The input contains a field that can either be 26 bytes of prose OR those
same 26bytes broken up into a series of packed numeric fields and certain
kinds of text in specific locations of the string. A flag indicates which
style (narrative or specific) is used. (of course I am having to convert the
packed numerics to standard numbers first)

What is the best way to represent this data in an access table(s)?

If I use the same table and have this kind of set up:
Account, double
Prose, String(26)
--- in the cobol world, the following fields were a redefines of the
previous 26 bytes.
Distance, integer
direction, string(2)
linedef, string(1)
plcl, string(4)
refstreet,string(15)

Either, Prose will have data or the other fields will have data, never both.
If the datafields that do not have data are set to null will this take up
unnecessary space?

There are several constructs like this that I will have to convert.

Also, is there a way to import my data such that the fields are
automatically set to null? or will I have to run a conversion to changes
spaces to null after the table is imported (then compact the database).

 

[Jeff Boyce]

John

To get the best use of the features and functions Access offers, you'll need
to rethink your data design. Access is a relational database. Although you
can import "flat file" data from mainframes (or Excel spreadsheets, or text
files), there's a good chance the data is not well-normalized.

Access offers a variety of data types, and you can import "raw" data into a
(temporary) table, then use queries to parse the data into your permanent,
well-normalized table structure.

Regards

Jeff Boyce
Microsoft Office/Access MVP