Help setting up database

[ace214]

I’m having trouble figuring out how to setup a database to do what I want it to. I am trying to create an index of pieces in a volume of music, based on their potential use.

Here’s my spreadsheet: https://ethercalc.org/kzh8ijc11nzt

Each piece (115) is in a row. The potential days are in columns and if a piece is recommended for that day, it has an integer value in that column that I want to be sorted.
So I want to produce a report that looks something like

Day #1 
Piece #1 for Day #1 
Piece #2 for Day #2

Day #2 
Piece #1 for Day #2
Piece #2 for Day #2

I think I probably need a secondary table that sorts the days, but I can’t seem to figure out what will work without mixing value types.

Thanks for any help!

[Ratslinger]

Hello,

I would do this in three tables.

First table - Potential days

• Unique ID (sys gen)
• Title

Second table - Piece data

• Unique ID - FPI
• Composer
• First Name
• Incipit
• Voices

Third table - Day priority

• Unique ID (sys gen)
• Piece data ID (Link back to piece data FPI)
• Day (from list box of Potential days table)
• Priority (your integer)

All potential days are listed in the first table. This is used for entry into third table so there are no mis-spelled days.

All Pieces are listed in the second table.

The third table only need entries for a piece where there is an integer on that day.

Once all entered, the report can easily be generated using SQL to place in desired sequence.