Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297

    Unanswered: Linked tables and Inner Join Queries

    Hi once again!

    I'm having a bit of, not really trouble, but lack of understanding on the whole linked tables & join queries thing.

    Lets set the scene quickly...

    2 Tables (there's more records in them [obviously], but this is all that we're concerned about):

    Code:
       MAIN   |   PRODUCTS
    ----------+--------------
        ID    |  vProdName
       pCode  |    pCode
    pCode are the fields that I want to be linked, which I've done by setting up a relationship between the two tables for those fields.

    I've also made what I assume to be a joined query, by adding 'pCode' from 'MAIN', and both 'vProdName' and 'pCode' from 'PRODUCTS' into a query, named 'main2Product'. I assume that's what I'm meant to do (I'm using Access 2000), as the table produced in the query will only update if there's a 'pCode' that's matching in both tables.


    Now that the scenes set (hopefully), I can proceed.

    I have a dynamic form setup. In this form I have a combo box, called 'productSelect', which is populated at the moment with the 'pCode' from 'MAIN'. If it helps, the SQL used is:

    Code:
    strPSQL = "SELECT [pCode] " & _
              "FROM main " & _
              "WHERE [ID] IN (" & ids & ")"
    Which will populate the combo box with (for example):

    Code:
    567172
    562234
    672344
    BF-425-552

    Now, what I want ideally to happen, is to list the corresponding 'vProductName' record field for the corresponding 'pCode' in the 'PRODUCTS' table.

    So the combo box will look instead like:

    Code:
    Industrial sized coat hanger
    Chinese takeaway menu (22")
    Salad spinner
    Rubber tube (legacy item)

    I'm not really sure what the best way to go about this is. I'd love to do it just by modifying my SQL statement a little bit, in which case any insight would be great as I'm struggling to find relevant info through google.

    The real issue comes from the fact that I need to retain the 'ID's associated with the 'MAIN' table. Eventually a user is going to input into an empty field on the 'MAIN' table, and this combo box is used to identify the product that the user is associating their input with.


    I realise I could create a recordset that iterates through the 'PRODUCTS' table, looking for the product code, for each one, and add that to an Array, then iterate through the Array adding each value to the 'productSelect' combo box, but that's basically a hack, and doesn't require any of this relationship stuff to be setup.


    If anyone could shed some light on the correct way to go about doing this, I'd be extremely grateful. If you need any more info, please let me know, but I think I've included everything that's relevant.

    Thanks a lot guys!
    Last edited by kez1304; 06-30-11 at 09:48. Reason: typo

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Try adding the description:

    strPSQL = "SELECT [pCode], vProdName " & _

    Change the Column Count property to 2, leave the Bound Column as 1 and the Column Width's property to

    0"; 2"

    That should display the text to the user but save the code to the table.
    Paul

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Sorry, missed the second table. Also change this line:

    "FROM main INNER JOIN PRODUCTS ON main.pCode = PRODUCTS.pCode " & _
    Paul

  4. #4
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    I have created something like this before, if i follow correctly, you want a form, where you can type in a product code and below this the combo box shows the results?

    in the past, i have set this up using a seperate table for queries, and having this table in the query with the table from which we want to match the data with, and in the query do a record to record match eg:

    [query-record] = [record]

    Then you can use this query to create (with the wizrd if you desire) the combo box, if you need it to display text instead of part numbers, go into the query editor and swap the name column for the id column and this should resolve, any probs, let me know

    dan
    sometimes simple is best.... and i'm just a simple fellow.

  5. #5
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297

    Thumbs up

    I think I've got it working now. I was getting errors for a while, most of them because I used slightly longer names than those listed in my example (for ease of assistance really).

    One thing though, I was getting an error saying:

    The specified field '[Product Code]' could refer to more than on table listed in the FROM clause of your SQL statement
    From the code:

    Code:
    strPNSQL = "SELECT [Product Code], [Product vName] " & _
               "FROM main INNER JOIN ref_products ON main.[Product Code] = ref_products.[Product Code] " & _
               "WHERE [ID] IN (" & ids & ")"
               
               
    Me.product.RowSource = strPNSQL
    I understand where it's coming from, as I'm asking it to retrieve [Product Code], which is found in both the 'products' table and the 'main' table, and it's not sure which one to grab.

    It doesn't really matter, as I'm not interested in showing that [Product Code] field in the combo box (your setup [if my understanding is correct], is to split each row into two, and hide the first column anyway), but for future reference, do you know how I'd get the code to just grab either of the [Product Code]s, as they're both the same anyway? I can envisige the splitting and double entering of related data into the same combo box to be pretty useful in the future.


    For potential future readers, my working code (that produces the desired results) is:

    Code:
    strPNSQL = "SELECT DISTINCT [Product vName] " & _
               "FROM main INNER JOIN ref_products ON main.[Product Code] = ref_products.[Product Code] " & _
               "WHERE [ID] IN (" & ids & ")"
    To those involved, I thank you!

  6. #6
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Rubbish...

    All was well, now I'm getting an Error 2113, "The value you entered isn't valid for this field", "For example, you may have entered text in a numeric field or a number that is larger than the FieldSize setting permits".

    Anyone got any suggestions? I've tried just about everything I can think of, and haven't changed anything major that I can think of.

  7. #7
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297

    Talking

    Typical, pulling my hair out, post, then instantly fix it. I set the row source to 'main'. Not sure why it was giving me an error though... Maybe because it couldn't find the 'Product vName'? Even though it was listed in the box itself, I just couldn't select it without getting an error.

    Can anyone shed some light?

    Thanks once again

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Can you post the db, or a representative sample?
    Paul

Posting Permissions

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