Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    7

    Unanswered: Outer Joins not working?

    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?

    Thanks!

  2. #2
    Join Date
    Jun 2004
    Location
    Saratoga Springs
    Posts
    24
    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

    SELECT
    tbl_year.year, tbl_month.month
    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

    SELECT
    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.

    Hope this helps.

  3. #3
    Join Date
    Mar 2004
    Posts
    7

    sample

    Thanks--yes it would be great if you could email me the sample Access database!

    dreamdial@aol.com

  4. #4
    Join Date
    Jun 2004
    Location
    Saratoga Springs
    Posts
    24
    Done. If you dont get the attachment or there are any other questions, let me know.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •