Results 1 to 9 of 9

Thread: Query Problems

  1. #1
    Join Date
    Nov 2003
    Posts
    300

    Unanswered: Query Problems

    I have three tables, one is a supplier table that has a supplier ID and supplier name, next is a product table with product name and ID, the third table is supplier & product table that lists the product ID, supplier #1, supplier #2, supplier #3..

    My results in the query of the 3 tables gives me:

    product ID, product Name, Supplier ID 1, Supplier ID 2, Supplier ID 3...

    I WANT THIS

    profuct ID, Product Name, Supplier Name 1, Supplier Name 2, Supplier Name 3

    How do I get the supplier name to printe and not ID??

    Here is my SQL:
    SELECT Products.Name, Products.[Product Key], [IF: Suppliers].SupplierName, [Prod & Suppliers].[Primary Supplier ID], [Prod & Suppliers].[Alternate Supplier ID 1], [Prod & Suppliers].[Alternate Supplier ID 2], [Prod & Suppliers].[Alternate Supplier ID 3]
    FROM Products INNER JOIN ([Prod & Suppliers] INNER JOIN [IF: Suppliers] ON [Prod & Suppliers].[Primary Supplier ID] = [IF: Suppliers].[Supplier ID]) ON Products.[Product Key] = [Prod & Suppliers].[Product Key]
    GROUP BY Products.Name, Products.[Product Key], [IF: Suppliers].SupplierName, [Prod & Suppliers].[Primary Supplier ID], [Prod & Suppliers].[Alternate Supplier ID 1], [Prod & Suppliers].[Alternate Supplier ID 2], [Prod & Suppliers].[Alternate Supplier ID 3];

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Change your supplier/product table.

    ProductSupplier:
    --------------
    product_supplier_id
    product_id
    supplier_id
    supplier_type_id (optional, this would be where you define the "order" of suppliers)

    With this structure, you could then use a crosstab query to produce the results you want.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Nov 2003
    Posts
    300
    This is NOT what I am looking for. I am getting all the fields that I need , CrossTab limits the # of fields..

    I need to have the Product Names display and not the Product IDs.

  4. #4
    Join Date
    Nov 2003
    Posts
    300
    sorry, correction

    Supplier Names not Supplier IDs

  5. #5
    Join Date
    Nov 2002
    Location
    Norway
    Posts
    239
    Add the supplier table three times in your query.
    Set up references between Supplier ID 1, Supplier ID 2, Supplier ID 3 to each of the supplier table ID copys.

    That would do the job.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by databasemon
    This is NOT what I am looking for. I am getting all the fields that I need , CrossTab limits the # of fields..

    I need to have the Product Names display and not the Product IDs.
    How does a crosstab limit the # of fields?

    Read this

    Protip: Don't be so quick to dismiss something that doesn't immediately point you in the direction you're currently heading, it's quite possible that someone is trying to steer you away from a brick wall...

    With your current schema, you will have to use a subquery for every single supplier you wish to get a name for. With a normalized design, you can do it all in one shot and produce exactly the same data, I'm not sure where your perception of a field limitation comes from...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Nov 2003
    Posts
    300
    I appreciate your comments Teddy, but the tables are normalized. I did follow your advice created a new table and used the wizard to create a crosstab query, but as I mentioned before, it did not allow me to pick all the information I needed.

    I did try the multiple subqueries, that worked but then I checked back in the forum for other posts and Vestigo's suggestion worked! I just needed a quick fix to this problem.

    Again, I appreciate the refresh of basic information but there are often times I don't have the luxury of recreating tables or such when I just need to restructure a query or SQL statement. Next time I'll try to state if I need a bandaid fix or a major rework for a squirting artery.

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    good to hear you got it worked out!

    I have that little "best-practices" gremlin that always screams in my ear when I see certain table designs, I just can't help myself sometimes...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Nov 2002
    Location
    Norway
    Posts
    239

    Smile

    This forum has helped me a lot, I'm glad I could give a little back.

    sincerely
    Vestigo

Posting Permissions

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