Access 2002 Data entry combo box or list box

[Cassandra]

I'm creating a database to track/log quotes for products. I have created a
master table for the all the information (quote log# {PK}, RepID, Rep Name,
Customer Number, Customer Name, Customer City, Enduser, stock#, part code#
(other fields for specs itemized), quantity, unit of measure, price, etc. I
also have tables for the Reps to include the RepID and Rep Name. Another
table for product information (over 3000 products linked from the mainframe
through Excel) another table for Customer info.

What I'm trying to do is create a form where the user can enter their rep
id, and it populate the name, the use populate the customer number and the
customer name and city be populated and finally, have the user enter a stock#
or part# and the other specs be populated. I've created queries for each
table to filter on that info but how can I get the data to my form? That way
we can report on how many customers and were quoted on a specific stock or
part#.

Any help would be greatly appreciated.

 

[Jeff Boyce]

Cassandra

If you have a table that stores RepID, RepName, CustID, CustName, CustCity,
etc., you might as well be using Excel! That's how you'd probably end up
setting this up in a spreadsheet, but Access is a relational database.

If the terms "normalization" and "relational" aren't familiar, familiarize
yourself with them before you paint yourself any deeper into that corner...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[DStegon via AccessMonster.com]

i could not disagree more. Using Excel for data storage? Since when? Excel
was NEVER designed to store data, if it was you would not be limited to ~65,
000 rows. Excel was created to be a financial spreadsheet not a DB and using
it as such is NEVER a good idea, sorry. MS has seen many people use Excel
wrongly and have tried to make it more DB like, but it will never be a
database and should never be used in place of one.

Cassandra, what you need to do is look at your "master table" and any fields
that would be duplicated that are not PK's should not be included because
simple select qrys can be written to show all the fields in the various table.
So, in your master table, you dont need RepID and RepName because the RepID
and can be joined to the Rep table in a query and you can display the RepName
without having to store the repsname in tow different table, so all your need
is RepID. The same for customer name, address, city, phone, etc etc etc.
The storing of the customerID and addressID a phoneID would allow you to
report on any and all of the data in those various tables. If you have a
separate table (recommended) for customers then have each customer have it
own unique ID (autonumber is good here... if you are in SQL a GUID is better
but that is advanced) and you store the CustID for the quote/job.

Understand????

Cassandra

If you have a table that stores RepID, RepName, CustID, CustName, CustCity,
etc., you might as well be using Excel! That's how you'd probably end up
setting this up in a spreadsheet, but Access is a relational database.

If the terms "normalization" and "relational" aren't familiar, familiarize
yourself with them before you paint yourself any deeper into that corner...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm creating a database to track/log quotes for products. I have created
a

[quoted text clipped - 20 lines]

Any help would be greatly appreciated.

 

[Jeff Boyce]

Please re-read my response. I wasn't suggesting that Excel works better
than Access for a database.

I was suggesting that if the data model will not be changed, that it better
fits a spreadsheet design than a relational database.

Regards

Jeff Boyce

i could not disagree more. Using Excel for data storage? Since when?
Excel
was NEVER designed to store data, if it was you would not be limited to
~65,
000 rows. Excel was created to be a financial spreadsheet not a DB and
using
it as such is NEVER a good idea, sorry. MS has seen many people use Excel
wrongly and have tried to make it more DB like, but it will never be a
database and should never be used in place of one.

Cassandra, what you need to do is look at your "master table" and any
fields
that would be duplicated that are not PK's should not be included because
simple select qrys can be written to show all the fields in the various
table.
So, in your master table, you dont need RepID and RepName because the
RepID
and can be joined to the Rep table in a query and you can display the
RepName
without having to store the repsname in tow different table, so all your
need
is RepID. The same for customer name, address, city, phone, etc etc etc.
The storing of the customerID and addressID a phoneID would allow you to
report on any and all of the data in those various tables. If you have a
separate table (recommended) for customers then have each customer have it
own unique ID (autonumber is good here... if you are in SQL a GUID is
better
but that is advanced) and you store the CustID for the quote/job.

Understand????

Cassandra

If you have a table that stores RepID, RepName, CustID, CustName,
CustCity,
etc., you might as well be using Excel! That's how you'd probably end up
setting this up in a spreadsheet, but Access is a relational database.

If the terms "normalization" and "relational" aren't familiar, familiarize
yourself with them before you paint yourself any deeper into that
corner...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm creating a database to track/log quotes for products. I have
created
a

[quoted text clipped - 20 lines]

Any help would be greatly appreciated.

 

[DStegon via AccessMonster.com]

Sorry.

I did not take it that you said Excel works better than Access, I said Excel
should never be used in place of an db. Your suggestion was that their
problem better fit an Excel Spreadsheet and since it is data related I
disgreed and I still have to disagree with you.

Just because Access is relational in nature doesnt mean that to store data it
must be normalized. I have seen so many databases in SQL, Pervasive, Access,
etc that leave one to wonder about the designer because of the lack of
normalization. You must have seen similar things. I even catch myself when
I look back at tables and code written 6 months or 6 years ago and think...
DUH!!! Storing their data un-normalized (or flat) is not effiecent as you
and I are both aware, but that doesnt mean they cant do it or should use
something else to store their data. )

Whether the layout fits a spreadsheet design (flat structure) or not, using
Excel to store "data" instead of a non-normalized db should still not be the
answer. I will agree that their layout is "excel-ish" but they does not
mean that they should use it versus a proper db. That's all. ;o)

Best to you and probably not the best place to discuss "theory", so I
apologize once again.

Please re-read my response. I wasn't suggesting that Excel works better
than Access for a database.

I was suggesting that if the data model will not be changed, that it better
fits a spreadsheet design than a relational database.

Regards

Jeff Boyce

i could not disagree more. Using Excel for data storage? Since when?
Excel

[quoted text clipped - 49 lines]

 

[Jeff Boyce]

No apologies necessary. Sounds like we agree that the OP's data might
benefit from further normalization.

While it is possible to put "flat" data into a relational database, that
usually results in having to work much harder to accomplish some of the
tasks that are simpler with normalized data. As I like to remind folks, I
can drive nails with my chainsaw, but that doesn't make it a good idea...

Best Regards

Jeff Boyce

Sorry.

I did not take it that you said Excel works better than Access, I said
Excel
should never be used in place of an db. Your suggestion was that their
problem better fit an Excel Spreadsheet and since it is data related I
disgreed and I still have to disagree with you.

Just because Access is relational in nature doesnt mean that to store data
it
must be normalized. I have seen so many databases in SQL, Pervasive,
Access,
etc that leave one to wonder about the designer because of the lack of
normalization. You must have seen similar things. I even catch myself
when
I look back at tables and code written 6 months or 6 years ago and
think...
DUH!!! Storing their data un-normalized (or flat) is not effiecent as you
and I are both aware, but that doesnt mean they cant do it or should use
something else to store their data. )

Whether the layout fits a spreadsheet design (flat structure) or not,
using
Excel to store "data" instead of a non-normalized db should still not be
the
answer. I will agree that their layout is "excel-ish" but they does not
mean that they should use it versus a proper db. That's all. ;o)

Best to you and probably not the best place to discuss "theory", so I
apologize once again.

Please re-read my response. I wasn't suggesting that Excel works better
than Access for a database.

I was suggesting that if the data model will not be changed, that it
better
fits a spreadsheet design than a relational database.

Regards

Jeff Boyce

i could not disagree more. Using Excel for data storage? Since when?
Excel

[quoted text clipped - 49 lines]

Any help would be greatly appreciated.