Relationship HELP...

[malhyp via AccessMonster.com]

Hi there, I have created a relationship in a database using the Lookup Wizard
which seems to work just fine. The problem I do have with it that once you do
create the relationship, it changes the text field to a numeric field. This
is causing problems with the website that draws information from it. The
field "somehow" needs to stay as text. Is this possible, or is there another
way to create the relationship without the wizard?

I have tried creating the two tables and then going in to the Relationships
and joining them there just by draging and driping, but I dont thing that is
the correct way. This is becuse when I look up the table with the
relationship it shows nothing.

PLZ HELP!!!!!

Mally.

 

[Tim Ferguson]

I have tried creating the two tables and then going in to the
Relationships and joining them there just by draging and driping, but
I dont thing that is the correct way. This is becuse when I look up
the table with the relationship it shows nothing.

This is the right way... the Lookup Wizard is a nasty old man with a mean
habit of screwing up people's databases. It is much safer, and hardly any
longer, to set up the tables and do it yourself.

You have not given much detail, so I will invent an example and you can
see whether it fits your environment. Say we have a table of Widgets:

Widgets(*WidgNum, Colour, NumberOfSeats, NextInjectionDue)

but you decide you need to control the entries in the Colour field.
Change the Colour field to a numeric ColourCode vis:

Widgets(*WidgNum, ColourCode, NumberOfSeats, NextInjectionDue)

and now create a new table called Colours vis:

Colours(ColourCode, FullName, LooksJustLike)

and fill it like this:

1, "Cerise", "A cherry red type of thing"
2, "Azure", "The deep blue of the mediterranean sea at noon"
3, "Blazing", "The red at the heart of the fires of Hell"

and so on.

Now, in the Relationships window, use Show Table to display both the
Widgets and the Colours tables. Now drag the Colours.ColourCode over to
the Widgets.ColourCode; when the dialog opens check the "Enforce RI" box
_on_; and click OK.

What you now have is a proper relationship between the Widgets and your
Colours look up table. When you want to see what colour a particular
widget is, you can either (a) base the form on a query that joins both of
the tables or (b) set the controlsource of the text box to a DLookUp()
function that fetches the individual colour information. The first method
is usually preferred. You mention a website: presumably you are using
something like ASP+ADO, in which case the solution (a) is almost
definitely the thing to do.

Hope that helps


Tim F

 

[malhyp via AccessMonster.com]

Hey there, I did give that a try but I got the following message when trying
to "Enforce RI".

"No unique index found for the referanced field of the primary table"

 

[malhyp via AccessMonster.com]

If it helps I have the following database.

tblSupplier
idSupplier (AutoNumber)
SupplierName (TextField)
SupplierAddress (TextField)
etc

tblCategory
idCategory (AutoNumber)
CategoryName (TextField)

tblSpecies
idSpecies (AutoNumber)
SpeciesType (TextField)

I am trying to bring all the results together in tblUpdateForm.

tblUpdateForm
idUpdate (AutoNumber)
SupplierName (TextField)
CategoryName (TextField)
SpeciesType (TextField)

Relationships I tried to create.

tblSupplier SupplierName (TextField) with tblUpdateForm SupplierName
(TextField)
tblCategory CategoryName (TextField) with tblUpdateForm CategoryName
(TextField)
tblSpecies SpeciesType (TextField) with tblUpdateForm SpeciesType (TextField)

 

[Tim Ferguson]

I am trying to bring all the results together in tblUpdateForm.

tblUpdateForm
idUpdate (AutoNumber)
SupplierName (TextField)
CategoryName (TextField)
SpeciesType (TextField)

Hmmm... this smells very suspicious. If it begins with "tbl" then I
assume we are talking about a table, but I don't understand why you would
have a table full of UpdateForms. Still, onward and upward...

Relationships I tried to create.

tblSupplier SupplierName (TextField) with tblUpdateForm SupplierName
(TextField)

No, this really does not make any sense in database world. I am trying to
work out what these entities actually mean. I guess it is something along
the lines of:

An Update does something to a particular kind of Species from one
particular Supplier, and is allocated to exactly one Category.

In that case, you should have

Updates
( IdUpdate Long Integer Autonumber Primary Key
IdSupplier Long Integer
IdCategory Long Integer
IdSpecies Long Integer
)


You _can_ match a foreign key to a non-primary key, but there is seldom
any need to. Let the db engine itself use and optimise its numeric keys.
You then create the relationship between (eg) Updates.IdSupplier and
Suppliers.IdSupplier. That is what primary keys are for.

On your form (I am guessing again that you have some kind of form to edit
details of these Updates), you can base the RecordSource on a query
joining all four tables. Note this:

If you want to change _which_ supplier supplied the update, then you
have to include the Updates.IdSupplier column. Set the SupplierName and
SupplierAddress controls to Locked and ReadOnly.

If you want to change the _details_ of the supplier but not change
which one it is, then you have to include the Suppliers.IdSupplier
column.

If you want to do both, you have to have a really good user interface
to avoid confusing your users.


Hope that helps


Tim F

 

[malhyp via AccessMonster.com]

Hi there Tim, thanks for your replies. To explain briefly the Update table is
being created so that a supplier can login to our web site and their details
are shown. From their they can update 5 categories and 5 species. An example
of this would be.

Supplier
Bobs Store

Category Species
Sawn Lumber Oregon
Dressed Lumber Oregon
Wet Lumber Pine
Joinery Lumber Hardwood
Joinery Lumber Pine

The reason we created the Update table is because it pulls all the
information together. When I go into the Update table currently it shows the
following.

Bobs Store Sawn Lumber Oregon
Bobs Store Dressed Lumber Oregon
Bobs Store Wet Lumber Pine
Bobs Store Joinery Lumber Hardwood
Bobs Store Joinery Lumber Pine

Thanks for your patience.

I am trying to bring all the results together in tblUpdateForm.

[quoted text clipped - 3 lines]

CategoryName (TextField)
SpeciesType (TextField)

Hmmm... this smells very suspicious. If it begins with "tbl" then I
assume we are talking about a table, but I don't understand why you would
have a table full of UpdateForms. Still, onward and upward...

Relationships I tried to create.

tblSupplier SupplierName (TextField) with tblUpdateForm SupplierName
(TextField)

No, this really does not make any sense in database world. I am trying to
work out what these entities actually mean. I guess it is something along
the lines of:

An Update does something to a particular kind of Species from one
particular Supplier, and is allocated to exactly one Category.

In that case, you should have

Updates
( IdUpdate Long Integer Autonumber Primary Key
IdSupplier Long Integer
IdCategory Long Integer
IdSpecies Long Integer
)

You _can_ match a foreign key to a non-primary key, but there is seldom
any need to. Let the db engine itself use and optimise its numeric keys.
You then create the relationship between (eg) Updates.IdSupplier and
Suppliers.IdSupplier. That is what primary keys are for.

On your form (I am guessing again that you have some kind of form to edit
details of these Updates), you can base the RecordSource on a query
joining all four tables. Note this:

If you want to change _which_ supplier supplied the update, then you
have to include the Updates.IdSupplier column. Set the SupplierName and
SupplierAddress controls to Locked and ReadOnly.

If you want to change the _details_ of the supplier but not change
which one it is, then you have to include the Suppliers.IdSupplier
column.

If you want to do both, you have to have a really good user interface
to avoid confusing your users.

Hope that helps

Tim F

 

[Tim Ferguson]

The reason we created the Update table is because it pulls all the
information together. When I go into the Update table currently it
shows the following.

I think this may be the problem. Tables don't "do" anything; they are
just piles of records that refer "to" things -- in other words, they
represent real-life business entities. Forms, on the other hand, don't
relate to things, they represent business processes. It is the form (or
web page) that should be doing the "pulling together" not a table"

That said, I think you seem to have a situation where you need to store
records like

Bob's store supplies SawnLumber made of Oregon

For me, this is not a Update; it just seems to be WhoSuppliesWhatOfWhat
and although there probably is a better name for a table at least that
would be descriptive! In any case, I don't think the design is far wrong
(this appears to be in 6th normal form, if you are interested) but you
have to understand about FKs (foreign keys) and PKs (primary keys). The
model I suggested up there still seems to be appropriate.

Hope that helps


Tim F