Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    todays dumb question!!

    I have three tables....
    1 a list of establishments with a primary key
    2 a list of products with a primary kay
    3 a link table that only contains two foreign keys
    this is because an establishment may have many products and a product may be available thru many establishments.

    Now the problem, I have to produce a report that will put this data out for processing by the printers (using Quark I think). The design is a grid, establishments along the top of the page, products down the side of the page with a 'blob' where that establishment provides that product.
    I have tried to run this as one query but the query generator tells me that there are ambiguous outer joins.
    I have tried building the query with a sub query but then i dont get all rows returned ( that is i only get rows where the establishment supplies a product )
    Can anyone help please ?

  2. #2
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: todays dumb question!!

    Here's some SQL I generated by creating a cross-tab query in the northwind sample database. I used the products, orders and order details tables in this example which closely resembles your table structure - just replace orders with establishments. I used the customerID in the orders table as the column heading because there is a limit to the number of columns in Access97 - 255? HTH!

    TRANSFORM Count(Products.ProductID) AS CountOfProductID
    SELECT Products.ProductName
    FROM Products INNER JOIN (Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID
    GROUP BY Products.ProductName
    PIVOT Orders.CustomerID;

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: todays dumb question!!

    Paul, thank you, you have saved me from a weekend of beating my head on a wall !!!! I had got the same effect from a pivot table in excel but it was the crosstab that gave me the results i needed.
    thanks again

Posting Permissions

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