I'm amalgamating data for 100 companies across ten years and 400 rows of data per company. All the data is currently separated by years and by 16 separate tables from which I'm pulling together the "template" of 400 rows of data per company. My goal is to have the same "template" of 400 rows for every company and to organize years as columns. The issue is that in the tables, when a company does not report on a specific item, instead of there being a blank, there is a missing row. When I do a Select query to join multiple years as columns, or to join multiple tables vertically to create the 400 row template, no matter which way I point the query arrow in design view, I end up whittling down on that 400 row template.
I've done this before and end up having to do a lot of data fix-ups in Excel to fill in the missing rows so that I can have the desired 400 row template (makes repeated calculations in excel easy to automate etc).
So what kind of query do I have to do to keep a "cookie cutter" version of the 400 row template across the numerous queries I have to do to create the final version?
One solution could be to create a Cartesian join for the time period you want (storing the time period range in a set of tables) then perform a join to include all the members of the timeframe and the data that is actually available.
You could also use an IIF to assign a value in case no data is available for a specific point in time. Here is an example:
'this is the Cartesian join named qry_ym_cj
FROM tbl_month, tbl_year
ORDER BY tbl_year.year, tbl_month.month;
'this is an example of how to join your data to the previous query
qry_ym_cj.year, qry_ym_cj.month, IIf(tbl_data.f1 Is Null,'no data',f1) AS f1_f
FROM qry_ym_cj LEFT JOIN tbl_data ON (qry_ym_cj.year = tbl_data.year) AND (qry_ym_cj.month = tbl_data.month)
ORDER BY qry_ym_cj.year, qry_ym_cj.month, IIf(tbl_data.f1 Is Null,'no data',f1);
If you want to see it in an Access 2000 database, I can send you the db itself in an email.