Append Query Question - Append records and 'extra' info 1

[Quintios]

I have a database to track song usage for my youth group at church. Every week we play five songs. The information I'd like to store is the five songs we play as well as where we played them and what the event was in a history log so I can look back at what we've performed, where, etc.

I have a table called 'Songs' that has all the song information in it (title, chords, key, author, copyright info, etc.) Within this table is a yes/no column called 'Selected'. When I pick the songs for the set in a given week, 'Selected' is set to 'Yes'.

Using 'Selected' as a basis for the append query, I append that information to a table called 'History'. The deal is that I'd like to create a form that will have other text boxes that I can enter more information about the event, such as the information I mentioned at the top of this note.

My question is, how do I append the five song ID's to the History table as well as have the event information duplicated (or propagated, if you will) to each 'History' table entry? For example, in the form I'd enter a location, a date, and event information into the text boxes. When I perform the append query, the five songs that are selected are appended along with the information in the form to the 'History' table.

Am I making sense? If you need clarification please let me know.

Thanks in advance!

Onwards,

Q-

 

[papparuff]

I see a need for three tables here.
Your Songs table, your History table (modified), and an Events table.

Your History table would only carry the Songs Selected ID and the Events ID (you could also have a HistoryID which would be an autoincrement field). This table is commonly called a linking table.

The Events table would store all the information about each event in which songs were played. The information that is now stored in your History table, except the songs, could be moved to the Events table but make sure only one record for each event is stored.

You can create a mainform and subform to update your data. The mainform would have all the information about the Event. You would also include a listbox of all songs from the Songs table on the mainform. You could then select the songs in the listbox that you want added to the History table. Code would be written once the songs were selected from the listbox to populate the History linking table.

The subform would list all the songs that were used for the event.

I know it may sound complicated, but it really is not. It just will take some time, patience, and testing. John Ruff - The Eternal Optimist

 

[Quintios]

I'm talking it out in my head.

The linking table would contain:

SongID
EventID

The event ID would correspond to an 'Events' table that contains all the info about the event.

Of Course! Why didn't I see it myself?

Excellent! Thanks for your help! I don't have a lot of experience with running queries on linking tables, but I imagine the GUI should make it pretty easy.


Onwards,

Q-