Results 1 to 6 of 6
  1. #1
    MichealShinn is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2012
    Posts
    24

    Query returns ro results

    I'm trying to put more than one table into a query. The first table has 55 fields. When I put only that table into the query, the wuery returns all 55 fields. As soon as I add a second table, even before I've entered any fields from that table into the query, the query stops returning results.



    According to what I've been able to find on the Internet, an Access 2007 query can have up to 32 tables and 255 fields. I have two tables and 55 fields. Does anybody know what the problem here might be?

    Thanks in advance for your help.

  2. #2
    mejia.j88 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Nov 2011
    Location
    california
    Posts
    228
    is there a relationship between the two tables?

  3. #3
    MichealShinn is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2012
    Posts
    24
    Quote Originally Posted by mejia.j88 View Post
    is there a relationship between the two tables?
    No.

    We're a utility company that offers our customers the chance to buy Green Power. Our customers can sign up to buy in tons of different ways. They can choose to pay a fixed amount per month or a buy a certain number of kwh per month or buy a certain percentage of their energy usage every month. So, I have have a query that goes out and grabs all the customers who have signed up to pay $3.75 per month. I pull their meter number, how much energy they paid for and how much money they paid us. Then I run another query to add up the total usage and revenue and count the number of meters. I put that results table into a query that produces a table named Tables Combined, which is the data source for a report.

    Then I pull all the records of people who pay $3.75 a month and started participating in January, 2012. Then, another query to count those, and that table goes into the Combine Tables query. Then, another query to pull the records of customers who were paying $3.75 a month and dropped out of the program in January, 2012 and another query to count those, and that results table goes into the Combine table query.

    When I finished with residential customers who pay a certain amount per month, I have 27 tables and 55 fields in the Combine Tables query. So far, everything has worked fine.

    Next, I start working on another set of queries for people who agreed to pay for 300 kwh per month. Another query to pull those records and then one to sum and count. When I try to add that table to the Combine Tables query, Access stops giving me results. So, I stop after I have the fixed dollar people in the query and get a table named Tables Combined - Res Dollars. I start a new query and add that table to it. I drag the star to the field list. Now, I've got a query with one table and 1 field (or maybe 55 fields, depending on how Access counts). Again, everything is fine so far. However, as soon as I add the second table to that query, the table named Tables Combined - Res kwh, Access stops returning results.

    We've just re-designed our Green Power Tomorrow program. When I developed the report for the previous incarnation of the program, I was able to run a query that combined the first group of tables and then made that results table the first table in the second query, and everything worked fine. Now I'm trying to do the same thing, and it's not working.

    Thanks for your help.

  4. #4
    MichealShinn is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2012
    Posts
    24
    I think I may have answered my own question. The problem, apparently, was that there were Nulls in a couple of the fields. After I put zeros into those fields, the query returns results.

  5. #5
    mejia.j88 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Nov 2011
    Location
    california
    Posts
    228
    if those fields are subject to nullity,
    you should use the Nz function to prevent this from happening again.

  6. #6
    MichealShinn is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2012
    Posts
    24
    Yupper, that's part of the plan. I just hadn't gotten to that part yet. Thanks again.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 05-14-2012, 07:24 AM
  2. CrossTab Query Max X Returns
    By systems013 in forum Access
    Replies: 5
    Last Post: 11-04-2011, 01:12 PM
  3. Replies: 13
    Last Post: 01-13-2011, 10:15 AM
  4. Query returns duplicates
    By RobRay in forum Queries
    Replies: 3
    Last Post: 10-26-2010, 01:38 PM
  5. Replies: 2
    Last Post: 08-05-2010, 09:29 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums