Related table

[New_Access]

Hi All,
I have three tables.
1.Model_tbl
*) Product Name = Text ( PK )

2.Spec_tbl
*) Product Name = Text ( PK )
*) Power limit = Number
*) Ampere limit = Number
*)Airflow limit = Number
*)Date registered = Date/Time
*)Registrant = Text

3.Inspection Result_tbl
*)Product Name = Text
*)Production date code = Text
*)Inspection date = Date/Time
*)Inspector Name
*)Power value = Number
*)Ampere value = Number
*)Airflow value = Number

I want to create form.
..1.Inspection.

But how to design a good relationship from these 3 tables.?
I 'm getting dizzy from here.
I want to retrieve a record from Spect _tbl if a user want to input
an inspection result.I had designed a form and insert 'Inspection
Result_tbl_form' as a subform but I can't see any fields in my subfrom.
Why?.Please teach me agood basic.

Thank's.

 

[Rod Plastow]

Hi there,

My first observation is that you have two tables with the same primary key.
This suggests that the information on each table pertains to the same entity,
so why two tables rather than one? There may be valid design reasons for
this but I suspect you have one too many tables here.

The Inspection table (I would call it that rather than Inspection_Result
simply because my sugggestion is shorter and both names mean the same thing -
result is simply an attribute of inspection) lacks a primary key. Some
contributors to this site would argue that a primary key is not necessary,
and they are correct. However when starting out the definition of a primary
key helps you understand your data. Look for a 'real life' primary (unique)
key for inspections. It may be that one does not exist - on the other hand
how do inspectors file their results? They must have a key for retrieval
purposes.

Your question is how to define a relationship between products and
inspections. I assume an inspection deals with one and only one product;
this is a simple one-to-many relationship. If it doesn't you have a
many-to-many relationship and there are database design techniques to
overcome this.

You already have Product Name as an attribute on your Inspection table.
This is a foreign key as it 'points back' to the primary key of the Product
table. (Another comment here: I feel a text attribute such as product name
is a poor choice for a primary key but this is another discussion.)

Now you have a choice. You can define this relationship to the Jet database
management system and that system will ensure your data integrity as far as
the relationship is concerned. To do this graphically display the
Relationships window (the method is slightly different according to the
version of Access you are using). Select the Product and Inspection tables
to be displayed in the window if they are not there already. Now drag and
drop Product Name from one table onto Product Name in the other table.
Access will display a dialog box; explore the options and choose what you
want. Finally click on Create and the relationship is defined. If you ever
want to change it right-click on the relationship line.

As I implied in the previous paragraph defining the database relationship is
optional. The database management system only ensures that data conforms to
the rules you have defined. I say 'only' but this is a hell of a benefit and
I recommend you always define relationships in this way.

Now when it comes to queries Access will by default display the database
relationships between any tables you use. You can cancel these relationships
and/or define new relationships for the purpose of this query. This is a
process known as joining tables. If you create a new relationship the
graphical technique and dialog box are identical to that I described for the
Relationship window in the foregoing. Whereas the database relationship
ensures data integrity the query relationship (or join) defines how the data
is to be related for the purpose of interrogation.

This I hope answers your question about good(?) relationships.

Now moving on to the second part of your question. If you are using the
main-subform construct of Access you don't necessarily need to define any
relationships at all. Try using the wizard and at some point Access will ask
you how the data on the subform is related to the data on the main form.
Link Master Fields refers to the one or more fields on the main form while
Link Child Fields refers to the matching field(s) on the subform.
Effectively Access is setting up the query joins for you and will also take
care of the synchronisation between main and subform. In your case you need
to specify Product Name as the Link Master Field and Product Name as the Link
Child Field.

If you have set this up correctly then as you move through the products on
your main form all related inspections will be shown on the subform. If
there are no related inspections then your subform will be empty. Could this
be the reason why you can't see any fields in your subform? I should pause
here and ask whether you can't see the fields or whether you mean that you
see the fields but they do not contain values? If you see no fields then
there is something wrong with the design of the form you are using as a
subform.

If you see the fields but they do not contain values then as I said there
are no related inspection records. Creating a new inspection record is
simply a matter of entering information into the empty row (assuming that you
have not switched off the Allow Additions property of the form you are using
as a subform).

Hope this helps.

Rod

 

[Jamie Collins]

My first observation is that you have two tables with the same primary key.
This suggests that the information on each table pertains to the same entity,
so why two tables rather than one?

The most likely reason would be that a product entity can be entered
into the database without supply details of a spec (assuming you
really do think that entity tables with 100+ nullable columns are to

Some
contributors to this site would argue that a primary key is not necessary,
and they are correct.

On the off chance you are alluding to me, I'll clarify: in the data
model, each table should have at least one key (otherwise it's a heap)
and the choice of which candidate key should be given the moniker
'primary' is arbitrary; in implementation, the use of PRIMARY KEY (SQL
keywords in uppercase, not shouting) is SQL-product-specific hence is
a very different consideration.

Look for a 'real life' primary (unique)
key for inspections. It may be that one does not exist - on the other hand
how do inspectors file their results?

Could be a compound of (product_name, inspection_date).

You already have Product Name as an attribute on your Inspection table.
This is a foreign key as it 'points back' to the primary key of the Product
table. (Another comment here: I feel a text attribute such as product name
is a poor choice for a primary key but this is another discussion.)

Now you have a choice. You can define this relationship to the Jet database
management system and that system will ensure your data integrity as far as
the relationship is concerned. To do this graphically display the
Relationships window (the method is slightly different according to the
version of Access you are using). Select the Product and Inspection tables
to be displayed in the window if they are not there already. Now drag and
drop Product Name from one table onto Product Name in the other table.
Access will display a dialog box; explore the options and choose what you
want. Finally click on Create and the relationship is defined.

Don't forget to check the 'referential integrity' box, otherwise you
won't end up with a Jet FOREIGN KEY but rather a nebulous Access
Relationship object.

Jamie.

--

 

[Rod Plastow]

Jamie,

Those slings and arrows now have barbs on them. To state my case I did
say: 'There may be valid design reasons for this ...' I was thinking that
maybe not all products are electrical fans (?) and that there could be
totally different inspection tests for other types of product, so the data
model reflects a sort of class/sub class situation. For the record I do
think tables with 100+ nullable columns should be avoided.

Yes I was thinking of you et al when talking of primary keys. However may I
respectfully suggest this is not the place to continue the debate (not that
we really disagree). Our job here is to jump-start New_Access and help
him/her achieve a design that he/she understands and can develop. I hope we
haven't frightened him/her away; it's gone somewhat quiet.

Regards,

Rod