Prev: if first digit of a WO is A or to Z...then substract first 5 d
Next: Selling a database
From: rbalkovec on 1 Jul 2008 12:54
Hello: I might be completely confused as to what I'm talking about and may
not use the correct terminology.

I have a database that has several tables, but in particular, there are 2
tables in question.

1 - The first table is called "Products" and it holds the following structure:
ProductID (AutoNumber) - Primary Key
ProductName (Text)
UnitPrice (Currency)
ProductDescription (Text)
*I planned on typing my product descriptions in this table under
"ProductDescription"

2 - The second table is called "Order Details" and it holds the following
structure:
OrderDetailID (AutoNumber) - PrimaryKey
OrderID (Number)
ProductID (Number) - this is a look up combo box to the "Products" table and
it displays the acutal name of the product - not the number it's connected to
ProductDescription - this is the field I want to be automatically filled in
once the above information is typed in. I was hoping this would pull from
the "Products" table.
Quantity (Number)
UnitPrice (Currency)
Price (Currency)
Discount (Number)

So - if ANYONE can help me out on this - I would greatly appreciate it.
I've tried the SQL stuff, but I'm not real familiar with it at all. I'm
thinking it's some simple command that is typed in and I'm making this harder
than it really is?

PLEASE HELP!





From: Christopher Robin on 1 Jul 2008 13:58
Are you on a form? From what I understand, you have a combo box for
ProductsID, which is displaying the incorrect information, and
ProductDescription, which is displaying nothing.

With a combo box on a form, you can actually display both of these pieces of
information, or just the description. Humans typically don't like looking at
IDs. For your combo box, the Row Source would be a simple query "SELECT
ProductID, ProductDescription FROM Products." The control source would be
ProductID. (As far as DB design is concerned, you should only store the
description in one place.) The bound column would be 0, and then on the
Format tab, Column Count = 2, and Column Widths = 0", 1". This will hide the
ProductID and display the ProductDescription.

Hopefully, I haven't completely misunderstood, what you want, or totally
confused you.

"rbalkovec" wrote:

> Hello: I might be completely confused as to what I'm talking about and may
> not use the correct terminology.
>
> I have a database that has several tables, but in particular, there are 2
> tables in question.
>
> 1 - The first table is called "Products" and it holds the following structure:
> ProductID (AutoNumber) - Primary Key
> ProductName (Text)
> UnitPrice (Currency)
> ProductDescription (Text)
> *I planned on typing my product descriptions in this table under
> "ProductDescription"
>
> 2 - The second table is called "Order Details" and it holds the following
> structure:
> OrderDetailID (AutoNumber) - PrimaryKey
> OrderID (Number)
> ProductID (Number) - this is a look up combo box to the "Products" table and
> it displays the acutal name of the product - not the number it's connected to
> ProductDescription - this is the field I want to be automatically filled in
> once the above information is typed in. I was hoping this would pull from
> the "Products" table.
> Quantity (Number)
> UnitPrice (Currency)
> Price (Currency)
> Discount (Number)
>
> So - if ANYONE can help me out on this - I would greatly appreciate it.
> I've tried the SQL stuff, but I'm not real familiar with it at all. I'm
> thinking it's some simple command that is typed in and I'm making this harder
> than it really is?
>
> PLEASE HELP!
>
>
>
>
>
From: John W. Vinson on 1 Jul 2008 15:23
On Tue, 1 Jul 2008 09:54:00 -0700, rbalkovec
<rbalkovec(a)discussions.microsoft.com> wrote:

>Hello: I might be completely confused as to what I'm talking about and may
>not use the correct terminology.
>
>I have a database that has several tables, but in particular, there are 2
>tables in question.
>
>1 - The first table is called "Products" and it holds the following structure:
>ProductID (AutoNumber) - Primary Key
>ProductName (Text)
>UnitPrice (Currency)
>ProductDescription (Text)
>*I planned on typing my product descriptions in this table under
>"ProductDescription"
>
>2 - The second table is called "Order Details" and it holds the following
>structure:
>OrderDetailID (AutoNumber) - PrimaryKey
>OrderID (Number)
>ProductID (Number) - this is a look up combo box to the "Products" table and
>it displays the acutal name of the product - not the number it's connected to
>ProductDescription - this is the field I want to be automatically filled in
>once the above information is typed in. I was hoping this would pull from
>the "Products" table.
>Quantity (Number)
>UnitPrice (Currency)
>Price (Currency)
>Discount (Number)
>
>So - if ANYONE can help me out on this - I would greatly appreciate it.
>I've tried the SQL stuff, but I'm not real familiar with it at all. I'm
>thinking it's some simple command that is typed in and I'm making this harder
>than it really is?

The ProductDescription field should simply NOT EXIST in the Order Details
table.

Relational databases use the "Grandmother's Pantry Principle" - "A place - ONE
place! - for everything, everything in its place". If the OrderDetails table
(hint: don't use blanks in table or field names) contains a ProductID, that is
*all* you need from the Products table.

Your users should never see the table datasheets in any case; they will be
interacting with the data via Forms. On the Form you can use a combo box bound
to the ProductID but showing the description; the users won't need to know,
see, or ever type the Productid, just select a row from the combo box.

Take a look at the Orders form in the Northwind sample database. It does
exactly what you're trying to accomplish.
--

John W. Vinson [MVP]
 | 
Pages: 1
Prev: if first digit of a WO is A or to Z...then substract first 5 d
Next: Selling a database