Table Relationship Problem Urgently

[Fastone]

Hey,

I'm having a strange problem nobody seems to be able to figure out and it is probably something small everybody (including me) overlooks. Here follows the discription:

If I do a query in the table called 'Products Complete' the required results are shown.
If, however, I add another table to the query, let's say 'Individual Policy Holders', I don't get any data which comes from the 'Products Complete' table. If have changed the relationships to all possible ways but nothing seems to change the result.

The query is correctly formatted and nothing is wrong with it.
Just tell me any ideas you might have but do it clearly because of the newbie status I'm still carrying around when it comes to Access .
Attached is a screenshot of the relations between the tables. Also let me know if you need any more information.

Thanks,

Stijn

 

[boblarson]

For starters, it would definitely help things if you normalized your database, instead of what you have here.

1. You shouldn't have a table for each year.
2. Because of the lack of table normalization, your relationships are not going to connect correctly and as such, you're causing yourself grief trying to get the results you want.

My recommendation:

Normalize, normalize, normalize! It will save you much more work in the future as years go by and as you add more data.

If you are unsure about that go here for a great post by Pat Hartman on normalization:
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=8894&highlight=normalize

 

[UthguardT]

Only thing I can think of is to check you have all the correct destination fields in the SELECT statement.

It might be just 'SELECT ProductsComplete.*' when you need 'SELECT ProductsComplete.*, IndividualPolicyHolders.*'.

If that not it, post the sql query if possible.

ya + normalise those year tables

 

[Fastone]

Thanks, I'll try and normalize.

If you want to see previous discussions i had with people about this problem including the SQL codes (although, then it was viewed as a query problem, not a DB relation problem) look here:

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=38051

http://www.developersdex.com/vb/mes...951fb.0211210251.43de7f34@posting.google.com>

Here is another screenshot with some changes.

 

[boblarson]

I still think most of your problem is not a query problem, but the way your tables are linked in almost a circular reference situation. If you normalize your tables and get rid of special characters in your table names (do NOT use & or / in a table name!!!) you will most likely get better results.

You even have Broker & Marketer tables in TWICE! It should only be there once.

Keep at it. You'll get it and in the process, you'll make the upkeep more manageable (and probably fix your problem in the process).

 

[Fastone]

Okay, I'll change the names.
The fact that there are two tables with the same name is something Access did. Because I already had a link between the two tables, and wanted to make a different kind of relation (no referential integrity) between the tables, Access put in the extra table.
Can you maybe let me know if it is possible to:
- have referential integrity without having a keyfield?
- make sure that all relations are leaving from only one table (ie not let Access put in the extra table in the view)

any suggestion on how to make the year tables in a different form.

Thanks

 

[boblarson]

So, to start, remove all of your relationships to be able to make some of the changes. You will re link them at the end of this.

Second, a quick question about the Company and Branches. I assume that there are more than one company and each company can have more than one branch, correct?


If so, then your year tables could be set up something like:
Client Number
Company ID Number
Branch ID Number
Year
Month


You currently have the Primary Key for Company as Company Number and that will work, but you are tying the name instead of the number to the other tables. You should have Company Number in the Branch Table and not company name.

And, what is the Broker/Marketer Table? It seems to have redundant data to the table Broker & Marketer. Get rid of one.

Then after you've got that all done and you've got no redundant data except the foreign key. Then you should logically think through where your relationships actually exist. Where is the Top and where is the bottom of the heirarchy? You should create those relationships so that certain tables are like bridges and will have "pass through" relationships. For example, the Branch table will have the Company table on the left side and it will flow into the Branch table (one to many relationship), not the other way around. The Brokers will be a natural extension of the Branch table and the Branch table will flow into the Brokers table.

I hope that makes sense and I wish you all the best in fixing this. It's tough, but it will definitely help you and it will make maintenance way easier!

 

[Fastone]

Yep, more than one company, more than one branch.

The reason why I changed the field from company ID to company number (because I had this in the beginning) was to allow easier entry for the data capturers (otherwise they have to remember 500 companies with their numbers, same for branches, ...)

The broker/marketer table is created after your previous post and me reading about the normalization (guess I didn't get it after all).
The reason I created this is because each Broker or Marketer can work for different companies/Branches. My previous setup was:

Broker/marketer number
First Name
Last Name
Adress details (spread over several fields)
Function
Company 1
Branch 1
Company 2
Branch 2
Company 3
Branch 3

In the normalize explanation, it mentioned something about this. This already creates a problem for a broker who has four companies/branches he works with. This is the reason why I changed it. Maybe you see it differently and can advice a different change.

The reasoning behind the year tables:
A customer can choose to take insurance from January to March, not in April, again for two months,... Therefore, the DB needs to be able to show which months the client paid and which one he didn't (this is for claims purposes so that the company can look up when the customer paid). If I follow the suggested setup for that table, this would result in a lot of records (at least 12 a year per customer time 200 000 customers)

Thanks for the help because I have been struggeling with these problems for more than a week now

 

[Fastone]

I have another question:
Is it possible to let somebody for example enter a client ID and that the form or tabel shows the client first and last name?

 

[Fastone]

Some Good news. The query is working and it shows the data as required. I thank everybody for the help.

I would still appreciate it if somebody could answer that last question of mine.

But at the moment, I'm very

 

[boblarson]

The answer to your last question is yes. There are several ways to do this. One, is to set a combo box that displays the client names but is bound to the ID. Then when you select the name, you can have it change to the record that goes with that ID.

There's a lot of archived posts that deal with this.

I'm not totally sure about how to best deal with your problem about the year tables containing so many records. It might be better to have it set up like:

Client ID
Policy ID
Branch ID
Begin Date (you could use mm/yyyy)
End Date (you could use mm/yyyy)

 

[Fastone]

Ok, I'll look into that combo box story, thanks.

The suggestion you made would be quite difficult because it is not known when the policy will lapse. It should be a way so that it is assumed every month is paid and that only the months that aren't paid have to be manually adjusted.