If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
forms for tables set up as "one to one"
I am having trouble with forms I designed for tables that have a one-to-one
relationship, or at least I believe that's the problem. Here's my overall design strategy: I have a total of 20 tables; 13 are set up as one-to-many and the forms are working fine. 4 are junction tables, which worked well until I designed the second and third forms for my 3 primary tables, (which I split because I kept getting error messages about required information in parts of the table not included in my first form). Our process happens in three phases, so I wanted three different forms to populate the records at different times). The "Phase 1" form was working nicely, including a subform, until I designed a form for "Phase 2". Now, suddenly, the subform has stopped allowing input. There is no error message; it just won't allow the cursor to rest there. The Phase 2 and 3 forms are based on tblPhase1 with subforms for tblPhase2 and tblPhase3. Please help, as my department needs this database badly, and I'm getting embarrased about saying "almost ready"! Thanks so much! |
#2
|
|||
|
|||
forms for tables set up as "one to one"
Babe,
In my experience, it is a mistake to design your table structure according to data entry or workflow or user interface considerations. It is much better to temporarily forget about these things, and focus first of all on designing your tables according to proper database principles. Then you can make your forms etc conform to the data realities, rather than the other way around. So can you tell us something about the data you are working with, and the nature of the relationships between the various data entities, and the tables and fields that you think you might need for normalised data storage requirements? -- Steve Schapel, Microsoft Access MVP Babe in the Woods wrote: I am having trouble with forms I designed for tables that have a one-to-one relationship, or at least I believe that's the problem. Here's my overall design strategy: I have a total of 20 tables; 13 are set up as one-to-many and the forms are working fine. 4 are junction tables, which worked well until I designed the second and third forms for my 3 primary tables, (which I split because I kept getting error messages about required information in parts of the table not included in my first form). Our process happens in three phases, so I wanted three different forms to populate the records at different times). The "Phase 1" form was working nicely, including a subform, until I designed a form for "Phase 2". Now, suddenly, the subform has stopped allowing input. There is no error message; it just won't allow the cursor to rest there. The Phase 2 and 3 forms are based on tblPhase1 with subforms for tblPhase2 and tblPhase3. Please help, as my department needs this database badly, and I'm getting embarrased about saying "almost ready"! Thanks so much! |
#3
|
|||
|
|||
forms for tables set up as "one to one"
Thanks so much, Steve. I tried to adhere to the principals of efficient
design in my tables, as follows. Each table also includes an auto-num, which the user does not see. Any suggestions yopu can offer would be appreciated! JobID (autonum) We want a “tracking number” on each item, which is a concatenation of three pieces of information: JobCategory, (two digit alpha/num representation of “brochure, web page, stationary, advertisement”, set up in separate many-side table, below) Year, (represented in two digits (06, 07 etc.) Counter- 3 digit number from 000 to 999 (using dMax + 1 with multi-user language) the other pieces of information in the main table(s) are set up as follows: ProjectTitle (text) ParentCampaign (link to Campaign table, below) ProjectManager (link to Contact table below) ClientID (link to Contact table below) BasedonExisting (text) Description (text) FirstContactDate (date) TargetMarket (text) MailingHouse (link to Contact table, below) To MailingHouse (date) Inv Recd Date (date) CircDropDate (date) Product (link to Product table, below) CrossSell (link to Product table, below) AssignDate (date) ArtistID (link to Contact table, below) ProofDate (date) VendorID (link to Contact table, below) ToVendDate (date) VendorProofDate (date) FirstEditorialDate (date) FinalEditorialDate (date) ClientSignDate (date) PgmManagerSignoffDate (date) Status (link to Job Status table, below) EstimatedDesignCost (currency) FinalDesignCost (currency) EstMailCost (currency) FinMailCost (currency) EstMktCost (currency) FinalMktCst (currency) EstPrintCost (currency) FinPrintCost (currency) TrimSize (text) BleedSize (text) LiveArea (text) ColorSpec (Link to ColorSpec table) ColorDetail (link to Color Detail table, below) ArtNotes (text) FileLoc (hyperlink to graphics files) toCDDate (date) CDIndex (text) DistributionTrack (subforms, link to Distribution Table, below) SuccessTrack (subforms, link to Success Table, below) __________________________________________________ _____________________ Campaign Table (lookup from main): ID (autonum) Title (text) Desc (text) ProdID (link to Product Table) BudgetNum (text) Product/Service Table (lookup from main):: ID (autonum) Name (text) Desc (text) Detail (memo) InvNum (text) ISBN (text) NoPages (text) SpineSize (text) Contact Table: Client, Vendor, Mailing, Artist, Project Manager (lookup from main): ID (autonum) First (text) Last (text) Office (text) addr1 (text) addr2 (text) city (text) state (text, input masked) zip (text, input masked) phone (text, input masked) ext (text, input masked) fax (text, input masked) email (text) notes (memo) Job Status Table (“new, to artist, to vendor, to manager, to client, completed”, etc. Note: appears on all forms in editable state to track job progression) StatusCode (two digit number) (lookup from main): Description (text) Distribution table: (Junction Table, Job to ClientContact) JobID (linked to autonum in primary table) Dist Quant (number) Client (linked to contact table) Color Spec Table (Black & White, 2 Color, 3 Color, 4Color etc) (lookup from main) Code (text) Description (text) Color Detail Table (Junction Table JobID to Detail ID) DetailID Color Location (text) PMS Number (from PMS Table, below) Success Table (With Junction Table, Success ID to Job ID ) ID (autonum) PeriodEnd WebClicks OrdsRef Revenue PMS Table PMS (text) Alias (text) "Steve Schapel" wrote: Babe, In my experience, it is a mistake to design your table structure according to data entry or workflow or user interface considerations. It is much better to temporarily forget about these things, and focus first of all on designing your tables according to proper database principles. Then you can make your forms etc conform to the data realities, rather than the other way around. So can you tell us something about the data you are working with, and the nature of the relationships between the various data entities, and the tables and fields that you think you might need for normalised data storage requirements? -- Steve Schapel, Microsoft Access MVP Babe in the Woods wrote: I am having trouble with forms I designed for tables that have a one-to-one relationship, or at least I believe that's the problem. Here's my overall design strategy: I have a total of 20 tables; 13 are set up as one-to-many and the forms are working fine. 4 are junction tables, which worked well until I designed the second and third forms for my 3 primary tables, (which I split because I kept getting error messages about required information in parts of the table not included in my first form). Our process happens in three phases, so I wanted three different forms to populate the records at different times). The "Phase 1" form was working nicely, including a subform, until I designed a form for "Phase 2". Now, suddenly, the subform has stopped allowing input. There is no error message; it just won't allow the cursor to rest there. The Phase 2 and 3 forms are based on tblPhase1 with subforms for tblPhase2 and tblPhase3. Please help, as my department needs this database badly, and I'm getting embarrased about saying "almost ready"! Thanks so much! |
#4
|
|||
|
|||
forms for tables set up as "one to one"
Well, I had this all neatly formatted in Word, with bolded field names and
indented notes, but when I tranferred it over here, I lost my formatting. If the sloppiness of the post is the reason I never heard back, please try to find it in your heart to forgive me. It would be awfully nice if Microsoft would support their products, rather than dierecting users to fend for themselves, in convoluted warrens of grief like this one. Our company has purchased about 300 copies of Access, and I purchased my own copy to work on this at home. The help language is drivel geared to either the lowest level use or the highest technical level. There is no middle ground. Of course, this is not the place to voice frustration, but I have been round and round the rugged Microsoft Web Sites until I am blue, and there's just no way to find the appropriate venue. I am bitter and frustrated. But what the hell...I just wanted to see this in print somewhere. No one reads it anyway.... "Steve Schapel" wrote: Babe, In my experience, it is a mistake to design your table structure according to data entry or workflow or user interface considerations. It is much better to temporarily forget about these things, and focus first of all on designing your tables according to proper database principles. Then you can make your forms etc conform to the data realities, rather than the other way around. So can you tell us something about the data you are working with, and the nature of the relationships between the various data entities, and the tables and fields that you think you might need for normalised data storage requirements? -- Steve Schapel, Microsoft Access MVP Babe in the Woods wrote: I am having trouble with forms I designed for tables that have a one-to-one relationship, or at least I believe that's the problem. Here's my overall design strategy: I have a total of 20 tables; 13 are set up as one-to-many and the forms are working fine. 4 are junction tables, which worked well until I designed the second and third forms for my 3 primary tables, (which I split because I kept getting error messages about required information in parts of the table not included in my first form). Our process happens in three phases, so I wanted three different forms to populate the records at different times). The "Phase 1" form was working nicely, including a subform, until I designed a form for "Phase 2". Now, suddenly, the subform has stopped allowing input. There is no error message; it just won't allow the cursor to rest there. The Phase 2 and 3 forms are based on tblPhase1 with subforms for tblPhase2 and tblPhase3. Please help, as my department needs this database badly, and I'm getting embarrased about saying "almost ready"! Thanks so much! |
#5
|
|||
|
|||
forms for tables set up as "one to one"
Babe,
I apologise that this thread slipped through my "watched threads" net, for some unknown reason. I am also sorry to hear of the frustrations you have been experiencing. I suppose it is not really comforting to know that almost everyone finds it a steep learning curve to get on top of database work. I hope you will continue to seek help in these forums, and that you can find a good Access book to refer to. The project you are working on seems to have some tricky aspects, so for a teeth-cutting example it's pretty challenging. I can't really fully grasp how it all hangs together, but it seems you have made a radical change from the design you first described to us, with its 20 tables. It would help if you could give a synopsis of the database and its purpose, just in English, i.e. not in terms of tables and fields. Well, it's good to see the tables and fields too, but I mean to help convey the meaning and purpose. Like this: "My database is for the operation of a library. We need to keep a complete catalogue of resources, including books, videos, and periodicals. We need a register of our members. And we need to track the members' borrowing and returning of items from the library, including the payment of any charges." In the meantime, there would appear to be a couple of immediate changes I would make to the design of the main table as you have outlined in your other post. I would remove these fields from that table: FirstContactDate (date) To MailingHouse (date) Inv Recd Date (date) CircDropDate (date) AssignDate (date) ArtistID (link to Contact table, below) ProofDate (date) VendorID (link to Contact table, below) ToVendDate (date) VendorProofDate (date) FirstEditorialDate (date) FinalEditorialDate (date) ClientSignDate (date) PgmManagerSignoffDate (date) Instead, I would have a separate linked table, let's say it is called Progress, with these fields: ProjectID (to link to main table) StepTaken ProgressDate AdditionalInfo So, each of the steps in the process becomes a separate record in this table, rather than a separate field in the main table. If I understand correctly, this field can also be removed: Status (link to Job Status table, below) This can ber deduced in a query based on the most recent entry in the Progress table, so should not be stored in the table, or subject to data entry/editing. I would also remove these fields from the main table: EstimatedDesignCost (currency) FinalDesignCost (currency) EstMailCost (currency) FinMailCost (currency) EstMktCost (currency) FinalMktCst (currency) EstPrintCost (currency) FinPrintCost (currency) Instead, I would have a separate table with these fields: ProjectID (to link to main table) Cost CostType So, each of the cost items becomes a separate record in this table, rather than a separate field in the main table. I hope these few comments are helpful. -- Steve Schapel, Microsoft Access MVP Babe in the Woods wrote: Well, I had this all neatly formatted in Word, with bolded field names and indented notes, but when I tranferred it over here, I lost my formatting. If the sloppiness of the post is the reason I never heard back, please try to find it in your heart to forgive me. It would be awfully nice if Microsoft would support their products, rather than dierecting users to fend for themselves, in convoluted warrens of grief like this one. Our company has purchased about 300 copies of Access, and I purchased my own copy to work on this at home. The help language is drivel geared to either the lowest level use or the highest technical level. There is no middle ground. Of course, this is not the place to voice frustration, but I have been round and round the rugged Microsoft Web Sites until I am blue, and there's just no way to find the appropriate venue. I am bitter and frustrated. But what the hell...I just wanted to see this in print somewhere. No one reads it anyway.... |
#6
|
|||
|
|||
forms for tables set up as "one to one"
Thank you, Steve. These suggestions are very helpful. Here's my situation
in plain English: I wish to set up a database to track all the tasks our marketing department works on. We process about 1,000 to 2,000 tasks each year. The information we need to store for each task is fairly complex; each task relates to a campaign: a campaign may require many tasks or only a few. The tasks vary by type: they may be magazine ads, book covers, pamphlets, web pages, forms, etc. We want to record when each step of the task is completed, (usually 5 to 8 steps), which Marketing Rep is in charge of it, which Artist it has been assigned to, which department (client) requested it, which vendor printed or produced it for us, which mailing house distributed it, and what the estimated and final costs are for each of four facets of the task: (Marketing Time, Artist Time, Printing Costs and Mailing Costs) . We also need to have the colors assigned by the artist and the particulars about the task on record: overall size, bleed size, graphic file type, hyperlink to graphics file, print specs, and notes to the vendor. Lastly, we want to record how many of these projects provoke a response from our customer, by recording how many orders are placed, the resulting revenue, and how many web click-throughs a particular ad recieved. I have tried various permutations of the tables setup I posted. In some cases, the vendor, client, mailing house, etc each had separate tables. In the example I poted, they were merged into "contacts", with types assigned. I welcome any and all suggestions! "Steve Schapel" wrote: Babe, I apologise that this thread slipped through my "watched threads" net, for some unknown reason. I am also sorry to hear of the frustrations you have been experiencing. I suppose it is not really comforting to know that almost everyone finds it a steep learning curve to get on top of database work. I hope you will continue to seek help in these forums, and that you can find a good Access book to refer to. The project you are working on seems to have some tricky aspects, so for a teeth-cutting example it's pretty challenging. I can't really fully grasp how it all hangs together, but it seems you have made a radical change from the design you first described to us, with its 20 tables. It would help if you could give a synopsis of the database and its purpose, just in English, i.e. not in terms of tables and fields. Well, it's good to see the tables and fields too, but I mean to help convey the meaning and purpose. Like this: "My database is for the operation of a library. We need to keep a complete catalogue of resources, including books, videos, and periodicals. We need a register of our members. And we need to track the members' borrowing and returning of items from the library, including the payment of any charges." In the meantime, there would appear to be a couple of immediate changes I would make to the design of the main table as you have outlined in your other post. I would remove these fields from that table: FirstContactDate (date) To MailingHouse (date) Inv Recd Date (date) CircDropDate (date) AssignDate (date) ArtistID (link to Contact table, below) ProofDate (date) VendorID (link to Contact table, below) ToVendDate (date) VendorProofDate (date) FirstEditorialDate (date) FinalEditorialDate (date) ClientSignDate (date) PgmManagerSignoffDate (date) Instead, I would have a separate linked table, let's say it is called Progress, with these fields: ProjectID (to link to main table) StepTaken ProgressDate AdditionalInfo So, each of the steps in the process becomes a separate record in this table, rather than a separate field in the main table. If I understand correctly, this field can also be removed: Status (link to Job Status table, below) This can ber deduced in a query based on the most recent entry in the Progress table, so should not be stored in the table, or subject to data entry/editing. I would also remove these fields from the main table: EstimatedDesignCost (currency) FinalDesignCost (currency) EstMailCost (currency) FinMailCost (currency) EstMktCost (currency) FinalMktCst (currency) EstPrintCost (currency) FinPrintCost (currency) Instead, I would have a separate table with these fields: ProjectID (to link to main table) Cost CostType So, each of the cost items becomes a separate record in this table, rather than a separate field in the main table. I hope these few comments are helpful. -- Steve Schapel, Microsoft Access MVP Babe in the Woods wrote: Well, I had this all neatly formatted in Word, with bolded field names and indented notes, but when I tranferred it over here, I lost my formatting. If the sloppiness of the post is the reason I never heard back, please try to find it in your heart to forgive me. It would be awfully nice if Microsoft would support their products, rather than dierecting users to fend for themselves, in convoluted warrens of grief like this one. Our company has purchased about 300 copies of Access, and I purchased my own copy to work on this at home. The help language is drivel geared to either the lowest level use or the highest technical level. There is no middle ground. Of course, this is not the place to voice frustration, but I have been round and round the rugged Microsoft Web Sites until I am blue, and there's just no way to find the appropriate venue. I am bitter and frustrated. But what the hell...I just wanted to see this in print somewhere. No one reads it anyway.... |
#7
|
|||
|
|||
forms for tables set up as "one to one"
"Babe in the Woods" wrote
. . . I tried to adhere to the principals of efficient design in my tables, as follows. . . . One-to-one relationships are (almost?) never due to good relational database design principles, but (almost?) always due to expediency -- as you stated that yours were, to "group" the data according to the three stages or phases. Form/subform relationships where the subform contains data from the same record as the main form are often problematical. It may be that some similar effect is occurring in your case. I am sure that you checked all the items under the Data tab in the Properties of the Form(s) embedded in the Subform Control to make certain none properties of those are set in a way that interferes with taking action on those forms. With these, perhaps extraneous, comments I am going to leave you in Steve's very capable care. Larry Linson Microsoft Access MVP |
#8
|
|||
|
|||
forms for tables set up as "one to one"
Babe,
Thanks for the further clarification of your project. Ok, well it seems that ou are getting it sorted. I will confirm my previous comments about a separate table for the steps completed for a task, and a separate table for costings, along the lines I suggested. I would also comment on your Tracking Number idea... These three atoms of information should not be combined into one field in your table. You need a field in the table for the TaskType, and you will also have the date, and you can do your incremented counter in a separate field. You can easily concatenate from the data in these three fields for your TrackingNumber when required on form or report. I agree with your current idea of a single Contacts table, with a Type field. I guess the only time I would vary from this is if it happened sometimes that a particular person fell into more than one contact category. So what are your specific questions now? -- Steve Schapel, Microsoft Access MVP Babe in the Woods wrote: Thank you, Steve. These suggestions are very helpful. Here's my situation in plain English: I wish to set up a database to track all the tasks our marketing department works on. We process about 1,000 to 2,000 tasks each year. The information we need to store for each task is fairly complex; each task relates to a campaign: a campaign may require many tasks or only a few. The tasks vary by type: they may be magazine ads, book covers, pamphlets, web pages, forms, etc. We want to record when each step of the task is completed, (usually 5 to 8 steps), which Marketing Rep is in charge of it, which Artist it has been assigned to, which department (client) requested it, which vendor printed or produced it for us, which mailing house distributed it, and what the estimated and final costs are for each of four facets of the task: (Marketing Time, Artist Time, Printing Costs and Mailing Costs) . We also need to have the colors assigned by the artist and the particulars about the task on record: overall size, bleed size, graphic file type, hyperlink to graphics file, print specs, and notes to the vendor. Lastly, we want to record how many of these projects provoke a response from our customer, by recording how many orders are placed, the resulting revenue, and how many web click-throughs a particular ad recieved. I have tried various permutations of the tables setup I posted. In some cases, the vendor, client, mailing house, etc each had separate tables. In the example I poted, they were merged into "contacts", with types assigned. I welcome any and all suggestions! |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
printing forms | Kagiso | General Discussion | 2 | December 14th, 2005 10:21 PM |
printing only forms | Kagiso | General Discussion | 1 | December 14th, 2005 04:32 PM |
Cascading forms driving me crazy | David | Using Forms | 1 | December 1st, 2005 04:08 AM |
Menubar listing open forms? | Harmannus | Using Forms | 0 | December 23rd, 2004 12:33 AM |
Forms Check Box not working | DLLower | General Discussion | 2 | June 17th, 2004 09:34 PM |