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

Thanks, John and Glen!

Let me go back to the drawing board to redesign the structure. It seems to be my issue.

Regards,

Tim

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> 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
>