Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2007
    Location
    Bedfordshire, UK
    Posts
    64

    Unanswered: Joining tables in a select query problem

    I have a query in which 2 tables ("Job" and "Customer") are joined by their "CustomerName" attributes. The "Customer" table contains, among other things, the attribute "FaxNumber". One of the attributes in the "Job" table is "JobNumber", which uniquely identifies each job. Running the query returns, correctly, "CustomerName" and "JobNumber" (from the "Job" table) and "FaxNumber" (from the "Customer" table). All records in the database are displayed. However, if I enter a specific Job number into the criteria field for "JobNumber" on the query, I get no records at all. I want to be able to select a single record from its JobNumber.
    Why is this happening, and how can I fix it?
    Many thanks

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Post your SQL. We can be more helpful with that.
    Inspiration Through Fermentation

  3. #3
    Join Date
    Mar 2007
    Location
    Bedfordshire, UK
    Posts
    64
    Thanks. Here's the SQL for this query:

    SELECT Job.CustomerName, Job.JobDescription, Job.JobNumber, Customer.FaxNumber
    FROM Job INNER JOIN Customer ON Job.CustomerName = Customer.CustomerName
    WHERE (((Job.JobNumber)=[Forms]![Job]![Text1]));

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    What data type is the JobNumber field in the Job table?
    "Number" implies that it's a numeric field, but...
    Inspiration Through Fermentation

  5. #5
    Join Date
    Mar 2007
    Location
    Bedfordshire, UK
    Posts
    64
    Yup - JobNumber is a "number" data type (Long Integer).
    All other attributes are "text" data types.

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    If you manually put the actual JobNumber in place of [Forms]![Job]![Text1]
    Do you get the right result?
    Inspiration Through Fermentation

  7. #7
    Join Date
    Mar 2007
    Location
    Bedfordshire, UK
    Posts
    64
    OK - I am officially a numpty! I'd forgotten that I had renamed the Text Box, and not changed it in the criteria field.
    It works fine now.

Posting Permissions

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