Results 1 to 3 of 3

Thread: Join Question

  1. #1
    Join Date
    Oct 2003
    Posts
    73

    Unanswered: Join Question

    Hey there,
    I'm having trouble coming up with a query that will add up all the categories of an order. If a category isn't even in the order, i want that total to be 0 and still show up with a 0.

    Code:
    SELECT DatePart("ww",[MaxOfLeaveDate]) AS week, tblCategory.categoryID, tblCategory.category, Sum(IIf([CabQty] Is Null,0,[cabqty])) AS cabqty1
    FROM qryOspecInfoCategory RIGHT JOIN tblCategory ON qryOspecInfoCategory.categoryID = tblCategory.categoryID
    GROUP BY DatePart("ww",[MaxOfLeaveDate]), tblCategory.categoryID, tblCategory.category;
    Right now, it only adds the totals correctly and outputs something like this:

    Code:
    week	categoryID	category	cabqty1
    	1	a	0
    	2	b	0
    	3	c	0
    	7	d	0
    	12	e	0
    36	4	f	223
    36	5	g	5
    36	6	h	157
    36	8	i	30
    36	9	j	10
    36	10	k	2
    As you can see, the week is null for the first 5 entries. I need to show ALL categories for EVERY week, even if they are 0. That means all 12 categories shown for every week. If someone could give me some insight, that would be great.

    thx

  2. #2
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    Save this query as qryOspecAllCategories (or the name of your choice, but you need to refer to it later, so change it everywhere if you choose to change it)
    Code:
    SELECT DatePart("ww",[MaxOfLeaveDate]) AS week, tblCategory.categoryID, tblCategory.category, IIf([CabQty] Is Null,0,[cabqty]) AS cabqty2 
    FROM qryOspecInfoCategory INNER JOIN tblCategory ON qryOspecInfoCategory.categoryID = tblCategory.categoryID 
    UNION 
    SELECT DISTINCT DatePart("ww",[MaxOfLeaveDate]) AS week, tblCategory.categoryID, tblCategory.category, 0 AS cabqty2 
    FROM qryOspecInfoCategory, tblCategory;
    Then, use this query to get the results you're looking for:
    Code:
    SELECT qryOspecAllCategories.week, qryOspecAllCategories.categoryID, qryOspecAllCategories.category, Sum(qryOspecAllCategories.cabqty2) AS cabqty1 
    FROM qryOspecAllCategories
    GROUP BY qryOspecAllCategories.week, qryOspecAllCategories.categoryID, qryOspecAllCategories.category;

  3. #3
    Join Date
    Oct 2003
    Posts
    73

    Thumbs up

    thx a ton JTRockville!
    that was exactly what I was looking for.

    Issue resolved

Posting Permissions

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