Displaying fields in a stock table

[Ray C]

I have a Stock Table and I have Entry Tables that go with the stock table.
The Make entry Table contains a Make ID and all the different Makes of Stock.
Similarly, the Model Table contains a ModelID and all the different Models. I
use a combo Box to make the selection and ID is the Bound Column so that the
Combo displays the Make/Model as Text but the Make/Model ID is stored in the
Stock Table. My problem is that when I subsequently use a Text Box to display
the Make/ Model stored in a particular Record of the Stock Table, all that is
displayed in the text box is the ID number. I should be able to get the Text
Box to display the Make/ Model text that is associated with the ID by using a
Query but I don't know how to go about that. Could anyone help please?
Thanks RayC

 

[Larry Daugherty]

It sounds like you may have used a lookup field in the underlying
table. If that's the case, the best solution is to go back to the
table design and take them out. Design your tables properly and
you'll have something on which you can effortlessly (relatively
speaking) layer other functionality. I suggest that you check out
www.vmps.org/access and review what's written there about lookup
fields.

HTH

 

[Steve]

Your Stock table looks something like this:

StockID
MakeID
ModelID
.....
.....

For relationships, you should then have MakeID joined to MakeID in the Make
table and ModelID joined to ModelID in the Model table.

In your query, include all three tables. Pull down the Make and Model text
fields from the respective tables. Base your form on this query. Set the
controlsource of one textbox to the Make text field and another textbox to
the Model text field.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Ray C]

Thanks for the response Larry but I am getting very confused. I thought that
not repeating the same information was a good thing in tables. The majority
of the stock items are serial numbered. Are you saying that I should have a
few hundred fields all with Make = "MakeA" and a few hundred fields with
Make = "MakeB" etc, etc. I thought it better that the few hundred (or
thousand) with Make =1 or Make = 2 where 1 = "MakeA" and 2 = MakeB etc.
I also thought that a Combo was good as it restricted the user to a choice
between correct and valid options.
I have looked through "the evils of Lookups fields in Tables by Arvin Mayer
and Joan Wild but that only seems to discuss the problems and does not
provide an adequate solution.
Thanks RayC

 

[Ray C]

Hi Steve, I really appreciate your help, thank you.you are right in your
assesment of how my Stock Table looks but the whole thing is somewhat more
complicated than that. However, witha view to trying to undestand what is
happening I created a Query and added the ent_tblModel, ent_tblMake and the
tblStock to it. I joined the two entry Table ID's to the respective entries
in the stock Table and I added the Stock Table ID but did not male it visible
in the query design view. I draged the Make (name) and the Model (name) into
the query and in the Criterion of the Stock ID, I added a number. Great, the
Query shows the Make (Name) and the Model Name of the Record that matches the
number I placed in the Criterion.
So Far so Good. I generated a Form and based the form on the Query but the
text boxes are Blank. The Control Source of the Make and Model Text Boxes in
the Form are both set to Make and Model in the Query. I must be doing
something wrong, any further thoughts?
RayC

 

[Steve]

Create your form using the form wizard and select the query as your record
source. Let us know what you get in the textboxes for Make and Model.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Ray C]

Hi Steve. Tried that and everything looks Fine. the problem would appear to
be something quite different (and to me Wierd). I mentioned earlier that I
added a non visible column that was bases on StockID in the Stock Table. The
form (and Query) performs well if I enter any number from 1 to 13 in this
Criterion box. The Form displays all the appropriate information from the
various forms linked to the Query allong with the correct Serial Numbers
stored in the appropriate fild of the Table Stock. However, if I enter any
number over 13 the resultant query(and Form) is Blank.
Thanks for your help RayC

 

[Steve]

Go to the database window and open the Stock table. Look at StockID. What is
the next number after 13?

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Larry Daugherty]

Assuming that you have a limited repertoire of models, you might have
a lookup table that lists each model. tblModel.

Parts in your inventory may have an attribute named "model". On the
Form you use to enter parts into tblPart there might be a combobox on
tblModel that allows for simple point and click selection of the
correct model for the current part. Save typing, typos, etc.
.............

Just went back to your initial post and am starting to perceive your
issue differently.

What you did was store the ID of the model. That is relationally
correct but not too informative to a human. I often break the rules
in cases like that and store the actual model Name. i.e. bound
column 2 or ???. That way the value shown on the form will make sense
to the human. Also, the textual value stored in the table will always
show up in subsequent queries and Reports.

There is an adage to do with RDBMS schema design that applies:
"Normalize 'til it hurts, de-normalize 'til it works". Like any
heuristic, use with informed caution.

In these cases, ease of use, ease of design and maintainability trump
storage space issues and relational purity.

There are other ways to solve your present issue but the above is the
way I'd choose.

HTH

 

[Steve]

<< ..... store the actual model Name ... >>

NOT a good idea! If you find for some reason that an error was made in a
particular model name associated with a Make, then you would need to go to
all the records that contained the model name and correct it. With a lot of
records, this could lead to additional errors in the data. The rule is there
for a reason to avoid this potential scenario.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Ray C]

Sorry for the delay in geting back to you but the next number is 14 (followed
by 15)

Rayc