Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
BlackAngel87
Starting Member
1 Post |
 Posted - 2006-04-11 : 06:20:44
|
I have a little problem with MS Access. I am building a little database for my father in which 3 zero-to-one relations are neccessary, but the thing is that Access doesn't seem to understand such relations exist, at least I can't find any way to set this type of relation.Does anyone know a solution to this problem?the database looks a bit like this:[customer] [sample] [method1] ID 1-N CustomerID /-1 SampleID ... SampleID 0-/ | [method2] |--1 SampleID | \ [method3] \-1 SampleID As you can see each sample belongs to 1 customer, and each customer has many samplesbut then each sample is processed with 1 to 3 out of 3 methods, giving different sets of results.So for each sample there is a record in at least 1 of the methods.With the 1-to-1 relation Access makes there is the problem that there can be samples for example not having a corresponding record in [method1] which gives referential integrity problems :S[edit]By the way, MS Access is the only available program for a database :( otherwise I would have installed/used a real database ;) [/edit] |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-04-11 : 08:41:22
|
| The problem is in your design. Instead of using three different tables to represent the methods, you should use a single table and establish a one-to-many relationship from samples. Or, create a table listing all three method variations as separate records and then create a many-to-many relationship with samples via an intermediary table.If I understand you correctly, what you are trying to do cannot be accomplished in any database engine purely through relational integrity. You would need some code to enforce this. |
 |
|
|
|
|
|
zero-to-one relations