Relational DB

[Jason Edwards]

Hello,

I have a database that I use for creating quotes for
customers. When entering a quote on a form I enter a
product ID and it pulls the information from a linked
database that has the desc, cost, vendor...etc and puts
the data in the fiels on the quotes database. What I want
to know is if I enter a products ID that does not exist
in the lookup database then it pulls no information. If I
continue and go ahead and manually enter this data on the
form will it add this new data to the products database
automatically or can I make it do this. Will I have to
use code or is there an option to add data to the child
database if it does not allready exist. I hope I
explained this right.

Jason

 

[John Vinson]

Hello,

I have a database that I use for creating quotes for
customers. When entering a quote on a form I enter a
product ID and it pulls the information from a linked
database that has the desc, cost, vendor...etc and puts
the data in the fiels on the quotes database. What I want
to know is if I enter a products ID that does not exist
in the lookup database then it pulls no information. If I
continue and go ahead and manually enter this data on the
form will it add this new data to the products database
automatically or can I make it do this. Will I have to
use code or is there an option to add data to the child
database if it does not allready exist. I hope I
explained this right.

You're missing the whole point of relational databases.

Relational databases use the "Grandmother's Cupboard Principle": "A
place - ONE place! - for everything, everything in its place". You
should enter the Product information once *and once only*, in the
Products table; the ONLY product field that should exist in the quotes
table is the ProductID. The other product description fields should
simply not *exist* in the Quotes table. If you want to see the product
information in connection with a Quote, use a Query joining the two
fields.

 

[Tim Ferguson]

When entering a quote on a form I enter a
product ID and it pulls the information from a linked
database that has the desc, cost, vendor...etc and puts
the data in the fiels on the quotes database.

Well, I am sure you don't mean the "quotes database" -- I assume we are
talking about the whole thing being in one database.

I really hope you are not talking about the "quotes table". Information
about a Product belongs in the Products table and nowhere else: in
particular not in the Quotes table which should only have details about the
Quote.

What should be happening is that when you see the Product information on
your form, it is being fetched from the Products table and just shown to
you. And not being moved or copied or anything. It is arguable whether you
should have any right to alter the Product fields at all: you are after all
making a quote, not designing products.

What I want
to know is if I enter a products ID that does not exist
in the lookup database then it pulls no information. If I
continue and go ahead and manually enter this data on the
form will it add this new data to the products database
automatically or can I make it do this.

Really depends on how the queries and the forms have been set up: it can be
done so that it does or it doesn't. Each method has its advantages and
disadvantages. It might be said that creating/ discovering/ changing/
editing a product is a process so fundamental that it should have its own
forms, permissions and procedures. Remember that changes to a Product
record will affect every customer that is linked to that product. On the
other hand, in some situations, maintenance of the Product records might be
undertaken by the same people as those who manage the Clients, so it might
be acceptable.

Hope that helps


Tim F