Windows 10 
Office 365
Dear all,
Please forgive me, I am sure that this is a question which is asked a lot, but I don't have the words necessary to articulate it to a search engine.
I am creating a database of pipe organ proposals for an academic project. I have read about relational databases and I thought I more or less understand that bit. I enclose the relationship view of my database. As you can see, there are a lot of layers. This is because one building can have multiple different organs, one organ will have multiple divisions, each division will have multiple stops and each stop may have multiple ranks. In addition, the couplers and accessories are sometimes assigned to a division and sometimes to the whole organ, which makes things even more complicated.
Having built the tables and assigned the relationships, I was intending to then create a master form which would enable me to enter in data. I envisaged a complicated structure with lots of boxes taken from multiple tables, perhaps with buttons to add rows; i.e. to try and fill in all the tables at the same time from one form. This doesn't appear to be possible. Then I thought that I would create a form for each table, with buttons to link between the two. i.e. you create a building record and then click a button which will take you to an organ form, with the foreign key already filled in. That doesn't seem to be possible either. So what is the best way of entering in this data? If I make a form from multiple tables I end up with database view, which is very confusing. Let's say I live with it though, and create a form for buildings with organs as a database view. I fill this in, and then what? If I create a form with organs with divisions as database view, how do I identify the organ except by its ID number? I don't want to have to make a spreadsheet with organ ID vs location and the same for the other tables, surely that negates the point of a relational database? The natural ID for an organ is the building name and the date. However, the buildings are normally churches and so they don't have a unique name. The name may be 'St Andrew's Church', of which there might be hundreds in the database. So I need to give the town as well. Even then there may be two churches of the same name in that town, one anglican one RC. So I end up having quite a large quantity of superfluous fields embedded within each and every table to try to link them together. Surely this is what the relationship function is for? Even if I have some sort of dropdown menu, I am likely to have maybe 5,000 organs (I have really no idea but it'll be in that ballpark) and I cannot possibly scroll through all that lot!
I know that I am missing something crucial in my understanding here, but I can't work out what it is. Does that make any sense at all to anybody? Any ideas?
Reply With Quote
