Any one use Access Database?

redeldo wrote:
Hopefully there are some that use Access as I am trying to set up a database and have a question.

The database is CASSETTE TAPE COLLECTION. I have 20 columns starting with ID, TITLE OF CASSETTE, SONG 1, ARTIST, SONG 2, ARTIST, etc.

Going alphabetically I incorrectly entered tapes out of order. When I sort Ascending, the TITLE OF CASSETTE is in order but the ID column is out of order. Instead of being 1 through 14, it is: 1 2 5 6 8 7 9 10 11 12 13 3 4 14. If I sort the ID Ascending, the ID is in order but the Title is out of order

Any way to get both columns in order or do I have to start over? Also I am using Access 2000 as I don't have a newer version which I would like to use.

Thanks

Wayne
01 Monaco Dynasty

Self taught Access user here. From your description, it looks like you are trying to use Access similarly to a spreadsheet. You have put all the data fields into one table, which is not the proper way to build a database.

If I were to make a database to record info about cassette tapes, CD's or other music, I would make three tables to hold the data. One table would hold the just cassette names and a primary key field. The second table would hold just the song names and a primary key field, and the third the artist names and a primary key field. The idea with a database is that each record in a table holds unique data. Each cassette name is entered once, each song name is entered once, even if it is on more than one cassette - each artist name is entered once, even if that artist sings many songs on many cassettes. The various song and artists names are related to a specific cassette by relating the tables themselves. The primary key field in each table is used to link one table to another and must be unique for each record in a specific table.

There's more to doing it than this, but I'm sure I've lost you already.

Since you have already entered the data for the cassettes, the easiest way to get the data sorted into a cassette ID number sequence would be to add an additional field to hold the Cassette ID and then enter the cassette number. You can then sort on this Cassette ID field.

I'm surprised anyone is still listening to cassettes! :)

If it's the unique identifier that's assigned by the computer, you won't be able to change it. A work around would be to add your own ID field and manually enter the numbers you want for each record.

Another fix would be to sort the data into the order you want and then port the data into a new database minus the ID field. The new database will ask if you want to add a unique identifier field and will renumber the records.

I don't know the answer, but I find it interesting because I did the same some years ago. Learned how to make forms and databases in Access (a difficult task for a neophyte!)and entered some 800 CD's into one database, and all their tracks into another.

It worked, but man it was cumbersome. And every time I wanted to redesign it I had to learn the stuff all over again, dumb as I am.

Eventually I exported the lot to Excel, and I like that better.

Now if only I could connect Excel to iTunes, I'd be happy!

To answer the question at hand, you can't have it both ways: you can sort by either ID or Title. There really is no reason to care about having ID in order unless you want to see the order of entry. I'd be more interested in sorting by song, artist, date of release, etc.

You should probably think about having Artist and Song tables that are related to the Cassette table. This way every song and artist is only stored once and then related to the cassette table.



Mike

When using a database, there shouldn't be any need for you to enter things 'in order'. That what's the computer does when you ask it to. I assume ID is the unique identifier Access is assigning. Each column will sort on its own, so either ID or TITLE will be in order.

I also see you're entering all that song information in the same file. To be a better design you may want to consider the 'one to many' relationship. That is, your ID and Title can be in one file, then the song information is in a separate file, indexed on ID (the same ID as the Title).

Nowadays, text file searches have become so fast it's almost unecessary to put small databases in database programs.

The ID shouldn't matter as it's just used to uniquely identify the record. You shouldn't have a need to sort on it so its order won't matter.

Still, if you really want to fix it can you tell me if it's configured as an Autonumber Primary Key or just a Number?

I've tried data bases a couple of times, found it was much easier to use Microsoft Excel. Much easier to sort in and much, much easier to change your data layout in. If you don't have MS Excel, there are other programs out there.

Bill