Confounded...

[knowshowrosegrows]

I have a fairly simply database.

Vendors have Sites and Sites have Site Visits
My user wants me to give her a form so she can take a set of Sites that are
currently related to a specific Vendor and change them to be related to a
different Vendor. She wants to do this and still keep the Site Visit history
for the Sites.

I am not even sure how to think about this kind of form/transformation. Any
ideas?

tblVendor
PK = Vendor_ID
FK = Site_ID

tblSite
PK = Site_ID
FK = SiteVisit_ID

tblSiteVisit
PK = SiteVisit_ID

Thanks

You all are teaching me so much

 

[Wayne-I-M]

Use the tblSiteVisit as the linking table and add both the Vendor_ID and
PSite_ID. This will give you a many to many relationship. You can then have
a choice (create an option group or something like it) for yur user to view
either the Site vists for each or any Vendor or the sites for each or any
vendor.

A bit like a school have lots of students in classes and each class has lot
of students

 

[Jason Lepack]

I could be way off base here.

Wouldn't a "site visit" be a vendor visiting a site?

If so then there should be:

vendors:
vendor_id - PK
more info about vendors

sites:
site_id
more info about sites

site_visits:
site_id
vendor_id
more info about the visits

Cheers,
Jassn Lepack

 

[knowshowrosegrows]

Can you speak a little more about what you mean when you say "view

 

[knowshowrosegrows]

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.

 

[Jason Lepack]

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

 

[Wayne-I-M]

At the moment I thin you wil have

tblvendors
VendorID
siteID
details of vendors

tblsites
siteID
details of sites

tblvisits
vendorID
siteID

As long as you keep it this simple can can create an option group that will
show either of 2 forms/reports that give the details of sites that vendors
have (a vendor can have more than one site) or the sites visited by the
vendors (sorry I am not too sure on what you are looking for here).

or

If you use one main form based it on the tblvisits and insert 2 subforms
based on the other 2 tables as long as the forms are linked child master you
could use the same main form to display the details your user needs.

 

[knowshowrosegrows]

Thanks for your response. This is helpful. The problem I have with your
listing of tables and keys is this. If I have the Vendor Table include a
Site_ID then won't I have to list the Vendor 5 times when the vendor has 5
different sites?

 

[knowshowrosegrows]

I'm reading about junction tables. In this case I would have a separate
table that listed both the Vendor_ID and the Site_ID and use this to create a
many to many relationship between the tblVendor and the tblSite. Would this
work for me?

 

[Jason Lepack]

Do you see my posts?

If you don't then I will stop making them.

If you do, then take a look at my most recent one for Apr 4 in
response to your questions.

 

[knowshowrosegrows]

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?

 

[Jason Lepack]

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