How can I join these 3 tables

I have 3 tables:

Trip  Promotion  Promotion Cost.
  1 ---- M   1  --------- M

Sample data include:

TripID    TripName    Date
XYZ123     Hawaii    09/06/09
YTU574     Japan     09/09/09
GHR752     US        11/07/09


PromotionID    TripID    Name
1              XYZ123    Poster
2    开发者_如何学Python          XYZ123    Brochure
3              GHR752    TV ad

CostID    PromotionID   Cost
  1           1         $50
  2           1         $100
  3           1         $120
  4           3         $2000
  5           2         $500

I'm trying to build a query like this:

TripID     Number of Promotions     Total Cost
XYZ123             2                  $770
GHR752             1                  $2000

What I have is this:

SELECT
      Trip.TripID, Count(Trip.TripID) AS [Number Of Promotions], Sum(PromotionCost.Cost) AS SumOfCost
FROM
     Trip
     INNER JOIN
         (Promotion
          INNER JOIN
          PromotionCost ON Promotion.PromotionID = PromotionCost.PromotionID
         ) ON Trip.TripID = Promotion.TripID
GROUP BY
       Trip.TripID;

And it gives me something like this:

TripID     Number of Promotions     Total Cost
XYZ123             4                  $770
GHR752             1                  $2000

I'm not sure why the Number of Promotions is messed up like that for the first one (XYZ123). It seems that somehow the JOIN is affecting it because if I use this:

SELECT
      Trip.TripID, Count(Trip.TripID) AS [Number Of Promotions], 
FROM
      Trip
      INNER JOIN
      Promotion ON Trip.TripID = Promotion.TripID
GROUP BY
      Trip.TripID;

It gives me the right number of promotions which is just 2.

You can add up the cost for each promotion in a subquery. That way, you only get one row for each promotion, and COUNT works to calculate the number of promotions per trip. For example:

select 
    t.TripId
,   count(p.PromotionId) as [Number of Promotions]
,   sum(pc.PromotionCost) as [Total Cost]
from trip t
left join promotions p on p.TripId = t.TripId
left join (
    select 
        PromotionId
    ,   PromotionCost = sum(cost)
    from Promotions
    group by PromotionId
) pc on pc.PromotionId = p.PromotionId
group by t.TripId

In case MS Access does not allow subqueries, you can store the subquery in a view, and join on that.

You can try to compensate for the duplicate Promotion rows by using COUNT(DISTINCT):

SELECT Trip.TripID, Count(DISTINCT Promotion.PromotionID) AS [Number Of Promotions],
  Sum(PromotionCost.Cost) AS SumOfCost
FROM Trip INNER JOIN Promotion ON Trip.TripID = Promotion.TripID
INNER JOIN PromotionCost ON Promotion.PromotionID = PromotionCost.PromotionID
GROUP BY Trip.TripID;

What's going on is that by default, COUNT() counts the rows produced after all joins have been done. There are four promotion costs for TripID XYZ123, so four rows, even though the TripId occurs multiple times among those four rows.

It's easier to visualize if you try a similar query without the GROUP BY:

SELECT Trip.TripID, Promotion.PromotionID, PromotionCost.Cost
FROM Trip INNER JOIN Promotion ON Trip.TripID = Promotion.TripID
INNER JOIN PromotionCost ON Promotion.PromotionID = PromotionCost.PromotionID;

You'll see the four rows for XYZ123 (with duplicate PromotionID values), and one row for GHR752.

Re comments that MS Access doesn't support COUNT(DISTINCT): if that's the case, then you shouldn't do this in a single query. Do it in two queries:

SELECT Trip.TripID, SUM(PromotionCost.Cost) AS SumOfCost
FROM Trip INNER JOIN Promotion ON Trip.TripID = Promotion.TripID
INNER JOIN PromotionCost ON Promotion.PromotionID = PromotionCost.PromotionID
GROUP BY Trip.TripID;

SELECT Trip.TripID, Count(Promotion.PromotionID) AS [Number Of Promotions]
FROM Trip INNER JOIN Promotion ON Trip.TripID = Promotion.TripID
GROUP BY Trip.TripID;

The alternative is a very convoluted solution using subqueries, described in this article at Microsoft:

http://blogs.msdn.com/access/archive/2007/09/19/writing-a-count-distinct-query-in-access.aspx

Not the answer to your question but a useful recommendation (I hope): convert your query into a view by using the visual designer of SQL Server Management Studio, and examine the generated SQL code. You don't have to actually keep and use the generated view, but it is a good way of learning by example. I do that whenever I'm struggled with a complex query.

EDIT. Shame on me, I hand't read the tags: the question is MS-Access related, not SQL Server related. Anyway I think that my advice is still valid as far as concept-learning is the concern, since the SQL syntax is similar.