Switching from FileMaker Pro 9 to Access '07

[bobby769]

Been at my job for 8yrs using FileMaker for Purchase Orders. This was my
first experience with databases and over the years I've realized the person
before me who created the original FP Purchase Order database did not create
a very clean design. The Database has only one table. I'm starting to switch
it to Access and learning as I go.

The single table from FM contains 87 Fields.
I'd like to seperate this into a # of different tables.
One table will be Vendor Address info another table
will be Sales rep info.

How do I decide if I should just make this one table?

It is possible to have one vendor with multiple sales reps. So I was
thinking it would be better to have a Vendor Address table that would include
a unique Vendor #. Then the Sales Rep table would have sales rep names and
contact#s with the same Unique Vendor# as the linking item.

Am I looking at this situation correctly?

It seems like my other option is to have a vendor table that includes Vendor
Address as well as Sales rep info but then wouldn't I have a cumbersome
tables?
That is because I could have an Entry for Microsoft as a vendor with a
unique vendor# but I could have 5 different reps at Microsoft so in sense the
vendor # would no longer be the Unique Identifier but rather the Sales Rep
name would be the unique identifier.

 

[John W. Vinson]

Been at my job for 8yrs using FileMaker for Purchase Orders. This was my
first experience with databases and over the years I've realized the person
before me who created the original FP Purchase Order database did not create
a very clean design. The Database has only one table. I'm starting to switch
it to Access and learning as I go.

Well, that's pretty typical of FM databases in my experience.

The single table from FM contains 87 Fields.
I'd like to seperate this into a # of different tables.
One table will be Vendor Address info another table
will be Sales rep info.

How do I decide if I should just make this one table?

You shouldn't.

It is possible to have one vendor with multiple sales reps. So I was
thinking it would be better to have a Vendor Address table that would include
a unique Vendor #. Then the Sales Rep table would have sales rep names and
contact#s with the same Unique Vendor# as the linking item.

Am I looking at this situation correctly?

Sounds good to me.

That is because I could have an Entry for Microsoft as a vendor with a
unique vendor# but I could have 5 different reps at Microsoft so in sense the
vendor # would no longer be the Unique Identifier but rather the Sales Rep
name would be the unique identifier.

You would have a VendorID (don't use # in fieldnames!) as the primary key of
the Vendor table, and SalesRepID (NOT name, names change and are not unique)
as the primary key of the SalesRep table; the SalesRep table would contain a
VendorID as a foreign key link to the vendor table. You could use Append
queries to migrate the data from the FM wide-flat table into the normalized
tables.

 

[bobby769]

I may be misunderstanding something.
I thought I would have a vendor table with the vendor address and one of the
fields would be the unique identifier, a vendor#.

I would also have another table with the sales rep name, contact#, and
another field that would have the vendor# that that sales rep should to
associated with.

Are saying that I can not have a # that connects two fields from different
tables?

 

[Douglas J. Steele]

I believe all John is saying is do not include the character # in your field
names. Field names should only contain letters and numbers, no special
characters (nor spaces).

 

[bobby769]

ok. I see. That makes sense.

I believe all John is saying is do not include the character # in your field
names. Field names should only contain letters and numbers, no special
characters (nor spaces).