New User needs to learn about auto populating

[Guest]

Hello;

I am in desperate need of help. I offered to pay four people to help me with
this question. They want to charge $150/hr for advice. For $150/hr I want it
done. I will ask the undenyably ask the wrong question for the next six
months.

I have read through normalizing, think I have a grasp on it and require the
database to have a limited number of like fields. It increases the speed with
which I can select those fields to do what I want to do, which is
autopopulate.

Someone suggested that I try to accomplish something with some code. I am a
complete idiot and understood none of it.

For ease of discussion I have posted the database here.

http://home.sc.rr.com/pytelfamily the file name is orders.mdb.

I have two tables. Customers and Products. In my Orders form I can add
information to both tables, I think. Products is the subform(?).

On the Products subform I want to be able to select a value from the Product
Name field and have all of the other fields in the form autopopulate based on
the key. I think the key because I can always add new products in later and
have a new key that will be choosable. I am not worried at this time how much
room this table takes up as I can't forsee putting more than 2000 items in
it. It is imperative that this table remain pretty small and will for the
forseeable future.

The products are not being sold to the customer. The customer already owns
them. Kind of like the people you see going into grocery and convenience
stores that do inventory. The principle is the same. I want to permanently
link the product to the customer, but I want to be able to link the product
to more than one customer.

Does this even begin to look like what I have done in the database that I
have created thus far?

I'ld gladly pay someone to complete the code for this. Or if you prefer to
just help, I will be greatly appreciative. I just can't see it as being that
big of a job. Am I way off base here?

Frank

 

[John W. Vinson]

The products are not being sold to the customer. The customer already owns
them. Kind of like the people you see going into grocery and convenience
stores that do inventory. The principle is the same. I want to permanently
link the product to the customer, but I want to be able to link the product
to more than one customer.

Then you need A THIRD TABLE.

The Customer table should have information about customers... and only
about customers.

The Products table should have information about products... and only
about products.

The third table - Ownership, maybe??? - should have a ProductID
(linked to the Products table) indicating which product is owned, and
a CustomerID field (linked to Customers) indicating who owns it.
Adding a record to this table provides the link.

If you're assuming that the table should ALSO have the customer name,
or the product description - you're falling into a very common trap.
It *should not*. You can always see the product-specific or
customer-specific information in a Query, joining the tables; or you
can use a Combo Box which is bound to the ProductID but shows the
product name on your form, so the user can see the human-meaningful
product name, but the computer sees the computer-meaningful ProductID.

Does this help? I haven't downloaded the database but it may not be
necessary for me to do so... I hope!

You may also want to look at some of the references in

Jeff Conrad's resources page:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

particularly the Database Design 101 links on Jeff's site.

John W. Vinson [MVP]

 

[Guest]

Alright John, I need a third table. I can name this table Ownership. I assume
that it will have fields similar to it that the Product table has. Or is this
just a key to link the CustomerID and ProductID to that specific product. It
will have 3 columns only?

 

[John W. Vinson]

Alright John, I need a third table. I can name this table Ownership. I assume
that it will have fields similar to it that the Product table has. Or is this
just a key to link the CustomerID and ProductID to that specific product. It
will have 3 columns only?

Possibly just two, unless you need to record some information about
THIS customer's ownership of THIS product. The two obligatory fields
are CustomerID and ProductID (Long Integers, if the primary keys of
Customers and Products are Autonumber; otherwise the same datatype as
the corresponding primary key). It will certainly NOT have the product
fields - or the customer fields - stored redundantly.

John W. Vinson [MVP]

 

[Joan Wild]

I suggest you read the links at Jeff's site. Getting the design right is
essential. Later, you'll be thankful that you spent the time to get it
right. Have you looked at Northwind, the sample database that ships with
Access?