Difficult User Requirement - Appending Blank Columns

[dark.matter]

I have created a Matrix Style form using Pivot Table format in Forms. Columns are grouped under 'Years'. For example, Item No. 1 can be present in Year 2010,2011 etc. Here is a rough layout:

Year
Item No. Item Desc. Quantity

Suppose the data entries are :

Year
2009 2010 2013
Item No. Item Desc. Quantity
1 Chocolates 100 150 200
2 Cakes 50 139 250

I enter data through a separate Data Entry Form.
What I need is that, those years which do not contain any information, should be displayed as blank. In the above case, 2011 and 2012 must be displayed with NULL values.

Year
2009 2010 2011 2012 2013

Item No. Item Desc. Quantity
1 Chocolates 100 150 NULL NULL 200
2 Cakes 50 139 NULL NULL 250

I tried using Queries and Expressions but I am unable to meet my requirement. I am entirely new to Office Access. Any help is really appreciated.

[rpeare]

so your form is based on a crosstab query, can you either give an example (table name, field names and contents) of your original data or provide a sample of your database with what you've done so far. What you're asking for isn't that hard to do but hard to explain.

[dark.matter]

As you can see in the screenshot, there is data only for Year 2010 and 2013. What I need is that, 2011 and 2012 should also show up with NULL values for Quantity.

[rpeare]

can you either give an example (table name, field names and contents) of your original data or provide a sample of your database with what you've done so far.

I understand the problem with your crosstab, what I need is information on the structure of the data in the table.

[dark.matter]

Here. Let me know if you need any further details.

[rpeare]

Alright, that's better, now my main question is: The table in your database you are representing with 'Database Table' I assume has data that has at least one record for every FY IN TOTAL. In other words if it's orders for a customer, I don't really care who the customer is, but there is at least one order for every year from 2010 through 2013. Is that correct?

In other words let's say that your GROUPCODE is actually a representation of your customer if you add one record for group 2 and put the Year as 2012 we would have all years represented across all customers.

I put in item number 1, quantity 5, group code of Group 2, year of 2012 for the purposes of my example.

I also removed all spaces from field/table names (they cause more problems than they are worth and you should try to stay away from the practice of having anything but text and underscores(_) in your object names.

So the first thing is to create a list of years:

SELECT DatabaseTable.Year
FROM DatabaseTable
GROUP BY DatabaseTable.Year;

One other thing to mention here is that YEAR is a reserved word and you probably shouldn't use it.

Name this query qryYears

next a summary of the crosstab items:

SELECT DatabaseTable.ItemNo, Sum(DatabaseTable.Qty) AS Qty, DatabaseTable.Year
FROM DatabaseTable
GROUP BY DatabaseTable.ItemNo, DatabaseTable.Year, DatabaseTable.GroupCode
HAVING (((DatabaseTable.GroupCode)="GROUP 1"));

Name this query qryOrders

Now the crosstab itself:

TRANSFORM Sum(qryOrders.Qty) AS SumOfQty
SELECT qryOrders.ItemNo
FROM qryYears LEFT JOIN qryOrders ON qryYears.Year = qryOrders.Year
GROUP BY qryOrders.ItemNo
PIVOT qryYears.Year;

you're always going to have a 'blank' line in this query but you can remove it by building another query based on the crosstab query and put is not null in the itemno column.

There are ways to economize on the number of queries to build this but I've always prefered having readily editable sub queries you can change easily as demand necessitates.

[dark.matter]

Thanks a lot for your time.
It is not necessary that a group. code has at least one entry for each year from 2010 to 2013. Let me give you an example. From the period 2000 to 2013 for group code 1,there are QTY data entries only for years 2000-2005 and 2010-2013.But the final report view should contain all years from min to max I.e. 2000-2013 with QTY fields blank/Null for years 2006-2009. I hope I am clear.

[Dal Jeanis]

dark.matter - rpeare has provided the critical concept you need - a LEFT JOIN where the far left table is a table of the years you want to always show up in your matrix. That is how you force the existence of null data records for the blank year columns that you are asking for.

FROM qryYears LEFT JOIN qryOrders ON qryYears.Year = qryOrders.Year

[dark.matter]

I got that fully. The thing is I don't have any idea what years would be the input. I have to select min and max year values including all between them from the final table after data is entered and display all along with blank year columns. Am I missing something already told above?

[dark.matter]

I am talking about this assumption rpeare made, which is incorrect : "*In other words if it's orders for a customer, I don't really care who the customer is, but there is at least one order for every year from 2010 through 2013. Is that correct?"

[dark.matter]

In the query where rpeare created a list of years, he is in fact selecting only those years present in the database table. It can be the case that some years might not have a data entry.

[rpeare]

Can I suggest that you run the first query (the one that summarizes the years) against your actual dataset. Do not put in any criteria or fields other than the year, just as I did in my example and see if it gives you a 'complete' list of years. I am not familiar with your business but it seems to me that if there was no activity for a full year the place would be out of business

If you really do not have all years represented in your database (not just on this table but on any table in your database) you're going to have to create a temporary table that lists all the years either dynamically at run time or as a 'constant' table.

[Dal Jeanis]

dark.matter - There's a dozen ways to get the list of years for the far-left table. If nothing else, you could build a comprehensive permanent list of ALL years and then limit it to Where myYear <= Select Max(year-of-datefield) and myYear >= Select Min(year-of-datefield) of the result set. Would you like help figuring out that code?

[dark.matter]

rpeare:Thank you for your help. I am just a student learning databases and got a project to do with this being a small assignment. So this is not my business ;-)

Dal Jeanis:That is exactly how I thought of doing it but I have no idea how to create a query to return years not present in the database table. I would be grateful to you if you could show me the way.

[Dal Jeanis]

1) Brute force works fine. Create a dumb, stupid, fixed table tblPotYears that contains one field PotYr, and load the table with one record each for the integer years from 1980 to 2050. For a class, you could limit this to the years between the min and max values that are currently in the database.

2A) Code a Query qryRelevantYears against table tblPotYears

SELECT PotYr FROM tblPotYears 
WHERE PotYr >= (Select MIN(Yr) from [DatabaseTable]) 
AND PotYr <= (Select MAX(Yr) from [DatabaseTable]) ;

That query will get you one line per year in the relevant range.

Once again, for a class, you could shortcut the query, only include the relevant years in tblPotYear, and not have the min/max tests. However, if I were your professor I'd take off points for writing a solution that would break predictably and permanently six months from now, or unpredictably if anyone imported earlier historical data for analysis.

2B) You could also create an load temp variables using VBA in Access 2010 and use those to limit the date range.

SELECT PotYr FROM tblPotYears 
WHERE PotYr >= tmpLowYear AND PotYr <= tmpHighYear;

Same result. Same ability to shortcut. Same warning about grading.

3. Use the results of that query in the left side of a LEFT JOIN to create the data that will underlie your Crosstab. I don't use crosstabs much myself, so you should use rpeare's code as model for your first approximation, then modify as needed.

4. In general, you should avoid spaces in field and table names, and you should make the table names represent whatever entity you are storing information about. That starts with understanding what the purpose is for storing the underlying data in the first place. It looks like you're solving a very generic problem here, so I'll suggest very generic changes to the names, primarily to eliminate the need for square brackets -

Old Name                  New Name
[Database Table]          tblItemInfo
[Item No]                 ItemNo
[Item Desc]               ItemDesc
Qty                       Qty
Remarks                   Remarks
[Group or Subject Code]   SubjCode
Yr                        Yr

Please check out these pages on naming conventions.

Common Access naming Conventions - http://access.mvps.org/access/general/gen0012.htm
Reserved words to avoid - http://www.utteraccess.com/forum/Acc...s-t539419.html