Relationship not working

[wcrg]

Hi,new to the forum and novice access user.

I have a number of tables in by database with the main one being tblsites with the PK Site number. I am trying to create a relation to the table tblNTP with the PK site number. I have used the same format for other relationships within the database with any issue however this one does not want to cooperate. I have attached copies of the tables and the relationships for your viewing. If anyone can provide assistance I would greatly appreciate it!

Thanks

 

[MarkK]

You need to provide lots more detail about this . . .

. . . does not want to cooperate.

What does that mean? How do we troubleshoot with that info?

 

[arnelgp]

why do you need tblNTP_1 in the relation?

 

[sneuberg]

Why are you attempting to make a one-to-one relationship versus just having the fields in the same table?

 

[jdraw]

wcrg,

Tell readers about your application in plain English --no database or Access jargon. What is the business your database is intended to support? Your business rules will identify your relationships. We really can't offer much focused assistance until we understand WHAT you are trying to accomplish.

 

[plog]

1. Your screenshots aren't helpful. I am unable to see table names, field names or how your tables relate to each other. Everything is crammed and truncated.

2. You should only use alphanumeric characters for table/field names. When coding later it makes it a lot easier. So, [Structural Status]=>[StructuralStatus] and [Data On FTP]=>[DataOnFTP], etc.

3. 1->1 relationships are unnecessary. You simply put the data into one table.

4. Tables with 1 real field are unnecessary. You have a few of these. Instead of using a 'lookup table' to handle these values, you can put it in the table itself.

5. Use descriptions. In the design view of the table you posted you left the 'Description' field blank. Help us and whoever comes after you to work on this out by filling out that field. Say what that field is for and use it to store notes.

I'd follow those 5 things, then post a new, better relationship screenshot along with a plain-English description of what your data represents. Pretends its career day for 10 year olds and tell them what it is your business does--no database jargon, just plain english description of what it is you do.

 

[speakers_86]

I don't even like using the relationships. I think it just gets in the way.

 

[wcrg]

First thanks to everyone for replying! The consensus appears that I need to provide more details in plain English. I work for a wireless telecom company doing site acquisition, getting leases for new sites and all required permitting. As you can imagine there are many steps in the process; leasing, survey, zoning, etc. The purpose of the database is to one central place for me to track all task of each site, with over 100 active projects there is a lot of information for each site. The database is for my own use and is not shared within others in the company.

Each site is assigned a unique site number. I reasoning behind the one to one relationships was that since the site number is unique I do not want it to be accidentally duplicated. I have created tables and forms for each of the process leasing, survey, zoning, etc. I then used a tabbed subform to containing each of the processes, which allow for on place to enter and check the status of task. By using this concept I was attempting to avoid a table with many fields and a form that was not manageable. I have then created a few reports that allow for me to review the status of the projects. I have used one to one relationships with success until the table NTP not allowing for the relationship. I have looked for all of the obvious mistake; spelling, data type etc without any success.

I have attached the database if anyone would like to see it in more detail. Suggestions and comments are welcome as I am new and wanting to do it the correct way. If you need any additional information please let me know.

Thanks for all of your assistance!

 

[sneuberg]

I didn't have any problem adding the relationship between tblSites and tbINTP. I double clicked on the existing line, checked Enforce Referential Integrity. clicked OK and it accepted it. What's happening when you try it?

On the other hand I couldn't add one between tblNTP and tblINT1, but that's because they are the same table. Why do you have this in the relationships?

 

[sneuberg]

You should only use alphanumeric characters for table/field names. When coding later it makes it a lot easier. So, [Structural Status]=>[StructuralStatus] and [Data On FTP]=>[DataOnFTP], etc..

I used to think that too, but I believe if you provide a data sheet view you are stuck with the table field names. I'd be glad if I am wrong about that. Am I?

4. Tables with 1 real field are unnecessary. You have a few of these. Instead of using a 'lookup table' to handle these values, you can put it in the table itself.

We do this a lot in our systems. How do you "put it in the table itself" and still have the allowed values maintainable by the user?

 

[Frothingslosh]

I used to think that too, but I believe if you provide a data sheet view you are stuck with the table field names. I'd be glad if I am wrong about that. Am I?

When viewing a table in Design view, one of the options in the bottom section for each field is 'Caption'. If left blank, then the name of the field is used in datasheet mode. If you fill it in, however, whatever you entered is used as the name rather than the field name. It also becomes the default value of automatic labels for controls bound to this field.

We do this a lot in our systems. How do you "put it in the table itself" and still have the allowed values maintainable by the user?

You don't.

 

[plog]

I used to think that too, but I believe if you provide a data sheet view you are stuck with the table field names. I'd be glad if I am wrong about that. Am I?

I don't really understand your data sheet view issue. My point is that when you put non-alphanumeric characters in names you are required to bracket field/table names. There are tons of posts on here about people not understanding why they are getting query prompts for [My Field Name] when My Field Name is a field in their table.

How do you "put it in the table itself" and still have the allowed values maintainable by the user?

There's 2 ways to do this both involve clicking the Lookup tab of the table in design view and changing the Display Control to Combo Box. Now, here's where you really need to get practical--how important is restricting the values vs. how much do you trust your users vs. how often will new values actually be added?

Most restrictive: Change the Row Source Type to Value List and hard code your values. Change 'Limit To List' to Yes.

Least restrictive: Change the Row Source Type to Table/Query and base it on a query that looks back into itself for Distinct values. Change 'Limit To List' to No so users can type in whatever they want.

From a practical standpoint data that would go in a 1 real field table really isn't that important/changing.

 

[sneuberg]

I don't really understand your data sheet view issue. My point is that when you put non-alphanumeric characters in names you are required to bracket field/table names. There are tons of posts on here about people not understanding why they are getting query prompts for [My Field Name] when My Field Name is a field in their table.

It's no longer an issue given Frothingslosh post. As long as I can provide the user an interface that looks reasonable, then names like MyFieldName are better when it comes to programming.

 

[Frothingslosh]

There's 2 ways to do this both involve clicking the Lookup tab of the table in design view and changing the Display Control to Combo Box.

Least restrictive: Change the Row Source Type to Table/Query and base it on a query that looks back into itself for Distinct values. Change 'Limit To List' to No so users can type in whatever they want.

Huh.

I stand corrected.

Neat trick, been here over 3 years now and I've never even seen that referenced before.

 

[Minty]

Huh.

I stand corrected.

Neat trick, been here over 3 years now and I've never even seen that referenced before.

I've not seen that before either, very neat - but it won't work with a SQL backend unfortunately, unless you created a query to do the same.

 

[wcrg]

Thanks for your time!

I didn't have any problem adding the relationship between tblSites and tbINTP. I double clicked on the existing line, checked Enforce Referential Integrity. clicked OK and it accepted it. What's happening when you try it?

On the other hand I couldn't add one between tblNTP and tblINT1, but that's because they are the same table. Why do you have this in the relationships?

The problem is that it once the relationship is made the table and form is not showing the 129 sites.

 

[sneuberg]

The problem is that it once the relationship is made the table and form is not showing the 129 sites.

Why would it? I only see three records in the tblNPT table? Is there some missing VBA that's suppose to add blank records? Maybe one of the other forum members will say otherwise by I don't think adding a relationship does this for you.

You could avoid this type of problem by just adding the fields in the tblNPT table to the tblSites table. Why do you have this all split up into different tables when the relationships are one-to-one?