Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2007
    Posts
    16

    Question Unanswered: Matching records from a query to a table records

    Hi

    I am tryig to match records from a query in access to records stored in a table. I have a little problem which i am trying to solve?

    Table1
    -manufacturer
    -model (primary key)
    -picture

    Query1
    -Model (this is in a query as it has been split from other data using the InStr and left function)
    -price

    I have created a relationship between the 2 model fields in the query and table in query2:
    Which has the following fields:-
    -manufacturer
    -model
    -picture
    -price

    Then I changed the view to data sheet the message box appeared saying “Invaild procedure call” and all data in the fields is changed to “#Name?”
    Can someone please help me out cause I really need to solve this problem ASAP. Or is there another way to match these records.
    Thank you; any help will be much appreciated.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Fancy posting the actual SQL of the query in question?
    You know how you select design view in the top left? Well click the little arrow next to that and select SQL
    George
    Home | Blog

  3. #3
    Join Date
    Jul 2007
    Posts
    16

    Sql

    Query2
    SELECT [Query1].Manufacturer, [Query1].Price, [Query1].Deal, [Query1].Model, Table1.picture
    FROM Table1 INNER JOIN [Query1] ON Table1.Mode; = [Query1].Model;
    Last edited by Weebot; 07-30-07 at 14:14.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    How about the SQL for Query1?
    George
    Home | Blog

  5. #5
    Join Date
    Jul 2007
    Posts
    16
    Query1
    SELECT Table2.Manufacturer, Table2.Price, Table2.Deal, Mid([Deal],InStr([Deal]," on ")+4,InStr([Deal]," (")-(InStr([Deal]," on ")+4)) AS Model
    FROM Table2;

    Query2
    SELECT [Query1].Manufacturer, [Query1].Price, [Query1].Deal, [Query1].Model, Table1.picture
    FROM Table1 INNER JOIN [Query1] ON Table1.Mode; = [Query1].Model;

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I think this may do what you want. Let me know
    Code:
    SELECT Table2.Manufacturer, Table2.Price, Table2.Deal, Table2.Model, Table1.picture
    FROM Table1
     INNER
     JOIN Table2
     ON Table1.Mode = Mid(Table2.Deal,InStr(Table2.Deal," on ")+4,InStr(Table2.Deal," (")-(InStr(Table2.Deal," on ")+4))
    George
    Home | Blog

  7. #7
    Join Date
    Jul 2007
    Posts
    16
    Thank you

    Sorry about the late reply
    The SQL works exactly how I want it to but still after a second or so in design view says "invalid procedure call" then all the data changes to “#Name?”

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Errr... yewhat?

    I tell you what, upload a cut down version of your database (basically all you'll need is the two tables and query)... Compact the database, zip it up and upload it here.
    George
    Home | Blog

  9. #9
    Join Date
    Jul 2007
    Posts
    16
    Thank you very much for your help

    I have done what I need to do by using the following SQL:-

    SELECT Table2.Manufacturer, Table2.Price, Table2.Deal, Table2].Model, Table1.picture
    FROM Table1 INNER JOIN Table2 ON Table2.Model Like "*" & Table1.Model & "*"

Posting Permissions

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