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 |
#11
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 |