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.
[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.
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.
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., DATA_Footnotes., DATA_Footnotes., 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.)="02" Or (DATA_Footnotes.)="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;
To find a solution quickly, it is better to provide your tables’ definition codes, some sample data and your desired result here for analysis.
We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
Thanks for helping make community forums a great place.