 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / February 2006Where to start
I have no idea how to design a database to provide the information I need to provide directions on a monthly interval from data accumulated over 6 years. • The data includes 165 unique seasonal EVENT descriptions. o The events occur approximately 12 times a year. o These 12 events repeat but are different the 2nd and 3rd year and then repeat for the next 3 years, and so on. • For each event there are 11 ACTIONS, each having a descriptor NAME and ID NUMBER. Process: • The goal is to pick an event. • View each action from that unique event reviewing all the name descriptors that occurred for each action over the previous 6 years. • Select one name and its corresponding ID number for each action. • Print the results in a report. The data input form seems easy enough but viewing each NAME per ACTION and selecting one of many to print in a report is where I fall apart. Any suggestions on how to start will be greatly appreciated. Linronamy The information you have provided so far is excellent. It would make it a lot easier for anyone to fully grasp your meaning if you could provide some examples of your events, actions, and other data. Thanks.  SignatureSteve Schapel, Microsoft Access MVP > I have no idea how to design a database to provide the information I need to > provide directions on a monthly interval from data accumulated over 6 years. [quoted text clipped - 18 lines] > selecting one of many to print in a report is where I fall apart. Any > suggestions on how to start will be greatly appreciated. MUSIC SELECTION PLANNER FOR CHURCH LITURGIES USING PREVIOUS 6 YEARS OF DATA. EXAMPLES: Events - lit1 cycle a, lit1 cycle b, lit1 cycle c (lit1 - lit12, cyca,b,c for each) Actions - opening, responsorial, gospel accl., etc.,, (parts of liturgy) Name - song title Id - song number reference DATA INPUT: using defined/restricted input form. USERS (NOT COMPUTER LITERATE) NEED TO BE ABLE TO(from one screen): • Select a liturgy, • Review each song previously used for each part of the liturgy (with drop down), • Select a song for each part they will use for the upcoming liturgy (from drop down), • Print the results of all selections on a report to be used as a liturgy order for musicians and vocalists. THANX for your interest and help > Linronamy > [quoted text clipped - 24 lines] > > selecting one of many to print in a report is where I fall apart. Any > > suggestions on how to start will be greatly appreciated. Linronamy, It seems to me that from the point of view of table design, you will need this structure: Table: Liturgies LiturgyNumber LiturgyName Table: LiturgyEvents LiturgyEventID LiturgyNumber LiturgyCycle Table: Actions Action Table: Songs SongID SongName Table: SongAssignments AssignmentID LiturgyEventID Action SongID SignatureSteve Schapel, Microsoft Access MVP > MUSIC SELECTION PLANNER FOR CHURCH LITURGIES USING PREVIOUS 6 YEARS OF DATA. > [quoted text clipped - 18 lines] > > THANX for your interest and help I have created the following suggested (modified) tables with actual labels in the second column: Table: Liturgies Table: Liturgies LiturgyNumber LitNumber (Index Number) Primary key R3 LiturgyName LitName (Liturgical day) LiturgyCycle LitCycle (Cycle–A, All, B, C, SP or M) R1 Table: LiturgyEvents Table: LitFunctions LiturgyEventID LitPart (Part of liturgy where hymn is played) R2 R4 LiturgyNumber LitNumber (Index Number) Primary key R3 Table: Actions Not required Action Not required Table: Songs Table: Hymns SongID HymnID (Index Number) Primary key R5 SongNumber HymnNumber (Hymn Number) SongName HymnName (Hymn Title) Table: SongAssgnmt Table: HymnAssignments AssignmentID AssignmentID (Index Number) Primary key LiturgyEventID LitPart (Part of liturgy where hymn is played) R4 Action Not required SongID HymnID (Index Number) R5 I also created two Lookup Tables: LitPart R2 Cycle R1 I created relationships with different fields as indicated by Rnumbers (R1 relates to R1, etc.). Should I create one form to input all data? The worth of this effort is to allow a non-computer person to review which hymns were used for each part of the liturgy for a specific Liturgical Day from the past selections. This is where I am lost; How do I design the query and report? Thanx for any suggestions > Linronamy, > [quoted text clipped - 45 lines] > > > > THANX for your interest and help Linronamy, As regards data entry, no you would not use one form for management of all the data. Normally you would use a number of forms, and for the key data (i.e. which hymns are assigned on which action (litpart)), you would have a subform on a main form. But anyway, that's jumping ahead, as is any talk about queries and reports. None of this is pertinent until the table design is right, and I'm afraid the schema you suggested is not workable. I would strongly suggest you revert to something more similar to the design that I suggested before. SignatureSteve Schapel, Microsoft Access MVP > I have created the following suggested (modified) tables with actual labels > in the second column: [quoted text clipped - 37 lines] > > Thanx for any suggestions I thought I structured the tables per your suggestion. Please bear with me. I want to understand how I blew it. I suspect my first mistake was in how I didn’t define it properly. There is a liturgy with 11 parts, a song and song number for each part. Project Definition: 1. For each Liturgy (there are 165 unique) 2. there will be eleven parts (in each liturgy), 3. and each part will have one song with it’s song number. Data Input would consist of typing: 1. One Liturgy name 2. Part 1 and its song and song number, Part 2 and its song and song number and so on thru Part 11. Query: 1. Select a Liturgy 2. View each part of that Liturgy (1 thru 11- one at a time) 3. View each song and song number for each part (can be 4 or 5) 4. Select one song and song number from each part 5. Eleven (11) selected results will be used in a report to be printed (one song and it's song number from each part) Report: 1. Print Liturgy name 2. Print each part with the query selected song and song number Trying to do as you suggested helped me recognize I didn’t give you the necessary description to understand what I was trying to do. My apology. Thanks again. > Linronamy, > [quoted text clipped - 50 lines] > > > > Thanx for any suggestions Linronamy, To be honest, I thought your original descriptions were pretty good, apart from the confusion caused by changing terminology on me (events, actions, and songs, in your first description, became something else later). Can you give some specific examples of the data you are using? For example, some sample data for 'Liturgies'? In the meantime, here is my (still incomplete) take on it... If you have 165 Liturgies, and each Liturgy can be assigned to one of several Cycles, then the Cycle field does not belong in the Liturgies table. There needs to be a separate table, related many-to-one with the Liturgies table, to create this entity. In my suggested table structure, this is exactly what I did... a Liturgies table and a LiturgyEvents table, but you modified this idea by collapsing these two tables into one. Then, you named a table in your schema as LiturgyEvents, which is actually a different concept, and renamed the Action field (which we are now calling LitPart) in the HymnAssignment table but in so doing will lose the ability to relate the hymn assignment to the Cycle. Mind you, when I look again at your last post about 'Data Input', we have lost the aspect about Cycles now, so where does that fit in? SignatureSteve Schapel, Microsoft Access MVP > I thought I structured the tables per your suggestion. > Please bear with me. I want to understand how I blew it. [quoted text clipped - 26 lines] > necessary description to understand what I was trying to do. My apology. > Thanks again. Steve, Sorry for the confusion. I will try to clear it up. My previous reply is accurate. Here are the examples in lowercase alpha’s. Project Definition: 1. For each Liturgy (there are 165 fixed and unique names with cycle designation of A, B, or C). TABLE: LITURGIES a. Table – LitNumber(PriKey) LitName 1 Advent 1st Sunday A 2 Advent 1st Sunday B 3 Advent 1st Sunday C 4 Advent 2nd Sunday A 5 Advent 2nd Sunday B 6 Advent 2nd Sunday C (Thru 4 Sundays) 7 Easter 2nd Sunday A 8 Easter 2nd Sunday B 9 Easter 2nd Sunday C (etc. thru 165) 2. there will be eleven parts (in each liturgy) TABLE: LITFUNCTIONS a. Table - LitNumber(PriKey) LitPart 1 Prelude 2 Instrumental Processional 3 Gathering 4 Responsorial Psalm 5 Gospel Acclamation 6 Preparation 7 Pre-Communion Instrumental 8 Communion 1 9 Communion 2 10 Closing 11 Instrumental Postlude 3. and each part will have one song with it’s song number. TABLE: HYMNS a. Table – HymnID(PriKey) HymnName Hymnumber 1 Morning Has Broken G748 2 Sing to the Mountains G452 3 We Remember G578 Data Input would consist of typing: 1. Liturgy name, eleven parts and the hymns and hymn numbers for each. a. This would be an example of one data entry sheet Easter 2nd Sunday A Prelude Healer Of Our Every Ill G854 Instrumental Processional Eye Has Not Seen G616 Gathering Eye Has Not Seen G616 Responsorial Psalm This Is The Day G64 Gospel Acclamation Alleluia, Alleluia, Alleluia G137 Preparation Lord, I Believe G535 Pre-Communion Instrumental Behold The Lamb G824 Communion 1 Behold The Lamb G824 Communion 2 We Remember G578 Closing Glory And Praise To Our God G537 Instrumental Postlude Eye Has Not Seen G616 Query: 1. Select a Liturgy (Liturgies drop down list) 2. View each part of that Liturgy (1 thru 11- one at a time) 3. View each song and song number for each part (can be 4 or 5) 4. Select one song and song number from each part 5. Eleven (11) selected results will be used in a report to be printed (one song and it's song number from each part) Report: 1. Print Liturgy name 2. Print each part with the query selected song and song number I hope this is helpful to fully understand what I hope to accomplish. I really appreciate your patience with my inexperience. Thanx Steve > Linronamy, > [quoted text clipped - 48 lines] > > necessary description to understand what I was trying to do. My apology. > > Thanks again. Linronamy, Thanks for the further explanation, the examples help a lot in making clear what you are doing, and I see (of course) that I have misinterpreted some of what you previously wrote. At the same time, it confirms for me at least one of the confusions here. You have made great progress. Here are my suggested changes to what you've got now... 1. You are storing 2 pieces of information (liturgy, and cycle) in one field. You will have much greater power and flexibility in your database if you "atomise" it. That means putting the Cycle information into a separate table, similar to my earlier recommendation. Set up the Liturgies table like this... a. Table – LitNumber(PriKey) LitName 1 Advent 1st Sunday 2 Advent 2nd Sunday (Thru 4 Sundays) 3 Easter 2nd Sunday (etc. thru 55?) 2. As a relatively minor point, there is no need at all for a HymnID (presumably AutoNumber) field in the Hymns table. The only purpose this could posibly serve is unique identification of each Hymn, and you already have a real-life HymnNumber that serves that purpose, so table: Hymns a. Table – HymnNumber(PriKey) HymnName G748 Morning Has Broken G452 Sing to the Mountains G578 We Remember 3. Similarly, the names of the 11 Parts are unique, so I wouldn't bother with a LitNumber field in the LitFunctions table either. So... a. Table - LitPart(PriKey) Prelude Instrumental Processional Gathering Responsorial Psalm Gospel Acclamation Preparation Pre-Communion Instrumental Communion 1 Communion 2 Closing Instrumental Postlude 4. Change your LiturgyEvents table so that is results in a combination of the Liturgy and Cycle - this is where your 165 unique liturgies comes in. The purpose of this may not be immediately apparent to you, but I promise the extra effort will be worth it. So... a. LitEventId(PriKey) LitNumber Cycle 1 1 A 2 1 B 3 1 C 4 2 A 5 2 B (etc) 5. The table that we earlier designated as HymnAssignments is the place where the main data entry you mentioned will be saved. The table needs to be like this... a. AssignmentID(PriKey) LitEventID LitPart HymnNumber 1 2 Prelude G854 2 2 Gathering G616 etc Ofv course, some of the data as it ends up in the table is difficult for the avrerage person to interpret with the naked eye. But then tables are not meant for human consumption anyway. Correct design of your forms will make this user friendly... but that's for another day. SignatureSteve Schapel, Microsoft Access MVP > Steve, > [quoted text clipped - 66 lines] > I hope this is helpful to fully understand what I hope to accomplish. I > really appreciate your patience with my inexperience. Thanx Steve Linronamy, As regards my point 3 below concerning the ID field in the LitFunctions table, I have revised my thinking here! :-) If you want to determine the order in which the LitParts are displayed in comboboxes, or in your reports or whatever, then it will be a good idea to add a numeric field for this purpose. SignatureSteve Schapel, Microsoft Access MVP > 3. Similarly, the names of the 11 Parts are unique, so I wouldn't > bother with a LitNumber field in the LitFunctions table either. So... Steve, These are my current TABLE designs: TABLE: Liturgies LitNumber LitName (Pkey, AutoNum) (Liturgy Names using Look-Up Table) 1 Advent 1st Sunday 2 Advent 2nd Sunday 3 Easter 2nd Sunday TABLE: LiturgiesLU (Look up table possible choices) LitNameLU (Unique names) Advent 1st Sunday Advent 2nd Sunday Easter 2nd Sunday (There are actually 65 unique names w/o Cycles) TABLE: Hymns HymnName HymnNumber (Pkey, Hymn Title) (Hymn#) Morning Has Broken G748 Sing to the Mountains G452 We Remember G578 ** Must use Pkey with HymnName as HymnNumbers may not be unique where the names will be. TABLE: Cycles CycleName (Pkey, Cycle designation) A B TABLE: CyclesLU (Look up table possible choices) CycleName (Pkey, Cycle designation) A, B, C, Special, Memorial, Wedding TABLE: LitPart LitPart (Describes the Part of the Liturgy where hymn is being played/sung.) Prelude Instrumental Processional Gathering TABLE: LitPartLU LitPartID(Number) LitPart (Look up table choices) 1 Prelude 2 Instrumental Processional 3 Gathering TABLE: LitEvents LitEventID LitNumber Cycle (Pkey, AutoNum.) (Ref. Liturgies Table) (Ref. Cycles Table) 1 1 A 2 1 B 3 1 C 4 2 A TABLE: Assignments AssignID LitEventID LitPart HymnNumber (Pkey, AutoNum.) (Ref. LitEvents Table) (Ref. LitPart Table) (Ref. Hymns Table) 1 2 Prelude G854 2 2 Gathering G616 etc I am comfortable with most of the tables, but not sure about redundancies. How does it look? Am I ready for Data input yet? Eagerly awaiting your reply, THAX again. > Linronamy, > [quoted text clipped - 6 lines] > > 3. Similarly, the names of the 11 Parts are unique, so I wouldn't > > bother with a LitNumber field in the LitFunctions table either. So... Linronamy, Well, a few comments... 1. I can't see any purpose for the LiturgiesLU table. It holds exactly the same data as the Liturgies table, except for the LitNumber id field. The Liturgies table *is* your lookup table for liturgies, you don't need another one. Just remove the LiturgiesLU table from the design altogether. 2. Do you mean to say that you could have more than one Hymn with the same HymnNumber? Like two hymns, both G578? This is very strange, but if so, I would not use the HymnName as the Primary Key field in the Hymns table. If you really can have more than one Hymn with the same HymnNumber, you should put back a HymnID Autonumber field in this table as the PK. And, whatever is the PK, HumnNumber or HymnID, has to be the field that links to the hymn entry in the Assignments table. 3. Similar to point 1, the Cycles and CyclesLU tables duplicate each other. There is no need for the CyclesLU table, so you might as well trash it. 4. As I indicated in an earlier reply, the LitPart table is fine like it is, from a functional point of view. But any combobox for data entry of the Part, or reports, will be sorted alphabetically, which may not be what you want. Gathering will come before Prelude, for example. If you want to have them sorted in order or how they occur, you will need to add another field to the table to control this. Probably a Number data type is easiest. SignatureSteve Schapel, Microsoft Access MVP > Steve, > [quoted text clipped - 70 lines] > > Eagerly awaiting your reply, THAX again. Steve, Perhaps I misinterpretted the lookup application. I was using the LiturgiesLU and CyclesLU tables to facilitate data entry on a form. Allows less keystrokes and eliminates typo errors. The Liturgy list is fixed as is the Cycles list and I thought including them in their tables as lookups before any other data was input would be helpful at data entry time on a form. I'm not sure I understand the big picture as to how data relates to each other to facilitata DB efficiency. Hymns Table: ** HymnNumber Examples: G748 (G for Hymnal description - #748) unique B147 (B for Hymnal description - #147) unique S (Song Sheet where not in hymnal) repetitive Kevin (Author’s name) Repetitive The HymnName would be unique where the HymnNumber may not be. Adding a HymnID is not a problem. Is sort order an issue (for query purposes)? Thanx for help > Linronamy, > [quoted text clipped - 100 lines] > > > > Eagerly awaiting your reply, THAX again. Linronamy, See comments inline... > Perhaps I misinterpretted the lookup application. I was using the > LiturgiesLU and CyclesLU tables to facilitate data entry on a form. Allows > less keystrokes and eliminates typo errors. The Liturgy list is fixed as is > the Cycles list and I thought including them in their tables as lookups > before any other data was input would be helpful at data entry time on a > form. You are absolutely correct in your thinking here. However, the Liturgies and Cycles tables, as you described them, will serve this exact purpose. > Hymns Table: > ** HymnNumber Examples: G748 (G for Hymnal description - #748) unique > B147 (B for Hymnal description - #147) unique > S (Song Sheet where not in hymnal) repetitive > Kevin (Author’s name) Repetitive > The HymnName would be unique where the HymnNumber may not be. Ok, thanks for the further explanation. Unless you want to institute a system of S1, S2, etc, Kevin1, Kevin2, etc, then the Autonumber ID field is the way to go. > Adding a > HymnID is not a problem. Is sort order an issue (for query purposes)? No. > Thanx for help You're welcome. SignatureSteve Schapel, Microsoft Access MVP Steve, Based on your last reply I think my table designs are set except for relationships between tables. I'm not sure how to approach which tables must have a link between them. Do I simply link all fields with similar field names between tables? Can I proceed to Form design? I'm not sure how to approach the design using more than one form for data input. Thanx > Linronamy, > [quoted text clipped - 30 lines] > > You're welcome. Linronamy, The purpose of defining Relationships is so you can set up Referential Integrity. And what Referential Integrity does is ensure that you can't have a record on the "many" side of a one-to-many relationship without a corresponding record on the "one" side. So, for example, you could set up a Relationship between the HymnID field in the Hymns table and the HymnID in the Assignments table, so this will mean you couldn't delete a hymn from the Hymns table if that hymn was allocated to a liturgy somewhere in the system. So, that's the tables, which is by far the most important aspect of the design of your database. As regards forms, I think I would have a simple form, continuous view, based on the Hymns table. Similarly, I think I would have a similar form based on the Liturgies table, for ease of reference to this relatively stable lookup data. I don't think I would bother with a form for management of the Cycles table or the LitPart table... these are very simple, and I imagine the data is very unlikely to change much. There aren't really any right or wrongs about this, but I think that's how I would probably go about it. But here's the general concept with the main data forms... You need 2 forms: 1. Single view form, based on the LitEvents table. This will use comboboxes whose Row Sources are the Liturgies table and the Cycles table for data entry. So each record on this form will define a Liturgy for a given Cycle. 2. A continuous view form based on the Assignments table. This form will be places on the LitEvents form as a subform, with its LinkMasterFields and LinkChildFields properties set to LitEventID. Comboboxes, with their Row Sources being the LitPart and Hymns tables, will be used for data entry into the LitPart and HymnID fields. You may need to reference a good book or some other resource in getting onto a mastery of subforms and comboboxes. SignatureSteve Schapel, Microsoft Access MVP > Steve, > [quoted text clipped - 6 lines] > I'm not sure how to approach the design using more than one form for data > input. Steve, I see that I have a lot of reading to do and I thank you for pointing me in a focused direction to the specifics of my next step. Can you clarify for me the advantages of using more than one form for data entry over a single form or will this all become obvious after studying your recommendations in your last reply? A sample of my existing hard copy data that will be used when entering data: 2nd Sunday of Advent Cycle B Prelude RISE UP JERUSALEM G305 Instrumental Processional CHRIST BE OUR LIGHT Song Sheet Gathering LIKE A SHEPHERD G332 Responsorial Psalm LORD, LET US SEE YOUR KINDNESS G48 Gospel Acclamation ALLELUIA, ALLELUIA, ALLELUIA Advent GA Preparation PROMISE Song Sheet Pre-Communion Instr. TURN TO ME G278 Communion 1 A VOICE CRIES OUT G343 Communion 2 SHEPHERD ME, O GOD G23 Closing CITY OF GOD G663 Instrumental Postlude LET THE VALLEYS BE RAISED B339 With my database naivety in mind, it seems to me there would be rewards in not requiring the people (not computer literate) doing the data entry to select different forms for different data. Thanx again for your patience and help > Linronamy, > [quoted text clipped - 44 lines] > > I'm not sure how to approach the design using more than one form for data > > input. Linronamy, It would be possible to use one form for the data entry of all the hymn assignments. This would involve making a query based on the LitEvents and Assignments tables, and basing the form on this query. This has 2 major disadvantages over the model I suggested. First, the data '2nd Sunday of Advent' and 'Cycle B' would have to be entered 11 times instead of 1. Secondly, there will be 2000 records all on the same form and very hard to see your way around them. The ability to use a form/subform structure for data management is one of the key benefits to using a database like Access. As regards usability, well, that is another topic. Ultimately you will need to provide a means of selecting the required tasks and navigating around the database. Once again, a good book, and a look at some other database applications (for example the Northwind database that comes with Access), will help give an overview of what you are trying to achieve here. SignatureSteve Schapel, Microsoft Access MVP > Steve, > [quoted text clipped - 22 lines] > not requiring the people (not computer literate) doing the data entry to > select different forms for different data. Update: Upgraded from Access 200 to Access 2003. My tables are still as we last discussed. Am spending the last 1 1/2 sstudying sample exercise (NorthWind) to facilitate my DB education. Am having difficulty getting my arms around the multiple form for data input rather than single form concept. Trying to visualize but can't seem to figure how to select/configure fields that make sense. Any inputs would be greatly appreciated as I want to be correct following your suggestions. THANX > Linronamy, > [quoted text clipped - 45 lines] > > > > THANX for your interest and help Linronamy, Well, you are certainly on track to become an expert. In the sense that what you are doing is the only way that I know of to learn this stuff. It is great. Do it a few more times and it will be second nature :-). In the end, there is more that one way to skin the cat, so my version is not necessarily the way it will end up I suppose. Not only that, but once your data is in place, you may want to have a number of different forms &/or reports in order to look at it from a number of angles. But here's how I see it, as a baseline... You need 2 forms: 1. Single view form, based on the LitEvents table. This will use comboboxes whose Row Sources are the Liturgies table and the Cycles table for data entry. So each record on this form will define a Liturgy for a given Cycle. 2. A continuous view form based on the Assignments table. This form will be placed on the LitEvents form as a subform, with its LinkMasterFields and LinkChildFields properties set to LitEventID. Comboboxes, with their Row Sources being the LitPart and Hymns tables, will be used for data entry into the LitPart and HymnID fields. Part of the "secret" here will be using the Properties of the comboboxes to ensure that the "human-friendly" data is displayed. So, for example, your main form, based on the LitEvents table, has a combobox bound to the LitNumber field. The Row Source of this combobox is based on the Liturgies table, which is basically a lookup table, and already has the 65 data records in it. The Combobox has these property settings: Bound Column: 1 Column Count: 2 Column Widths: 0;3 The effect of this is that the combobox will list Advent 1st Sunday etc, and when your selection is made, it will show Advent 1st Sunday or whatever, but the correct data (i.e. Lit Number) will be in the form's underlying table. And so on. So here we have it... Your main form will contain your 200 or whatever it is records, and you will see each of these records one by one, for example: Liturgy - Advent 1st Sunday Cycle - B And then the subform will list, in continuous view, all the hymn assignments for that liturgy/cycle event, for example: Prelude G854 Morning Has Broken So, see if you can get anywhere with these ideas. SignatureSteve Schapel, Microsoft Access MVP > Update: > Upgraded from Access 200 to Access 2003. My tables are still as we last [quoted text clipped - 7 lines] > > THANX Steve, I suspect I may be suffering from brain saturation at this point. I felt the need to review my current tables and relationships with you to verify that I haven’t gone off track. I realize that I have reverted to continuing to use the lookup tables (crutch) as I still am not grasping using the original table to both input data and select from the lookup info as well. Also since I established the relationships between tables the form design screen/process has forced subforms that don’t appear to reflect my vision of data entry. Perhaps these few inputs will provide some awareness of level of confusion. TABLE DESIGNS: TABLE: Liturgies LitNumber LitName (Pkey, AutoNum) (Liturgy Names using Look-Up Table) 1 Advent 1st Sunday 2 Advent 2nd Sunday 3 Easter 2nd Sunday TABLE: LiturgiesLU (Look up table possible choices) LitNameLU (Unique names) Advent 1st Sunday Advent 2nd Sunday Easter 2nd Sunday (There are actually 65 unique names w/o Cycles) TABLE: Hymns HymnID HymnName HymnNumber (Pkey, AutoN) (Hymn Name) (Hymn#) 1 Morning Has Broken G748 2 Sing to the Mountains G452 3 We Remember G578 TABLE: Cycles CycleID CycleName (Pkey, AutoN) (Cycle designation) 1 A 2 B TABLE: CyclesLU (Look up table possible choices) CycleName (Cycle designation) A, B, C, Special, Memorial, Wedding TABLE: LitPart LitPartID LitPart (Pkey, AutoNumber) (Describes the Part of the Liturgy where hymn is being played/sung.) 1 Prelude 2 Instrumental Processional 3 Gathering TABLE: LitPartLU LitPartID(Number) LitPart (Look up table choices) 1 Prelude 2 Instrumental Processional 3 Gathering TABLE: LitEvents LitEventID LitNumber CycleID (Pkey, AutoNum.) (Ref. Liturgies Table) (Ref. Cycles Table) 1 1 1 2 1 2 3 1 3 4 2 4 TABLE: Assignments AssignID LitEventID LitPartID HymnID (Pkey, AutoNum.) (Ref. LitEvents Table) (Ref. LitPart Table) (Ref. Hymns Table) 1 1 1 G854 2 2 2 G616 etc RELATIONSHIPS (Links are shown using numbers with the ∞ symbol for the MANY side) LITEVENTS ASSIGNMENTS LITPART LITURGIES LitEventID4 ASSignID 5LitPartID LITPARTLU LITUGIESLU LitNumber2 2∞LitNumber 4LitEventID LitPart8 8LitPart LitName1 1LitName CycleID∞3 LitPartID∞5 HymnID∞6 HYMNS CYCLES 6HymnID 3CycleID HymnName CycleName7 HymnNumber CYCLELU 7CycleName > Linronamy, > [quoted text clipped - 52 lines] > > > > THANX >I still am not grasping using the original >table to both input data and select from the lookup info as well PMFJI but... The reason it's hard to grasp using the table to input data is that you should NEVER use a Table to input data. Tables are data repositories. They are NOT designed or intended as user interfaces to the data. Use a Form based on your table instead. The only time you need to open a table datasheet is for debugging. John W. Vinson[MVP] That was a bit of gobble-d-gook. "...as I still am not grasping using the original table to both input data and select from the lookup info as well" What I meant to say was, "as I still am not grasping REFERENCING the original table on the FORM I was designing to both input data and select from the lookup info as well. Sorry, not very clear. I was referring to a previous response from Steve on 1/17/06. > >I still am not grasping using the original > >table to both input data and select from the lookup info as well [quoted text clipped - 12 lines] > > John W. Vinson[MVP] >What I meant to say was, "as I still am not grasping REFERENCING the original >table on the FORM I was designing to both input data and select from the >lookup info as well. Sorry, not very clear. I was referring to a previous >response from Steve on 1/17/06. My fault for jumping into the middle of the thread - sorry! A Form has a Recordsource property; you can view this (and the other) properties by right-clicking the little square at the upper left of the form. Select Properties from the dropdown menu. The Recordsource property might be a table or (more often) a query based on the table. If your Form's Recordsource is tblMyTable, then all fields in tblMyTable are available for use on the form. John W. Vinson[MVP] Steve, Okay, a light bulb maybe!! 1st I'm not sure of difference between single and continuous forms (how to select and configure in design?) I see a form with LitName and CycleName fields at top of detail followed by eleven HymnName and HymnNumber field sets with each having a label identifying it's Liturgy Part. Each label has a control button linked to a query to view all the hymns associated with the Liturgy and cycle at top for that LitPart. It would be nice if operator could choose the hymn by clicking on it to enter it into the next record. What do you think? > Linronamy, > [quoted text clipped - 52 lines] > > > > THANX Hi linronamy, > I have no idea how to design a database to provide the information I need... You should spend some time gaining an understanding of database design and normalization before attempting to build something in Access (or any RDBMS software for that matter). Here are some links to get you started. Don't underestimate the importance of gaining a good understanding of database design. Brew a good pot of tea or coffee and enjoy reading! http://www.datatexcg.com/Downloads/DatabaseDesignTips1997.pdf http://www.datadynamicsnw.com/accesssig/downloads.htm (See the last download titled "Understanding Normalization") Also, head on over to Jeff Conrad's site. He has lots of links for database design http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#Database Design101 > For each event there are 11 ACTIONS, each having a descriptor NAME and > ID NUMBER. Okay, it sounds like you have a one-to-many (1:M) relationship between Events and Actions, ie. one event may have many actions. This data should be stored in two tables; one for events and one for actions. The question that comes up is do you have a many-to-many relationship between these two entities? In other words, is the following statement true or false? An action can apply to many events If you answered yes, then you have a M:N relationship, which requires the use of a third join or linking table. By the way, NAME is a reserved word in Access. You should not use any reserved words or special characters for things that you assign a name to in Access. By avoiding reserved words and special characters (for example spaces), you will automatically avoid many problems routinely encountered by others. Here are some KB articles that should be helpful to you: Reserved Words in Microsoft Access http://support.microsoft.com/?id=286335 List of reserved words in Jet 4.0 http://support.microsoft.com/?id=321266 Special characters that you must avoid when you work with Access databases http://support.microsoft.com/?id=826763 > Process: > • The goal is to pick an event. > • View each action from that unique event reviewing all the name descriptors that occurred for each action over the previous 6 years. You should be able to display the event data in a form, with the corresponding actions in a subform. > • Select one name and its corresponding ID number for each action. > • Print the results in a report. Here's where it will get a bit trickier, because if you select one or more records in a subform, and then click on a print button on the main form, the selected records in the subform will have been lost. Try this KB article out: How to enumerate selected form records in Access 2002 http://support.microsoft.com/?id=294202 You should be able to use VBA code to create an appropriate WhereCondition argument for the DoCmd.OpenReport method. Try the above article out first, to gain some practice with identifying the selected records. Tom http://www.access.qbuilt.com/html/expert_contributors.html http://www.access.qbuilt.com/html/search.html __________________________________________ I have no idea how to design a database to provide the information I need to provide directions on a monthly interval from data accumulated over 6 years. • The data includes 165 unique seasonal EVENT descriptions. o The events occur approximately 12 times a year. o These 12 events repeat but are different the 2nd and 3rd year and then repeat for the next 3 years, and so on. • For each event there are 11 ACTIONS, each having a descriptor NAME and ID NUMBER. Process: • The goal is to pick an event. • View each action from that unique event reviewing all the name descriptors that occurred for each action over the previous 6 years. • Select one name and its corresponding ID number for each action. • Print the results in a report. The data input form seems easy enough but viewing each NAME per ACTION and selecting one of many to print in a report is where I fall apart. Any suggestions on how to start will be greatly appreciated. Before ever considering building anything but a simple relational database, you need to understand the basics. For that I highly recommend Database Design for Mere Mortals by Hernandez. Amazon carries the latest version: http://www.amazon.com/gp/product/0201752840 SignatureJerry Whittle Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. > I have no idea how to design a database to provide the information I need to > provide directions on a monthly interval from data accumulated over 6 years. [quoted text clipped - 18 lines] > selecting one of many to print in a report is where I fall apart. Any > suggestions on how to start will be greatly appreciated. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.