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
|
|||
|
|||
Advice Needed on Designing a database
Hello,
I'm trying to create a database for my church to store all the information of each member: Lname, Fname, Age, Gender, Phone, Address, Email, ... My objective for this is to be able to keep a record of each member and their pledge amount. Also, avoid any members being entered twice (I cannot use Social Security numbers). What would be your suggestion? Is there an existing template or templates database that I can use? Thank you in advance. John |
#2
|
|||
|
|||
Advice Needed on Designing a database
well, if the data is really that basic... for instance, do you need to store
just the amount pledged to give - and *not* the amount actually given each week, month, year, whatever? do you need only a mailing address for each member - or a physical address too? what if more than one member lives at the same address, is it okay to list each separately with the address essentially duplicated - or are you planning to use the database to generate a mailing list, and don't want multiple mailings going to the same address? and phone numbers / email addresses, are you sure you want to be able to store only one of each, for each member - what about home phones, work phones, cell phones, home email, work email? as you can see, even seemingly simple data requires considerable thought before creating a database. the only concrete suggestion i can give you, based on the information in your post, is this: don't store a member's age - unless you want to be constantly updating it! age is by definition a calculated value, based on the time passed from date of birth to the present (or any chosen date); as such it should not be stored as hard data in a table. store each member's date of birth instead; you can calculate current age whenever you need it, in a query, form, or report. my only other suggestion is that you don't try to make the database itself prevent "duplicate" member entries. instead suggest you use code on a data entry form to check the first and last names and DOB of each new record against existing records - and *warn* the user when it finds a match. then leave it up to the user to decide if the new record should be added or not. that will give you some flexibility just in case you actually have two John Smiths born on the same date. if you have a very large membership, you can add a middle-initial field, and include that in the data entry check, which will provide even greater distinction between persons. hth "moving the database" wrote in message ... Hello, I'm trying to create a database for my church to store all the information of each member: Lname, Fname, Age, Gender, Phone, Address, Email, ... My objective for this is to be able to keep a record of each member and their pledge amount. Also, avoid any members being entered twice (I cannot use Social Security numbers). What would be your suggestion? Is there an existing template or templates database that I can use? Thank you in advance. John |
#3
|
|||
|
|||
Advice Needed on Designing a database
Hi Tina,
You mentioned that there is a way to write a code to "Warn" the user who is using the form when entering the data if there is any identical records already exists. How can I do that? Can you please explain it in detail so I can use this feature? Thanks. John "moving the database" wrote: Hello, I'm trying to create a database for my church to store all the information of each member: Lname, Fname, Age, Gender, Phone, Address, Email, ... My objective for this is to be able to keep a record of each member and their pledge amount. Also, avoid any members being entered twice (I cannot use Social Security numbers). What would be your suggestion? Is there an existing template or templates database that I can use? Thank you in advance. John |
#4
|
|||
|
|||
Advice Needed on Designing a database
there are several ways you could set this up. since the user may in fact
decide to add a "duplicate" record, having decided that it is not actually a duplicate, i'd favor opening a second form, read-only, which shows the suspect record(s). here's how i set up my test: a data entry form, i called it Form10, with the following code on the form's BeforeUpdate event, as Private Sub Form_BeforeUpdate(Cancel As Integer) On Error Resume Next Me.Tag = 0 DoCmd.OpenForm "Form10A", , , _ "aField= '" & Me!Text0 & "'", acFormReadOnly, acDialog If Me.Tag Then Cancel = True Me.Undo End If End Sub the WHERE clause controls what records are included in the "review" form (i called it Form10A), so you set up the WHERE clause to pull records that match the fields which identify a possible duplicate. make the form read-only so records can't be changed in it, and finally set the mode as Dialog, so the calling code pauses while the review form is open. in the review form, add two command buttons; i called them cmdAdd and cmdCancel. and add some kind of text next to the buttons to guide the user, such as "Do you want to save the new/edited record, or cancel the changes?". then add code to the command buttons, as Private Sub cmdAdd_Click() Forms("Form10").Tag = 0 DoCmd.Close , , acSaveNo DoCmd.SelectObject acForm, "Form10" End Sub Private Sub cmdCancel_Click() Forms("Form10").Tag = -1 DoCmd.Close , , acSaveNo DoCmd.SelectObject acForm, "Form10" End Sub the command button sets the value of the Tag property in the first form (Form10). and when the second form closes, the code in the first form resumes executing, reads the value of Tag, and cancels the record update where appropriate. note that this code runs when a new record is added AND when an existing record is edited - which is perhaps a good way of identifying duplicates that escaped the net first time around due to spelling typos, which are now being corrected. that said, you have to give some thought to how tightly you want the comparison for duplicate records to be. one consideration is the condition of the raw data as the user will see it: how likely is it that there will be different spellings of names, for instance, or J Smith and James Smith and Jim Smith. how likely is it the user will make typos, entering Jom instead of Jim, for example. depending on the size of your data table, you may want a broader comparison to return more possible matches for the user to review. remember the use of wildcards, they work the same in the WHERE clause as they do in a query's criteria. none of this will be perfect, because of the many possible parameters you may use to check for matching records, and because of the "user error" factor. but i think having a *person* make the decision is still, in many cases, a better option than being dependent on the rigid parameters of a unique index in the data table itself. and btw, John, i'd like to comment on your statement that you "cannot use Social Security numbers", for others who may read this thread. that's good; in my opinion, especially sensitive personal data that could be used, among other things, for identity theft, doesn't belong in an Access database. the security features are too-easily circumvented to make it a secure data storage file. i've personally never used any database software besides Access; but from my reading in these newsgroups and elsewhere, i've gathered that a server-based solution, such as SQL Server or Oracle, is a better option for that sort of data. hth "John" wrote in message ... Hi Tina, You mentioned that there is a way to write a code to "Warn" the user who is using the form when entering the data if there is any identical records already exists. How can I do that? Can you please explain it in detail so I can use this feature? Thanks. John "moving the database" wrote: Hello, I'm trying to create a database for my church to store all the information of each member: Lname, Fname, Age, Gender, Phone, Address, Email, ... My objective for this is to be able to keep a record of each member and their pledge amount. Also, avoid any members being entered twice (I cannot use Social Security numbers). What would be your suggestion? Is there an existing template or templates database that I can use? Thank you in advance. John |
#5
|
|||
|
|||
Advice Needed on Designing a database
Hi Tina,
Is there any way I can get a little bit more help on this. I like the BeforeUpdate event precedure, but I don't know how to start this. All I want to do is to check the following fields: Fname, Lname, Address, Phone, and to warn me with a message if I enter a record record for the second time that matchs all the above fields. I'm not an expert in Access, but I'm a hard worker and I firmly beleive that I can do this. Therefore, lets assume that my table is called "Members", and the above fields are in that "Members" field. Where do I start from here? Thank you. John "tina" wrote: well, if the data is really that basic... for instance, do you need to store just the amount pledged to give - and *not* the amount actually given each week, month, year, whatever? do you need only a mailing address for each member - or a physical address too? what if more than one member lives at the same address, is it okay to list each separately with the address essentially duplicated - or are you planning to use the database to generate a mailing list, and don't want multiple mailings going to the same address? and phone numbers / email addresses, are you sure you want to be able to store only one of each, for each member - what about home phones, work phones, cell phones, home email, work email? as you can see, even seemingly simple data requires considerable thought before creating a database. the only concrete suggestion i can give you, based on the information in your post, is this: don't store a member's age - unless you want to be constantly updating it! age is by definition a calculated value, based on the time passed from date of birth to the present (or any chosen date); as such it should not be stored as hard data in a table. store each member's date of birth instead; you can calculate current age whenever you need it, in a query, form, or report. my only other suggestion is that you don't try to make the database itself prevent "duplicate" member entries. instead suggest you use code on a data entry form to check the first and last names and DOB of each new record against existing records - and *warn* the user when it finds a match. then leave it up to the user to decide if the new record should be added or not. that will give you some flexibility just in case you actually have two John Smiths born on the same date. if you have a very large membership, you can add a middle-initial field, and include that in the data entry check, which will provide even greater distinction between persons. hth "moving the database" wrote in message ... Hello, I'm trying to create a database for my church to store all the information of each member: Lname, Fname, Age, Gender, Phone, Address, Email, ... My objective for this is to be able to keep a record of each member and their pledge amount. Also, avoid any members being entered twice (I cannot use Social Security numbers). What would be your suggestion? Is there an existing template or templates database that I can use? Thank you in advance. John |
#6
|
|||
|
|||
Advice Needed on Designing a database
i firmly believe you can do it, too, so let's get started.
let's back up a bit and start with the members table. you list your fields as Fname Lname Address Phone okay, except for "Address". any address has several components, which each belong in their own field - following normalization rules which call for "atomic" data in each field. recommend you substitute the following for Address: Street1 Street2 City State (include this field even if all your members are currently in the same state) Zip Country (include this field only if appropriate to your needs) btw, make sure that the PhoneNumber field is a Text data type, not a Number data type. also recommend you have a tblStates listing each state abbreviation and full name, as tblStates StateCode (AK, AL, AZ, etc, one code per record.) (primary key) StateName and further recommend a tblCities, as tblCities CityID (autonumber, primary key) CityName having city and state tables will cut down on data entry time, and help prevent typos in those two fields in tblMembers, at least - and every little bit helps. now, you need a form for data entry. create a form, and set its' RecordSource property to tblMembers. add controls for the table fields you want to see in the form; use a combobox control for the City field, and set its' RowSource to tblCities; ditto that for the State field, tblStates. if most or all of your members are currently in the same state, you can set the DefaultValue of the state combobox to that state code, to save data entry keystrokes. ditto that for the city combobox. name the form, frmMembers, and save and close it. now copy it (Ctrl+c, then Ctrl+v). name the copy frmMembersReview. open frmMembersReview in Design view, and add the command buttons i described in my previous post, add the code i posted, and replace the name "Form10" in that code with the name "frmMembers". save and close. open frmMembers in Design view, in the form's BeforeUpdate event, add the code i posted previously, replacing the DoCmd.OpenForms lines with the following, as DoCmd.OpenForm "Form10A", , , _ "Fname = '" & Me!Fname & "' And " _ "Lname = '" & Me!Lname & "' And " _ "Address1 = '" & Me!Address1 & "' And " _ "Address2 = '" & Me!Address2 & "' And " _ "City = " & Me!City & " And " _ "State = '" & Me!State & "' And " _ "Phone = '" & Me!Phone & "'" _ , acFormReadOnly, acDialog read up on the OpenForm action in VBA Help, so you understand how the arguments work. the above WHERE clause looks for exact matches only in all those fields. if you want more "fuzzy" matching, employ the use of wildcards and the Like operator in place of the "=" sign. read up on wild cards in Access Help so you'll understand how to use them. hth "John" wrote in message ... Hi Tina, Is there any way I can get a little bit more help on this. I like the BeforeUpdate event precedure, but I don't know how to start this. All I want to do is to check the following fields: Fname, Lname, Address, Phone, and to warn me with a message if I enter a record record for the second time that matchs all the above fields. I'm not an expert in Access, but I'm a hard worker and I firmly beleive that I can do this. Therefore, lets assume that my table is called "Members", and the above fields are in that "Members" field. Where do I start from here? Thank you. John "tina" wrote: well, if the data is really that basic... for instance, do you need to store just the amount pledged to give - and *not* the amount actually given each week, month, year, whatever? do you need only a mailing address for each member - or a physical address too? what if more than one member lives at the same address, is it okay to list each separately with the address essentially duplicated - or are you planning to use the database to generate a mailing list, and don't want multiple mailings going to the same address? and phone numbers / email addresses, are you sure you want to be able to store only one of each, for each member - what about home phones, work phones, cell phones, home email, work email? as you can see, even seemingly simple data requires considerable thought before creating a database. the only concrete suggestion i can give you, based on the information in your post, is this: don't store a member's age - unless you want to be constantly updating it! age is by definition a calculated value, based on the time passed from date of birth to the present (or any chosen date); as such it should not be stored as hard data in a table. store each member's date of birth instead; you can calculate current age whenever you need it, in a query, form, or report. my only other suggestion is that you don't try to make the database itself prevent "duplicate" member entries. instead suggest you use code on a data entry form to check the first and last names and DOB of each new record against existing records - and *warn* the user when it finds a match. then leave it up to the user to decide if the new record should be added or not. that will give you some flexibility just in case you actually have two John Smiths born on the same date. if you have a very large membership, you can add a middle-initial field, and include that in the data entry check, which will provide even greater distinction between persons. hth "moving the database" wrote in message ... Hello, I'm trying to create a database for my church to store all the information of each member: Lname, Fname, Age, Gender, Phone, Address, Email, ... My objective for this is to be able to keep a record of each member and their pledge amount. Also, avoid any members being entered twice (I cannot use Social Security numbers). What would be your suggestion? Is there an existing template or templates database that I can use? Thank you in advance. John |
#7
|
|||
|
|||
Advice Needed on Designing a database
Hi Tina,
I have a few questions but I'm not sure if you can help me over the phone. Can I give you my phone number? Some of my questions a 1. For the tbleState = I created a StateCode field (AK, AL, AZ, ...) , and StateName field(Arkansas, Alaska, Arizona, ...) I assume that I want to make the StateName primary key? Also, when I add this field on my form as a combobox, how do I make sure that the default value is null or blank when I'm entering a new record? currently, it pick up the last state I selected for the previous record. There are a couple of other questions I have but it would take a long time for me to type them. Can I email you my phone, or can you email your phone so we can go over these questions? I really appreciate it. My email is herat1 at hotmail dot com. Thanks. John "tina" wrote: i firmly believe you can do it, too, so let's get started. let's back up a bit and start with the members table. you list your fields as Fname Lname Address Phone okay, except for "Address". any address has several components, which each belong in their own field - following normalization rules which call for "atomic" data in each field. recommend you substitute the following for Address: Street1 Street2 City State (include this field even if all your members are currently in the same state) Zip Country (include this field only if appropriate to your needs) btw, make sure that the PhoneNumber field is a Text data type, not a Number data type. also recommend you have a tblStates listing each state abbreviation and full name, as tblStates StateCode (AK, AL, AZ, etc, one code per record.) (primary key) StateName and further recommend a tblCities, as tblCities CityID (autonumber, primary key) CityName having city and state tables will cut down on data entry time, and help prevent typos in those two fields in tblMembers, at least - and every little bit helps. now, you need a form for data entry. create a form, and set its' RecordSource property to tblMembers. add controls for the table fields you want to see in the form; use a combobox control for the City field, and set its' RowSource to tblCities; ditto that for the State field, tblStates. if most or all of your members are currently in the same state, you can set the DefaultValue of the state combobox to that state code, to save data entry keystrokes. ditto that for the city combobox. name the form, frmMembers, and save and close it. now copy it (Ctrl+c, then Ctrl+v). name the copy frmMembersReview. open frmMembersReview in Design view, and add the command buttons i described in my previous post, add the code i posted, and replace the name "Form10" in that code with the name "frmMembers". save and close. open frmMembers in Design view, in the form's BeforeUpdate event, add the code i posted previously, replacing the DoCmd.OpenForms lines with the following, as DoCmd.OpenForm "Form10A", , , _ "Fname = '" & Me!Fname & "' And " _ "Lname = '" & Me!Lname & "' And " _ "Address1 = '" & Me!Address1 & "' And " _ "Address2 = '" & Me!Address2 & "' And " _ "City = " & Me!City & " And " _ "State = '" & Me!State & "' And " _ "Phone = '" & Me!Phone & "'" _ , acFormReadOnly, acDialog read up on the OpenForm action in VBA Help, so you understand how the arguments work. the above WHERE clause looks for exact matches only in all those fields. if you want more "fuzzy" matching, employ the use of wildcards and the Like operator in place of the "=" sign. read up on wild cards in Access Help so you'll understand how to use them. hth "John" wrote in message ... Hi Tina, Is there any way I can get a little bit more help on this. I like the BeforeUpdate event precedure, but I don't know how to start this. All I want to do is to check the following fields: Fname, Lname, Address, Phone, and to warn me with a message if I enter a record record for the second time that matchs all the above fields. I'm not an expert in Access, but I'm a hard worker and I firmly beleive that I can do this. Therefore, lets assume that my table is called "Members", and the above fields are in that "Members" field. Where do I start from here? Thank you. John "tina" wrote: well, if the data is really that basic... for instance, do you need to store just the amount pledged to give - and *not* the amount actually given each week, month, year, whatever? do you need only a mailing address for each member - or a physical address too? what if more than one member lives at the same address, is it okay to list each separately with the address essentially duplicated - or are you planning to use the database to generate a mailing list, and don't want multiple mailings going to the same address? and phone numbers / email addresses, are you sure you want to be able to store only one of each, for each member - what about home phones, work phones, cell phones, home email, work email? as you can see, even seemingly simple data requires considerable thought before creating a database. the only concrete suggestion i can give you, based on the information in your post, is this: don't store a member's age - unless you want to be constantly updating it! age is by definition a calculated value, based on the time passed from date of birth to the present (or any chosen date); as such it should not be stored as hard data in a table. store each member's date of birth instead; you can calculate current age whenever you need it, in a query, form, or report. my only other suggestion is that you don't try to make the database itself prevent "duplicate" member entries. instead suggest you use code on a data entry form to check the first and last names and DOB of each new record against existing records - and *warn* the user when it finds a match. then leave it up to the user to decide if the new record should be added or not. that will give you some flexibility just in case you actually have two John Smiths born on the same date. if you have a very large membership, you can add a middle-initial field, and include that in the data entry check, which will provide even greater distinction between persons. hth "moving the database" wrote in message ... Hello, I'm trying to create a database for my church to store all the information of each member: Lname, Fname, Age, Gender, Phone, Address, Email, ... My objective for this is to be able to keep a record of each member and their pledge amount. Also, avoid any members being entered twice (I cannot use Social Security numbers). What would be your suggestion? Is there an existing template or templates database that I can use? Thank you in advance. John |
#8
|
|||
|
|||
Advice Needed on Designing a database
i've been out of town, John, just now reading your last post to this thread.
see my comments below. "John" wrote in message ... Hi Tina, I have a few questions but I'm not sure if you can help me over the phone. Can I give you my phone number? no, let's keep it in the newsgroups, where the discussion may benefit others, and/or the discussion may benefit from others' input. Some of my questions a 1. For the tbleState = I created a StateCode field (AK, AL, AZ, ...) , and StateName field(Arkansas, Alaska, Arizona, ...) I assume that I want to make the StateName primary key? i'd make the StateCode field the primary key, as i said in my previous post when describing the table. Also, when I add this field on my form as a combobox, how do I make sure that the default value is null or blank when I'm entering a new record? 1) make sure the combobox's DefaultValue property is blank, and 2) make sure the combobox's ControlSource property is set to the name of the "state" field in the form's underlying RecordSource. hth currently, it pick up the last state I selected for the previous record. There are a couple of other questions I have but it would take a long time for me to type them. Can I email you my phone, or can you email your phone so we can go over these questions? I really appreciate it. My email is herat1 at hotmail dot com. Thanks. John "tina" wrote: i firmly believe you can do it, too, so let's get started. let's back up a bit and start with the members table. you list your fields as Fname Lname Address Phone okay, except for "Address". any address has several components, which each belong in their own field - following normalization rules which call for "atomic" data in each field. recommend you substitute the following for Address: Street1 Street2 City State (include this field even if all your members are currently in the same state) Zip Country (include this field only if appropriate to your needs) btw, make sure that the PhoneNumber field is a Text data type, not a Number data type. also recommend you have a tblStates listing each state abbreviation and full name, as tblStates StateCode (AK, AL, AZ, etc, one code per record.) (primary key) StateName and further recommend a tblCities, as tblCities CityID (autonumber, primary key) CityName having city and state tables will cut down on data entry time, and help prevent typos in those two fields in tblMembers, at least - and every little bit helps. now, you need a form for data entry. create a form, and set its' RecordSource property to tblMembers. add controls for the table fields you want to see in the form; use a combobox control for the City field, and set its' RowSource to tblCities; ditto that for the State field, tblStates. if most or all of your members are currently in the same state, you can set the DefaultValue of the state combobox to that state code, to save data entry keystrokes. ditto that for the city combobox. name the form, frmMembers, and save and close it. now copy it (Ctrl+c, then Ctrl+v). name the copy frmMembersReview. open frmMembersReview in Design view, and add the command buttons i described in my previous post, add the code i posted, and replace the name "Form10" in that code with the name "frmMembers". save and close. open frmMembers in Design view, in the form's BeforeUpdate event, add the code i posted previously, replacing the DoCmd.OpenForms lines with the following, as DoCmd.OpenForm "Form10A", , , _ "Fname = '" & Me!Fname & "' And " _ "Lname = '" & Me!Lname & "' And " _ "Address1 = '" & Me!Address1 & "' And " _ "Address2 = '" & Me!Address2 & "' And " _ "City = " & Me!City & " And " _ "State = '" & Me!State & "' And " _ "Phone = '" & Me!Phone & "'" _ , acFormReadOnly, acDialog read up on the OpenForm action in VBA Help, so you understand how the arguments work. the above WHERE clause looks for exact matches only in all those fields. if you want more "fuzzy" matching, employ the use of wildcards and the Like operator in place of the "=" sign. read up on wild cards in Access Help so you'll understand how to use them. hth "John" wrote in message ... Hi Tina, Is there any way I can get a little bit more help on this. I like the BeforeUpdate event precedure, but I don't know how to start this. All I want to do is to check the following fields: Fname, Lname, Address, Phone, and to warn me with a message if I enter a record record for the second time that matchs all the above fields. I'm not an expert in Access, but I'm a hard worker and I firmly beleive that I can do this. Therefore, lets assume that my table is called "Members", and the above fields are in that "Members" field. Where do I start from here? Thank you. John "tina" wrote: well, if the data is really that basic... for instance, do you need to store just the amount pledged to give - and *not* the amount actually given each week, month, year, whatever? do you need only a mailing address for each member - or a physical address too? what if more than one member lives at the same address, is it okay to list each separately with the address essentially duplicated - or are you planning to use the database to generate a mailing list, and don't want multiple mailings going to the same address? and phone numbers / email addresses, are you sure you want to be able to store only one of each, for each member - what about home phones, work phones, cell phones, home email, work email? as you can see, even seemingly simple data requires considerable thought before creating a database. the only concrete suggestion i can give you, based on the information in your post, is this: don't store a member's age - unless you want to be constantly updating it! age is by definition a calculated value, based on the time passed from date of birth to the present (or any chosen date); as such it should not be stored as hard data in a table. store each member's date of birth instead; you can calculate current age whenever you need it, in a query, form, or report. my only other suggestion is that you don't try to make the database itself prevent "duplicate" member entries. instead suggest you use code on a data entry form to check the first and last names and DOB of each new record against existing records - and *warn* the user when it finds a match. then leave it up to the user to decide if the new record should be added or not. that will give you some flexibility just in case you actually have two John Smiths born on the same date. if you have a very large membership, you can add a middle-initial field, and include that in the data entry check, which will provide even greater distinction between persons. hth "moving the database" wrote in message ... Hello, I'm trying to create a database for my church to store all the information of each member: Lname, Fname, Age, Gender, Phone, Address, Email, .... My objective for this is to be able to keep a record of each member and their pledge amount. Also, avoid any members being entered twice (I cannot use Social Security numbers). What would be your suggestion? Is there an existing template or templates database that I can use? Thank you in advance. John |
Thread Tools | |
Display Modes | |
|
|