For either event you need the tblCompanyEventMedia to record what "stuff" was sent or left related to the event. What the "stuff" is, I'll leave to you. You would list the "stuff" in the tblMediaTypes, but feel free to rename the table to something more appropriate to your needs The "media" event would just be another choice of events you would see in the dropdown box (that you get from the event types table). I would still recommend adding the additional fields to the tblEvents for the description and notes. You can hide the controls on the form if the event is not a meeting.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
Table Design re one-to-one relationships
- Thread starter lmcc007
- Start date
For either event you need the tblCompanyEventMedia to record what "stuff" was sent or left related to the event. What the "stuff" is, I'll leave to you. You would list the "stuff" in the tblMediaTypes, but feel free to rename the table to something more appropriate to your needs The "media" event would just be another choice of events you would see in the dropdown box (that you get from the event types table). I would still recommend adding the additional fields to the tblEvents for the description and notes. You can hide the controls on the form if the event is not a meeting.
For either event you need the tblCompanyEventMedia to record what "stuff" was sent or left related to the event. What the "stuff" is, I'll leave to you. You would list the "stuff" in the tblMediaTypes, but feel free to rename the table to something more appropriate to your needs The "media" event would just be another choice of events you would see in the dropdown box (that you get from the event types table). I would still recommend adding the additional fields to the tblEvents for the description and notes. You can hide the controls on the form if the event is not a meeting.
Gotcha! I just went back over your db. It's coming back to me. I keep forgetting stuff and have to relearn it. I know this is basic stuff but I was always get stuck in that one area when trying to normalize the db.
Anyway, thanks a bunch!
You're welcome. Good luck with the project.
Now, I see what's confused me your db. It was the naming--that is, tblCompanyEventMedia. When I saw that I kept thinking about linking tables--Company table and Event table to Media table.
Hello MajP and The_Doc_Man,
I finally did the database over, but have run into one problem. I need to keep track of each job they send a worker on or to interview for.
They want it to be like:
I finally did the database over, but have run into one problem. I need to keep track of each job they send a worker on or to interview for.
They want it to be like:
1. Open Company form – enter company information
2. Open Event form – enter events.
a. If the event is Training then they want it to be able to enter the Job Title training for.
b. If the event is Interviewing workers then they want the Job Title interviewing for.
That way they can trace what jobs require training and so on.2. Open Event form – enter events.
a. If the event is Training then they want it to be able to enter the Job Title training for.
b. If the event is Interviewing workers then they want the Job Title interviewing for.
Attachments
If you need to tie a job title to an event, you can just add another field to the event table to hold that info. On a form, you would just hide or unhide the control depending on the event type. This assumes that only 1 job title will be associated with an event. If multiple job titles can be associated with an event then that describes a one-to-many relationship which would require a separate but related table.
When I was looking at your database I noticed that you had phone1, phone2, phone3, phone4 in the company table. Having fields such as this is an example of repeating groups which is not normalized. If a company can have many phone numbers associated with it (one company-to-many phone number relationship) , the phone numbers should be stored as records in a separate but related table. What would happen if a company had 5 phone numbers? You would have to redesign your table and all related forms, queries and reports!
Regarding the address1 and address2, if these fields just represent the two lines of a single physical address, it is probably OK as is. I assume that is what you intended. If you need to store the addresses of multiple company locations, then you would need a separate but related table.
When I was looking at your database I noticed that you had phone1, phone2, phone3, phone4 in the company table. Having fields such as this is an example of repeating groups which is not normalized. If a company can have many phone numbers associated with it (one company-to-many phone number relationship) , the phone numbers should be stored as records in a separate but related table. What would happen if a company had 5 phone numbers? You would have to redesign your table and all related forms, queries and reports!
Regarding the address1 and address2, if these fields just represent the two lines of a single physical address, it is probably OK as is. I assume that is what you intended. If you need to store the addresses of multiple company locations, then you would need a separate but related table.
If you need to tie a job title to an event, you can just add another field to the event table to hold that info. On a form, you would just hide or unhide the control depending on the event type. This assumes that only 1 job title will be associated with an event. If multiple job titles can be associated with an event then that describes a one-to-many relationship which would require a separate but related table.
When I was looking at your database I noticed that you had phone1, phone2, phone3, phone4 in the company table. Having fields such as this is an example of repeating groups which is not normalized. If a company can have many phone numbers associated with it (one company-to-many phone number relationship) , the phone numbers should be stored as records in a separate but related table. What would happen if a company had 5 phone numbers? You would have to redesign your table and all related forms, queries and reports!
Regarding the address1 and address2, if these fields just represent the two lines of a single physical address, it is probably OK as is. I assume that is what you intended. If you need to store the addresses of multiple company locations, then you would need a separate but related table.
Okay, gotcha. They don't like the look of subforms. The problem is a lot of their databases are using the MS template; they like that look. You know,
Home (888) 888-8888
Work (888) 888-8888
Mobile (888) 888-8888
Fax (___) ___-____
Work (888) 888-8888
Mobile (888) 888-8888
Fax (___) ___-____
You can have a type field to supply the appropriate name: home, work etc. I would not let the user dictate the table structure, so I guess I would have the correct table structure and then try to find a way in the forms to display the info the way they want. I might suggest a form/subform design for data entry of the phone info but then use something different when editing existing customer records; perhaps some controls that use the DLookup() function to pull in the various phone numbers. If someone needs to edit a phone number then give them a button that opens up the form with the form/subform design. Others on the forum might have some other ideas on form design. I have seen some DB's that have some very creative form designs that put simple ones to shame.
You can have a type field to supply the appropriate name: home, work etc. I would not let the user dictate the table structure, so I guess I would have the correct table structure and then try to find a way in the forms to display the info the way they want. I might suggest a form/subform design for data entry of the phone info but then use something different when editing existing customer records; perhaps some controls that use the DLookup() function to pull in the various phone numbers. If someone needs to edit a phone number then give them a button that opens up the form with the form/subform design. Others on the forum might have some other ideas on form design. I have seen some DB's that have some very creative form designs that put simple ones to shame.
Do you have any examples I can see?
For this particular design I have search for months. Other sites have the same design--basic subform look. Not what they wanted.
MS templates look good but the table structure is Phone1, Phone2, Phone3, Phone4, etc.
I generally use the form/subform design, so I don't have any personal examples. You might try posting your specific question in the forms forum and see what others have to offer.
I did, but no takers.
Anway, how do you do the below?
... I might suggest a form/subform design for data entry of the phone info but then use something different when editing existing customer records; perhaps some controls that use the DLookup() function to pull in the various phone numbers. If someone needs to edit a phone number then give them a button that opens up the form with the form/subform design....
This sounds like it may do it.
I took the DB you posted and modified your frmCompany. The modified DB is attached.
Okay, I see what you mean. When you see it then you say "Oh, that's what he/she meant."
A year ago I did this with another database (see attached PhoneNumber db) but abandon it because of the constant calls they can't tab from this number to the next or go back.
What do you think?
Attachments
If I had other data related to the company (other many sides of the relationship), I would set up a tabbed subform as shown in frmCompany2 in the attached database. There would be no problem tabbing from one phone number to another and the tabbed design keeps data organized a little better than having a bunch of subforms scattered about the main form.
Attachments
If I had other data related to the company (other many sides of the relationship), I would set up a tabbed subform as shown in frmCompany2 in the attached database. There would be no problem tabbing from one phone number to another and the tabbed design keeps data organized a little better than having a bunch of subforms scattered about the main form.
That's what I started off with--frmCompany2, and that's what they hate. The like the style of frmCompany.
My users don't generally care as much about what the forms look like as long as the information and functionality are there. Once they see what the database can do, they want to incorporate other stuff into it. I had a small project tracking database for a lab and then I expanded it to incorporate multiple labs. Then the users wanted all of their test equipment and associated quality data in the database, so it just kept expanding that way.
I'll leave it to you to satisfy your users, but I would not compromise the table structure because doing so may cause problems in the long run.
I'll leave it to you to satisfy your users, but I would not compromise the table structure because doing so may cause problems in the long run.
My users don't generally care as much about what the forms look like as long as the information and functionality are there. Once they see what the database can do, they want to incorporate other stuff into it. I had a small project tracking database for a lab and then I expanded it to incorporate multiple labs. Then the users wanted all of their test equipment and associated quality data in the database, so it just kept expanding that way.
I'll leave it to you to satisfy your users, but I would not compromise the table structure because doing so may cause problems in the long run.
I understand what you're saying, but I don't have that control. I get a lot of stuff done by the secretary and a few by the attorney--who is the client--you get it. They've taken classes and many have used MS templates so going there with them is like going no where. They don't want you to redesign their forms; they just want you to fix it and make it work.
Unfortunately, it sounds like you don't have complete control of the DB, you'll just have to make the best of it. Good luck.
Yep, that's correct.
Similar threads
- Replies
- 2
- Views
- 687
- Replies
- 31
- Views
- 4,197
Users who are viewing this thread
Total: 1 (members: 0, guests: 1)