Crosstab query, Can I "force" rows? - left join?

[Andydtaylor]

Hi all,

I'm stuck!

This is my first post, so thanks for welcoming me to your forum. I have done bits and pieces in Access before, and Access with Excel but have not yet had formal training - ironically I need to get this work out of the way first. Now I've managed your expectations.....

I've hit a limit in understanding:

I would to force the rows in my crosstab query below to be the periods 1-50, and populate with zero if there is no data to return. i.e. so there are always 50 rows. Columns are "Period","T_Unit-1","T_Unit-2","T_Unit-3","T_Unit-4" , and relate to electricity from powerstations. i.e. so there are always 5 columns.

As things stand, depending on the month/year coming from Excel spreadsheet, the query returns:
1) Periods 1-50 with zeros for capacity data This is exactly what I want, but it will only happen naturally in a clock-change month
2) Periods 1-48 with zeros for capacity data
3) Periods x-48 e.g. 9-48 producing a much smaller table. This is my real problem case that I don't know how to handle.

I need to standardise this output because it's being picked up by Excel and a good 36 tabs are being generated, with a series of query results like this on each one, and I want everything to line up. As I have it at the moment, all of the SQL is being created in VBA and customised as required by each tab.

Can this be done? I tried doing a left-join on a table I created t_Periods with one Field, "Period" with 1-50 i.e. 50 rows but got ambiguous join warnings, which I haven't been able to resolve.

Could Ordering my join statements fix this? I really would like to keep this as a single SQL query if I can, because I don't know how I can plumb this into my excel spreadsheet otherwise.

There are (4) tables being referenced here:
1) t_Unit_Cap. Gives Generation Units IDs and Generating Capacity
2) t_MEL_data. Gives Gigawatts of power generated per unit, for every half-hour period for every day for over 18 months (hence 48/50 periods in a day)
3) EFA_Calendar. In the UK power isn't sold in calendar months but instead accoring to an industry-standard "EFA" calendar.
4) t_outages. Was a generation unit scheduled to be out for maintenance.

5) Plus I mentionned I has created a table which is just the numbers 1-50 in ascending order, as the column "Period" in t_period.

TRANSFORM IIf(IsNull(Sum([Capacity]/2)),0,Sum([Capacity]/2))+0 AS Expr1
SELECT t_mel_data.Period
FROM t_Unit_Cap 
   INNER JOIN (t_mel_data 
   INNER JOIN EFA_Calendar ON t_mel_data.MEL_Date = EFA_Calendar.Date)
   ON t_Unit_Cap.Unit = t_mel_data.Unit
WHERE (((t_mel_data.Revision_code) In (3,4,5,6,7)) 
AND ((EFA_Calendar.Month)='Apr') AND ((EFA_Calendar.Year)=2010) 
AND ((Weekday([MEL_date])) In (2,3,4,5,6)) 
AND ((Not Exists (select * from t_outages where t_mel_data.unit = t_outages.unit 
   AND t_mel_data.MEL_date >= t_outages.from_date 
   AND t_mel_data.MEL_date <= t_outages.to_date))<>False) 
AND ((t_mel_data.MEL_Date) Between #4/1/2010# And #11/7/2011#))
GROUP BY t_mel_data.Period
PIVOT t_mel_data.unit In ("T_Unit-1","T_Unit-2","T_Unit-3","T_Unit-4");

Many thanks,


Andy

 

[Pat Hartman]

When you have an inner join logically to the right of a left join, you get the ambiguous join message. The solution is to break the inner join off into a separate query and then left join that query to the other table.