Linking Tables

[Patriot_BC]

I have the following structure

Table #1
Issue

Table #2
Recommended Changes (Multiple recommended changes per issue)

Table #3
Notes (Multiple Notes per recommended change)

How would I link this to avoid creating redundant relationships and duplicate data? I originally had table 1 & 2 linked and then 2 & 3, but this seemed to cause problems. Sorry for the basic question, but I want to blow up this database at this point !

Shannon

 

[Andrew Fergus]

Hi Shannon

I don't know if this will help, given I can't see your table structures, but if you format your 'notes' as a memo field, then you can get 65,535 characters into that field (I would expect that would be more than enough). When you enter data into a memo field you can press 'Ctrl' and 'Enter' at the same time to get a new line (i.e. a carriage return), so that you can get some primitive formatting into that field. If you do this then you could include the 'notes' in table 2 and eliminate table 3. Incidentally, when using the memo field on a report set the 'Can Grow' property to yes to display all of the data in that field, which means you don't have to reserve a large portion of your report for that one field.

However, given you have a one-to-many-to-many relationship then you have set up your tables correctly (assuming you used a unique id in table 1 and linked on that to table 2, and linked another unique id from table 2 to table 3). If this hasn't helped, can you elaborate on the problems?

HTH, Andrew.

 

[Patriot_BC]

Update...Fixed

I ended up recreating the db and it worked. Somewhere the relationships were very broken. Also, I used another person's db and it didn't seem to ever work properly. I just made sure only one of the linking fields was an autonumber and everything is good.