Results 1 to 6 of 6
  1. #1
    cowboy is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2010
    Posts
    291

    is it possible to put these 2 queries into 1 ??

    What I would like is 1 query that displays the columns Area , CountofArea, CountofArea2. I tried using the design query but it put a LEFT JOIN on the whole first statement which gave me some crazy numbers. The first query is:

    SELECT Area.Area, Count(InspectionsDI.Area) AS CountOfArea
    FROM Area LEFT JOIN InspectionsDI ON Area.Area = InspectionsDI.Area
    GROUP BY Area.Area, Area.ID
    HAVING (((Area.Area)<>"" And (Area.Area)<>"ALL"))
    ORDER BY Area.ID;

    and then second query is:



    SELECT Area.Area, Count(Inspections.Area) AS CountOfArea2
    FROM Area LEFT JOIN Inspections ON Area.Area = Inspections.Area
    GROUP BY Area.Area, Area.ID
    HAVING (((Area.Area)<>"" And (Area.Area)<>"ALL"))
    ORDER BY Area.ID;

    thanks

  2. #2
    NassauBob's Avatar
    NassauBob is offline Not THAT Green
    Windows XP Access 2003
    Join Date
    Feb 2010
    Location
    Augusta, GA
    Posts
    61
    Could you possibly return a visual of your two tables, including two or three rows from each? It would really help minimize my confusion.

  3. #3
    cowboy is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2010
    Posts
    291
    Was this what you were looking for?

  4. #4
    NassauBob's Avatar
    NassauBob is offline Not THAT Green
    Windows XP Access 2003
    Join Date
    Feb 2010
    Location
    Augusta, GA
    Posts
    61
    Okay, so I gather that you have a table full of inspection data on (manholes?). You want to display each area, then a count of inspection hits for each area, yes?

    What I do not understand is the need for two different counts. What is each count totaling?

  5. #5
    cowboy is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2010
    Posts
    291
    Well the reason I needed 2 totals was one of the totals where manholes and one was for drop inlets. They have distinct differences so they cant be put in the same table without wasting a lot of space. I have decided to go another route instead of putting these 2 tables together I am gonna make a report for DIs and a report for manholes. Only problem I am having now is summing the info up but you are helping me do that on another thread :-)

    Check out my report so far in this jpeg.

    I used text boxes and set the border to transparent and then backcolor a light color to create the shading you see. I didnt like the dividing lines and putting a border on the text boxes didnt help see the rows very clearly. If you have any ideas on making it look clearer I would love to hear them. Other than that this thread is done, just waiting on the other thread to get the sum of the columns. Thanks much.

  6. #6
    NassauBob's Avatar
    NassauBob is offline Not THAT Green
    Windows XP Access 2003
    Join Date
    Feb 2010
    Location
    Augusta, GA
    Posts
    61
    The report does look very nice - concise and clean.

    Great work on solving your own problem!

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

Similar Threads

  1. Two Queries not getting along in another query
    By colorado in forum Queries
    Replies: 1
    Last Post: 06-29-2009, 03:24 PM
  2. Help with Queries and forms
    By dcwang3 in forum Access
    Replies: 0
    Last Post: 05-28-2009, 08:00 AM
  3. Percentage Queries
    By bangemd in forum Queries
    Replies: 18
    Last Post: 05-21-2009, 09:32 AM
  4. need help with queries
    By gromit95 in forum Queries
    Replies: 1
    Last Post: 02-06-2009, 06:50 AM
  5. Crosstab Queries
    By albst130 in forum Queries
    Replies: 0
    Last Post: 03-07-2007, 09:32 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