Cross-tab Query: Showing Rows without Records/Data

[terces]

Hi,

I have attached part of my db to ask for assistance.

I need to show the complete rows for all the status in each year with or without any record/data.

Maybe this is very simple task, but i somehow can't manage to do so.

Thanks in advance,
Terces

 

[Guus2005]

When you are creating a crosstab query/table and want to include empty lines you need to add the empty lines using a UNION to add additional empty (status) records to your query.

Pseudo example:

select "" as ProjectCode, "" as Title, Status from Status
UNION
select ProjectCode, Title, Status from Projects

This will be the base of your crosstab query.

Play with it and post the crosstab query if you still can't figure it out.

HTH

 

[terces]

okay, it took me time but i still cant figure out where to insert the UNION. please refer to my post below:

PARAMETERS [Forms]![ProgramsF].[ProgramStatusYear] Text ( 255 );
TRANSFORM CLng(Nz(Count([CountYearQ1].[Status]),0)) AS CountofStatus
SELECT Program.BARProgram, Status.Status
FROM (YearStarted INNER JOIN (Program INNER JOIN CountYearQ1 ON Program.BARProgram = CountYearQ1.BARProgram) ON YearStarted.YearStarted = CountYearQ1.YearStarted) INNER JOIN Status ON CountYearQ1.Status = Status.Status
WHERE (((Program.BARProgram) Like "*" & [Forms]![ProgramsF]![ProgramStatusYear] & "*" Or (Program.BARProgram) Is Null))
GROUP BY Program.ID, Program.BARProgram, Status.Status, Status.ID
ORDER BY Program.ID, Status.ID
PIVOT YearStarted.YearStarted In ("2001","2002","2003","2004","2005","2006","2007","2008","2009","2010","2011","2012","2013");

the db attached above has same sql as this.

thanks in advance.

 

[terces]

as i've read on other sources, PIVOT and UNION queries are not supported in Jet. I think there's still workaround in this case.

 

[Guus2005]

You can make it as complicated as you want.
I prefer the easy route.

First create a query which gives you every record you want in the end result including the additional records added with the UNION keyword.

After that create the crosstab query.

Doing it this way you will get a better understanding of the queries and JET will less likely find your query to complex.

Step 1: Create your query including additional records.
Step 2: Add parameters
Step 3: Add reference to a form field
Step 4: Create a crosstab query

After each step verify if the result matches what you expect. If it doesn't, you will know where to look.

Later on you can always create one big query which does everything. Rendering yourself indispensable

HTH

 

[Guus2005]

as i've read on other sources, PIVOT and UNION queries are not supported in Jet. I think there's still workaround in this case.

PIVOT and UNION (Ansi SQL-92) are valid keywords in MS Access.

If you are not sure if SQL statements are valid in Access, create the query in the QBE (Query By Example). The query is created for you while designing what you want.

HTH