ACCESS FEED: Re: [MS_AccessPros] Linking Tables Question (Junction Table ?)

Hi Glen and John,

Much appreciation for both of your suggestions!

I can see normalization is a problem that I am facing, but my main issue is trying to capture as much data as possible and attempting to keep the historical information as well, and in as few tables as possible.

Here is my actual data situation:

Every quarter, the Federal Reserve Bank releases a boat load of financial data on banks in the U.S. I wanted to create a database to capture these data for analysis. I can download the data from the Federal Reserve Bank in a text file that has ticker as the primary key and 100+ fields which include loan categories, different type of investments, different sources of funding, asset quality, loan losses by different loan types, type of overhead expenses,......So for all of the latest data available is March. I named it FQ032013.

All of the fields in the table FQ032013 relate to only one company, and the table contains about 300 companies. If I try to normalize the table FQ032013 by breaking up into information from the income statement, balance sheet, cash flow, asset quality, I will have 4 or 5 tables for each quarter. If I try to maintain a record of, say 10 years, then I will have 200 tables (10 years x 4 quarters x 5 tables per quarter), and the number of tables keep increasing each quarter as new data come in.

Even if I do this I still have the problem of creating a formula or query that reaches the most recent data. I am trying to come up with a way (if possible) to have a "master" table that control the time of each quarter (0 being the most recent and 1 being one older,...) and the queries can just reference this master table to to get the most recent data.

A real example: I wanted to calculate the change in loan losses as a percent of loans outstanding per quarter to see a trend over the last 20 quarters. A "master" table would just look up the most recent one being FQ032013 for now, but will switch to FQ062013 in two months.

My way of solving the issue is to save the most recent data (from 03/2013)in the file FQ_0 and data from 12/2012 in FQ_1, ...But this causes me to download all quarterly data and rename them by FQ_0, FQ_1, FQ_2,FQ_3, FQ_4,...the number of downloading will increase every quarter.

I am not sure if I am approaching this problem the right way. Any suggestions would be very much appreciated. I am grateful for all of you taking the time to help us users!

Tim

--- In MS_Access_Professionals@yahoogroups.com, "Glenn Lloyd" <argeedblu@...> wrote:
>
> Thanks John
>
>
>
> I am not clear on what the second field would be. I assume the first is the
> Quarter field.
>
>
>
> Glenn
>
>
>
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
> Sent: Monday, May 20, 2013 9:56 AM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: RE: [MS_AccessPros] Linking Tables Question (Junction Table ?)
>
>
>
>
>
> Glenn-
>
> Small correction: Adding the quarter info will add, at most, two fields,
> but that shouldn't exceed the 255 limit. And I agree about design issues.
>
> John Viescas, Author
> Microsoft Access 2010 Inside Out
> Microsoft Access 2007 Inside Out
> Microsoft Access 2003 Inside Out
> Building Microsoft Access Applications
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Glenn
> Lloyd
> Sent: Monday, May 20, 2013 6:27 AM
> To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> Subject: RE: [MS_AccessPros] Linking Tables Question (Junction Table ?)
>
> Tim,
>
> It seems then that your application is suffering from major structural
> issues. Are you aware of the rules of normalization? Chances are that with
> more than 100 fields in a table, that table is not properly normalized.
>
> Simply put, adding additional quarters to the same table would not increase
> the number of fields. One field would indicate to which quarter any
> particular record belongs.
>
> Glenn
>
> From: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of timdbui
> Sent: Monday, May 20, 2013 9:19 AM
> To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> Subject: Re: [MS_AccessPros] Linking Tables Question (Junction Table ?)
>
> Thank you for your reply, Glen!
>
> However, the actual tables for each of my quarter contain about 100 fields.
> The example I used in my question is to simplify the question so that I can
> concentrate on the key issue.
>
> Since I have to use most of the data from several tables, I can't put them
> all together because of the 255 column limitation. In addition, I would like
> to keep the historical data, as each quarter I will have new data. Therefore
> I thought about a "master" table that could control the dates might be the
> way to go (the most recent quarter (value 0) and 1 quarter older (value 1),
> and so on.)
>
> Thanks again, Glen!
>
> Tim
>
> --- In MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com> , "Glenn Lloyd"
> <argeedblu@> wrote:
> >
> > Tim,
> >
> >
> >
> > It would be far more efficient and far less time consuming to have one
> table
> > with an additional field to indicate the quarter rather than creating
> > a
> new
> > table for each quarter.
> >
> >
> >
> > You could then use a parameter query to select the quarter of interest.
> >
> >
> >
> > Glenn
> >
> >
> >
> > From: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of timdbui
> > Sent: Monday, May 20, 2013 8:57 AM
> > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > Subject: [MS_AccessPros] Linking Tables Question (Junction Table ?)
> >
> >
> >
> >
> >
> > Hi, would you please help me how to set up this query:
> >
> > I have several tables that list quarterly information on companies.
> > (Col A is Company Name, Col. B is quarterly date, Co. C is Sales of
> > the quarter, and D is Net Income of the Quarter.
> >
> > I would have these table for each quarter: FQ032013, FQ122012,
> > FQ092012, FQ062012, and FQ032012.
> >
> > So at the end of each quarter, I would have a new table. By the end of
> June,
> > I will have a new table named FQ062013.
> >
> > For now, my most recent data available is from table FQ032013, but by
> July,
> > my most recent data will be from the new table FQ062013 and 1 older
> quarter
> > will be from FQ032013 and 2 older quarter will be from FQ122012.
> >
> > Would you please show me how to create a junction table (?) so that I
> > can
> up
> > date the Most Recent Quarter (0) to be the FQ032013 for now, but in a
> couple
> > of month I will change it to FQ062013.
> >
> > The goal is for me to be able to write a query to to get the most
> > recent data from the table indicated as most recent table in the junction
> table.
> >
> > Thanks in advance for your instruction!
> > Tim
> >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
> [Non-text portions of this message have been removed]
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>
>
>
> [Non-text portions of this message have been removed]
>