A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Unable to have multiple queries feeding a single report



 
 
Thread Tools Display Modes
  #11  
Old June 13th, 2005, 06:46 PM
PZ Straube
external usenet poster
 
Posts: n/a
Default

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.










  #12  
Old June 13th, 2005, 07:18 PM
Duane Hookom
external usenet poster
 
Posts: n/a
Default

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.












  #13  
Old June 13th, 2005, 07:50 PM
PZ Straube
external usenet poster
 
Posts: n/a
Default

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.













  #14  
Old June 14th, 2005, 01:28 AM
Duane Hookom
external usenet poster
 
Posts: n/a
Default

I was just a babe when that film came out (or maybe I was 10).

--
Duane Hookom
MS Access MVP


"PZ Straube" wrote in message
...
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.















  #15  
Old June 15th, 2005, 01:00 AM
PZ Straube
external usenet poster
 
Posts: n/a
Default

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" wrote in message
...
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

  #16  
Old June 15th, 2005, 08:16 AM
Duane Hookom
external usenet poster
 
Posts: n/a
Default

I think there is very good software for managing stock prices, fund
activity,...

--
Duane Hookom
MS Access MVP


"PZ Straube" wrote in message
...
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" wrote in message
...
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



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
how to build a report to show multiple queries? _Adrian Setting Up & Running Reports 4 December 24th, 2004 08:00 AM
joining multiple queries auntiejack Running & Setting Up Queries 2 September 16th, 2004 11:56 AM
Merging with Access queries - Multiple CPutnam Mailmerge 5 August 21st, 2004 01:11 PM
Multiple SQL Queries on one sheet Kevin General Discussion 2 August 21st, 2004 03:03 AM
Multiple Queries Richard General Discussion 3 August 16th, 2004 11:26 PM


All times are GMT +1. The time now is 07:33 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.