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
|
|||
|
|||
Need to add a table, I think
Hi. Requirements have cropped up (of course!) that make
me think I need a new table, or two. I'm going to list the requirements and hope someone can tell me how to make it work! Currently I have Contacts table- it holds pertinent contact stuff: LastName, FirstName, Address, City, State, EmailName, PhoneNumber, Member(Yes) I'm competing against Address Book program: 1) I need to be able to link spouses who are members (many of whom have different last names) so we don't send mail to Jane Doe and John Deer (2 mailings); rather Jane Doe and John Deer (1 mailing). 2) How to deal with name changes- if someone gets married/divorced and changes name (I learned the hard way- never change your name!), we still want the "link" to the old name, since we can never remember the new one. 3)Some lucky folks live here part of the year, and there part of the year. How to deal with 2 addresses for someone. Address Book allows you to set a date field: between these 2 dates, mail to Address1, else mail to Address2. 4) Members who both belong can pay the "family membership". How to track that Jane Doe paid and that covers John Deer as well. 5) A little off topic: but we have people we want to mail stuff to that aren't "members", such as to the media to cover our events. Should I set up an entirely different table for them? I hope I can be salvaged. Thanks, Steph |
#2
|
|||
|
|||
"Stephm" wrote in news:230f01c51db0
: I'm competing against Address Book program: Errrm: if you have something that does everything already, why _are_ you competing? 1) I need to be able to link spouses who are members (many of whom have different last names) so we don't send mail to Jane Doe and John Deer (2 mailings); rather Jane Doe and John Deer (1 mailing). A Mailings table, and a FK in each Member record to point to the correct Mailing record. Straightforward 1:M relationship, one mailing to many members. 2) How to deal with name changes- if someone gets married/divorced and changes name (I learned the hard way- never change your name!), we still want the "link" to the old name, since we can never remember the new one. The "correct" answer to this is to create a new table of names with a 1:m relationship as above. This can lead to horrid UI manouevres, however, so if it's a rare problem, you might prefer a pragmatic workround. Try creating a new field called OldNames, and put the old names in there. Then searches can use a criterion like WHERE LastName = "Doe" OR OldName LIKE "*Doe*" and so on. 3)Some lucky folks live here part of the year, and there part of the year. How to deal with 2 addresses for someone. Address Book allows you to set a date field: between these 2 dates, mail to Address1, else mail to Address2. Not too hard: it's an Addresses table and a LivingAt table to manage the many-to-many relationship. Put date stuff in the LivingAt table too, but think quite carefully about how you are going to deal with overlapping dates, periods with gaps in them, etc. You can make this extremely sophisticated or simple, pretty much according to taste. 4) Members who both belong can pay the "family membership". How to track that Jane Doe paid and that covers John Deer as well. Payments table tracks the sums that come in; ContributesTo records how much goes to each PaymentDue record. Systems like SQL Server can make sure that all the ContributesTo records add up to the same as the Payments record, but it's harder in Access (but not impossible). 5) A little off topic: but we have people we want to mail stuff to that aren't "members", such as to the media to cover our events. Should I set up an entirely different table for them? Up to you. If they are ever going to be membership prospects, I would keep them in the same People table. If they are different entities, appear in different queries, have different attributes (fields) then make them separate. You _can_ use UNION queries to stick different things together, although it's ugly and slow. If you find yourself doing a lot of that, then it's better to have a single table and loads of WHERE clauses! Hope that helps Tim F |
#3
|
|||
|
|||
See inserts.
I'm competing against Address Book program: Errrm: if you have something that does everything already, why _are_ you competing? Address book was made to handle 250 records. We have 1700. We're trying to move to Access and it would be less painful if they keep the functionality they have (and get so much more!) 1) I need to be able to link spouses who are members (many of whom have different last names) so we don't send mail to Jane Doe and John Deer (2 mailings); rather Jane Doe and John Deer (1 mailing). A Mailings table, and a FK in each Member record to point to the correct Mailing record. Straightforward 1:M relationship, one mailing to many members. OK, I built the Mailings table and added MailingsID to the Contacts table. What fields would you expect to see in the Mailings table? And embarrassingly, I don't know what to do next. Do I add some field in my frmContacts and somehow link two Contacts to one Mailing? 2) How to deal with name changes- if someone gets married/divorced and changes name (I learned the hard way- never change your name!), we still want the "link" to the old name, since we can never remember the new one. The "correct" answer to this is to create a new table of names with a 1:m relationship as above. This can lead to horrid UI manouevres, however, so if it's a rare problem, you might prefer a pragmatic workround. Try creating a new field called OldNames, and put the old names in there. Then searches can use a criterion like WHERE LastName = "Doe" OR OldName LIKE "*Doe*" and so on. Good on me- I figured this out. Thanks. 3)Some lucky folks live here part of the year, and there part of the year. How to deal with 2 addresses for someone. Address Book allows you to set a date field: between these 2 dates, mail to Address1, else mail to Address2. Not too hard: it's an Addresses table and a LivingAt table to manage the many-to-many relationship. Put date stuff in the LivingAt table too, but think quite carefully about how you are going to deal with overlapping dates, periods with gaps in them, etc. You can make this extremely sophisticated or simple, pretty much according to taste. hmm. I currently have Address fields in Contacts. Would I just need to create one new table? I don't think I've created a many-to-many, usually have an in-between-table. So, sadly- what would the table structure look like and once established, how would I handle the fields (sort of my reply to your suggestion in #1, where I admit that I don't know what I'm doing). 4) Members who both belong can pay the "family membership". How to track that Jane Doe paid and that covers John Deer as well. Payments table tracks the sums that come in; ContributesTo records how much goes to each PaymentDue record. Systems like SQL Server can make sure that all the ContributesTo records add up to the same as the Payments record, but it's harder in Access (but not impossible). I have a tblMembershipType that contains AmountOfDuesPaid, DateDuesPaid. So I'd set up a ContributesTo table by having ContributesToID in tblMembershipType? Confusion is that each spouse is set up as a family membership, showing that they owe $45 each. When the $45 comes in, I'm not sure how to handle it. 5) A little off topic: but we have people we want to mail stuff to that aren't "members", such as to the media to cover our events. Should I set up an entirely different table for them? Up to you. If they are ever going to be membership prospects, I would keep them in the same People table. If they are different entities, appear in different queries, have different attributes (fields) then make them separate. You _can_ use UNION queries to stick different things together, although it's ugly and slow. If you find yourself doing a lot of that, then it's better to have a single table and loads of WHERE clauses! Thanks- I think I'll keep them in the same table and give them a different "classification" like "others" rather than "member". Maybe build a form that defaults/shows just "others" - that way if we change their classification, they will show up on the member form. Hope that helps Helps. Wish I were you. Thanks, Steph Tim F . |
#4
|
|||
|
|||
"Stephm" wrote in
: I'm competing against Address Book program: Errrm: if you have something that does everything already, why _are_ you competing? Address book was made to handle 250 records. We have 1700. There are any number of contact managers and address books out there. Outlook springs to mind... We're trying to move to Access and it would be less painful Just remember that debugging and beta testing a new application can be extremely painful too. Does the business rely on this database? Who can you sue for compensation if it all blows up? 1) I need to be able to link spouses who are members (many of whom have different last names) so we don't send mail to Jane Doe and John Deer (2 mailings); rather Jane Doe and John Deer (1 mailing). A Mailings table, and a FK in each Member record to OK, I built the Mailings table and added MailingsID to the Contacts table. What fields would you expect to see in the Mailings table? Umm: AddressToSendTheThingTo (FK references Addresses) DateOfFirstSending PersonalLogoToPutOnTheOutsideOfTheEnvelope The first one is probably the only important one -- otherwise you won't know which Member's address to use. You might think they are the same, but they don't have to be... On the other hand, you might want to point it to the Member that will appear on the envelope. And embarrassingly, I don't know what to do next. Do I add some field in my frmContacts and somehow link two Contacts to one Mailing? Not embarassing at all. The User Interface can indeed be very tricky: essentially they'll want a button that says "Send a newsletter to this member alone" and one that says "Choose another member who will share this member's newsletter". The first one just creates a new Mailing record and points the Contacts.MailTo value to it. The second one displays a list of all members with their MailTo values (hidden) so the user can pick one, and that mailTo value is copied to the current record. Or something like that! you might prefer a pragmatic workround. Try creating a Good on me- I figured this out. Thanks. Good on you. KISS = "Keep It Simple, Stoopid!".. hmm. I currently have Address fields in Contacts. Would I just need to create one new table? This lets you point more than one address to each Member; but you have already indicated that often an Address is shared between more than one Member. This makes it a many-to-many. I don't think I've created a many-to-many, usually have an in-between-table. That is right: strictly there is no such thing as a many-to-many relationship; it's always a pair of one-many relationships and a table in the middle. what would the table structure look like and once established, how would I handle the fields Members -- LivingAt -- Addresses The LivingAt table looks like this *MemberID *Addresses *MonthFrom Eric 1034 Jan Sam 1049 Jan Sam 1982 May Sam 1049 Nov Which means that Sam lives at 1982 from May to September, and 1049 for the rest of the year. What makes the UI tricky here is letting the user know that making changes to address number 1099 affects all the people who receive mailings there, when they may have wanted actually to create a new address. Deduplicating the Addresses table can be hard too, as users are likely just to create a new record instead of looking to see if it already there. Your interface needs to make it very clear that they are "Updating the details of this address" or "Moving the address for this member". Without them ever being aware of these magic numbers, of course!! I have a tblMembershipType that contains AmountOfDuesPaid, DateDuesPaid. Quibble: MembershipTypes are Full, Junior, Life, Family etc. You need a table like this to control current charges and so on. You also need a table to track what individual people owe you: call it Dues or FeesOwed or something. If you _never_ accept partial payments, then you can have a one to many relationship between FeesOwed and PaymentsReceived, so that one or more FeesOwed.Payment points at each PaymentsReceived record. If you even consider accepting more than one PaymentReceived for a particular FeesOwed, then you are back into many-to-many territory, with a between- table called ContributesTo. Confusion is that each spouse is set up as a family membership, showing that they owe $45 each. When the $45 comes in, I'm not sure how to handle it. I may have misunderstood you here, but if you don't absolutely understand the business, you are never going to model it correctly! Up to you. If they are ever going to be membership prospects, I would keep them in the same People table. Thanks- I think I'll keep them in the same table and give them a different "classification" like "others" rather than "member". If it comes to that: they are members if they have a current FeesOwed record and they aren't if they don't. You could even say they are only members if they have a current FeesOwed record and a non-null FeesOwed.Payment field. Helps. Wish I were you. Oh no you don't g All the best Tim F |
#5
|
|||
|
|||
Tim,
I haven't slept well since your last reply. Why wouldn't I want to be you. No, actaully, it was Who would we sue?! Home-grown non-profit project. Hopefully not me. When I added the Mailings table, the Contacts table wasn't happy unless it had a MailingsID -so I deleted the field ;-). Now I'm ready to redo the link- Do I need to empty out all of the test Contacts or can I just have the MailingsID going forward? I think I'd integrate the Address field so that the user wouldn't know that I'm dealing with 2 different tables and then have a combo box to select "shared" Contacts to link. Do I add MailingsID (hidden) to Contacts? So would I have code (really good at that to) to move the address to the "shared" contact if shared contact is not null? Then, how do I make sure the "flip-side" contact got linked. (Mary is linked to John, is John linked to Mary?). I'll try to get Members -- LivingAt -- Addresses going. On payments, point taken. Another tables it is. Because I'm so good at tables. We are allowed to "cash-in" bucks (earned by volunteering) to the tune of $35 per individual and $45 per family. So say a $75 fee (family) with $22 cashed in for John and $5 for Mary = (math don't fail me now) $48 owed. So $48 will fulfill the requirement for both Mary and John together. So perhaps I do need 2 tables: Payments and ContributesTo? I understand the business. Just not how to model it. I rather understand how to model and then figure out the business! I didn't mean "confusion"- how about convolution. Thanks, Steph -----Original Message----- "Stephm" wrote in : I'm competing against Address Book program: Errrm: if you have something that does everything already, why _are_ you competing? Address book was made to handle 250 records. We have 1700. There are any number of contact managers and address books out there. Outlook springs to mind... We're trying to move to Access and it would be less painful Just remember that debugging and beta testing a new application can be extremely painful too. Does the business rely on this database? Who can you sue for compensation if it all blows up? 1) I need to be able to link spouses who are members (many of whom have different last names) so we don't send mail to Jane Doe and John Deer (2 mailings); rather Jane Doe and John Deer (1 mailing). A Mailings table, and a FK in each Member record to OK, I built the Mailings table and added MailingsID to the Contacts table. What fields would you expect to see in the Mailings table? Umm: AddressToSendTheThingTo (FK references Addresses) DateOfFirstSending PersonalLogoToPutOnTheOutsideOfTheEnvelope The first one is probably the only important one -- otherwise you won't know which Member's address to use. You might think they are the same, but they don't have to be... On the other hand, you might want to point it to the Member that will appear on the envelope. And embarrassingly, I don't know what to do next. Do I add some field in my frmContacts and somehow link two Contacts to one Mailing? Not embarassing at all. The User Interface can indeed be very tricky: essentially they'll want a button that says "Send a newsletter to this member alone" and one that says "Choose another member who will share this member's newsletter". The first one just creates a new Mailing record and points the Contacts.MailTo value to it. The second one displays a list of all members with their MailTo values (hidden) so the user can pick one, and that mailTo value is copied to the current record. Or something like that! you might prefer a pragmatic workround. Try creating a Good on me- I figured this out. Thanks. Good on you. KISS = "Keep It Simple, Stoopid!".. hmm. I currently have Address fields in Contacts. Would I just need to create one new table? This lets you point more than one address to each Member; but you have already indicated that often an Address is shared between more than one Member. This makes it a many-to-many. I don't think I've created a many-to-many, usually have an in-between- table. That is right: strictly there is no such thing as a many- to-many relationship; it's always a pair of one-many relationships and a table in the middle. what would the table structure look like and once established, how would I handle the fields Members -- LivingAt -- Addresses The LivingAt table looks like this *MemberID *Addresses *MonthFrom Eric 1034 Jan Sam 1049 Jan Sam 1982 May Sam 1049 Nov Which means that Sam lives at 1982 from May to September, and 1049 for the rest of the year. What makes the UI tricky here is letting the user know that making changes to address number 1099 affects all the people who receive mailings there, when they may have wanted actually to create a new address. Deduplicating the Addresses table can be hard too, as users are likely just to create a new record instead of looking to see if it already there. Your interface needs to make it very clear that they are "Updating the details of this address" or "Moving the address for this member". Without them ever being aware of these magic numbers, of course!! I have a tblMembershipType that contains AmountOfDuesPaid, DateDuesPaid. Quibble: MembershipTypes are Full, Junior, Life, Family etc. You need a table like this to control current charges and so on. You also need a table to track what individual people owe you: call it Dues or FeesOwed or something. If you _never_ accept partial payments, then you can have a one to many relationship between FeesOwed and PaymentsReceived, so that one or more FeesOwed.Payment points at each PaymentsReceived record. If you even consider accepting more than one PaymentReceived for a particular FeesOwed, then you are back into many-to-many territory, with a between- table called ContributesTo. Confusion is that each spouse is set up as a family membership, showing that they owe $45 each. When the $45 comes in, I'm not sure how to handle it. I may have misunderstood you here, but if you don't absolutely understand the business, you are never going to model it correctly! Up to you. If they are ever going to be membership prospects, I would keep them in the same People table. Thanks- I think I'll keep them in the same table and give them a different "classification" like "others" rather than "member". If it comes to that: they are members if they have a current FeesOwed record and they aren't if they don't. You could even say they are only members if they have a current FeesOwed record and a non- null FeesOwed.Payment field. Helps. Wish I were you. Oh no you don't g All the best Tim F . |
#6
|
|||
|
|||
"Stephm" wrote in news:3d3101c5201b
: When I added the Mailings table, the Contacts table wasn't happy unless it had a MailingsID -so I deleted the field ;-). Now I'm ready to redo the link- Do I need to empty out all of the test Contacts or can I just have the MailingsID going forward? You don't have to have a MailingID value in a Contacts record -- if it's NULL though, that member won't get a newsletter. Make sure that the Required property is False, and the DefaultValue is blank or NULL (Access unhelpfully puts a zero in the default value box, which is really stupid and has killed more than one database before). Then the ref integrity will be fine. You shouldn't have to empty out any contacts records. I think I'd integrate the Address field so that the user wouldn't know that I'm dealing with 2 different tables and then have a combo box to select "shared" Contacts to link. Do I add MailingsID (hidden) to Contacts? I cannot really visualise what you are getting at here, except to comment that hidden fields often mean that you are getting into trouble :-) So would I have code (really good at that to) to move the address to the "shared" contact if shared contact is not null? If "moving" records means you are holding the same stuff in separate tables, then you are definitely into the Bad Lands. Then, how do I make sure the "flip-side" contact got linked. (Mary is linked to John, is John linked to Mary?). If Mary's stuff is going to John's address, then that fact does not have to be recorded in John's record at all -- or vice versa. It's easy to run a query that gathers up who is using whose-else's addresses. We are allowed to "cash-in" bucks (earned by volunteering) to the tune of $35 per individual and $45 per family. So say a $75 fee (family) with $22 cashed in for John and $5 for Mary = (math don't fail me now) $48 owed. So $48 will fulfill the requirement for both Mary and John together. So perhaps I do need 2 tables: Payments and ContributesTo? Sounds like yes. All the best Tim F |
#7
|
|||
|
|||
Thanks for all the help. Steph
-----Original Message----- "Stephm" wrote in news:3d3101c5201b : When I added the Mailings table, the Contacts table wasn't happy unless it had a MailingsID -so I deleted the field ;-). Now I'm ready to redo the link- Do I need to empty out all of the test Contacts or can I just have the MailingsID going forward? You don't have to have a MailingID value in a Contacts record -- if it's NULL though, that member won't get a newsletter. Make sure that the Required property is False, and the DefaultValue is blank or NULL (Access unhelpfully puts a zero in the default value box, which is really stupid and has killed more than one database before). Then the ref integrity will be fine. You shouldn't have to empty out any contacts records. I think I'd integrate the Address field so that the user wouldn't know that I'm dealing with 2 different tables and then have a combo box to select "shared" Contacts to link. Do I add MailingsID (hidden) to Contacts? I cannot really visualise what you are getting at here, except to comment that hidden fields often mean that you are getting into trouble :-) So would I have code (really good at that to) to move the address to the "shared" contact if shared contact is not null? If "moving" records means you are holding the same stuff in separate tables, then you are definitely into the Bad Lands. Then, how do I make sure the "flip-side" contact got linked. (Mary is linked to John, is John linked to Mary?). If Mary's stuff is going to John's address, then that fact does not have to be recorded in John's record at all -- or vice versa. It's easy to run a query that gathers up who is using whose-else's addresses. We are allowed to "cash-in" bucks (earned by volunteering) to the tune of $35 per individual and $45 per family. So say a $75 fee (family) with $22 cashed in for John and $5 for Mary = (math don't fail me now) $48 owed. So $48 will fulfill the requirement for both Mary and John together. So perhaps I do need 2 tables: Payments and ContributesTo? Sounds like yes. All the best Tim F . |
#8
|
|||
|
|||
"Stephm" schreef in bericht ... Thanks for all the help. Steph -----Original Message----- "Stephm" wrote in news:3d3101c5201b : When I added the Mailings table, the Contacts table wasn't happy unless it had a MailingsID -so I deleted the field ;-). Now I'm ready to redo the link- Do I need to empty out all of the test Contacts or can I just have the MailingsID going forward? You don't have to have a MailingID value in a Contacts record -- if it's NULL though, that member won't get a newsletter. Make sure that the Required property is False, and the DefaultValue is blank or NULL (Access unhelpfully puts a zero in the default value box, which is really stupid and has killed more than one database before). Then the ref integrity will be fine. You shouldn't have to empty out any contacts records. I think I'd integrate the Address field so that the user wouldn't know that I'm dealing with 2 different tables and then have a combo box to select "shared" Contacts to link. Do I add MailingsID (hidden) to Contacts? I cannot really visualise what you are getting at here, except to comment that hidden fields often mean that you are getting into trouble :-) So would I have code (really good at that to) to move the address to the "shared" contact if shared contact is not null? If "moving" records means you are holding the same stuff in separate tables, then you are definitely into the Bad Lands. Then, how do I make sure the "flip-side" contact got linked. (Mary is linked to John, is John linked to Mary?). If Mary's stuff is going to John's address, then that fact does not have to be recorded in John's record at all -- or vice versa. It's easy to run a query that gathers up who is using whose-else's addresses. We are allowed to "cash-in" bucks (earned by volunteering) to the tune of $35 per individual and $45 per family. So say a $75 fee (family) with $22 cashed in for John and $5 for Mary = (math don't fail me now) $48 owed. So $48 will fulfill the requirement for both Mary and John together. So perhaps I do need 2 tables: Payments and ContributesTo? Sounds like yes. All the best Tim F . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Design help, please | SillySally | Using Forms | 27 | March 6th, 2005 04:11 AM |
Update - If statement | Dan @BCBS | Running & Setting Up Queries | 13 | December 14th, 2004 06:02 PM |
transpose | john | Using Forms | 1 | November 24th, 2004 06:16 PM |
Automatic filling of fields in table two from table one | Jim Kelly | Database Design | 1 | September 27th, 2004 10:16 PM |
Name not showing ID is | René | Setting Up & Running Reports | 11 | June 29th, 2004 01:40 AM |