Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2005
    Posts
    23

    Unanswered: building a summary query for a report

    I have 4 tables that I want to bring together for a summary report.
    tbl_vendors
    tbl_tire_Orders
    tbl_Brake_Orders
    tbl_Exhust_Orders

    I tried to build the query so every vendor from tbl_Vendor showed regardless of orders and then count the numbers of orders from each table by vendor. So the query would have four columns
    Vendor name
    count of tire orders
    count of brake orders
    count of exhust orders

    However when I do that the counts add together and repeat in each column.

    For example:
    it would read
    vendor Tires Brakes Exhust
    A 11 11 11
    B 9 9 9

    Instead of
    vendor Tires Brakes Exhust
    A 4 4 3
    B 4 4 1

    I don't know how to fix this other then creating a seperate query for each count through a make table then bring them all back together. Thanks for any help you can provide.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what is your SQL?
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2005
    Posts
    23
    I didn't write sql. I just brought the four tables into query design view. I joined the three tables to the vendor table showing all records from the vendor table and any that match from the joined table.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Open the query, and change to SQL view.
    George
    Home | Blog

  5. #5
    Join Date
    Sep 2005
    Posts
    23
    Here is the sql as it appears from the db.Thanks

    SELECT tbl_Drop_Downs.Value, Count(tbl_New_Request.Report_Name_ID) AS CountOfReport_Name_ID, Count(tbl_Change_Request.Report_Name_ID) AS CountOfReport_Name_ID1, Count(tbl_Distribution_Change.Id) AS CountOfId
    FROM tbl_New_Request RIGHT JOIN (tbl_Change_Request RIGHT JOIN (tbl_Distribution_Change RIGHT JOIN tbl_Drop_Downs ON tbl_Distribution_Change.SA_Team_Player = tbl_Drop_Downs.Value) ON tbl_Change_Request.SA_Team_Player = tbl_Drop_Downs.Value) ON tbl_New_Request.SA_Team_Player = tbl_Drop_Downs.Value
    WHERE (((tbl_Drop_Downs.Field)="SA_Team_Player"))
    GROUP BY tbl_Drop_Downs.Value;

Posting Permissions

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