Help setting up table structure

[Alex]

I hope someone can help me. I've been working on this database for week and
the more my customer requests the form does, the more I realize I may have
set this up incorrectly. I am at a complete loss now and feeling incapable.
If someone can hang in there with me a bit and help I will be so
appreciative. Thanks. It's a long explaination, but I wanted to include
everything. Here's my situation:

Tables:
tbl called NewParts where users enter new parts. Key fields are Model,
Part, NHL.
tbl called RefPartsA that is imported from mainframe and never needs
updating. Key fields are Model, Part, NHL.
tbl called RefPartsB that is imported from mainframe and never needs
updating. Key fields are Model, Part, NHL.
tbl called PO that is imported from mainframe and never needs updating. Key
field Part.
tbl called Models where user enter information about each Model. Key field
model.

Process:

In my user form, I need users to enter information about NewParts that
should update the NewParts tbl. In the same form, they will pick, from a
combo box, a Ref Part to be stored in NewParts tbl, which is a list of Parts
from the RefPartA tbl where tbl NewParts Model field = tbl RefPartA Model
field. That same picked Ref Part may also have a matching Part in the tbl
PO. At that moment, I need several fields that are in the RefPartA tbl and
PO tbl to update in my user form that are not stored in the NewParts tbl.

In my same user form, I need users to enter a Part to be stored in NewParts
tbl and automatically display matching fields from RefPartsB tbl where tbl
NewParts Part field = tbl RefPartsB Part field. At that moment, I need
several fields that are in the RefPartB tbl to update in my user form that
are not stored in NewParts tbl.

Since tbls RefPartsA and RefPartsB and PO are often imported from mainframe
reports will need to be created using the most recent data from the imported
tables, I created a query with all tbls mentioned above and the following
joins.

Current Joins:
tbl NewParts tbl RefPartA
model model
refpart part
refpartnhl aspart
*include all records from NewParts and only matching records from RefPartA

tblNewParts tblRefPartB
model model
part part
nhl nhl
*include all records from NewParts and only matching records from RefPartB


tblNewParts tblPO
part part
*include all records from NewParts and only matching records from PO


tblNewParts tblModel
model model
*include all records from NewParts and only matching records from Model

In trying to get the RefPartA and RefPartB information to populate fields on
the form afterupdating, I did a requery, which was taking forever. So then I
setvalues on Part and RefPart AfterUpdate events for those values coming from
tbl RefPartA RefPartB and PO. I thought that was working fine, but I'm
getting error messages like "cannot enter value into blank field on 'one'
side of outer join.

I read in help groups to use a main form and subforms, but I don't think I
can set up my relationships to work correctly. Help system says to have main
form have a a one-to-many with first subform and the first subform to have a
one-to-many with second subform. I don't think I can do that in my
situation, can I?

Thanks again for your help.

 

[Jeff Boyce]

Alex

Just off the top, you have three tables with identical structure. And you
are using the three tables to differentiate among the various sources of the
data that goes into these tables.

Embedding data in tablenames is not a good idea, and will only cause you
(and Access) headaches.

An alternate approach would be to have ONE table with Model, PartNumber,
NHL, PLUS one more field that designates the source of the data (eg, "new",
....)

From my perspective, trying to force Access to adapt to a table structure
rather than use relational database design to create a well-normalized table
structure will cost you a lot more time and effort.

Take a look at revising your structure first...

If the topics of "normalization" and "relational database design" don't ring
a bell, take a look at the resouces Jeff Conrad offers at:

http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Alex]

Thanks Jeff. I will certainly take a look at Jeff Conrad's information. I
have a couple questions. My 3 tables with identical structure, aside from
the 3 key fields, have entirely different information in the fields so I
don't see how I could combine them into one table with a column for
datasource. What am I missing? Also, what do you mean by 'Embedding data in
tablenames'? Thanks for your help.

 

[Alex]

Do mean to add the datasource column as a 4th primary key field?

 

[Jeff Boyce]

Alex

I may have made an incorrect assumption. Because the names of the fields of
the three tables were identical, I assumed that they held exactly the same
KIND OF information. I understand that the data from each source is
different, but don't all three sources provide the same pieces of
information?

If so, my suggestion was a single table, with those three pieces, plus one
more field, to hold "source". You would be able to create a simple query to
find all rows that came from Source1 (or Source2, or Source3).

Regards

Jeff Boyce
Microsoft Office/Access MVP