Would like assistance with sorting entries in Access 2003

Topic

New Reply

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

Reply | Quote

Replies

GG,

Setup a query like the following:
32175-DBQuery
When run it will prompt you for the Theatre name.
32176-QryPrompt
:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

Hi Jeremy,[/SIZE][/FONT]

Thank you for taking the time to help me solve my problem. [/SIZE][/FONT]

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. [/SIZE][/FONT]
[/FONT][/COLOR]
Thanks again for responding. Respectfully, Graphics Guy[/SIZE][/FONT]
[/FONT][/COLOR]

Reply | Quote

Hi Jeremy,[/SIZE][/FONT]

Thank you for taking the time to help me solve my problem. [/SIZE][/FONT]

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. [/SIZE][/FONT]
[/FONT][/COLOR]
Thanks again for responding. Respectfully, Graphics Guy[/SIZE][/FONT]
[/FONT][/COLOR]

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!).

Reply | Quote

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…[/SIZE]

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
[/FONT][/SIZE]
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.

[/SIZE]

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.[/SIZE]

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[/SIZE]

[/FONT][/COLOR]
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

Reply | Quote

GG,

Sorry I was off the grid for the last 5 days. Have you managed to work the problem out yet? :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi RG,

GG,

Sorry I was off the grid for the last 5 days. Have you managed to work the problem out yet? :cheers:

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 [/B]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

Reply | Quote

Hi Graphics Guy,

Would you be okay with posting a zipped copy of this database, so that others can provide more effective help?

Reply | Quote

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

Reply | Quote

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. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi Retired Geek,
Thanks for your message and the Query.
I have plans for this evening so I will check it out in the morning.
Respectfully, Graphics Guy

Reply | Quote

Hi RetiredGeek
Your Query worked great and is just what she originally wanted. Many thanks for taking the time to assist us.
Respectfully, Graphcs Guy

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Hi Graphics Guy,

I added a dialog form, similar (I believe) to the form that Patt described in an earlier message. This dialog form is named “fdlgAskForParameters”. This form is based on a Word document written many years ago, by Michael Hernandez. Michael is the author of Database Design for Mere Mortals, and co-author of SQL Queries for Mere Mortals. I gave a presentation to the Seattle Access Group, in January, 2008, based on this method. You can find the orginal sample, with Word document, at this site:
[/SIZE]
http://www.seattleaccess.org/downloads.htm

I hope you don’t mind, but I took the liberty of renaming some of your objects (tables, queries, forms) by using standard naming conventions. This includes not using any spaces in the names. I also renamed the Year field, to ProductionYear, so that you don’t have a field that is named with a reserved word. I added a table of locations, tblLocations, so that we can get rid of the lookup field in the main table that had 5 cities indicated. To add a new city, simply open the tblLocations table and add it there. We can even get fancier by adding “Not-in-List” code, for the combo box, to automatically display a form based on locations, if the user attempts to enter a city directly into the combo box on the form that is not already in the table. Lots of possibilities.

For the present time, I left the four Actor fields alone, but these really should be normalized by creating a table of actors, along with a join or linking table, so that you can have what is known as a “Many-to-Many” (abbreviated M:N) relationship. Your current design, for the actor’s names, is a multi-field design. It is not so flexible as far as querying, and it adds difficulty if you wanted to store 5 or more actor’s names in the future. Just something to consider for future improvement.

Along the way, I found what I believe is suspect data: you have one record that shows Olney, VA., and 24 records that show Olney, MD. The lone record for Olney, VA. is for the title “Show Me Where The Good Times Are” (October 1, 1993).

Reply | Quote

Hi tgw7078,
WOW! Thank you for the modifications. You must have spent a lot of time correcting my original mistakes. Now I have to find out how to understand and implement all of the changes before I can explain it to her, so I have quite a few questions. If you are willing to offer additional assistance, do you think it might be best to take this off Forum so we don’t take up space and bore others? Respectfully, Graphics Guy

Reply | Quote

Hi Graphics Guy,

I probably spent about two hours last night…not too bad. I’m currently on a remote trip, for the company I work for [think large commercial jet aircraft], in Kona, Hawaii. There may be days when I simply do not have time to reply, as my paid work must come first. I’ll send a private reply to you, and let you make the call, if you want to take it off-line.

Tom

Reply | Quote

i’d prefer it if all correspondence is kept in here rather than being private, the reason is some people will certainly learn from people like Tom.
Btw i thought you wanted to select by theatre, if so you could change the record source of the combo box to be:

SELECT DISTINCT tblProgramInformation.Theatre
FROM tblProgramInformation
ORDER BY tblProgramInformation.Theatre;

This is fine for a few 100 records but will take longer if there are a 1000 or more.

Reply | Quote

Hi patt,
Thanks for the input. I have no problem with continuing this topic here and I certainly do not want to deprive others of Tom’s expertise. My only concern was that Tom has suggested far more corrections and modifications than I realized were possible and I was afraid we were taking up too much Forum space on a topic that was limited in scope to me and my friend’s concerns. With Tom’s, and others input the project has now expanded from a simple Date/ShowTitle/Theatre chart that can be printed out to sophisticated forms with additional input Combo Boxes, Forms, and Queries which is great and most appreciated; perhaps with everyone’s input we can turn this thing from a simple class project into something a lot more polished that we can be proud of. Please keep in mind I am not trying to keep the information or anyone’s help secret. I attached a copy of the original database to one of my messages so anyone that was interested could follow along or provide suggestions or input. As I mentioned above since this has gone far beyond the scope of my original request I was more worried that we were taking up far too much Forum space.
Btw: I do appreciate your input also and was not trying to shut you or anyone out. Nuff’ said!
You wrote: ‘Btw i thought you wanted to select by theatre, if so you could change the record source of the combo box to be:’
I am not sure where or how to assimilate the information you sent into the original database.
Please keep in mind that my knowledge of the program is very limited, but with all the help I am slowly learning. Btw: Her data base now contains about 400+ records.Thanks again for your message. Respectfully, Graphics Guy
[/COLOR]

Reply | Quote

I was not being critical at all, i was just thinking of other people who would get value from Tom’s experience.
So go with Tom’s idea.

Reply | Quote