MS Access question

[Barbara]

Okay, I'm an Access newbie and I'm completely stuck.

I'm trying to design what I thought was a fairly simple database to help a bank manage which vendors are used by which of its facilities.

I designed a db that had two tables. One contained the location information with a field for each service (janitorial, landscaping, etc.) The second one contained the information about each vendor.

I populated the vendor fields and I (thought I) linked the vendor to the appropriate service field in the location table. There are about 15 different services.

What I want is a report for each location that lists the service, the vendor, and the vendor contact information. Well, all I can get is a report that pulls from the location db. If I try to get any info from the vendor table, the query is blank. I have relationships set from the vendor name to the different services.

Also, it won't let me enforce referential integrity. It babbles about differing field sizes and types, even though they're the same.

Is this one-to-many, many-to-one or many-to-many? I honestly can't figure it out.

Should the various service types be their own table?

I didn't used to think I was stupid and would appreciate any advice my technical betters could give me.

[Foosinho]

Is this one-to-many, many-to-one or many-to-many? I honestly can't figure it out.
Could you post a screenshot of the relationships screen? And maybe provide the actual table definitions?

Should the various service types be their own table?
Probably. Any time you have repeating data, you should probably create a lookup table for that data. The way it seems to be set up now, if you misspell "landscaping" in one field it won't show up in any queries for landscaping services.

I didn't used to think I was stupid and would appreciate any advice my technical betters could give me.
Databases can be tricky. Really, getting the table definitions and relationships right is the hardest (and most important) part. It took me a while to get the hang of relational databases (I am self-taught on this particular topic).

[Barbara]

Could you post a screenshot of the relationships screen? And maybe provide the actual table definitions?

I tried to attach something. We'll see if it works.


Probably. Any time you have repeating data, you should probably create a lookup table for that data. The way it seems to be set up now, if you misspell "landscaping" in one field it won't show up in any queries for landscaping services.

I think I do have a lookup table so that when you want to pick a landscaping vendor, there's a drop down list linked to the vendor table.


Location Table:
Field Name/Type
ID/Autonumber (Primary key)
Location/Text (indexed)
Backflow/Text
Bank Equip./Text
Copier/Fax /Text
Electrical/Text
etc.

Vendor Table:
VendorID/Autonumber (Primary key)
VendorName/Text (indexed)
ContactFirst/Text
ContactLast/Text
Office phone/Text
Mobile/Text
etc

I have two short term goals:
- One is to be able to print a report, by location, of what vendor (including contact information) provides what service for that location.
- Get "enforce referential integrity" to work so that when I update something in a Vendor field it updates the appropriate fields in Locations.

I'd appreciate any help I can get.

I hope this was somewhat coherent.

[TheBestMan]

I tried to attach something. We'll see if it works.
Yipes!

That relationship table looks wacky.

Too many relationships!

Can you give some brief examples of filled in data. Does any vendor do multiple functions? Like landscaping, etc.?

And a textual version of a report you'd like to see output?

[Barbara]

Yipes!

That relationship table looks wacky.

Too many relationships!

That's kinda what I thought.

Can you give some brief examples of filled in data. Does any vendor do multiple functions? Like landscaping, etc.?

Any given vendor can do multiple functions at multiple locations.

And a textual version of a report you'd like to see output?

See the attached very quick and dirty report, but I think it shows what I'm trying to get at.

Thanks.

[Foosinho]

Yipes!

That relationship table looks wacky.

Too many relationships!
Bingo!

Here is what you need to do. First, each individual service field in the locations table needs to store a *number*, not text. Don't worry, when we are all done your forms/reports will *show* the name associated with that vendor ID.

Second, in your relationships diagram you need to put a copy of the vendor table for each corresponding service in the locations table, and then make a 1-to-many relationship between the VENDOR ID and the appropriate field in the location table. Note, this is not creating multiple copies of the actual vendor table, but creating multiple independent relationships.

Make sense?

Actually, I attached a screenshot...2444

[Barbara]

Bingo!

Here is what you need to do. First, each individual service field in the locations table needs to store a *number*, not text. Don't worry, when we are all done your forms/reports will *show* the name associated with that vendor ID.

Second, in your relationships diagram you need to put a copy of the vendor table for each corresponding service in the locations table, and then make a 1-to-many relationship between the VENDOR ID and the appropriate field in the location table. Note, this is not creating multiple copies of the actual vendor table, but creating multiple independent relationships.

Make sense?

Actually, I attached a screenshot...2444

Excellent! Thank you! I'll give that a try right now.

[Foosinho]

Excellent! Thank you! I'll give that a try right now.
I will say that with that many relationships between two tables, Access might get "squirrelly" with some queries, based on my previous experience. It's been a few years, but you *may* have to tinker with advanced queries to get 'em to work how you expect.

Ie, you can do what you want, but it may not work exactly right straight out of the gate/wizard.

[Barbara]

I will say that with that many relationships between two tables, Access might get "squirrelly" with some queries, based on my previous experience. It's been a few years, but you *may* have to tinker with advanced queries to get 'em to work how you expect.

Ie, you can do what you want, but it may not work exactly right straight out of the gate/wizard.


Okay, that didn't work. When I tried to "enforce refertial integrity" I got an error message that said, "Relationship must be on the same number of fields with the same data types." I was linking the vendor ID with the service type name. I'm starting to think that I need a table for each service type.

[Foosinho]

Okay, that didn't work. When I tried to "enforce refertial integrity" I got an error message that said, "Relationship must be on the same number of fields with the same data types." I was linking the vendor ID with the service type name. I'm starting to think that I need a table for each service type.
Did you remember to change the field type in the Locations table?

Honestly, looking at your data, I would probably set it up like so...

[Locations]
- Information about each location

[Vendors]
- Information about each vendor

[Services]
- Information about the service types you wish to track

[Vendors x Services]
- Vendor ID
- Service ID
(This table allows you to specify which vendors are capable of providing which services)

[Locations x Services]
- Location ID
- Service ID
- Vendor ID
(What services each location needs, and which vendor provides it)

Those last two tables will literally be columns of numbers, but they point at important information, and this setup makes it easy to add/remove vendors, locations, or even service types you wish to track without making schema changes.

[Barbara]

Thanks, Foos. I might have to join your church after all.

I finally got this to work. I spent a few days resisting this because it means a lot of additional data entry but, in the end, what worked was the following:

[Locations]

[Services]

[Vendors]

[Locations x Services x Vendors]


The last table isn't full of numbers because I dispensed with the numbers and just used the names as primary keys since they're all unique anyway. I'm now able to get the reports I want.

Thanks again.

[Foosinho]

I finally got this to work. I spent a few days resisting this because it means a lot of additional data entry but, in the end, what worked was the following:
Trust in the Force, Barb. Let it guide you.

In the long run, you'll be glad you did it this way the first time you add a service you didn't think of ahead of time.

[_chachi]

google for 3nf.