Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    mcalder4 is offline Novice
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    13

    My First Field is in Sequential Order. How Do I get a subsequent Field also in Sequential Order?

    A picture is worth a thousand words they say. Please refer to attached image to view a section of my Music database so that I can explain the problem:


    You will see that the first Field is in sequential order, A0041 represents the Abba Album "Ring Ring", A0042 represents the Abba Album "Abba - The Album", A0046 represents the Abba Album "Waterloo" .... etc.
    My problem is simply this: I would like the third Field, i.e. Track Number to also be in sequential order for each particular Album, i.e. I would like the Database to list Tracks A1, A2, A3, A4, A5, A6, B1, B2, B3, B4, B5, B6 in order for each Album. At the moment, as you can see, the tracks are listed in random order. Of course the Song Title (Field 7) should still correspond to the correct Track Number.
    I am an old man and have taught myself what little computer skills I know, but this is beyond me. By the way this database, which now amounts to over 85,000 lines has been compiled over a period of 10 years. It represents my entire music collection of some 5,000 LP's and about 1,000 CD's. I guess it goes without saying (but I will say it anyway) that this database is very important to me!
    This is my first posting on this Forum, in fact my first posting ever in any Forum. I do hope that someone will help me with this problem. Thanks in advance and I'll keep my fingers crossed.
    Attached Thumbnails Attached Thumbnails MusicDatabase.jpg  

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    You can create a Query. In Access you can Create, Save, and Name Query Objects that can be referenced at a later date. For instance, you can double click a Query Object by clicking its icon on the left hand side of Access. The side bar on the left hand side is known as the Navigation Pane. Objects like tables, forms, queries, etc. are located within the Navigation pane.

    To create a new query, you can click 'Query Builder' from within the Ribbon. The Ribbon is located at the very top of Access and there are different tabs you can click on the ribbon to view different groups.

    When you launch the Query Builder/Designer there will be a dialog asking you to select a table or query. It will default to listing the tables. Select the desired table, 'Music' and click OK.

    Now you can add the various fields by double clicking the field names or dragging a field name onto the grid at the bottom of the Query Designer. The table and its fields were added to the Designer when you selected your Music table.

    Now you can use the Grid at the bottom of the Designer to adjust the Sorting. Locate your Album field that you added to the grid and, within the Sorting field, select 'Ascending'. Then, adjust the Sorting for the Title field.

    Go ahead and save your Query and give it a name like qryMusic. You can close your query and open it from the navigation pane or use the buttons from within the Ribbon to use Data Sheet View.

    That should do it. Access will generate an SQL statement based on your interaction with the Graphical User Interface. The order you selected you fields' Sorting should determine the order Access will evaluate the sorting. Data retrieved by your Query will be dictated by the SQL code behind the query object. If there is an issue, post here and let us know. We will ask you to show us the resulting SQL code. It is not difficult to edit the code with help.

  3. #3
    mcalder4 is offline Novice
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    13
    I hope that this is correct place for me (the original poster) to continue discussion about this issue. Thank you so much ItsMe for your reply. I have tried to follow your instructions but somewhere I am misinterpreting or misunderstanding something. I got a huge shock when something went wrong and my precious database got completely messed up. I am sure if you were actually showing me on the computer I would understand but I do not feel confident to try again on my own. However I have had another idea how you could possibly help me: My database is not private or personal. It is merely a list of my music collection. Therefore it is of no concern to me to show or share it with others. I wonder if I sent the database to you as an email attachment, could you make the necessary adjustment and return it to me? I guess this would involve exchanging personal email addresses and I am not sure if this is allowable, or even if you would be prepared to tell me your email address. As I am a novice you may know of other ways I can send my database. So many things are possible these days that I am not even sure about. Perhaps I could post it "on the cloud" or some file sharing site where you could access it. Please tell me if you are prepared to help me and what I need to do to assist. Unfortunately I am not comfortable with your initial instructions, not because there is anything wrong with them, but simply because of my own uncertainty. This is my email address: mcalder4@optusnet.com.au and you can contact me directly on that email if you wish.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Can attach files to post. Follow instructions at bottom of my post.

    Just building a query should not have damaged db. Table should be intact.

    This is basic Access functionality. Have you reviewed an introductory tutorial (book or on-line)? Here is a good site http://www.rogersaccesslibrary.com/
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    One option might be for me to provide remote assistance and log into your computer. If you have a headset connected to your computer for talking and listening, this might be a viable option. The fact that you are on the opposite side of the world from me might pose a small issue with remote assistance and latency (slow internet).

    Another option might be for me to provide you with a login to a SharePoint server and you could upload your file there, to a Team Site. I am going to be pretty busy the next couple of days but might be able to block out some time anyway. I am GMT -7 over here.

    As for your DB having issues, I am having a hard time imaging what could have gone wrong. You would have to start deleting records or updating fields by changing the text within said fields in order to effect change.

  6. #6
    mcalder4 is offline Novice
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    13
    Thanks June7 and to ItsMe again. No permanent damage was done to my database when I unsuccessfully tried building a query as outline by ItsMe above. Only a temporary glitch occurred which frightened me. If I was a person who was going to use Access on a continual basis for a range of databases, or if I needed Access for work purposes I would certainly make the effort to learn more and study Access in greater detail. However I have one database, my Music database, and I chose to use Access when I first started compiling it in 2003. Apart from this database, I will probably never use Access again in my life. Please find attached (I hope) my Music Database file. If you are able to modify it so that both the Album Numbers and the corresponding Tracks are in correct sequential order I would be ever so grateful. - Bad news: couldn't attach my database file Music6August2015.accdb , probably because it is too large (50Mb). It uploaded to 100% (took ~10mins) but then it did not appear in the Manage Attachments window.

    I am happy to grant remote access to my computer. I do not have headphone voice setup but I don't think it is absolutely necessary. I can have my database open on screen when you have remote control and you can just do your thing and save changes. I will make myself available at any time to suit you, no matter if it is 3.30am here. I live in Adelaide, South Australia, which is GMT +9.5hrs. Just tell me how I can connect with you and give you access. At worst we can make short communications by email during the process if necessary - you have my personal email address (above).

  7. #7
    mcalder4 is offline Novice
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    13
    Please disregard all the nonsense in my previous reply immediately above. I have thought of a far simpler. easier way for you to help me with this issue. I have uploaded my Music Database (Music6August2015.accdb) to Google Drive and made it so anyone with the following link can edit it: https://drive.google.com/file/d/0B8nqXyBYZBwBbS16N2tQUkdGNEU/view?usp=sharing
    Please do your thing!

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I can't download from Google Drive. Home internet too slow and blocked at work.

    You could make copy and delete most data to attach file to post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I was unsuccessful in downloading the file via Google Drive. If you wish, you can upload your file to a Team Site I created. I will email you the info ...

  10. #10
    mcalder4 is offline Novice
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    13
    Hello ItsMe. I have uploaded my compressed Music Database to SharePoint Team Site as you have outlined. It works as I have checked it by downloading and opening the file myself! You have my Login details so you can access this file and modify. I look forward to hearing from you after that and thank you ever so much for your time, effort and kindness.

  11. #11
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I was able to down load the (huge) file.

    I changed field names to remove spaces and special characters.....
    Created a query (qryMusic) and a couple of forms (Music and MusicList).

    Zipped, the file was too large to attach, so I deleted the table.

    1) Open the attached db, holding down the shift key
    2) Import the table "Music"
    3) Save
    4) Open this dB.

  12. #12
    mcalder4 is offline Novice
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    13
    Thanks ssanfu for your good intentions. I have been able to download and unzip the db file you attached to your reply. I have then been able to open it in Access. At that stage I am confused. I think you want me to Import my existing Music database into the db you supplied. However I have not been able to find how to do this. Attached to this reply is an image (ssanfu Database.jpg) of what I see with your db open and with Access Help window for "Import" (which I had hoped would show me how to Import). However I can't even find "Settings" so either I am doing something wrong or misunderstanding your instructions (or both).
    Attached Thumbnails Attached Thumbnails ssanfu Database.jpg  

  13. #13
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    In Access 2010, I would go to the menu "External Data". Then select "Access".
    Select the dB that has the Music table. Select the Import (do not link) radio button.
    Click on the "Tables" tab.
    Select "Music". Click OK. OK

    Close the db (the one I sent) then open it.

  14. #14
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    @mcalder4

    I downloaded the file you previously uploaded to the Team Site. I, then, moved the file you uploaded to the Recycle Bin. I worked on your DB. When I looked inside there were some forms and queries that did not seem to be doing anything in particular. I decided to rebuild your DB from scratch. As you mentioned, the thing of value is the data; the one table. I imported the data into the new DB I created.

    I uploaded the new DB I created to the Shared Folder in the Team Site. When you download it, be sure to unzip it. In other words, do not double click the zip folder you download. You want to Right Click and 'Extract All'. After you do that, delete the compressed/zip folder and keep the folder/file you extracted.

    Your new DB has the query you requested. You can double click the query object named, "qryMusic", from within the Navigation Pane. Understand that you can edit data from this query. Changes to text within the fields made via the query will be reflected in the table (the table named Music).

    In addition to the query, I created some other objects too. I figured you might like to have a form to search your Music table. When you open the DB, the form will automatically launch/open. The form can be closed or moved to the side in order to work in your table or query objects.

    To use the form's search functions type text into the fields that you would like to use as criteria. Clicking the search button at the top of the form will execute code and display the results in the lower portion of the form. You cannot make edits to the data from the form. I think the search functions are self explanatory so go ahead and play around with it to see how it works. If you have any questions, just ask.

  15. #15
    mcalder4 is offline Novice
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    13
    To ItsMe: Thankyou so much for all your work. I have downloaded the db (my original db which you have now modified) from the Sharepoint Team site and extracted it. I received a bit of a surprise when I opened the Table "Music" to discover that it looked identical to my original db and that Field 3 (Track Number) was still in random order. I was hoping that the Table would have Track numbers in order. I soon discovered that if I double clicked "qryMusic" that the Track Numbers changed to sequential order. I am not used to seeing so many objects in the Navigation pane (and still really don't understand their significance). After closing and re-opening the db however Field 3 is again in random order.
    Q1. Is there any way that I can make Table default to showing Field 3 in correct order when I open the db?
    Q2. Over the last 3 days or so I have been adding records (many various changes to Field entries as well as new records added) to my original db. These changes were not included on the db file which you modified. Is there any way I can incorporate these recent changes into the new db you made for me (without disturbing anything else)? e.g. copying from old and pasting into new?

    The new db which you made looks very promising and I am so grateful. I am sure I will appreciate the additional functionality (over my old db) as I become more familiar with it. The search function which pops up on opening looks interesting, I haven't had chance to explore its use yet, but I wonder if it is much different from the Find/Replace function which I use from the toolbar? Which leads me to my last question:
    Q3. I use Find/Replace frequently. I used to know how to do the following (several years ago) but have now forgotten: My db is about 85000 records (horizontal lines) long. This means I have 85000 songs listed. "Some Enchanted Evening" or "Somewhere My Love" appear on multiple occasions. I used to be able to do a "Find" and then (from memory) do something with Filter and a list containing every "Some Enchanted Evening" would appear. Now I don't know how to do this anymore. This is probably a very simple and basic function in Access (but I am a pretty simple and basic user!). Can you either tell me how to do this or direct me to something online that will tell me? I find Access Help pretty useless, mainly because I don't know correct terminologies or how to phrase (explain) what I am trying to search for (find out).

    Thanks again ItsMe and thanks also to others, including ssanfu who have taken time to assist me. It is reassuring and gives some faith in humankind (in amongst this often sad world) to know that there are people out there who will put themselves out for no reward other than to help another individual. You two (and others above) are in amongst "the good guys" of this world.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Sequential Number by group in a field
    By bennyhana88 in forum Access
    Replies: 16
    Last Post: 07-22-2015, 10:44 AM
  2. sequential count field in query by Id
    By rbremer in forum Queries
    Replies: 4
    Last Post: 06-03-2015, 02:33 PM
  3. Replies: 4
    Last Post: 01-03-2013, 08:29 PM
  4. Sequential Order ID on Form
    By charya in forum Forms
    Replies: 1
    Last Post: 01-15-2011, 10:51 AM
  5. Replies: 3
    Last Post: 10-18-2009, 08:38 PM

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