Queries and the benefits of using Primary Key values vs actual value?

[Christine]

As a newbie, I the more I try to design forms and queries,
the less I understand the value of working with Primary
Key values instead of the actual data. For example,

TableA has ProductID and ProductName.
TableB has ProductID

The two tables are joined on ProductID. When you enter
data in TableB, the source is a lookup to TableA.
ProductID is then stored in TableB.

Why do all the pros say to do this? Every time you want to
produce a form, chart or report that will display the
ProductName - NOT the ProductID - it seems you have to
write a query. Wouldn't it be much easier just to store
the information you actually want (ProductName)?

Right now I'm struggling to display the ProductName on a
form but can't figure out how to write the query (or the
expression, if that's the way to do it).

 

[Christine]

Thanks, Howard. Perhaps I didn't explain myself well
enough. Here's what the table design looks like:

Table: tblServiceCalls
Field Name: ProductID
Display Control: Combo Box
Row Source Type: Table/Query
Row Source: SELECT Products.ProductID,
Products.Product FROM Product;
(The wizard created that code, I
didn't)
Bound Column: 1
Column Count: 2
Column Widths: 0cm;5.662cm

When you enter data into the table, you can see Product
("Colour Monitor", for example), but only the ProductID
for "Colour Monitor" is stored in tblServiceCalls.

When I want to display information in tblServiceCalls in a
form, again for example,

I want to see: Name of Product: [Colour Monitor]
(where "Colour Monitor" is Product
I do not want to see: Name of Product: [205]
(where "205" is ProductID)

I do not know how to write the code (query or expression?)
make a form, report or chart display Product. The only way
I've been successful is by letting a wizard do its thing
with Combo Boxes - but I don't want a combo box.

Yes, I now understand why the unique ID fields are really
necessary, but it seems so bloody complicated and I can
see this little project having hundreds of queries just to
get at the info in the table. Can you or anyone help with
the query or expression?

 

[John Vinson]

As a newbie, I the more I try to design forms and queries,
the less I understand the value of working with Primary
Key values instead of the actual data. For example,

TableA has ProductID and ProductName.
TableB has ProductID

The two tables are joined on ProductID. When you enter
data in TableB, the source is a lookup to TableA.
ProductID is then stored in TableB.

Well... one suggestion is: DON'T "enter data in TableB", certainly not
by using a Table Datasheet. Datasheets are good for design and
debugging but (despite Microsoft's misleading, confusing, and all but
useless "lookup" misfeature) are not suitable for routine data entry.
Use a Form instead.

Why do all the pros say to do this? Every time you want to
produce a form, chart or report that will display the
ProductName - NOT the ProductID - it seems you have to
write a query. Wouldn't it be much easier just to store
the information you actually want (ProductName)?

The trouble with that is that product names can change; they're
typically much longer than the 4 bytes of an ID; and you might have
(for example) a current product named "Super Widget" which has
superseded a previous product also named "Super Widget".

Right now I'm struggling to display the ProductName on a
form but can't figure out how to write the query (or the
expression, if that's the way to do it).

Neither a query nor an expression is needed. Just use the Combo Box
wizard on the toolbox to create a combo box, based on TableA; use
ProductID as the bound column and the product name as the first (only)
visible column.

For a Report, simply create a Query linking TableB to TableA by
ProductID. Pull the product name from TableA, and all the other
information (about the order, or whatever TableB represents) from
TableB.

 

[John Vinson]

I do not know how to write the code (query or expression?)
make a form, report or chart display Product. The only way
I've been successful is by letting a wizard do its thing
with Combo Boxes - but I don't want a combo box.

Umm... WHY NOT? No, you don't want a combo box on a Report or a Chart;
but you also don't want to make users type in a correct Product Name
every time they try to enter data on a form, do you? Forms are
primarily used for data entry and updating, and combos make it
*really* easy for users to do so.

Yes, I now understand why the unique ID fields are really
necessary, but it seems so bloody complicated and I can
see this little project having hundreds of queries just to
get at the info in the table.

I'm missing something. I see one query so far. What "hundreds"?

Also, queries are ABSOLUTELY essential to any productive use of
Access. They're as essential to Access as using expressions in cells
is essential to Excel; they both take somewhat of a learning curve,
but they're the basic "small change" of the program.

 

[Guest]

Thanks for your two posts, John.

I do not want to use a combo box because in this instance
the user is not going to be given a choice; they won't be
editing the information. The decision of ProductID/Product
has already been made and stored in tlbServiceCalls. All I
want to do is to display the previously-recorded
information.

For example, in tlbServiceCalls the ProductID = 205.
ProductID is linked to Products, where the Product (name
of the Product) for ProductID of "205" is "Colour
Monitor". Thus, on the form the user will see...

Product: [Colour Monitor]

Seeing what you mean about unique identification, let's
say both ProductID and Name are displayed as they would be
in a combo box with both columns showing, i.e.

Product: [205 Colour Monitor]

How is this done without using the combo box?

Is this explanation better?

(By the way, I'm not performing data entry directly into
the table; I do have a form. I just mentioned the table to
illustrate that ProductID and Product combo box works for
setting the value of ProductID based on the joined tables.)

 

[John Vinson]

Thanks for your two posts, John.

I do not want to use a combo box because in this instance
the user is not going to be given a choice; they won't be
editing the information. The decision of ProductID/Product
has already been made and stored in tlbServiceCalls. All I
want to do is to display the previously-recorded
information.

A combo box is still a good tool for this purpose. Set its Enabled
property to False, and Locked to True; it will look normal but won't
allow the user to enter or edit the value.

Or, you can base the Form on a query joining the product table to
tblServiceCalls, and pull the Product name from the joined table.

Or, yet again, you can (at the cost of some inefficiency) set the
Control Source of a textbox to

=DLookUp("[Product]", "[tblProducts]", "[ProductID] = " & [ProductID])

For simplicity and efficiency, I'd be inclined just to go with the
combo box, using it only as a lookup tool; it's perfectly well suited
for that functionality!

 

[Guest]

Thank you SO MUCH, John! - That's exactly what I was
looking for! (I used your suggestion for the locked combo
box).