*So* frustrating... (geek whinge/cry for help)

[taimatsu]

I am putting together a MS Access database to function as a high-tech searcheable songbook. It's all laid out and working well. I have assorted fields and checkboxes at the top, and then the song title and so on, and then a field, obviously, for the song lyrics. I can't put these in as text or memo fields because the length for those is too restrictive. I have, therefore, a lot of plain text documents with the lyrics in, in a separate folder, and I want to apply these to the database. I thought I had it taped, because dragging-and-dropping adds them in as OLE bound objects (stored in the database file). That's fine. I'd also be happy to do them as linked docs, hyperlinks, whatever.

However, it has just fallen apart. I have *two* versions of 'The Dawning of the Day', and I want to record both using separate text files. I just cannot work out how to do this. An OLE Object field will only take one object. AFAICT, a hyperlink field will only take one hyperlink. Text fields won't do any of this stuff. I'm stuck! Anyone got any ideas?

Comments

[crocodilewings.livejournal.com]

Do you know what a junction table is?

[taimatsu]

Nope. *googles* Oh, I see. I'm not sure how to use that in practice, though.

(As it happens I have simply added another couple of OLE fields and am shoving extra lyrics in there.)

[crocodilewings.livejournal.com]

Heresy.

OK. Illustrative diagram.



Table A contains the basic song information, as well as a primary key (in this case a non-meaningful, surrogate autonumber called ID. This is not the only way to handle primary keys, and many brutal wars have been fought over the issue, but for demonstration purposes we'll stick with this one).

Table C contains a series of lyrics stored as OLE objects, and a primary key, also an autonumber named ID.

Table B is the junction table. It defines which lyrics go with which song, by saying which SongsID goes with which LyricsID.

Actually, you don't even need a junction table, since if it's a songbook, it's unlikely that you're going to have one set of lyrics that go with many songs. You could do it like this:



So you have one table for songs, and one table for lyrics, and in the Lyrics table you have a field to designate a song to the record.

I think I'm just junction-happy. :-)

[chrisvenus]

I'd just have two rows, one for "The Dawning of the Day (normal)" and one for "The Dawning of the Day (Smut filk)" or something like that. Treat them as different songs and I'm sure it'll be fine. :)

added another couple of OLE fields

[undyingking.livejournal.com]

This is extremely evil behaviour ;-)

crocodilewings's advice is good, you should get normalizing!

[danfossydan.livejournal.com]

Either do that! Or merge the two text files together - so that the first version follows on from the seccond version in the same text file.

Then you just have one entry.

[taimatsu]

I really do want to keep the separate text files, so I can (for example) just print out the one I use most of the time, and have the other version for reference.

[taimatsu]

Sadly traditional song doesn't work like that. These really genuinely are two different versions of the same song. I can't designate them with any particular extra 'name'. Whatever fudge solution I come up with is bound not to work for the next time this happens, so I might as well fix it properly.

[taimatsu]

You are an extremely high-quality Rikk. Thank you very much indeed. I will thank you again when I have got it working. :)

[taimatsu]

Right, I have a pair of tables set up: SONGS contains an ID, the Title, the Artist, and some checkboxes and notes. LYRICS contains an ID, the Lyric File, and a How Well Do I Know This? option. I think the ID in each table is the same, and the 'Relationships' tool suggests this.

What I can't work out is how to *use* that to give me a nice form with the data from the SONGS table at the top, and a thing to click for the lyric file from the LYRICS table.

I appear to lose at Access. I'm going to try putting some data in the second table, though, and will see what happens. :)

[crocodilewings.livejournal.com]

You'll want the Lyrics table to have its own primary key, and a field for the primary key of the song each relationship relates to (called a foreign key when it's not in its own table).

Once that's done, have a look at using the Lookup Wizard on the foreign key.

[danfossydan.livejournal.com]

Then having "The Song I love" And "The Song I love (alternate version 1)" Is I think the easiest solution.

Completely unrelated to post

[angel-feather.livejournal.com]

But this is mistress_carrot's writing journal and because I'm using it again I've updated my friends list to include you :)

[taimatsu]

Argle! I still can't make this make sense! I think I will have to buy some kind of Microsoft Access for Dummies book. :)

[crocodilewings.livejournal.com]

Or I could show you at some point.

[theghostcat.livejournal.com]

Hi! I saw in you interest "rosacea". Do you have any personal experience with using seabuckthorn products? Positive or negative.

[taimatsu]

None whatsoever, I'm afraid. I really need to get a dermatologist referral...

I have Metrogel (active ingredient metronidazole) prescribed by the doctor, which seems to prevent the lumpiness and some of the dryness, but not touch the redness. I use a very gentle face-wash by L'Oreal and a simple hypoallergenic moisturiser. That's it! I haven't found anything that makes much difference yet. How about you?