Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Getting Data From A control Table (Access 2000.)

    I have a table which states which consultant receives a new product on there next order. The table has the ProductID and ConsultantID. What I want to do is show all the products that are in this table. For some reason, I can only get the ProductName or product color to show up once in the results, so I endup getting results as below: The two tables themselves are not related in the system, but an outjoin is used in this case.

    Any ideas would be great

    Thanks
    Attached Images Attached Images

  2. #2
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting Data From A control Table (Access 2000.)

    Second Image
    Attached Images Attached Images

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Getting Data From A control Table (Access 2000.)

    The question is, what does your SQL look like and what are you actually trying to show? Do you want to see a list of unshipped products or are you looking for a list of unshipped products and the consultants they whould go to or simply a list of all products in the table, however many times they occur? If you don't have any referential integrity between these tables, then I would guess that some of the product IDs in your table do not have matches in the Products table.
    Charlotte

  4. #4
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting Data From A control Table (Access 2000.)

    SELECT NewProductsList.Received, NewProductsList.NewProductID, Products.Name, Products.Color, NewProductsList.ConsultantID, Consultants.FirstName, Consultants.LastName
    FROM Consultants INNER JOIN (NewProductsList LEFT JOIN Products ON NewProductsList.NewProductID = Products.ProductID) ON Consultants.ConsultantID = NewProductsList.ConsultantID
    WHERE (((NewProductsList.Received)=False));

    I would like to see a list of all unshipped products and the consultant associated with the unshipped product. With the result I get, I only get the produt name once, they the rest of the time the productname field is blank

    (I did not have the consultant table in before, but it is now)

  5. #5
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting Data From A control Table (Access 2000.)

    I got the problem solved. I have some bad data in the table <img src=/S/aflame.gif border=0 alt=aflame width=16 height=16> . Once of the products no longer existed.

    I thought it was odd that it was not working

    Thank for your help

Posting Permissions

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