2 Tables that are alike but I need reverse data in both. 1

[magister011]

I have a rather unique problem that I have seen solved on a forum yet. I am using MS Access 2003. I have three tables.

tblProject
-idsProjPK = Primary Key
-chrProjNum = Project Number
-chrProjNam = Project Name
-chrProjContNum = Project Contract Number

tblIntermediate
-idsIntPK = Primary Key
-lngzIntProjFK1 = Foreign Key from tblProject
-lngzIntProjFK2 = Foreign Key from tblProject1

tblProject1
-idsProjPK = Primary Key
-chrProjNum = Project Number
-chrProjNam = Project Name
-chrProjContNum = Project Contract Number

My relationships are a one to many from tblProject.idsProjPK to tblIntermediate.lngzIntProjFK1 and a one to many from tblProject1.idsProjPK to tblIntermediate.lngzIntProjFK2

The Master Field is tblProject.ProjPK
The Child Field is tblIntermediate.IntProjFK1

I have a form and a subform for the user to input the data. The subform contains the fields from tblProject1
-idsProjPK = Primary Key
-chrProjNum = Project Number
-chrProjNam = Project Name

and the fields from tblIntermediate
-lngzIntProjFK1 = Foreign Key from tblProject
-lngzIntProjFK2 = Foreign Key from tblProject1

The form contains the subform and the fields from tblProject
-idsProjPK = Primary Key
-chrProjNum = Project Number
-chrProjNam = Project Name
-chrProjContNum = Project Contract Number

Right now when the user enters data, the data goes into the correct tables. What I want to do is to be able to add data to the tblIntermediate but in reverse. I want to be able to enter data on the form and sub form and be able to have the data inputted to the tbl.Project and tblProject1 with both the

Master Field = tblProject.ProjPK
Child Field = tblIntermediate.IntProjFK1

and

Master Field = tblProject1.ProjPK
Child Field = tblIntermediate.IntProjFK2

I hope that I am explaining this correctly. I have been working on this for about a month with no luck at all in doing this both ways, unless I actually enter the data into the tblIntermediate, which is what I do not want to do. I also would not like to have to enter any of the data twice.

If anyone has any suggestions, I would greatly apprciate it.

Thank you very much for your time,
magister011

 

[MajP]

1) This is not a very unique problem, it is actually a very common data structure. It is a traditional "self referenceing" many to many. Unfortunately you designed it incorrectly.
2)You should only have one table. A project is a project, they are defined by the same fields. Two tables with identical fields is always an indication of an incorrect data base design. At a minimum you might need one field such a "projType", to designate a difference between a project1 project and a project2 project. Your current design makes it harder than needs to be. There are a lot of good resources on relational databases and normalized data base design out on the web. You should read up on them.
2) You have a Many to Many relationship. Many projects in table 1 are related to many projects in table 2.
3) A table can be self referencing. If you look at the Northwind database there is a table of employees. Each employee reports to a "boss" employee. So in the employee table is a key relating back to a record in the employee table. In your case it is a many to many. So a key in tblIntermediate should relate back to the projects table.
3) What is your relationship? One way or two way. If project A is related to B, and B is related to A in the same way, then you have a two way relation and you will have to write some code so that when you create a link like:

lngzIntProjFK1 lngzIntProjFK2
A B
it automatically creates
B A
in the table.

This may or may not be true. It depends on your buisiness rules. If the relation is parent to child then it is one way. A has children B and C, but A is not a child of B or C. If it is sibling to sibling it is two way. A is related to B, B is related to A. I am sensing it is two way.

Here is the solution:

http://www.4shared.com/file/145299913/a7ff428a/tblProject.html

I have a single projects table. I have a form where I can enter a project and in the below subform see all the related projects. I can select a project that is not yet related to my project, and then relate it to my project. I added the code so that if I relate ProjectB to ProjectA it also relates ProjectA to ProjectB. If you want to remove a relation there exists a button. Remove B from A and it will remove A from B.

 

[magister011]

I think this might work. I am doing this project for work so let me take this and have my co-worker, who is assisting me with the design, take a look at this. I will let you know on Monday. It looks very promising. What we are really hoping to gain is having the projects table have data with both an A to B and a B to A relationship. We thought that Access could do this without making two duplicate tables. We were worried if we had two tables that one could get "unsynchronized" with the other and with lots of data inputted, how would we get the tables "synched" again without losing data. Again, I am pretty sure the solution you gave me will work for exactly what we need.

Thank you for your assistance,

Dave

 

[MajP]

If you have a many to many like this

ID1 ID2
ProjectA ProjectB

This says A is related to B, and depending on your query it does not necessarily say anything about the relationship of B to A.

But you can design it that way. You could simply make one of the inputs. And to ensure you have the other you do a union query and reverse the order.

ID1 ID2
projectA ProjectB

Something like
qryAllRelations

Select ID1, ID2 from tblIntermediate union select ID2 as ID1, ID1 as ID2 tblIntermediate

and this would give you
ID1 ID2
ProjectA ProjectB
ProjectB ProjectA

 

[magister011]

The form and subforms work great! They are exactly what we want to do. Now with your last message I am struggling a bit. Should this qryAllRelations be done in VBA like the other ones or just a regular SQL Coded query? I tried for several hours trying to use your examples in VBA code to make the requery work, but I was not able to. I tried making a command button. All I got was a bunch of case errors. Not sure if I had the syntax correct.

Dave

 

[MajP]

My last post was just to say that there are two ways different ways to do this. I demonstrated the first method.
1) Either, every time you put something like ProjectA ProjectB in tblintermediate, you also use code create a record: ProjectB ProjectA
2) Or you could just make the first relation: ProjectA ProjectB. Just like a standard parent child form/subform.

Then with only the one relations, you could build the query

qryUnionRelations

SELECT A.lngzIntProjFK1, A.lngzIntProjFK2
FROM tblIntermediate as A UNION Select B.lngzIntProjFK2, B.lngzIntProjFK1
FROM tblIntermediate as B;

And use that query in subform

SELECT qryUnionRelations.lngzIntProjFK1, qryUnionRelations.lngzIntProjFK2, tblProjects.chrProjNum, tblProjects.chrProjNam, tblProjects.chrProjContNum
FROM qryUnionRelations INNER JOIN tblProjects ON qryUnionRelations.lngzIntProjFK2 = tblProjects.idsProjPK;

Either way will work. Either you build a table that has

ProjectA ProjectB
ProjectB ProjectA

or you create a single record

ProjectA ProjectB

And use a union query to make
ProjectA ProjectB
ProjectB ProjectA

Either design works. I prefer the first solution where I create the forward and backward relation in the table because a union query is a non updateable query.

The code I posted was only a demo of an idea, you probably could get a lot fancier. I think people try to do too much on a single form: navigate, add records, create relations, edit/delete. I usually seperate the functions, but make the navigation seamless. I would have a form to see current project relations, but have a pop up to edit and a seperate area to add new.

What are you having trouble with specifically.