SQL Help! (repeating data in field that repeats in

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Help! (repeating data in field that repeats in

Author

Topic

Access_no_nothing
Starting Member

2 Posts

Posted - 2013-10-19 : 15:09:54

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'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;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-20 : 03:25:27

are you looking T-SQL or Access query? If latter you may be better off posting this in Access forum.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Access_no_nothing
Starting Member

2 Posts

Posted - 2013-10-20 : 11:07:32

It is an Access query....I'll move the thread. Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources