Join statment help.. Please!

Results 1 to 9 of 9

Thread: Join statment help.. Please!

  1. #1
    Join Date
    Dec 1969
    Posts
    339

    Default Join statment help.. Please!

    I&#039;m bald from pullin my hair out! I posted this 2 days ago and I tried to hack it out from there but I keep having problems. It&#039;s my first attempt at perfroming a JOIN.<BR>Scenario:<BR>1 Access2k DB<BR>2 tables<BR><BR><BR>This table is the main data storage table. All form input info is kept here. 4 subject fields values can be null and there can be a value for all fields. <BR><BR>Bibliog<BR>ID subject1 subject2 subject3 subject4<BR>1 10 20<BR>2 20 50<BR><BR>*that&#039;s a an example of the first two records<BR><BR>Subjects<BR>ID Subjects CodeName<BR>1 10 Accounting<BR>2 20 Science<BR>3 50 Geology<BR><BR>There is a sample of what&#039;s in table 2. This is used to dynamically populate drop down boxes for the forms which submit those values to table1. <BR><BR>Here&#039;s the problem. When I submit to table one, all the values are the sujbect numbers not the CodeName.. now what I need to do is to retrieve the data on a form that will update, say, record one as well as use the dynamically populated dropdown box that shows the CodeName for that subject#. So I am able to update just fine.. BUT!, when the form comes up I need to pull the value of the subject from table1 for that Item and the CodeName from Table2 that = the Subjet value.<BR><BR>I hope you can understand my ranting. BTW, The data already exists are I would have done this differently. So I&#039;m sort of stuck. <BR>Any help would be appreciated. Oh yeah, i tried making the query in access but it kept giving me a type mismatch error.. <BR>Thanks<BR>-K

  2. #2
    Join Date
    Dec 1969
    Posts
    25

    Default RE: Join statment help.. Please!

    Im a bit confused on what exactly you want. Is each drop down box for only one ID from the Biblog? What do you want in each box? For Biblog ID 1 would you want this?<BR><BR>10 - Accounting<BR>20 - Science<BR><BR>Should these be in the same drop down box?<BR>

  3. #3
    Join Date
    Dec 1969
    Posts
    339

    Default RE: Join statment help.. Please!

    Here is the first drop down select code as it stands now. "sSelect" is my SQL select statement that pulls the data from table 2 to populate the drop down box. nSelect pulls that stored data from table1. As you can see the data BEFORE sSelect is coming from Table1 (the stored data). Subject1 is a numeric field. <BR><BR> &#060;select size=1 name="subject1"&#062;<BR><BR>***What I need to do here is to have RS("subject1") pull from table 1 and match up that RS with the Text equivalent CodeName of the subjects1 value**** <BR><BR>&#060;% nSelect %&#062;<BR> &#060;option value="&#060;%=RS("Subject1")%&#062;"&#062; &#060;%=RS("CodeName")%&#062;<BR>&#060;/option&#062;<BR> &#060;%<BR> <BR> sSelect<BR> While NOT RS.EOF <BR> %&#062;<BR> &#060;option value="&#060;%=RS("Subjects")%&#062;"&#062; &#060;%=RS("code")%&#062;&#060;/option&#062;<BR> &#060;%<BR> RS.MoveNext<BR> Wend<BR> Rs.Close<BR> <BR> nSelect<BR> %&#062;<BR> &#060;/select&#062;<BR><BR>Sub sSelect <BR>SQL="SELECT subjects,code FROM Subjects"<BR>set RS=MyConn.execute(SQL)<BR>end Sub<BR><BR>Sub nSelect<BR>SQL="SELECT * FROM BIBLIOG WHERE ID="&QueryID&" "<BR>set rs=MyConn.execute(SQL)<BR>end sub<BR><BR>Of course this is not going to work as it stands because the CodeName field is non existant in the first table. It only exists in the second table. SO I have to figure out how to join them and re-write my select statements. I know this is really confusing, But i do appreciate the help.

  4. #4
    Join Date
    Dec 1969
    Posts
    25

    Default RE: Join statment help.. Please!

    This should do it.<BR>Sub nSelect<BR>SQL="SELECT A.Subject1, B.CodeName " &_<BR>"FROM BIBLIOG WHERE ID="&QueryID&" " &_<BR>"AND A.Subject1 = B.Subjects"<BR>set rs=MyConn.execute(SQL)<BR>end sub


  5. #5
    Join Date
    Dec 1969
    Posts
    339

    Default RE: Join statment help.. Please!

    I&#039;m assuming I replace A and B with my respective table names?<BR><BR>when doing so I get this error.<BR><BR>Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)<BR>[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.<BR>/union/admin/updbib.asp, line 12<BR><BR>line 12 is the execution of the SQL statment.<BR><BR>Thanks for your assistance.. I can feel I am getting closer. I also put in the second table name in the FROM clause but it gave me an error as well.."ID can have more than one blah blah". I&#039;m starting to understand joins better however.. Thanks :)

  6. #6
    Join Date
    Dec 1969
    Posts
    25

    Default RE: Join statment help.. Please!

    I&#039;m sorry, I messed it up (didn&#039;t finish it). The A and B are just aliases for the table names. I guess it is just a habit to name them that. A actually refers to the BIBLIOG table and B the Subjects table. You set the aliases in the FROM line where it says BIBLIOG A, Subjects B. That is just telling the query what A is for the rest of the query. <BR><BR>You should be able to just paste this in and it should work.<BR><BR>Sub nSelect <BR>SQL="SELECT A.Subject1, B.CodeName " &_ <BR>"FROM BIBLIOG A, Subjects B " &_<BR>"WHERE ID=" & QueryID & " " &_ <BR>"AND A.Subject1 = B.Subjects" <BR>set rs=MyConn.execute(SQL) <BR>end sub

  7. #7
    Join Date
    Dec 1969
    Posts
    339

    Default RE: Join statment help.. Please!

    OMG OMG OMG OMG!! THANK YOU THANK YOU THANK YOU THANK YOU!!!!<BR><BR>i had to change one thing, but that was it<BR><BR>Sub nSelect <BR>SQL="SELECT BIBLIOG.*, Subjects.Code " &_ <BR>"FROM BIBLIOG, Subjects " &_ <BR>"WHERE ID=" & QueryID & " " &_ <BR>"AND BIBLIOG.Subject1 = Subjects.Subjects" <BR>set rs=MyConn.execute(SQL) <BR>end sub <BR><BR>and NOW, I am going to look at this and try to understand just what the frack is taking place in that statement! :) HOLY COW!! Next level shyte!

  8. #8
    Join Date
    Dec 1969
    Posts
    25

    Default RE: Join statment help.. Please!

    Glad it works. All it is doing is saying to only select the records from the tables where Subject1 is equal to Subects and the id is equal to the query id. <BR><BR>Try this link if you have any more problems with SQL, it is pretty helpful<BR><BR>http://w3.one.net/~jhoffman/sqltut.htm

  9. #9
    Join Date
    Dec 1969
    Posts
    339

    Default RE: Join statment help.. Please!

    Thanks! Bmk&#039;d! The next thing I need to do is have that statement compare ALL four subject fields from the first table to the 2nd table.. How would I do that? Do I have to make separate statments?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •