Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    New Lounger
    Join Date
    Sep 2014
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query - Report Help

    3 Tables: Month, Names and Exclude

    The following query is used to match by ACCOUNT the MONTH data to the NAMES table while excluding the accounts from the EXCLUDE table. It is a query I have been using for years. After I run the query I export the results into Excel and isolate the Top 10 Accounts for each of the 12 districts in the database file.

    Instead of exporting to Excel, I would like to create a report in Access with the same desired output. I found a query to create a top 10, but the result was for all of the districts combined. My search to create a top 10 for each district has been exhausting. Any help you can provide would be greatly appreciated.

    SELECT [Month].DISTRICT, [Names].COMPANY, [Month].ACCOUNT, [Month].DOLLARS, [Month].ITEMS
    FROM ([Month] LEFT JOIN [Exclude] ON [Month].RT = [Exclude].RT) LEFT JOIN [Names] ON [Month].RT = [Names].RT
    WHERE ((([Exclude].ACCOUNT) Is Null));

  2. #2
    New Lounger
    Join Date
    Sep 2014
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If anyone could assist me with this query it would be greatly appreciated.

    Thanks, Dave

  3. #3
    New Lounger
    Join Date
    Sep 2014
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I could really use some help with this query. If I am not posting this in the correct forum, can someone please direct me to the proper one.

  4. #4
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Can you provide an example database, with some data (maybe not real data), that could be used to develop the result you need?
    Rui
    -------
    R4

  5. #5
    WS Lounge VIP Browni's Avatar
    Join Date
    Dec 2009
    Location
    Rochdale, UK
    Posts
    1,653
    Thanks
    38
    Thanked 161 Times in 139 Posts
    There's no order by clause and there isn't a group by clause.

  6. #6
    New Lounger
    Join Date
    Sep 2014
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by ruirib View Post
    Can you provide an example database, with some data (maybe not real data), that could be used to develop the result you need?
    I am not sure how I would go about that. My database includes 12 districts, but the example could be Cities, States or anything for that matter. The database has thousands of accounts and I want to know the Top 10 for each district. I am trying to create a report with this data to avoid exporting the data into Excel and using filters to extract my top 10.

    It seems relatively straightforward when I explain it, but it has been far from that when I try to create it.

  7. #7
    New Lounger
    Join Date
    Sep 2014
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Browni View Post
    There's no order by clause and there isn't a group by clause.
    I have tried to add clauses into the query but I got error messages everytime I tried something.

  8. #8
    New Lounger
    Join Date
    Sep 2014
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Is there anyone here that can help me with this? I need to come up with this report by year end

  9. #9
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,726
    Thanks
    147
    Thanked 156 Times in 149 Posts
    Trouble is, without some example data it's very hard to suggest a way forward. I would ask though, did you write the SQL in SQL view or design view. In the latter there's a sigma option in the tool bar called totals. It can help with grouping and other methods. You group on a field, then apply something to that field e.g. group on a field called subtotal and produce totals. You can add criteria to those fields as well. That's the best I can suggest.

    Good luck

    p.s. I'm referring to Access 2003 here, the names might be different in newer versions.

  10. #10
    New Lounger
    Join Date
    Sep 2014
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by access-mdb View Post
    Trouble is, without some example data it's very hard to suggest a way forward. I would ask though, did you write the SQL in SQL view or design view. In the latter there's a sigma option in the tool bar called totals. It can help with grouping and other methods. You group on a field, then apply something to that field e.g. group on a field called subtotal and produce totals. You can add criteria to those fields as well. That's the best I can suggest.

    Good luck

    p.s. I'm referring to Access 2003 here, the names might be different in newer versions.
    I created a sample database with the intent to upload it but it appears that it is not an acceptable attachment. I used design view to create the query.

  11. #11
    New Lounger
    Join Date
    Sep 2014
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This is the SQL of the sample database I created.

    SELECT [All Accounts and Districts].DISTRICT, [Account Names].NAME, [All Accounts and Districts].NUMBER, [All Accounts and Districts].DOLLARS, [All Accounts and Districts].ITEMS
    FROM ([All Accounts and Districts] LEFT JOIN Exclude ON [All Accounts and Districts].NUMBER = Exclude.NUMBER) LEFT JOIN [Account Names] ON [All Accounts and Districts].NUMBER = [Account Names].NUMBER
    WHERE (((Exclude.NUMBER) Is Null));

    Any suggestions on how to provide a sample?

  12. #12
    New Lounger
    Join Date
    Sep 2014
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Here is a print screen of my query results. What I am hoping to do is to create another query that only pulls in the Top 10 (dollars) for each district.
    Attached Files Attached Files

  13. #13
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Quote Originally Posted by daveinpgh View Post
    I created a sample database with the intent to upload it but it appears that it is not an acceptable attachment. I used design view to create the query.
    If you want to attach an Access database, compress it to a zip file, and you should be able to upload that. Before you do that be sure to compact and compress it if you have done any significant work in it. Seeing your table structure would help considerably - there may be issues with the schema that you have chosen that makes the problem more difficult.
    Wendell

  14. #14
    New Lounger
    Join Date
    Sep 2014
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I feel like beating my head against a wall. My company only has 7-Zip available to zip the file. I did that and tried to upload it but it is not an acceptable format here. I do not have admin rights to download any other Zip programs.

  15. #15
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    7-zip allows you to create a .zip file. Probably it's not the default format, but you surely can choose it when compressing.
    Rui
    -------
    R4

Page 1 of 2 12 LastLast

Tags for this Thread

Posting Permissions

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