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/JCR...abaseDesign101.

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" wrote in message
...
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 spreadsheet 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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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
same
query,
Filter_2 (which, via Query Filter_1, uses data from the a single
table
called
Main_Data). Both of the Filter_3 queries have the same primary
key
field
from table Main_Data.

I need to figure out what I am doing wrong so I can use data from
these
two
queries in my report but I don't know where to start.

Thanks to anyone can point me in the right direction.