Any one use Access Database?

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

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?

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.

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

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!

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'm surprised anyone is still listening to cassettes! :)

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.

CloudDriver wrote:
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.

How would you search your database design to find out how many cassettes Artist A appears on? Perhaps I'm misunderstanding 'artist name is entered once.'

This is probably way too much for this application, but for searchable internet databases, yeah.

2oldman wrote:

CloudDriver wrote:
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.

How would you search your database design to find out how many cassettes Artist A appears on? Perhaps I'm misunderstanding 'artist name is entered once.'

This is probably way too much for this application, but for searchable internet databases, yeah.

As I said, it's more complicated than that.

For a cassette database, I would create a data entry form that showed one cassette and also a subform that showed the list of songs and artists for that cassette. The subform would have a drop down list containing the song names that had already been entered for other cassettes, along with the option to enter a new song name. The artist field would also have a drop down for previously entered artists, plus the option to enter a new artist. By using drop downs, the song name and artist name needs to be entered only once in a table. A query would likely be needed when creating the subform vs. using just the tables.

Since an artist can sing on more than one cassette and a song can be sung by several artists and on more than one cassette, two more tables are needed to store the links (which are the table primary key field numbers).

And yes, it is easy with a database to do searches by artist name, song name, etc. to display which cassette or cassettes have a particular song or artist. It wouldn't be necessary to type in an artist name, just use a drop down list of the names already entered to select the desired artist.

Databases are extremely powerful, but are best used when there is a lot of data. 14 cassettes isn't a lot of data, so a spreadsheet is easier/quicker.