By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,952 Members | 1,438 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,952 IT Pros & Developers. It's quick & easy.

HELP - I goofed when creating a database

P: n/a
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)

Mar 22 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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" <dr****@nelsonwatson.comwrote in message
news:11*********************@l77g2000hsb.googlegro ups.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)

Mar 22 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.