Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Locust Grove, Virginia, USA
    Posts
    105
    Thanks
    52
    Thanked 1 Time in 1 Post

    Would like assistance with sorting entries in Access 2003

    I have a friend that is a big theater fan and years ago she asked me to make a database listing her 400 + programs that she has kept up to date by adding the new shows.
    It was set up in MS ACCESS 2003 using the following format:
    Field 1 (Increasing numerical entry order so she would know the total number), Title, Theatre (name), Location (City & Country), Year (includes month), Actor 1, 2, 3, 4.
    Recently she sent me a copy and would like me to separate or sort the information into individual theatre print outs, but no matter what I have tried to do with the various sort functions I donít end up with what she is looking for. She would like the final readout of a particular theatre to just include in this order: Year (starting with earliest ) & Title (of show) but with the theatre name not included in the readout.
    For example from The Kennedy Center entries the print out information would just include: June 1952, My Fair Lady
    Can someone assist me in how to set up the proper sort entries so I can get these results? Thank You.
    Respectfully, Graphics Guy

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    GG,

    Setup a query like the following:
    DBQuery.JPG
    When run it will prompt you for the Theatre name.
    QryPrompt.JPG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Graphics Guy (2012-10-24)

  4. #3
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Locust Grove, Virginia, USA
    Posts
    105
    Thanks
    52
    Thanked 1 Time in 1 Post
    Hi, Retired Geek,
    Thank you for the speedy response but unfortunately everything was so small that my 77 year old eyes could not read the top graphic and when I tried to enlarge it and make a print I could follow everything fell apart and all I end up with is indiscernible blobs.
    I do appreciate your trying though.
    Respectfully, Graphics Guy

  5. #4
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Locust Grove, Virginia, USA
    Posts
    105
    Thanks
    52
    Thanked 1 Time in 1 Post

    Arrow

    Hi Retired Geek,
    I think I have figured out what you were showing in your diagrams but my 2003 version does not show the same information.
    I went into Records>Filter>Advanced Filter Sort where I found a module similar to the one you show but
    I do not see any Show or Table option in my version. I am including a jpg image of what I have on my computer.
    Thank you. Respectfully, Graphics Guy
    Attached Images Attached Images

  6. #5
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    Quote Originally Posted by Graphics Guy View Post
    Hi Retired Geek,
    I think I have figured out what you were showing in your diagrams but my 2003 version does not show the same information.
    I went into Records>Filter>Advanced Filter Sort where I found a module similar to the one you show but
    I do not see any Show or Table option in my version. I am including a jpg image of what I have on my computer.
    Thank you. Respectfully, Graphics Guy
    I think RetiredGeek was trying to steer you towards creating a new Query based on the table, rather than applying a Filter/Sort to the table itself (which can get rather confusing if it gets saved with the table and you can't work out where your records have disappeared to!).

    Are you familiar with creating queries?

  7. #6
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Locust Grove, Virginia, USA
    Posts
    105
    Thanks
    52
    Thanked 1 Time in 1 Post
    Hi Jeremy,

    Thank you for taking the time to help me solve my problem.

    No, I do not remember how to set up queries.
    A little background. About 10 years ago I took an Access 2003 course at a local college. I happened to mention the fact to my friend and she suggested that I make my final database project using her box of assorted theater programs. She liked it so much that she has continued to add to it after each show and has kept it up to date, while I have never used Access since then. I find it easier to create simple databases in Excel. Now she would like to make print-outs showing what shows she has seen at various venues like Kennedy Center, New York, London, and various local theater groups. I can’t figure out how to just break out the desired information and leave the overall database intact. I would like to be able to just turn over the procedure to her so she can do it on her own in the future.


    Thanks again for responding. Respectfully, Graphics Guy

  8. #7
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    Quote Originally Posted by Graphics Guy View Post
    Hi Jeremy,

    Thank you for taking the time to help me solve my problem.

    No, I do not remember how to set up queries.
    A little background. About 10 years ago I took an Access 2003 course at a local college. I happened to mention the fact to my friend and she suggested that I make my final database project using her box of assorted theater programs. She liked it so much that she has continued to add to it after each show and has kept it up to date, while I have never used Access since then. I find it easier to create simple databases in Excel. Now she would like to make print-outs showing what shows she has seen at various venues like Kennedy Center, New York, London, and various local theater groups. I can’t figure out how to just break out the desired information and leave the overall database intact. I would like to be able to just turn over the procedure to her so she can do it on her own in the future.


    Thanks again for responding. Respectfully, Graphics Guy
    I'm not at an Access 2003 machine at the moment, but presumably at the moment you have one (or more) tables displayed when you have the Tables tab selected in the main Access Window. If you click on the Queries tab instead there should be a button marked New that will create a new query for you. I think (from memory) you want the top option - something like Simple query in Design View - then you are prompted for the table on which you base the query. Select your table of shows and click OK. This should get you to the screen posted earlier in this thread where you can select columns for display and enter criteria. When you exit the query by clicking the close button you will be prompted to save it under a name you choose - e.g. Visits for Theatre. You can then run the query at any time by double-clicking it from the main Access window.

    If this isn't clear, please post back for more help. You can't do any damage by creating a query (as long as it's selecting rows from the table, as opposed to a Delete or Update query!).

  9. The Following User Says Thank You to jeremybarker For This Useful Post:

    Graphics Guy (2012-10-24)

  10. #8
    Star Lounger tgw7078's Avatar
    Join Date
    Jul 2010
    Location
    Seattle, WA., USA
    Posts
    90
    Thanks
    1
    Thanked 12 Times in 12 Posts
    Quote Originally Posted by Graphics Guy View Post

    Field 1 (Increasing numerical entry order so she would know the total number)

    There are easier ways to determine totals than having a dedicated field with a numeric series...


    Quote Originally Posted by Graphics Guy View Post
    Year (includes month), Actor 1, 2, 3, 4.

    Year is a reserved word. You will always be doing yourself a favor to avoid the use of any reserved words, when giving names to objects and controls in Access. Here is a link you may want to follow, to learn more on this topic:
    Problem names and reserved words in Access

    http://allenbrowne.com/AppIssueBadWord.html

    Also, are you saying that you have four fields in this table for actors (Actor1, Actor2, Actor3 and Actor4)? If so, be aware that this design is not properly normalized; it could be improved, which will make future requests easier to accomodate.

    Quote Originally Posted by Graphics Guy View Post
    Recently she sent me a copy and would like me to separate or sort the information into individual theatre print outs

    I suggest creating a nice report, based on a query.


    Quote Originally Posted by Graphics Guy View Post
    She would like the final readout of a particular theatre to just include in this order: Year (starting with earliest ) & Title (of show) but with the theatre name not included in the readout. For example from The Kennedy Center entries the print out information would just include: June 1952, My Fair Lady
    Quote Originally Posted by Graphics Guy View Post

    As others have indicated, create a query instead of trying to apply a filter. In Access 2003, select "Queries" in the database window. Click on the New button, and then on Design View. Add the Program Information table to the query, and then click on the OK button to dismiss the Show Table dialog.

    You can drag fields from the table to the QBE (Query By Example) grid, in the order that you wish to display them. Alternatively, you can double-click on the field names, or you can select them in the QBE grid by clicking into the Field area, and using the dropdown. Add an ascending sort to the Year field. Note: It appears as if your Year field is a Date/Time data type (this is good), with an applied format, since the values are right justified.

    Alternatively, look for the SQL indication in the upper left corner, in query design view. Click on this toolbar button to open the SQL View. If you have not yet added any fields, you should see just the word "Select" highlighted. Backspace over this to remove it. Then copy and paste the following SQL (Structured Query Language) statement:

    SELECT [Year], Title FROM [Program Information] ORDER BY [Year]

    Save the query with a descriptive name, preferably with a naming convention prefix such as "qry" and without any spaces or special characters in the name. For example, save as: qryProductionTitlesByYear

    You can now use this query as the source for a new report. However, a report will not obey sort orders applied at the query level--for that you will need to use View | Sorting and grouping in report design view.

    ~~~~~~~~~~~~

    Here is the SQL statement for another query, which allows you to easily count records (without having to rely on Field1):

    SELECT COUNT(*) FROM [Program Information]

    This query has no criteria or grouping but that is easy to add as well, if you want.

    Good Luck
    Last edited by tgw7078; 2012-10-22 at 04:29.
    Tom Wickerath
    Microsoft Access MVP
    4/1/2006 - 3/31/2012

  11. The Following User Says Thank You to tgw7078 For This Useful Post:

    Graphics Guy (2012-10-24)

  12. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    GG,

    Sorry I was off the grid for the last 5 days. Have you managed to work the problem out yet?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  13. #10
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Locust Grove, Virginia, USA
    Posts
    105
    Thanks
    52
    Thanked 1 Time in 1 Post
    Hi RG,

    Quote Originally Posted by RetiredGeek View Post
    GG,

    Sorry I was off the grid for the last 5 days. Have you managed to work the problem out yet?
    Me too! I wasn't available last weekend. I am just now going over what others have written and don't fully understand the process but have printed out most of the comments so I can study them and attempt to follow their suggestions. I am now working on a copy of the original database, but eventually I want to set something up so she can still add updates and also be able to print out a report without my dubious help. I am SO AFRAID that I am going to change something that will crash or corrupt the basic database and I don't have any idea how I created it years ago while attending the class and having an instructor supervising.

    I do appreciate the suggestions what you and others have sent to me, and would be completely lost without all the help.
    Respectfully, Graphics Guy

  14. #11
    Star Lounger tgw7078's Avatar
    Join Date
    Jul 2010
    Location
    Seattle, WA., USA
    Posts
    90
    Thanks
    1
    Thanked 12 Times in 12 Posts
    Hi Graphics Guy,

    Would you be okay with posting a zipped copy of this database, so that others can provide more effective help?
    Tom Wickerath
    Microsoft Access MVP
    4/1/2006 - 3/31/2012

  15. #12
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Locust Grove, Virginia, USA
    Posts
    105
    Thanks
    52
    Thanked 1 Time in 1 Post
    Hi tgw7078,
    Attached is a copy of the Master Database.
    I am unsure of how to send copies but will try, please forgive me if you get nothing.
    Respectfully, Graphics Guy
    Attached Files Attached Files

  16. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    GG,

    Attached is your DB with the query "qryTheaterPrograms".
    Run it and type "Kennedy Center" when prompted and you get 42 results.
    (don't enter the quotes)
    I hope this is what you're after.
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by RetiredGeek; 2012-10-23 at 17:33.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  17. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Graphics Guy (2012-10-24)

  18. #14
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    What you could do is to put a Like in the query so you don't have to keyin the whole theatre name.

    What you could also do is to put a combo box on a form with Distinct theatre names and run the query based upon the value of the combo box.

    I noticed you wanted to print a report in your original, so you could use the combobox to select the theatre and produce a report based upon the query that uses the combobox as its selection.

  19. #15
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Locust Grove, Virginia, USA
    Posts
    105
    Thanks
    52
    Thanked 1 Time in 1 Post
    Quote Originally Posted by patt View Post
    What you could do is to put a Like in the query so you don't have to keyin the whole theatre name.

    What you could also do is to put a combo box on a form with Distinct theatre names and run the query based upon the value of the combo box.

    I noticed you wanted to print a report in your original, so you could use the combobox to select the theatre and produce a report based upon the query that uses the combobox as its selection.
    Hi Patt,
    Thanks for your message.
    I don’t quite understand where to put Like in the Query.
    There is a Combo Box for the Theater location but they have changed and I don’t remember how to make corrections.
    If you get a chance look in my message above where I sent a copy of the database for all to see and comment on.
    I have plans for this evening so I will check out all messages in the morning.
    I do appreciate everyone's comments and input.
    Respectfully, Graphics Guy

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
  •