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

Tim-

Glenn's suggestion is the ideal solution, but you could keep the data
unnormalized and simply add year and quarter fields. All the data will be
in ONE table, and it will be simple to find the quarter you want with a
filter. Your Primary Key should be year, quarter, and company ID.

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@yahoogroups.com] On Behalf Of Glenn Lloyd
Sent: Monday, May 20, 2013 7:38 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Linking Tables Question (Junction Table ?)

Tim,

Your design constraint of having "as few tables as possible" is most likely
contrary to having a normalized structure. If I understand your present
approach correctly, it is forcing you to create new tables every quarter.
While this may seem feasible in the short term, in the long run you will end
up with far more tables than you would have if you work with a well-designed
and normalized structure. Maintenance and querying will become increasingly
complex and time consuming as you gather more data.

In a normalize structure you will only add data as new records to the
various tables as time goes by. You may have to maintain some tables if, for
example, the Federal Reserve defines new loan categories.

Glenn

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of timdbui
Sent: Monday, May 20, 2013 10:27 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: 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
<mailto:MS_Access_Professionals%40yahoogroups.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%40yahoogroups.com>
> [mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of John
Viescas
> Sent: Monday, May 20, 2013 9:56 AM
> To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.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%40yahoogroups.com>
> [mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.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>
> <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%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 9:19 AM
> To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.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>
> <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%40yahoogroups.com>
> > [mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.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>
> <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]
>

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links