Receiving duplicate entries

[GTP]

I have one table with Company, City, and jobsite and I
have three linked combo boxes for company to select the
company, city to select the city that the company is in
from the first dropdown box, and jobsite to select only
the jobsites for the company in the city selected.

All three fileds in the single table are text Keys.

Is there some code or validation rule to use because my
companies have many jobsitsites in many cities. So when i
eneter in a new company, and a new city, the dropdown on
for city on my form will duplicate the cities, IE: Los
Angeles would appear many times.

How do I limit the dropdown to know if there is repeat
cities and only display the city once instead of multiple
times?

 

[ReferenceMan]

I seemed to me replied to your message before.
OK, here is the thing.

You'd better create three tables:
tbl_Company with CompanyID key, autonumber
tbl_Jobcite with JobcityID, autonumber
tbl_Jobsite with JobsiteID, autonumber
(this is in case if you need all three of your dropdown
fields, if I were you I would make only two tables,
Company and Projects, where I would combine city with
jobsite)

In any way:
Create a forth table called:
tbl_Projects.
there you have three fields:
CompanyID, JobsiteID, and JobcityID that are related to
corresponding key fields in the tables above.

You won't be able to make any entry in the table
tbl_Projects unless you have something in your tables
tbl_Company, tbl_Jobsite, tbl_Jobcity.
So when you have some entries in those tables, go ahead
and put a new record.

To make a new record in table Projects just choose a
Client, City and Jobsite from the dropdown menu, there
won't be any of the repetition because the structure in
three main tables won't allow you to do so, unless you
have clients with the same identical names, jobsites with
the identical names and you decide to have different
cityID's with the same City name.

so, you don't have to mess around, as I understand you
are doing right now.

When you need to do filtering, as I described it in my
last post to you, you just put filter on of the three
dropdown fields in the form retated to tbl_Projects.


Best wishes,

ReferencingMan

 

[Guest]

How do I "enforce integrity" if the data types are
different?, IE, autonumber in other tables created. Do I
make them autonumber as well?

Thanks,
Greg

 

[John Vinson]

How do I "enforce integrity" if the data types are
different?, IE, autonumber in other tables created. Do I
make them autonumber as well?

Autonumbers are a special case of Long Integer. If you have a field
linked to an Autonumber, make it Long Integer.