Unable to have multiple queries feeding a single report

[Guest]

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.

 

[Duane Hookom]

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.

 

[Guest]

PZ, it just sounds like bad query design. Why can't the report be based on a
single query? If the database is small, tell me what your goal is and send
over a copy stripped of the most of the data.

Destin Richter
(e-mail address removed)

 

[Guest]

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.

 

[Guest]

Destin,
Thanks for taking the time to respond.
As I mentioned to Duane, I screwed up - I didn't think this through
carefully. I have now put another query on top of the one that was
previously feeding data to my reports and in this new query, I only included
the fields needed for the reports, not some of the supporting fields contain
preliminary calculations, and that gives me room to add the fields I couldn't
before - whcih is what led em to think I needed a second query to feed the
report.
Sorry to have bothered everyone with this rookie mistake.

If you don't mind me asking, what happens when you have a report containing
more than the maximum number of fields? What is the key point when using two
queries to feed a report to make sure it works?
Thanks again.

 

[John Spencer (MVP)]

A report can only be based on a single query.
A report can contain sub-reports.
A query can contain up to 255 columns (fields, calculated fields) and therefore
a report can have a maximum of 255 fields.

Are you using the report wizard to build your report? If so, it has a much
lower limit than if you design the report yourself. I don't know what the limit is.

 

[Guest]

John,
Thank you as always for your help.
I was not aware that the Report Wizard has limitations. That might explain
a portion of my problem. Interesting.
I'll do a search and see if having subreports increases the number of fields
beyond 255.
Thanks again.

 

[Duane Hookom]

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.

 

[Guest]

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]

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.

 

[Guest]

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]

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.

 

[Guest]

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]

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

 

[Guest]

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]

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