Look-up Datatype

[Cal]

Hi there,

I'm creating a database for a department I work in.. and
I'm running into a problem with lookup data types.
The way I have it set up is that there is a table for
WorkOrders, and it references a table called Manufacturers
to pull Mfg names. I want one called Models to be
dependent on the chosen Mfg name, but I don't know how to
get the Lookup to do that.

Do I create a new table for Models and link it to Mfg_ID?
It's seems like such an easy question, but it's driving me
mad.

Thanks!

 

[Guest]

Ok.. I take that back...
I read a similar newspost regarding this.. and have most
of it setup. I'm running into an error that says my
primary key is null when I do a dual between Manufacturer
and Model_Id.

 

[Rebecca Riordan]

Okay, I'll assume that the other post that you found told you a) lookup
fields can't do that, and b) they're really very ugly, and best avoided

So tell me what "do a dual" means, and we'll go on from there <g>

--
Rebecca Riordan, MVP

Designing Relational Database Systems
Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step

http://www.microsoft.com/mspress

Blessed are they who can laugh at themselves,
for they shall never cease to be amused...

 

[Cal]

Trying to make a duplicate key

I'm running myself into walls, here.
Right now I have:

Manufacturers
-Mfg Name - Primary Key
-Mfg Address
-MfgCity
-MfgZip
-MfgPhone

Models
-ModelID -Primary Key
-Model
-Manufacturers - Foreign Key - Primary Key
-Machine Type

Machine Type
-MachTypeID - Primary Key
-Name

What I want is when the secretary enters a new workorder,
she can drop-down to the manufacturer, pick one, and then
get a list of applicable models. Ideally.. this would come
from a customer list of machines, so I don't think I've
modeled it correctly. It seems simple, but it's driving me
crazzzzy.

 

[Rebecca Riordan]

What's a Machine Type? Is a given type exclusive to a Manufacturer, or are
them multiple manufacturers for any given type?

--
Rebecca Riordan, MVP

Designing Relational Database Systems
Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step

http://www.microsoft.com/mspress

Blessed are they who can laugh at themselves,
for they shall never cease to be amused...

 

[Guest]

The machine types will be independent of manufacturers,
sorry I forgot to mention that.

 

[Rebecca Riordan]

Oh, I'm sorry, I asked the wrong question. What's the relationshp between
Model and MachineType? (Sorry, I space some times). That's one to many,
isn't it? If it's one to many, then the MachineType field in the Model
table is also a foreign key.

To set up your form, then, you need to build a bunch of queries that
reference value of the control that represents the next highest level in the
hierarchy. So, for example, you reference the Manufacturer control in the
query for the Model combo. The syntax for that is:

=Forms!<formName>!<controlName>

You put that in the criteria cell for the appropriate field. (In this case,
it would be the Manufacturers field of the Models table). Then, in the
OnChange event of the Manufacturer's combo box, you re-query the Models
combo box.

HTH

--
Rebecca Riordan, MVP

Designing Relational Database Systems
Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step

http://www.microsoft.com/mspress

Blessed are they who can laugh at themselves,
for they shall never cease to be amused...

 

[Guest]

Hey, that makes sense At least until I try it and get
flustered as usual.
Thanks for your help!

-CAL