none
SQL Help! (repeating data in field that repeats in multiple tables)

    Question

  • Hi all,

    I'm wrangling with a rather simple query. However, given my low level of Access and SQL competency, I've hit a roadblock with my current project. Details are below and thank you all in advance for your patience.

    Basically, I am trying to use historical financial data to test a well known bankruptcy predictor model. The financial data is stored in an Annual format (table listing below). The database is structured such that the each company has one general information record in the company record table (IDX_FS) and multiple records for each year of existence in the financial statement data tables (DATA_BS etc.).

    In each DATA table, there is one field that contains the specific year of the data record for each company [4DTYR] and its respective financial data. The data in this field repeats and exists for each company and for every year it existed.

    For example:

    [CONAME] [4DTYR] [A_TOTAL]

    Apple Inc. 2009 200
    Apple Inc. 2010 220
    Apple Inc. 2011 240
    Google Inc. 2009 180
    Google Inc. 2010 170
    Google Inc. 2011 160

    The problem I am running into is given that the data in the field [4DTYR] exists and repeats in various tables from which data is being used to calculate arithmatic in a handful of expressions, I end up with a huge amount of repeated (and what looks a permutation) data in my query output. I'll attach a screenshot as soon as my account is verified.

    I've detailed the tables, fields and expressions below in addition to the SQL script. Note that I've tried adding a condition under "WHERE" that attempts to set all the [4DTYR] dates in the different tables as the same. That portion is highlighted in magenta. This still doesn't seem to work as I only get output for 1 year only, when there are 20 years of data. Furthermore, when I run the query without the expressions, the existing paramaters gives me output with ~500 records.

    Help!?!

    Thanks again for your advice and time. I've tried to explain my problem as concisely as possible and will be more than happy to reply to clarify anything as required.

    Tables

    IDX_FS: Company Record - contains basic company information (company names, cusip, etc) with no dates assigned to records. CUSIP and CONAME fields only appear once in this table, unlike the DATA tables (below), where they appear in multiple records for every year that each company existed.

    DATA_BS: Annual Balance sheet data

    DATA_Footnotes: Annual Footnote data

    DATA_IS: Annual Income Statement data

    DATA_SP: Annual Supplemental data

    Pertinent Fields and Expressions

    IDX_FS.CUSIP - This is a unique identifier used for stocks and also functions as the primary key for the database, appears in multiple records in DATA tables for each year the company existed.

    IDX_FS.CONAME - Company name, appears in multiple records in DATA tables for each year the company existed.

    DATA_BS.[4DTYR] - 4 digit date for balance sheet data of that year (ex. 1982)

    DATA_IS.[4DTYR] - same as balance sheet data year

    DATA_SP.[4DTYR] - same as balance sheet data year

    DATA_Footnotes.[4DTYR] - same as balance sheet data year

    ([DATA_IS]![EBIT])/(DATA_BS]![A_TOTAL]) AS X3 - Expression dividing Income Statement data by Balance Sheet Data.

    (([DATA_SP]![C_OUT])*([DATA_SP]![PRICE_FYE_CL]))/([DATA_BS]![L_TOTAL]) AS X4 - Expression that divides Supplemental Data by Balance Sheet data.

    ([DATA_IS]![SALES_NET])/([DATA_BS]![A_TOTAL]) AS X5 - Expression that divides Income Statement data by Balance Sheet data.

    Current SQL Query (please excuse the format, I was using Access Query builder to start - I'm a noob.)

    SELECT IDX_FS.CUSIP, IDX_FS.CONAME, IDX_FS.SOURCE_FILE, DATA_BS.[4DTYR], DATA_Footnotes.[34], DATA_Footnotes.[33], DATA_Footnotes.[35], IDX_FS.DNUM, DATA_BS.A_TOTAL, ([DATA_BS]![CA_TOTAL]-[DATA_BS]![CL_TOTAL])/([DATA_BS]![A_TOTAL]) AS X1, ([DATA_BS]![RE])/([DATA_BS]![A_TOTAL]) AS X2, ([DATA_IS]![EBIT])/([DATA_BS]![A_TOTAL]) AS X3, (([DATA_SP]![C_OUT])*([DATA_SP]![PRICE_FYE_CL]))/([DATA_BS]![L_TOTAL]) AS X4, ([DATA_IS]![SALES_NET])/([DATA_BS]![A_TOTAL]) AS X5

    FROM (((IDX_FS LEFT JOIN DATA_BS ON IDX_FS.CUSIP = DATA_BS.CUSIP) LEFT JOIN DATA_Footnotes ON IDX_FS.CUSIP = DATA_Footnotes.CUSIP) LEFT JOIN DATA_IS ON IDX_FS.CUSIP = DATA_IS.CUSIP) LEFT JOIN DATA_SP ON IDX_FS.CUSIP = DATA_SP.CUSIP

    WHERE (((IDX_FS.SOURCE_FILE)="R") AND ((DATA_Footnotes.[35])="02" Or (DATA_Footnotes.[35])="03") AND ((IDX_FS.DNUM) Between 2000 And 3000) AND ((DATA_BS.A_TOTAL) Between 1 And 25)) and DATA_Footnotes.[4DTYR] = DATA_BS.[4DTYR] and DATA_BS.[4DTYR] = DATA_sp.[4DTYR] and DATA_sp.[4DTYR] = DATA_is.[4DTYR]
    ORDER BY IDX_FS.DNUM;
    Saturday, October 19, 2013 6:53 PM

All replies