Ms-access – How to edit subform based on joined tables in Access 2010

join;ms-accessms-access-2010

I'm designing an Access 2010 web database, and I'm trying to create a subform that will display data based on joined tables. It displays alright, but I can't modify the data. The two tables are called Consortiums and PrincipleInvestigators. There's a many-to-many link between them, so I have a ConsortiumsPrincipleInvestigators join table.

I have a "consortium" form which displays the data related to a specific consortium, and has a subform within that form that displays the principle investigators part that consortium. I was able to successfully all the PIs, but when I try to add a principle investigator within that subform, it gives me an error: "Cannot add record(s); join key of table 'PrincipleInvestigatorsConsortiums' not in recordset. What gives? I imagine it's because I need to update both the PrincipleInvestigators table and the join table. (Also, MS, why can't I just use standard relational database stuff instead of this silly lookup field business?) Here's some screenshots of my table layout and my join table:

http://i.imgur.com/j4RJQ.png

Bleh. I feel noobish although I've done a decent amount of database projects, but the lookup fields and query builder is throwing me off. Does anyone know of any good tutorials on how to design queries?

Best Answer

There is really nothing that changes in terms of building a relationship between two tables. The fact that you use a GUI, a SQL DLL command, or now some relationship wizard changes nothing.

I mean who cares what the process is much to setup a relationship between two tables?

I mean, in client based you could type in something like this:

ALTER TABLE Cars
   ADD CONSTRAINT MyColorIDRelationship
   FOREIGN KEY (ColorID) REFERENCES Colors (ColorID)

So, now you use some wizard that is a few mouse clicks and this is some big deal? Really, who cares! No big woopy here, all we care is that you execute some command, or some wizard, or something here and you at the end of your day the result is your beloved relation between the two tables.

Check out the following video of mine where I "hook up" some existing related data into a web database. And I ALSO share how you can printout the related tables using the Access relationship window for a web database:

http://www.youtube.com/playlist?list=PL27E956A1537FE1C5&feature=plcp

So, at the end of the day, no need to get twisted up in some details of having to use some DDL sql command, some GUI relationships window, or some wizard. All you are doing as the above video shows is hooking up some tables and setting up a relation – nothing more, nothing less to worry about.

As for how to add child records that are to be related to a parent record? Well, in the past for the last 18 years, quite much every bird, dog, and beetle using Access would simply do this:

a) Create a main form based on the ONE main parent record table. There is NO NEED to build a query here, and in fact NO need to build a query that is a join of the two tables – this was never required and is not required. All you do is build a form based on the one simple table. We are done this part "a".

b) Create a form based on the ONE child table. There is NO NEED TO build a query here, and in fact NO need to build a query that is a join of the two tables – this was never required and is not required. All you do is build a form based on the one simple table.

c) open up first form (the form based on parent record table) and then in layout mode, now drag + drop in the child form from the nav pane.

The above a,b,c steps is how virtually EVERY parent to child setup in Access I seen done, and this long time forever setup CONTINUES to work 100% in web based applications.

In both cases (web or non web), the setup remains the same, the setup does not involve building quires, and the setup does not require ANY coding on your part.

As long as the link/master child settings are correct in the sub form control, then you are free to add child records to the child form and Access will do the rest of the dirty work of setting up and maintain the relation for you by setting the FK column in that child form for you.

So, how the basic setup works here has not changed in 18 years of using Access, and as such this does not change when building a web form here.

You don't need a query based on more than one table, and in fact JUST like in the past, the two forms will as a general rule will have their data source based only on the one table.

So in most cases there was never a need to even use a query for that one table that the form is going to be based on. This long time basic approach and setup has not change for web forms either.

In your case I would assume the main form is Consortium. You child form could be a continues form based on Principleinvegiartors. And in place of having to manually enter some PrincipleInvetigaor ID, you use a combo box based on table PrincipleInvetgioars. However, again in all cases, we are simply building forms that are based on a single base table.

Related Solutions

R – How to join (merge) data frames (inner, outer, left, right)

By using the merge function and its optional parameters:

Inner join: merge(df1, df2) will work for these examples because R automatically joins the frames by common variable names, but you would most likely want to specify merge(df1, df2, by = "CustomerId") to make sure that you were matching on only the fields you desired. You can also use the by.x and by.y parameters if the matching variables have different names in the different data frames.

Outer join: merge(x = df1, y = df2, by = "CustomerId", all = TRUE)

Left outer: merge(x = df1, y = df2, by = "CustomerId", all.x = TRUE)

Right outer: merge(x = df1, y = df2, by = "CustomerId", all.y = TRUE)

Cross join: merge(x = df1, y = df2, by = NULL)

~~Just as with the inner join, you would probably want to explicitly pass "CustomerId" to R as the matching variable.~~ I think it's almost always best to explicitly state the identifiers on which you want to merge; it's safer if the input data.frames change unexpectedly and easier to read later on.

You can merge on multiple columns by giving by a vector, e.g., by = c("CustomerId", "OrderId").

If the column names to merge on are not the same, you can specify, e.g., by.x = "CustomerId_in_df1", by.y = "CustomerId_in_df2" where CustomerId_in_df1 is the name of the column in the first data frame and CustomerId_in_df2 is the name of the column in the second data frame. (These can also be vectors if you need to merge on multiple columns.)

SQL Inner-join with 3 tables

You can do the following (I guessed on table fields,etc)

SELECT s.studentname
    , s.studentid
    , s.studentdesc
    , h.hallname
FROM students s
INNER JOIN hallprefs hp
    on s.studentid = hp.studentid
INNER JOIN halls h
    on hp.hallid = h.hallid

Based on your request for multiple halls you could do it this way. You just join on your Hall table multiple times for each room pref id:

SELECT     s.StudentID
    , s.FName
    , s.LName
    , s.Gender
    , s.BirthDate
    , s.Email
    , r.HallPref1
    , h1.hallName as Pref1HallName
    , r.HallPref2 
    , h2.hallName as Pref2HallName
    , r.HallPref3
    , h3.hallName as Pref3HallName
FROM  dbo.StudentSignUp AS s 
INNER JOIN RoomSignUp.dbo.Incoming_Applications_Current AS r 
    ON s.StudentID = r.StudentID 
INNER JOIN HallData.dbo.Halls AS h1 
    ON r.HallPref1 = h1.HallID
INNER JOIN HallData.dbo.Halls AS h2
    ON r.HallPref2 = h2.HallID
INNER JOIN HallData.dbo.Halls AS h3
    ON r.HallPref3 = h3.HallID

Best Answer

Related Solutions

R – How to join (merge) data frames (inner, outer, left, right)

SQL Inner-join with 3 tables

Related Topic