hymnal form populating coding

[mikevds]

I have a DB with three fields

song
Hymnal/cd song
Media Type

I want them to populate I have the coding,
So it I pick a song, it only displays the cd's or hymnals from that song


End Sub
'Move to the record selected in the control
Me.RecordsetClone.Findfirst "[ID] = " & Me![Name of Song]
Me.Bookmark = Me.RecordsetClone.Bookmark

However they are Not populating

 

[MSAccessRookie]

I have a DB with three fields

song
Hymnal/cd song
Media Type

I want them to populate I have the coding,
So it I pick a song, it only displays the cd's or hymnals from that song


End Sub
'Move to the record selected in the control
Me.RecordsetClone.Findfirst "[ID] = " & Me![Name of Song]
Me.Bookmark = Me.RecordsetClone.Bookmark

However they are Not populating

I am a mite bit confused. I loaded the sample, and there are a lot more than three fields. I do have the following observations so far:

1. It looks like its origin was an Excel Spreadsheet that was sorted first by Hymbook songs, then by Cd songs.
2. The database contained a single table with over 16000 entries, each having a complete record of information (Excel Style)
3. There was duplication in at least one column (hymnalnumber) that I assumed should have been to be intended to be a unique key.

Am I looking at the right database?

As an aside (and only if you choose). Where in NJ? I Live and Work in Central NJ.

 

[mikevds]

I am a mite bit confused. I loaded the sample, and there are a lot more than three fields. I do have the following observations so far:

1. It looks like its origin was an Excel Spreadsheet that was sorted first by Hymbook songs, then by Cd songs.
2. The database contained a single table with over 16000 entries, each having a complete record of information (Excel Style)
3. There was duplication in at least one column (hymnalnumber) that I assumed should have been to be intended to be a unique key.

Am I looking at the right database?

As an aside (and only if you choose). Where in NJ? I Live and Work in Central NJ.

You are looking at the right database.
The table has more then three fields
Should I have a seperate table for cd and hymnals?
I do then I do have one that is merged together. I am undecided about this.
I would love to send you the actual database it has 370 mg for you can see and sort it out better.

I do believe I am better off with one table for the cd's another for the hymnals. Then I wouldn't be able to see from the one table.
I don't understand the not null when I try to view the songs by name of song, or cd,

But I would want to view data more then one table and get all the information from one table and definitly all on one form when I veiw it to see the song on the cd or hymnal on one form. So then I can populate the three list boxes for name of song and hymnal or cd.
Like as you can see if you open the database I would send you which has some other forms with populating list boxes.


I think in the table it's better off to see if it's on a cd or hymnal. But it's more important not what way but to get it working in access so it all works together.

Do you mind if I can always email you a winzip of the actual database. Like I say it's quite large or mail you the cd so you can look at it.

 

[MSAccessRookie]

You are looking at the right database.
The table has more then three fields
Should I have a seperate table for cd and hymnals?
I do then I do have one that is merged together. I am undecided about this.
I would love to send you the actual database it has 370 mg for you can see and sort it out better.

I do believe I am better off with one table for the cd's another for the hymnals. Then I wouldn't be able to see from the one table.
I don't understand the not null when I try to view the songs by name of song, or cd,

But I would want to view data more then one table and get all the information from one table and definitly all on one form when I veiw it to see the song on the cd or hymnal on one form. So then I can populate the three list boxes for name of song and hymnal or cd.
Like as you can see if you open the database I would send you which has some other forms with populating list boxes.


I think in the table it's better off to see if it's on a cd or hymnal. But it's more important not what way but to get it working in access so it all works together.

Do you mind if I can always email you a winzip of the actual database. Like I say it's quite large or mail you the cd so you can look at it.

Let's look at some of your questions.

1. Should I have a separate table for cd and hymnals?

There should be no need to do this. Essentially, a CD is a small (<24) collection of Songs, while a Hymnbook is normally a Much larger (>100) collection of songs. The individual items contained within each can be described the same way, so they can be treated as equal, as long as you have an internal identifier for the purpose of Forms and reports.
​

2. I would love to send you the actual database it has 370 mg for you can see and sort it out better.

It sounds like a lot of work has gone into this.
​

3. I do believe I am better off with one table for the cd's another for the hymnals. Then I wouldn't be able to see from the one table.

I disagree in principle at least (Explained in #1)
​

4. I don't understand the not null when I try to view the songs by name of song, or cd

I had no problem viewing the data
​

5. I think in the table it's better off to see if it's on a cd or hymnal. But it's more important not what way but to get it working in access so it all works together.

The structure of the table is a primary issue. You should not have a single table, but instead have a series of tables linked together to attain what you are looking for. Read up on the concept of Database Normalization to understand what I mean.

One basic advantage of Normalization is the elimination of redundancy due to clerical errors such as typos or description differences. In addition, it would eliminate the potential for duplication of entries or keys.
​

6. Do you mind if I can always email you a winzip of the actual database. Like I say it's quite large or mail you the cd so you can look at it.

Feel free, but before you do, please consider referring to the aside on comment #2
​

 

[mikevds]

Access Rockie,

Thanks for your information on the tables,

Did I send you a sample of the Hymnals mine db?

If you see the database wit hthe cd's and hymnals the tables makes sense.

The table structure makes sence what you say. What is the normalization you mean with the tables.

The forms I want to populate so it looks up the songs and it populates with what hymnal.

You say I can send you the database to work on it, see reference 2)
How much would you charge?

Regards,

Michael Van Der Stad

 

[Ron_dK]

Hi there,

If you want to get an idea about normalization and so, please see attached
link to a CD database.
This example might give you an idea on how to set up the necessary tables and their relationships.

Hth

Music collection database
http://office.microsoft.com/en-us/templates/results.aspx?qu=cd+database&av=TPL000

 

[jal]

I have a DB with three fields

song
Hymnal/cd song
Media Type

I want them to populate I have the coding,
So if I pick a song, it only displays the cd's or hymnals from that song

I agree with the other posters about normalization. However, maybe this is one of those rare cases where the database is only serving an EXTREMELY trivial purpose in which case any quick solution will suffice.

If that's the case, maybe you are making this too complicated. Why not just sort the data by song-name. When the user scrolls to that song he will see, say, three listings, viz:

NameOfSong.........Hymnal/CD
Jesus Loves Me.....The Power of God
Jesus Loves Me.....Greatest Gospel Music
Jesus Loves Me.....Transcendence of God

Thus the form would only need to do two things:
(1) Show the entire table, sorted by song name.
(2) Show it as read-only, so the user doesn't accidentally alter the data.

On the other hand, you seem to have some columns which are unpopulated. I'm not sure how you intend to populate them.

 

[MSAccessRookie]

I agree with the other posters about normalization. However, maybe this is one of those rare cases where the database is only serving an EXTREMELY trivial purpose in which case any quick solution will suffice.

If that's the case, maybe you are making this too complicated. Why not just sort the data by song-name. When the user scrolls to that song he will see, say, three listings, viz:

NameOfSong.........Hymnal/CD
Jesus Loves Me.....The Power of God
Jesus Loves Me.....Greatest Gospel Music
Jesus Loves Me.....Transcendence of God

Thus the form would only need to do two things:
(1) Show the entire table, sorted by song name.
(2) Show it as read-only, so the user doesn't accidentally alter the data.

On the other hand, you seem to have some columns which are unpopulated. I'm not sure how you intend to populate them.

I would like to respond to the comment "...the database is only serving an EXTREMELY trivial purpose in which case any quick solution will suffice.".

I am sure that it can (and perhaps rightfully should) be argued that although a quick solution should be avoided, and a proper, more normalized solution is what should be strived for in most cases, a quick solution can be a viable solution in some cases. I would propose that this would not be one of those cases.

I have seen a copy of the DB, which may or may not have been complete. It contains over 16000 records in a single table format, that represent the information related to Hymns and Songs that exist in one of the many Hymnals, SongBooks, and CDs that are in their collection. If they intend to grow their collection to any significant degree (which I suspect they will do over time), then normalization should be a primary consideration.

I experimented over the weekend with breaking the table up and starting to normalize the data, but the data has a minor level of duplicated keys and missing data which made final determination of any changes a user level requirement, since data would need to be affected.

 

[MSAccessRookie]

Access Rockie,

Thanks for your information on the tables,

Did I send you a sample of the Hymnals mine db?

If you see the database wit hthe cd's and hymnals the tables makes sense.

The table structure makes sence what you say. What is the normalization you mean with the tables.

The forms I want to populate so it looks up the songs and it populates with what hymnal.

You say I can send you the database to work on it, see reference 2)
How much would you charge?

Regards,

Michael Van Der Stad

I would be more than willing to continue the effort here, since the main purpose of this forum (as I see it) is to assist others in resolving their own issues, as opposed to searching for new clients. If you prefer to take the discussion away from the forum, then we can arrange to do that as well.

 

[MSAccessRookie]

I take it that your project was successful. We are still here if you have any more questions.