HELP - I goofed when creating a database

[Dan Rolfe]

Ok I just kinda stumbled upon an error I made when I was building this
database.

It is a multitable relational DB consisting of 2 levels of grouping.
Here is a rough design view:


Owner
---Date Ordered
-------Parts Ordered & Flags

There is a main form to access the DB and insert information, first
you select the owner or enter a new one, enter the date ordered, then
enter part numbers and check off some options.

The database has primary and foreign keys but I made a big mistake:
there is nothing linking the Parts ordered to the date ordered (i.e no
order_key in the parts_ordered table) see example below

Owner_TBL:
---------------------------
ownerID - PK
ownerNAME

Date_TBL
--------------------
dateID - PK
ownerID - FK
dateData

Parts_TBL
---------------
partsID - PK
ownerID - FK

******** THIS is the problem. Now I have already created the field i
need to in the parts table, but what I am not understanding is how: 1)
I did not notice this before 2) how this program functioned without
it.

When accessing the data through the form, it shows only the accounts
for each specific date record. Where there is nothing wrong with that
in theory, I am promoting this application to SQL server and will be
developing my own front end for it. But if i take the data out of
this program there is no way for me to determine which parts goto
which order. I know which owner but i wont know to which order they
go to if somebody has more than one order.

Can anybody offer any insight on this issue? I know that I can just
manually go in and do all of the changes I need to but there are
almost 10000 records in this database (hence the neccessity to upgrade
to sql)

[tina]

Re: HELP - I goofed when creating a database

well, i don't see any way around it. the data you need - which parts (order
details) go to which date record (order) - is not stored in your database;
you'll have to enter it one way or another. note that once you update the
parts records with the appropriate foreign key values from the date table,
you won't need the owner foreign key in the parts table - it will be
*indirectly* linked via the date table.

suggest you read up on relational design principles; for more information,
see http://home.att.net/~california.db/tips.html#aTip1. also, suggest you do
some reading on the merits of SQL Server vs Access. for Access, 10,000
records in small potatoes - so if you don't have a better reason for moving
to SQL Server, probably you should reconsider.

hth


"Dan Rolfe" <drolfe@nelsonw atson.comwrote in message
news:1174526410 .290146.20590@l 77g2000hsb.goog legroups.com...

Ok I just kinda stumbled upon an error I made when I was building this
database.
>
It is a multitable relational DB consisting of 2 levels of grouping.
Here is a rough design view:
>
>
Owner
---Date Ordered
-------Parts Ordered & Flags
>
There is a main form to access the DB and insert information, first
you select the owner or enter a new one, enter the date ordered, then
enter part numbers and check off some options.
>
The database has primary and foreign keys but I made a big mistake:
there is nothing linking the Parts ordered to the date ordered (i.e no
order_key in the parts_ordered table) see example below
>
Owner_TBL:
---------------------------
ownerID - PK
ownerNAME
>
Date_TBL
--------------------
dateID - PK
ownerID - FK
dateData
>
Parts_TBL
---------------
partsID - PK
ownerID - FK
>
******** THIS is the problem. Now I have already created the field i
need to in the parts table, but what I am not understanding is how: 1)
I did not notice this before 2) how this program functioned without
it.
>
When accessing the data through the form, it shows only the accounts
for each specific date record. Where there is nothing wrong with that
in theory, I am promoting this application to SQL server and will be
developing my own front end for it. But if i take the data out of
this program there is no way for me to determine which parts goto
which order. I know which owner but i wont know to which order they
go to if somebody has more than one order.
>
Can anybody offer any insight on this issue? I know that I can just
manually go in and do all of the changes I need to but there are
almost 10000 records in this database (hence the neccessity to upgrade
to sql)
>