Re: Unable to have multiple queries feeding a single report

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

I was only about 5 ...

Another question or two if you don't mind. Oh, by the way, do you know how
much of my time you are going to cause me to waste by trying to normalize the
gosh darn databases I have and have them set up in the right/proper/correct
fashion?? huh? Huh!?! :)

Anyway...

Before I get to the questions, please tell me if the following seems makes
sense as you telepathically look into my database. The first thing I
envision doing is creating a table with the funds' ticker symbols (a unique
governmental ID, of sorts, but one that sometimes, albeit rarely, gets
reused) and an AutoNumber identifier unique to each fund - which would be a
primary key. Then, I would have another table with the AutoNumber identifier
along with the current fund name. Fund names do change periodically, by the
way. The third table would be a list of my clients with a unique client ID
primary key. A fourth table would be a "linking table" or whatever it is
called where I match up the client ID primary key with the fund ID primary
key in order to establish which funds my clients currently use. The fifth
and sixth tables would be similar to the client-related tables but would be
for 401k providers and the funds they offer in their various programs.
Finally (I hope) would be my main data table that would use the AutoNumber
identifier from the ticker symbol table (not used as a primary key) and I
suppose might have an Autonumber field too (a primary key).

Please note that some of my data is numeric and some is text. Should I have
one data table for text and another for numbers or should I have, horrors of
horrors, two data fields in one table with one for text and the other for
numbers, or is there another suggestion?

Also, I'm not sure how to handle similar data in terms of table
construction. For example, a few of my way-too-many current data fields
include: 1 Month Return, 1 Month Return %tile Ranking, 3 Month Return, 3
Month Return %tile Ranking, 1 Year Return, 1 Year Return %tile Ranking, etc.,
etc. When making a Data Type field and subsequently adding data, would you
put in place only one Data Type field (using some variation of those current
data field names as the Data Type names (maybe underscores in place of the
spaces)) or would you split it up two ways where one I have Data Type field
for descriptions such as "Return" or "Standard Deviation" with a second Data
Type field for the time period (e.g., 1 month, 3 months, 1 year, etc.). This
second field would be separate from the ending date field, of course. I
assume having the two fields is not the way to go but I figured I better
check.

Finally, how the heck am I going to take a total of over 100 million
individual pieces of data from 29 tables of quarterly data that have an
average of 225 fields and enter it all into a single normalized table?
Individual queries for each data field? I suppose it would help if I would
first combine all the tables into one with a new field of "Ending Date". I
think I will still be under the 2Gig table size limit but I didn't see
anything about a maximum number of records in the Access specs list.

This should be fun.


And to think my parents worried I would never be normal...ized.

Thanks again for your help!


"Duane Hookom" wrote:

> I was just a babe when that film came out (or maybe I was 10).
>
> --
> Duane Hookom
> MS Access MVP
>
>
> "PZ Straube" <PZStraube@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:F391882D-0D76-47F2-9BDF-7DE054784448@xxxxxxxxxxxxxxxx
> > It's very strange...
> > I keep hearing Rex Harrison and Wilfrid Hyde-White singing to me, "He's
> > got
> > it! I think he's got it. By George, he's got it." as I alternate with a
> > Microsoft Access version of "The Rain in Spain Stays Mainly in the Plain".
> > In case you are too young, http://www.filmsite.org/myfa2.html.
> >
> >
> > "Duane Hookom" wrote:
> >
> >> I believe you are catching on. There are some good links to normalization
> >> at
> >> http://www.ltcomputerdesigns.com/JCReferences.html#DatabaseDesign101.
> >>
> >> In most cases, it is better to have fewer fields and more records. As
> >> John
> >> Vinson has stated many times "Records are cheap, fields are expensive"
> >> (or
> >> something like that).
> >>
> >> You could normalize your record further by adding a table that has values
> >> like:
> >>
> >> tblIndicatorDates
> >> =====================
> >> IndicateDateID autonumber primary key
> >> Fund
> >> IndicatorDate
> >>
> >> Then your final two records below would contain the IndicatorDateID
> >> rather
> >> than the Fund and Date fields.
> >> --
> >> Duane Hookom
> >> MS Access MVP
> >>
> >>
> >> "PZ Straube" <PZStraube@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> news:0BD1601C-257F-4DDE-8B90-C357491CC12F@xxxxxxxxxxxxxxxx
> >> > Duane,
> >> >
> >> > Thanks for your comments.
> >> >
> >> > I'm going to need to research normalized tables a little more because
> >> > I'm
> >> > not sure what the benefit would be to setting things up as you
> >> > described.
> >> > That is a not a swipe at you but rather an acknowledgement that if I am
> >> > going
> >> > to be using Access as much as I think I need to be, I guess I better
> >> > start
> >> > understanding some of the basic principals such as table normalization
> >> > before
> >> > I go much further.
> >> >
> >> > Back to your example (and don't worry about your not tracking mutual
> >> > funds),
> >> > from my novice perspective, it would appear that I am taking one "row"
> >> > (yes,
> >> > I'm still in Excel mode - perhaps a problem) of having:
> >> >
> >> > Field Names: Fund Date 3yr % 5yr %
> >> > Data: MSFT, #6/1/2005#, 1.2, 1.6
> >> >
> >> > which has 1 record with four pieces of data and going to
> >> >
> >> > Fund Date Data Type Period Data
> >> > "MSFT" #6/1/2005# "Return" 36 1.2
> >> > "MSFT" #6/1/2005# "Return" 60 1.6
> >> >
> >> > which has two records containing 5 pieces of data each - something I
> >> > would
> >> > think is not as efficient in terms of table size.
> >> >
> >> > However, if I am understanding you, it seems that the trade-off of
> >> > having
> >> > fewer data fields but vastly more records in a table is a more
> >> > acceptable
> >> > way
> >> > of setting up a table than the typical spread*** setup I seem to be
> >> > fixated
> >> > on.
> >> >
> >> > I assume that when I start figuring this stuff out, there will be an
> >> > advantatge in designing queries and probably reports. Any good books
> >> > or
> >> > websites you'd recommend for Table Design?
> >> >
> >> > Thanks again.
> >> >
> >> > "Duane Hookom" wrote:
> >> >
> >> >> I would consider removing some of these fields and place them in a
> >> >> table
> >> >> like:
> >> >>
> >> >> Fund
> >> >> IndicatorDate
> >> >> Indicator
> >> >> IndicatorDuration (months)
> >> >> IndicatorValue
> >> >>
> >> >> Records might look like
> >> >> "MSFT" #6/1/2005# "Return" 36 1.2
> >> >> "MSFT" #6/1/2005# "Return" 60 1.6
> >> >>
> >> >> As you might be able to ascertain, I don't know much about mutual fund
> >> >> tracking.
> >> >>
> >> >> --
> >> >> Duane Hookom
> >> >> MS Access MVP
> >> >> --
> >> >>
> >> >> "PZ Straube" <PZStraube@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> >> news:BFB8637A-97A9-4063-A8DA-EBCA78D55A07@xxxxxxxxxxxxxxxx
> >> >> > Duane,
> >> >> > Yes, there's a ton of data to look at with mutual funds. Most
> >> >> > people
> >> >> > look
> >> >> > just at rates of return and forgot about a lot of other things that
> >> >> > I
> >> >> > won't
> >> >> > bore this discussion group with.
> >> >> > Not to take up too much more of your valuable time, but with the
> >> >> > concept
> >> >> > of
> >> >> > normalized data you so correctly suggest people understand, if I
> >> >> > have
> >> >> > unique
> >> >> > fields in my main table (granted, variations of some things such as
> >> >> > 3
> >> >> > year
> >> >> > return and 5 year return OR 3 year standard deviation and 5 year
> >> >> > standard
> >> >> > deviation), without seeing my actual table, does this sound
> >> >> > normalized?
> >> >> > Thanks!
> >> >> >
> >> >> >
> >> >> > "Duane Hookom" wrote:
> >> >> >
> >> >> >> I was mis-reading your query names as field names.
> >> >> >> 250 pieces of information about a single fund seems like a lot. If
> >> >> >> there
> >> >> >> were multiple fields for "dates" than I would consider this
> >> >> >> un-normalized.
> >> >> >> Since I don't know how all or your fields are used, it is just a
> >> >> >> guess
> >> >> >> that
> >> >> >> your tables might be un-normalized.
> >> >> >>
> >> >> >> --
> >> >> >> Duane Hookom
> >> >> >> MS Access MVP
> >> >> >>
> >> >> >>
> >> >> >> "PZ Straube" <PZStraube@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> >> >> news:C11518DE-0675-420D-B335-8CBD0E57C5FA@xxxxxxxxxxxxxxxx
> >> >> >> > Duane,
> >> >> >> >
> >> >> >> > Thanks for your response. I appreciate you taking the time to
> >> >> >> > help
> >> >> >> > me.
> >> >> >> >
> >> >> >> > Please forgive my ignorance but I'm not sure what you mean by
> >> >> >> > "storing
> >> >> >> > data
> >> >> >> > values in field names".
> >> >> >> >
> >> >> >> > Regarding "un-normalized tables", thanks for mentioning that to
> >> >> >> > me
> >> >> >> > because
> >> >> >> > now that I'm really getting into Access, I better be thinking of
> >> >> >> > that
> >> >> >> > constantly or things will be even more difficult. I have tables
> >> >> >> > containing
> >> >> >> > data on thousands of mutual funds (I'm a consultant to corporate
> >> >> >> > retirement
> >> >> >> > plans). Each quarter, I obtain updated data and it goes into a
> >> >> >> > new
> >> >> >> > table.
> >> >> >> > In each table, each record pertains to one specific mutual fund
> >> >> >> > and
> >> >> >> > there
> >> >> >> > are
> >> >> >> > aboout 250 unique pieces of data for each mutual fund (e.g., rate
> >> >> >> > of
> >> >> >> > return,
> >> >> >> > risk, style, market capm etc.). My queries will pick up data
> >> >> >> > from
> >> >> >> > the
> >> >> >> > current quarter's table plus one or more of the prior quaterly
> >> >> >> > tables
> >> >> >> > then
> >> >> >> > do
> >> >> >> > some calculations related to the scoring for each fund.
> >> >> >> >
> >> >> >> > For reasons I won't bore you with, I have three queries that do
> >> >> >> > calculations
> >> >> >> > with the last one feeding my reports. I think my problem might be
> >> >> >> > that
> >> >> >> > the
> >> >> >> > last query contains a ton of calculations plus data directly from
> >> >> >> > the
> >> >> >> > main
> >> >> >> > table that I hit the limit. I have now put a fourth query in
> >> >> >> > place
> >> >> >> > using
> >> >> >> > only the calculations used in the reports and everything is fine.
> >> >> >> > Sorry
> >> >> >> > to
> >> >> >> > have bothered you with something I should have figured out on my
> >> >> >> > own
> >> >> >> > before
> >> >> >> > posting it.
> >> >> >> >
> >> >> >> > "Duane Hookom" wrote:
> >> >> >> >
> >> >> >> >> I believe your basic issue is un-normalized tables. It looks
> >> >> >> >> like
> >> >> >> >> you
> >> >> >> >> are
> >> >> >> >> storing data values in field names. This causes too many fields.
> >> >> >> >>
> >> >> >> >> You may be able to work around this by using subreports for some
> >> >> >> >> of
> >> >> >> >> your
> >> >> >> >> tables/fields.
> >> >> >> >>
> >> >> >> >> --
> >> >> >> >> Duane Hookom
> >> >> >> >> MS Access MVP
> >> >> >> >> --
> >> >> >> >>
> >> >> >> >> "PZ Straube" <PZStraube@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
> >> >> >> >> message
> >> >> >> >> news:448C3448-9582-478F-A96A-1FBF57F26F25@xxxxxxxxxxxxxxxx
> >> >> >> >> > Hello,
> >> >> >> >> >
> >> >> >> >> > For a report I have in Access 2003, I have apparently tried to
> >> >> >> >> > have
> >> >> >> >> > too
> >> >> >> >> > many
> >> >> >> >> > fields in the single query that feeds it data and received
> >> >> >> >> > error
> >> >> >> >> > message
> >> >> >> >> > 3190
> >> >> >> >> > when running this query. I took the new data fields I tried
> >> >> >> >> > to
> >> >> >> >> > add
> >> >> >> >> > to
> >> >> >> >> > the
> >> >> >> >> > original query and put them in a second query. Because I'm a
> >> >> >> >> > novice
> >> >> >> >> > and
> >> >> >> >> > haven't had two sources of data for a single report, I went to
> >> >> >> >> > the
> >> >> >> >> > Report
> >> >> >> >> > Wizard to play around with this before modifying my existing
> >> >> >> >> > report.
> >> >> >> >> > In
> >> >> >> >> > the
> >> >> >> >> > Report Wizard, I see that when I get to the point of selecting
> >> >> >> >> > fields,
> >> >> >> >> > I
> >> >> >> >> > can
> >> >> >> >> > change the query or table to be used, thus allowing for
> >> >> >> >> > multiple
> >> >> >> >> > sources
> >> >> >> >> > of
> >> >> >> >> > data. However, when I tried to have data from more than one
> >> >> >> >> > source,
> >> >> >> >> > I
> >> >> >> >> > received the following message:
> >> >> >> >> >
> >> >> >> >> > "You have chosen fields from record sources which the wizard
> >> >> >> >> > can't
> >> >> >> >> > connect.
> >> >> >> >> > You may have choosen fields from a table and from a query
> >> >> >> >> > based
> >> >> >> >> > on
> >> >> >> >> > that
> >> >> >> >> > table. If so, try choosing fields from only the table or only
> >> >> >> >> > the
> >> >> >> >> > query."
> >> >> >> >> >
> >> >> >> >> > Here's where I'm at. I am trying to get data from two
> >> >> >> >> > queries:
> >> >> >> >> > Filter_3_part_1 and Filter_3_part_2. Both rely on data from
> >> >> >> >> > the
.

Quantcast