Confounded...

OK, I can make the site visits dependent on the Vendor (I have had them
dependent on the site - that makes sense if my user is now going to want to
move sites to different vendors.

The trouble I now have is the fact that in the tblVendor and tblSite I have
the Vendor_ID and Site_ID as autonumbers so that as I add new sites and
vendors, they automatically get a unique identifyer. This newly designed
tblSiteVisit w/ a foreign Vendor_ID and Site_ID will not allow that many
autonumbers.

As I add new sites and vendors, how do I automatically get the Vendor_D and
Site_ID into the tblSiteVisit?
--
Thanks

You all are teaching me so much


"Jason Lepack" wrote:

Then based on you description your table design is a little twisted.

A vendor has many sites.
A site has many visits.

Therefore you should have these tables:

vendors:
vendor_id - PK
more info about vendors

sites: (currrent info about sites, including current vendor)
site_id - PK
vendor_id - FK (many sites per vendor)
more info about sites

Now, if a site_visit is dependent on the vendor, then there needs to
be two foreign keys.

site_visit:
site_id - FK to sites
vendor_id - FK to vendors (vendor in site at time of visit)
more info about site visit

Otherwise, if it's not dependent on the vendor then,

site_visit:
site_id - FK to sites
more info about site_visits

Cheers,
Jason Lepack

On Apr 4, 3:34 pm, knowshowrosegrows
wrote:
No, A site visit is when a monitor visits a site that is run by a vendor.
The vendors usually run 5 to 10 sites. The sites get numerous site visits
during the year.

The trouble I now have is the fact that in the tblVendor and tblSite I have
the Vendor_ID and Site_ID as autonumbers so that as I add new sites and
vendors, they automatically get a unique identifyer.

That's a good plan.

This newly designed
tblSiteVisit w/ a foreign Vendor_ID and Site_ID will not allow that many
autonumbers.

I site_id and vendor_id will be just regular numbers in site_visit.

As I add new sites and vendors, how do I automatically get the Vendor_D and
Site_ID into the tblSiteVisit?

You won't populate site_visit with any records until a site gets
visited. When there is a visit, your form will be used with a combo
box to select the site and vendor.

Cheers,
Jason Lepack