Query to show all years even when there are no data for a particular year (1 Viewer)

[whitespace]

Hello all,

We have had this ongoing problem that I'm sure must be fairly simple if we know how. Basically we have a lot of data in different groups that for example we need to display for specific years e.g. the last 5 years.

The trouble is, as the data is calculated, there may not be data for a certain year and so this whole row isn't stored in the data.

When we pull it into Excel though, we NEED a row for each year, even if there is no data for that year.

I've tried using LEFT/RIGHT joins to a table of years e.g.

tblYears:
Year
2002/03
2003/04
2004/05
2005/06
2006/07
2007/08

but this does not work, it still doesn't display 2003/04 of the linked table if no data is for this school. I've tried checking for nulls etc. but no joy.

We currently get around this problem by using vlookups in Excel but I'm sure this is unneccessary as it get's complex.

We have this same prob when displaying standard groups of data too.

Any help would be a great help.

Many thanks

 

[maxmangion]

Hi

try posting the SQL which you are using maybe someone can point out the solution.

 

[whitespace]

Ok, this is slightly different but the principle is the same:

SELECT tblLSIP_PLASC_Data.Type, tblLSIP_PLASC_Data.Level, tblLSIP_PLASC_Data.Level_Name, tblPLASCGroupReq.PLASCGroup, tblLSIP_PLASC_Data.[2003/04], tblLSIP_PLASC_Data.[2004/05], tblLSIP_PLASC_Data.[2005/06], tblLSIP_PLASC_Data.[2006/07], tblLSIP_PLASC_Data.[2007/08]
FROM tblPLASCGroupReq LEFT JOIN tblLSIP_PLASC_Data ON tblPLASCGroupReq.PLASCGroup = tblLSIP_PLASC_Data.Group
WHERE (((tblLSIP_PLASC_Data.NCYear)="A" Or (tblLSIP_PLASC_Data.NCYear) Is Null))
ORDER BY tblLSIP_PLASC_Data.Type, tblLSIP_PLASC_Data.Level, tblLSIP_PLASC_Data.Level_Name, tblPLASCGroupReq.PLASCGroup;

Basically I have a table 'tblPLASCGroupReq' which has the following fields and values:
GroupID PLASCGroup
1 NOR_A
2 NOR_M
3 NOR_F
4 FSM_T
5 SEN_A
6 SEN_P
7 SEN_S
8 SEN_LDD

The other table 'tblLSIP_PLASC_Data' contains a wealth of school specific information, the prob is. Without going into too much detail to show an example of the prob, in the above table NOR_F represents Female pupils in a school, however in the 'tblLSIP_PLASC_Data' table there is (for example) a male only school and so there is no matching row with NOR_F in the group, however I STILL NEED TO SHOW THIS ROW in the report - as blanks.

I have also tried testing for nulls (Since if there isn't a link this will return null), using something similar to:

SELECT IIf(IsNull([Level_Name]),Null,[Level_Name]) AS LName, tblPLASCGroupReq.PLASCGroup, IIf(IsNull([2003/04]),Null,[2003/04]) AS Year1
FROM tblPLASCGroupReq LEFT JOIN tblLSIP_PLASC_Data ON tblPLASCGroupReq.PLASCGroup = tblLSIP_PLASC_Data.Group
WHERE (((tblLSIP_PLASC_Data.NCYear)="A" Or (tblLSIP_PLASC_Data.NCYear) Is Null) AND ((tblLSIP_PLASC_Data.Type)="secondary" Or (tblLSIP_PLASC_Data.Type) Is Null))
ORDER BY IIf(IsNull([Level_Name]),Null,[Level_Name]), tblPLASCGroupReq.PLASCGroup;

This still does not return any rows with NOR_F though.

Does this make sense?

Many thanks

 

[whitespace]

Hello, I've sort of found a solution to this problem by using this below:

SELECT tblLSIP_PLASC_Data.Type, tblLSIP_PLASC_Data.Level, tblLSIP_PLASC_Data.Level_Name, tblPLASCGroupReq.PLASCGroup, Sum(IIf([Group]=[PLASCGroup],[2003/04],0)) AS [Y-4], Sum(IIf([Group]=[PLASCGroup],[2004/05],0)) AS [Y-3], Sum(IIf([Group]=[PLASCGroup],[2005/06],0)) AS [Y-2], Sum(IIf([Group]=[PLASCGroup],[2006/07],0)) AS [Y-1], Sum(IIf([Group]=[PLASCGroup],[2007/08],0)) AS [Y-0]
FROM tblPLASCGroupReq, tblLSIP_PLASC_Data
WHERE (((tblLSIP_PLASC_Data.NCYear)="A"))
GROUP BY tblLSIP_PLASC_Data.Type, tblLSIP_PLASC_Data.Level, tblLSIP_PLASC_Data.Level_Name, tblPLASCGroupReq.PLASCGroup
ORDER BY tblLSIP_PLASC_Data.Type, tblLSIP_PLASC_Data.Level, tblLSIP_PLASC_Data.Level_Name, tblPLASCGroupReq.PLASCGroup;

However, this means that the tables AREN'T actually linked, I'm sure this isn't the best practise - also, I can only use this method because I need to display zeros - it wouldn't work if I wanted to display nulls (Since you can't Sum nulls) and we do need to do that sometimes.

Any ideas?

Many thanks