Access 2003

[JAS]

I had to split a large table into smaller tables due to table field
constraints. I want to ensure that when the primary key (auto number) in the
main table is updated all the other primary keys in the other tables are
updated with the same number. How do I go about this.
Many thanks.

 

[Jeanette Cunningham]

Hi Jas,
this sounds like a very messy thing to do with your table.
If you post back with some more about one of the field constraints and how
you plan to put this field into a new table, we could suggest how to do it.

Jeanette Cunningham

 

[JAS]

Hi Jeanette,
Thanks for the response.
Basically the original table had about 230 fields in it and Access kept
crashing with error code regarding limits.
The database is for storing reports on ship inspections, and splitting the
table actually gives me more scope to add additional fields in the future.
As each report is written, I need to ensure that all the tables tie into the
main one so that when searching for a report to be printed out, I do not get
info from one report intermixed with another.
As you can see if I just had the one table it would have been really simple.
Hope you can help.

JAS

 

[Jeanette Cunningham]

Jas,
I can't tell from your post all the tables you will need.

tblShips ...
ShipID >Primary Key
ShipName
Owner
Other ship info in this table

tblInspections ....
InspectionID < Primary Key
ShipID < Foreign Key from tblShips
InspectorID < Foreign Key from tblInspectors
InspectionDate
Other inspection details

You probably need a table for Ship inspectors

tblInspectors ...
InspectorID > Primary Key
LName
FName


One ship can be inspected many times - tblShips is related one to many with
tblInspections
One Inspector can do many inspections - tblInspectors is related one to many
with tblInspections

If you need some help with deciding on what tables you need post back.

Before you split the data up, make a couple of backups of your main table
and be sure to always have at least one complete backup of this table before
any splitting is started, ie have one backup table that you keep just for
reference.
To split some data off for another table, use a query to select the data you
want. Open the query and check it has what you want before you add it to
another table.
It is easy to accidentally delete or lose some data for good by accidentally
changing it. Try to keep an ongoing backup as you make splits.

Jeanette Cunningham

 

[Peter Hibbs]

Jas

As Jeanette has pointed out, you need to separate your one large table
into several smaller tables. When you have worked out what tables you
need then the Excel to Access Converter program on Roger Carlson's web
site may be of interest. Go to :-

http://www.rogersaccesslibrary.com/...me='Excel to Access Converter Utility program'

HTH

Peter Hibbs.