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. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Unable to have multiple queries feeding a single report
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. |
#2
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. "Destin Richter" wrote: 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 "PZ Straube" wrote: 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. |
#6
|
|||
|
|||
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. PZ Straube wrote: 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. "Destin Richter" wrote: 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 "PZ Straube" wrote: 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. |
#7
|
|||
|
|||
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. "John Spencer (MVP)" wrote: 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. PZ Straube wrote: 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. "Destin Richter" wrote: 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 "PZ Straube" wrote: 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. |
#8
|
|||
|
|||
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. |
#9
|
|||
|
|||
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. |
#10
|
|||
|
|||
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. |
|
Thread Tools | |
Display Modes | |
|
|
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 07: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 |