Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: 'Qeury too complex' aggghh!! computers are stupid (Access 2002)

    The error message you are receiving ( too complex...) may be from running queries against other queries that are grouping data (i.e., you set the Totals functionality in the design view of the query). Is this happening?
    Regards,

    Gary
    (It's been a while!)

  2. #2
    Lounger
    Join Date
    Jun 2002
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Qeury too complex' aggghh!! computers are stupid (Access 2002)

    Gary,
    Umm...not so sure what you mean. In my original queries (that worked until about the April set of reports) I just had a query based on a query. Not with the totals function or anything. Just calculated fields. Those weren't grouped. The later ones I tried I based on a union query and therefore grouped by employee name. Is that what you mean? In either case, they were "too complex."

    -nez

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: 'Qeury too complex' aggghh!! computers are stupid (Access 2002)

    Queries based on queries should not give you a problem... I have several that go about seven levels deep. However, if you are not grouping data, they should still work. What I mean by grouping is that say, for instance, your tables contain hours by months. You would want to group or total the data by month to get the total hours for each month. Thus, you are grouping your data by month. If you are trying to group your data in one query and then try to run a query off of the query that is grouping the data, you may get the error you indicate.

    Union queries work, however, have given me many headaches in the past.

    I do not understand what you mean by "...I based on a union query and therefore grouped by employee name"

    I would suggest searching on the error message your received as there are many posts that discuss various ways to cause and resolve it.

    HTH.
    Regards,

    Gary
    (It's been a while!)

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: 'Qeury too complex' aggghh!! computers are stupid (Access 2002)

    I don't see why you would need a separate table, form, query, report, etc. for each month of the year, regardless of what printed form they're using. It sounds to me like your problem is really in the table design you're using. If you're trying to handle each day field in a table and all the other embellishments you've added on, I'm not really surprised that your query has finally broken down of its own weight. particularly if you have 24 tables involved with a single report.
    <hr>I have to do it this way, trust me, I've tried several different ways of doing it, it just won't work for the type of report I have to do. <hr>
    Sorry, but I don't buy that. In a fully relational design, you can pull any data you need out of the tables. If you're trying to design the table structure to reflect the fields in a report, then you've built yourself a nightmare, and it isn't likely that you're going to be able to come out of it unscathed unless you revisit the table design.

    And I taught myself Access too, ten years ago when it was first released. The hard part is that you never really learn Access, because it keeps changing and because it has such a tremendous range of capabilities. You have to keep updating your knowledge and skills all the time.
    Charlotte

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: 'Qeury too complex' aggghh!! computers are stupid (Access 2002)

    What does the report need to show.
    Why do you need a different table for each month?
    Pat

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 'Qeury too complex' aggghh!! computers are stupid (Access 2002)

    At the risk of piling on to other's replies: there should be no need to use so many tables, queries, forms and reports. Your present design will be nearly impossible to maintain. I think it's better to redesign now - however painful.

    You should be able to put all relevant data into a small number of tables, for instance:

    tblEmployees stores general information about employees (name, address, date of birth, SSN, etc.). The primary key is an AutoNumber field EmployeeID.

    tblPayroll stores payroll information. It contains fields EmplyeeID (used to link this table to tblEmployees, date/time info and a few other fields depending on what you want to do (for instance an Overtime status field). The date/time info could be in one field, but it's probably easier to have a date field and a time field. (Others may correct me here)

    This means that tblPayroll will contain lots of records, because it contains all payroll information about all employees, but the structure will be very simple. Access is quite good at handling such things.

    You can create queries, data entry forms and reports on these tables.

    Now, your payroll application needs a central form on which the user can select a time period. This can be passed to a report. To open a report from a command button on a form, you use code like

    DoCmd.OpenReport ReportName:="rptMyReport", View:=acViewPreview, _
    WhereCondition:="BookDate Between [Forms]![MyForm]![txtStartDate] And [Forms]![MyForm]![txtEndDate]"

    Although the report will be based on *all* records when opened directy, this way it will only display and aggregate the records whose BookDate is between the dates entered on the form.

    There is much more to this. If you decide to start redesigning, go step by step and don't hesitate to ask questions as you go along.

  7. #7
    Lounger
    Join Date
    Jun 2002
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Qeury too complex' aggghh!! computers are stupid (Access 2002)

    I'm not sure how to reply to everyone's post at once. But oh well, that's how this is directed. It is necessary to put the data I have in the tables I do. And I really don't think that's my problem. For one, I tried breaking up the tables so that like standard time is in a separate table than overtime and then I would pull them together in a query and base my form on that query. It failed miserably. I've done something like that in previous databases I've designed and it worked for those situations, so I do know how to do that. For some reason, however, I cannot do that in this one. I do have the employees table separate from the hours table and that's just fine. And I cannot do just one table that all the records are based on for every day of the year, though that would certainly simplify things if I could. The report has what would be subsequent records horizontally as opposed to vertically where it would be no problem. I don't know. I guess there's no thing I just didn't know existed here that's the secret to carrying data from one report to the next. Thank you all for your help.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 'Qeury too complex' aggghh!! computers are stupid (Access 2002)

    Have you considered using crosstab queries? They can be used to lay out data horizontally and vertically. For instance, employees could be row headers and hours could be column headers. By filtering, you can restrict the data to the records for one day or one week, although the table contains records for a whole year.
    You can base raports on crosstab queries.

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: 'Qeury too complex' aggghh!! computers are stupid (Access 2002)

    In addition to Han's suggestion of crosstabs, we do this sort of thing pretty frequently. People are always wanting to look at data in a horizontal grid even though we store things as individual records. One solution we frequently use is to create a temporary table where we put what ever data the user wants into fields in the table, and then base the form/report on that. Another thing you might want to explore is the use of subforms - they can often solve what otherwise appear to be nasty problems, though they do present data in a vertical format rather than a horizontal one. Is one of your constraints that the client is insisting that the report look exactly like the Excel report they have been using? If so, that is likely to be a fatal flaw in the long run. At the risk of sounding like an old duffer (I am), we did a project with some similarities to this in 1994 to replace a legacy system, and we are still working on it 8 years later. It took us nearly 6 years to get rid of all the legacy stuff we put in in the beginning. Are you sure you want to work on something with that possibility?

    As I don't know your relationship with the client, this may be totally inappropriate, but:
    Have you considered suggesting that they purchase an inexpensive payroll program to replace their Excel Payroll system - there are several that are access based, and can be readily customized to meet specific or unusual needs? Obviously if you are trying to make a living doing custom payroll systems that isn't the right choice, but sometimes it's easier to convince people to make changes when the product already exists. In any event, good luck with your project - payroll systems always seem to pose challenges.
    Wendell

  10. #10
    Lounger
    Join Date
    Jun 2002
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Qeury too complex' aggghh!! computers are stupid (Access 2002)

    Wendell, thanks so much for your reply and by making me feel a little better that maybe I'm not quite the idiot I appear to be. Yes, I have subforms in my database already (that was a "fun" task). I believe it is impossible to negotiate the layout of the report, unfortunately. I'll try the temp tables though, thanks!!!

  11. #11
    Lounger
    Join Date
    Jun 2002
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    'Qeury too complex' aggghh!! computers are stupid (Access 2002)

    Okay. I'm not sure how to explain my problem. First of all, I taught myself all I know about Access, so basically I don't know the lingo as well as someone who actually took a class. This also means that I know a lot about the specific things that I've been doing in Access and little to nothing about things I haven't had to do. That said, here's what I'm doing. I work for a company that has roughly 40 employees of whom they are in charge of doing payroll for. I am creating a payroll system in Access for them as opposed to their current one in Excel. As of now, the bookkeeper must enter the same information into several workbooks in Excel, I am eliminating that problem (or desperately attempting to). Because of the specific form (Report in Access terms) that they have to use for the payroll, I have to do a separate table, form, query, report, etc. etc. for each month in the year. I also have to specify a separate field for each day (either 1-15 or 16-31 depending on which half of the month we are talking about) within each table for several different categories. For example, I have fields defined ST1, ST2, ST3...ST15 that stands for Standard Time for each day. Similarly I have OT1, OT2, OT3...OT15 for Overtime, etc. I have to do it this way, trust me, I've tried several different ways of doing it, it just won't work for the type of report I have to do. Consequently, I have 24 tables just associated with one particular report with close to the limit of fields that I can define. (I actually had to work with that for a while, it kept telling me I had too many fields defined). So basically, in setting up this database, I worked with two tables instead of all 24, January 1 and January 16. I had everything working smoothly and had everything ready to go until I started adding in the rest of the tables, forms, queries, reports, etc. to go along with the other 22. My problem occurred because I have to deal with something called "Balance Brought Forward." This is a balance that is calculated at the end of each pay period (one table) that must be carried over to the next pay period to do the same calculation for that period and so on. This is a continual balance, and I simply have it set up to where the bookkeeper enters in the values from the previous year in at the beginning of a new year. Now, Access should take over so she doesn't have to enter it in at the beginning of each pay period. (I did this with Excel using the link function). I had it working with the first and second tables through a query and subreport. This worked fine, but when I tried to do this same thing with the rest of the pay periods, Access tells me its too complex. I have decided this is because Access doesn't like when you base a query on a query on a query......I have tried several different methods of doing this calculation, and every time I get that same error. I did have it working by using a union query and then a query that summed up the totals for each employee, but to do that all the way through December means you have to wait like 12 years (okay not really THAT long, but close) for your query to open, much less a report based on that query. Now, I could be a total idiot who's overlooking the obvious, I really just don't have any more ideas. I'm not sure I've been clear enough, if someone can offer me any help whatsoever, that would be great. If you need more clarification, let me know. Thanks ever so much...


    -----Gary, no the queries were not based on group data.
    HansV, thanks, I'll look into the crosstab thing.

    Everyone: I tried breaking it up again at everyone's suggestion and as far as I can see, it is impossible to use this design because of the report. I'm sorry I could not explain well enough how my report works. I have a few more ideas to try, so I'm going to it. Thanks again.


    ************************************************** ************************************************** ***************
    Actually, I thought about it some more and Wendell is exactly right. I have to make something that was Excel into Access. So my problem comes (hopefully this clears things up a bit) in finding a comparable Access function to Excel's "paste link" function. I cannot just link tables or whatever because it only affects certain fields and those certain fields then affect subsequent fields throughout the database. Is there ANY way to get a result in Access that compares to the paste link function in Excel?

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: 'Qeury too complex' aggghh!! computers are stupid (Access 2002)

    Actually, I've built applications that have input forms replicating columnar printed forms. I did it with unbound forms that used code to write each individual piece of information to the table, with more code to retrieve the data when I needed to populate a screen. That's the only way I would even consider projects like that because it allows you to make the form look like anything the client wants while still maintaining relational table designs underneath. Once you build the basic code, it isn't hard to adapt it to other forms and other applications.
    Charlotte

  13. #13
    Lounger
    Join Date
    Jun 2002
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Qeury too complex' aggghh!! computers are stupid (Access 2002)

    Wow, yah. I wouldn't even know where to start in something like that. I didn't know you could even do that. Something to look into, I guess, I'm out of options. Thanks!

  14. #14
    Lounger
    Join Date
    Jun 2002
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Qeury too complex' aggghh!! computers are stupid (Access 2002)

    Okay, I have a new problem. A solution to this may clear up all my other problems. Is there any way to bind calculated controls in a form to a field in a table? Or even in queries? I know how to do calculated controls, but I can't ever send the result back to the underlying table. Any help would be greatly appreciated. Thanks!

  15. #15
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: 'Qeury too complex' aggghh!! computers are stupid (Access 2002)

    In a word, No. You can't bind a calculated control to anything. It's bound to the expression that populates it. However, you *can* calculate a value in code and then set a bound control equal to that value. Note that you cannot set the value of a field bound to an expression.
    Charlotte

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •