Lavsa, Rich
Rich_Lavsa at pghcorning.com
Wed Sep 3 14:24:35 CDT 2003
I have had this problem in Access with too many calculated fields. The way i got around it was a very complex series of queries and USER DEFINED FUNCTIONS. These user defined functions do simple things such as calculate age, to tough multiple comparison of tables to return one value for each record based on entries put in by the user (almost like a dynamic lookup). I didn't have 1000, but was well over 100. All my calculations are done in the series of queries now, and the report simply calles the query. I call the userdefined functions in the necessary query which eventually will be pulled out in a Master Query which makes it ideal because every new report or new query for a new report is based off this Master query which holds all the information (including all calculated values, close to a warehouse but dynamic). The database structure itself was simple (only 18 tables) however the calculations were tough as anyone out there that has worked with Lawyers and HR at the same time would know. It was tough to design, and streamline all the calculations and queries but the reports all refresh in under 2 seconds with all calculations calculated. This was ideal in my case because if a change is required you can look at the documentation or simply follow back the query design to locate user defined functions or the query in which this query got its data from. I was doing some of what you were doing but the maintenance was incredible with reports and sub reports. Now I don't have to go into each report and change a bunch of controls if the Workers Union comes through and makes a new pension vested ruling, or accrued value decision that would make my life HELL. To me this was a much better solution, everything is Dynamic. Rich -----Original Message----- From: Paula Wright [mailto:paulawright at boddienoell.com] Sent: Wednesday, September 03, 2003 12:51 PM To: Access Developers discussion and problem solving Subject: RE: [AccessD] Crystal Reports Resubmit It's been a while since I recreated the reports in Crystal. If I remember correctly the message was something along the lines of "Too many fields defined." These reports are quite extensive with approximately 1000 calculated fields, some on the report, some in the query. Rather than having a detail that changes with the record, each field is based on one record in a table and is a separate field, if you understand what I'm saying. I did check the specs in Access and it truly was beyond the capabilities of Access. There are 7 pages to the report (it's actually all one report) and I broke it down as far as I could into 7 different reports. I have been writing in Access for 7 years and have written some very complex reports. I had never run across that either. It took me quite by surprise, not realizing that it was even possible and it was rather frustrating as I had spent many, many long hours writing these reports. I started with the basic foundation of displaying the base calculations themselves for each report and then went back to include percentages. That's when I ran across the problem. -----Original Message----- From: William Hindman [mailto:wdhindman at bellsouth.net] Sent: Wednesday, September 03, 2003 11:33 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Crystal Reports Resubmit Paula ...can't help you with CR since I don't use it ...but could you elaborate on "Access would not handle all the fields required for the reports" ...I've simply never run into that situation :) ...there may well be a much simpler and better solution available within Access itself if you can give us a bit more information. William Hindman "the world is full of trickery. But let this not blind you to what virtue there is; many persons strive for high ideals, and everywhere life is full of heroism." ...from Desiderata ----- Original Message ----- From: "Paula Wright" <paulawright at boddienoell.com> To: <accessD at databaseadvisors.com> Sent: Wednesday, September 03, 2003 11:07 AM Subject: [AccessD] Crystal Reports Resubmit > > I hope this is better. Funny, I don't remember having this much > trouble before. But thanks for your help. > > I have just been informed that my posts were coming through as > attachments and most readers could not see my question. So I am once > again resubmitting > in the hopes that someone knows how to answer my question. I > apologize and > thank you. > > I have an Access database (XP) from which I created > several Crystal Reports (8.5) because Access would not > handle all the fields required for the reports. I may have gone about > this backwards because now that I have started investigating how to be > able to view the reports in Access I find that one actually must > create the reports through Access when using the Wizard. If so then > hopefully this will be a simple copy/paste procedure once I get into > it. > > The problem is that once I started up the Crystal Report Wizard and > chose the query from which I wanted to create the first report, I was > asked to enter a database password. My database does not have a > password. I have searched for a means to handle this but to no avail. > Has anyone experienced this problem before? > > Or better yet, what I really need to do is view the > reports from within Access using the Crystal Active-X. > I've been over the Accocx97.mdb example on how to set up a > CR in Access, however in XP I don't see a place to put the file name > of the report. > > I'm hoping someone can help me real quick as I've already wasted too > much time re-doing these reports in Crystal after having found out > that Access won't handle them. > > > THANK YOU > > > > Paula Wright > Programmer/Analyst > paulawright at boddienoell.com <mailto:paulawright at boddienoell.com> > (252)937-2800 ext.1355 _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com