could someone please help with subquery

[chris davies]

Hi Folks,
I am trying to create a subquery which populates an intermediary table using the primary keys from four other tables.

Insert into Person_Character_Film_Role (PersonID, CharacterID, FilmID, RoleID) VALUES ('" & PersonID & "', '" & CharacterID & "', '" & FilmID & "', '" & RoleID & "') WHERE Person.PersonID = (SELECT PersonID FROM Person WHERE Person = Me.txtPerson) AND Character.CharacterID = (SELECT CharacterID FROM Character WHERE Character = Me.txtCharacter) AND Film.FilmID = (SELECT FilmID FROM Film WHERE Film = Me.cmbFilm) AND Role.RoleID = (SELECT RoleID FROM Role WHERE Role = Me.cmbRole);

It looks horrendous I know, and it most probably is since it doesn't work. Could someone please spare the time to look at this and tell me why I get a "MISSING SEMI COLON " error

 

[namliam]

The One thing that jumps out at me is that you are quoting all the IDs. IDs are usually number fields, which you dont want/need to quote.

Second thing is, Insert into .... values .... is meant to insert 1 record of values.
If you want to use a select statement to fill a table, use Insert into .... select

The "Where" clause looks horrible...

WHERE Person.PersonID = (SELECT PersonID FROM Person WHERE Person = Me.txtPerson)
AND Character.CharacterID = (SELECT CharacterID FROM Character WHERE Character = Me.txtCharacter)
AND Film.FilmID = (SELECT FilmID FROM Film WHERE Film = Me.cmbFilm)
AND Role.RoleID = (SELECT RoleID FROM Role WHERE Role = Me.cmbRole);

What is it you are trying to do? This where clause is not going to work at all...

 

[boblarson]

First of all you don't need to encapsulate everything with single quotes. The ID's you are populating are Long Integers and therefore you would just need

VALUES (PersonID & ", " & CharacterID & ", " & FilmID & ", " & RoleID & ") WHERE

Not sure about the rest at the moment.

 

[chris davies]

Hi
I want to Insert four values which are primary keys from other tables into an intermediary table

 

[namliam]

But you are fetching them from your form... for some reason...

 

[boblarson]

I just posted another sample to your other post. I really think you are trying to go about this in the most painful way possible. In the most current sample I used a subform to add the details to your junction table. For adding characters you would create a form for characters and films form. But, if you want to add persons and their roles then you can just use a subform which then can do all of the work for you.

 

[chris davies]

I see what your saying, but the thing that is niggling me the most is that all the tables are set for cascading updates and deletes so if the main tables have data posted/written to them, shouldn't the intermediary table be updated automatically?

I was hoping that I wouldn't have to use sub-forms and could put it all together on the one form. Is this not possible?

 

[boblarson]

Nope, Cascading updates are not what you are thinking they are. They only update existing information if the key changes but not adding information.

Actually you can put it all together on one form, using subforms and perhaps a tab control to organize it. You can make it so it doesn't look like a subform if you set the special effect to flat (instead of sunken) and change the subform control's border property to Transparent.

 

[chris davies]

HI there again, I tried what you suggested, but if I create a form this way, it adds the persons name again and the role and film and character data, all I want to be able to do is add the ID's to the intermediary table (characterId, roleID, filmID and personID) to avoid any replicating data. The thing that bugs me the most is that if I add the data manually, it's perfect. There must be a way to add these four ID's to the intermedairy table without affecting the other tables surely?

 

[chris davies]

OK peeps, I'e nailed the problem. After taking a long hard gander at the northwind database (an excellent piece of work that is I must say), I notice that some of the foriegn keys, which were numbers, didn't actually display as numbers, but as the actual records (eg product as opposed to productid) themselves.
having sussed this bit out, I changed the format of the Id's in the intermediary table, and hey presto, add a new record without adding any redundant data by just adding an entry to the intermediary table via the use of four combo boxes and it works a treat. Now where did I put my beer....

BTW, if you'd like to see what I've done, post here and I'll post a smple of the DB.

thanks for all your help anyways, it has opened my eyes to other problems and possible solutions.

 

[georgedwilkinson]

That's almost terrifying. Glad you got it working.