Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003

    Unanswered: ... execute a query that does not include the specified expression

    I'm trying to build a query that will predict demand over time and region (PC) given various growth parameters.

    The query below works _if_ I replace [BT].Demand_x1 and [BT].Demand_x0 with real numbers. However, BT is a Business Type, and I want to be able to use different parameters for different businesses, so I've tried using the code below (the second section), plus I've also added the second INNER JOIN statement.

    Now, when I'm trying to make it look up Demand_x0 and Demand_x1 for each BT, it says:
    "You tried to execute a query that does not include the specified expression 'Employees*([BT].Demand.... ^1.2)' as part of an aggregate function."

    What am I doing wrong? How can I do it right?

    SELECT Dates.MyDate AS [Date], p.PC, Sum(b.Employees) AS Employees,

    Employees*([BT].Demand_x0+([BT].Demand_x1-[BT].Demand_x0)*(([Date]-#6/1/2003#)/(#5/31/2013#-#6/1/2003#))^1.2) AS Demand

    FROM Dates, ([Bus Data] AS b INNER JOIN [PostCodes-PCPs] AS p ON b.PostCode = p.PostCode) INNER JOIN [BT] ON b.[Type] = [BT].Type
    GROUP BY Dates.MyDate, p.PCP;

    Thanks for your time and help.

  2. #2
    Join Date
    Sep 2003
    I think you need to include Demand_x0 and Demand_x1 as part of your group by, also, I don't see where Dates gets joined anywhere.
    All code ADO/ADOX unless otherwise specified.

  3. #3
    Join Date
    Nov 2003
    You're right, I don't join the dates: Dates is just that, a list of dates - the 1st of every month. This is combined with the PC (region) field to give every region-date combination. The Date is then used to calculate demand by comparing it to my start date and end date (and doing some maths).

    But the thing is, it works if I enter simple numbers (e.g. 1 and 5) instead of my Demand_x0 and Demand_x1, so I don't think it's got anything to do with not having a Date join.

    I don't think I want to group by Demand_x0 or Demand_x1 because I don't want these fields to be shown in the final result. This is just to allow me to have different Demand_x0 and Demand_x1 values for different business types, so for example shipping firms might have 1 and 5, while dot.coms might have 2 and 7, or whatever - my table 'b' in the query is a list of businesses, with each one categorised as one of my business types.

Posting Permissions

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