Setting up 3 fields in form with different values from one table

[toolman74]

I have tbl.Client Co for all of our clients' info. I need to ability to
select 3 different clients all from tbl.Client Co using combo boxes on each.
Is there a cleaner/simpler way than to create 3 different fields like
Client1, Client2 and Client 3 all with related to the Client ID in tbl.Client
Co? Thanks so much!

Pamela

 

[Jeff Boyce]

From your description, it sounds like you are working directly in the
tables, and using "lookup fields". Not a good idea!

If you have a company that has more than one client, how do you decide when
to stop adding new fields? If you were working with a spreadsheet, that's
pretty much how you'd have to handle it ... ?more clients?, more columns!

But Access is a relational database, not a spreadsheet.

Post back with a description of your table structure, and a bit more
information about the data you're working with ... it all starts with the
data!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

I have tbl.Client Co for all of our clients' info. I need to ability to
select 3 different clients all from tbl.Client Co using combo boxes on each.
Is there a cleaner/simpler way than to create 3 different fields like
Client1, Client2 and Client 3 all with related to the Client ID in tbl.Client
Co? Thanks so much!

Pamela

If one Company can be related to more than one Client, you have a one to many
relationship. The proper structure for this involves two tables in a one to
many relationship (using a Form for the company with a Subform for the
clients, typically). This will let you add zero, one, three, or sixty clients
for a given company, as needed.

 

[Pamela]

I am just starting out with this db and so trying to start it out correctly,
so any help on it is greatly appreciated. Right now, I am running about 40
tables (I'm only just now starting to create relationships between them).
Just to give a clearer picture, we are an appraisal company. We get
assignments to inspect (usually) vehicles from a claim company who got it as
a claim from an insurance company. We inspect the vehicle, write an estimate
on the damage and summary report(s), and send the paperwork back the same way
it came to us. On each summary report, I need to show that the appraiser
contacted 3 different aftermarket parts companies and 3 different used parts
companies and list out their names, cities and phone numbers. I created
tables for both the aftermarket parts companies and used parts companies. So
would that then be a many-to-many? Each assignment has many (3) different
companies and each company will be used for many different assignments. Any
ideas of how better to set this up? Thanks so much!!!

Pamela

 

[Jeff Boyce]

Pamela

If the terms "relational" and "normalized" aren't familiar, spend some time
brushing up on them. I generally advise folks that there are three separate
learning curves to making good use of Access.

First, you need to understand "relational" and "normalized". Access'
features expect data in a particular arrangement.

Next, you need to understand Access' "how to's". That is, the tips and
tricks that Access uses to get things done.

Finally (and most importantly in my opinion), you need to understand what
works for folks. Some call this graphical user interface design, but the
bottom line is that if the application is unfriendly and difficult to
understand and use, folks won't use it. And if the application let's them
make mistakes and doesn't make it easy to fix those, folks won't use it!

First things first...

Regards

Jeff Boyce
Microsoft Office/Access MVP