Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2005
    Posts
    4

    Red face Unanswered: How do you make a address database?

    I need to make a database of address for my club to make address labels can any one help please?
    Here is what I would like it to look like:
    First Column - Number that counts up itself (i.e. 1,2,3 etc.)
    Second Column - Last Name.
    Third Column - First Name.
    Fourth Column - Birth Date
    Fifth Column - Street Address
    Sixth Column - City
    Seventh Column - Zip Code
    Eighth Column - Date they signed up
    Ninth Column - phone Number


    I would like to be able to print address labels from this list with only the 2, 3, 5, 6 and 7 column of course, is that possible?
    A couple of other things I was thinking about is having a drop down list for the City and Zip Code column so I don’t have to keep typing the same one over and over. I also would like the CA for the state column to automatically be entered

    If any one can tell me how to set it up I would appreciate it very much or if you have extra time on your hands and feel like helping me out and setting it up and emailing me a copy I would really appreciate that.

    Thanks


    diablopker@yahoo.com

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Use a mail merge with MS Word
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Oct 2003
    Posts
    1,091
    Welcome to the Board!

    For the dropdown list, how many cities and/or zipcodes are there? You may be able to set up a lookup table so that all you need is the zipcode and the city/state will be populated autamatically.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  4. #4
    Join Date
    Jan 2005
    Posts
    4

    Question I dont know how may cities I would need?

    Hi again,
    Im not sure how many cities I would need. Would it be possible to enter as I go? This is for my clubs mailing list, we just enter them as they signup.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...and the reason you aren't using Access is....?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2004
    Posts
    533
    MS Access is a great tool for databases but there's Nothing wrong with using MS Excel for this either. I kept a database of a few hundred members for a bicycle club a few years back and used Excel. It worked great. An advantage of MS Access, if you have multiple people editing the database, you can lock down the fields and apply rules to help ensure correct user entry. There's more work upfront to design the database in Access.

    Pretty simple to setup in Excel. Open a new workbook, on the first row of a blank worksheet type a name for each field in your list across the top. Then enter a record for each member, one record per row. It's important to keep only one record per row and avoid merging cells or skipping any rows. Save the workbook with a descriptive name. It is good to save the workbook often and after updating save a backup copy. I also added a worksheet for Inactive members / members that did not renew. The inactive members had the same header names so I could paste from the active members sheet, then delete the entry from the active list.

    Excel will auto-complete entries so just typing the first few letters of the city for example will fill in the city after you have added it in the column once already. you can also right click and select "pick from list" to get a list box to select from entries that have been previously entered.

    IMO the first column with the sequence number is not necessary. You can see the number of the row on the side. you may like to have a column for memberID or number and assign a number to the each member. I used something like the first 3 characters of the last name and first initial of the firstname and a index number to make it unique. Make sure if you have an ID for memebers that it is unique to each member and does not change once assigned.

    Once you have your list filled in the Mail Merge to MS Word is a great way to get your print out, like Teddy suggested. In Word select 'Mail Merge...' from the 'Tools->Letters and Mailings' to get a wizard for setting this up. It allows you to select fields to print.

    There's lots of ways to make your index. This is the formula I used to create the member number used as the member index. Where column E is Lastname, D is FirstName, N is Date. You'd have to edit to fit your columns and row.

    Formula for making an index/ID based on 3 chars of lastname, first initial and a numeric value of the membership date.
    =UPPER(LEFT(E145,3)&LEFT(D145)&VALUE(N145))

    /
    Last edited by savbill; 01-20-05 at 18:17.
    ~

    Bill

  7. #7
    Join Date
    Jan 2005
    Posts
    4

    Question I found a templet - Just need a little help with it

    I found a template on Microsoft’s website at:
    http://office.microsoft.com/en-us/te...CT011366681033

    I am looking at the Members table in Design View. How do I change the Data Type (AutoNumber) next to the Field Name (MemberID) to use the Bill’s formula from the previous post?
    =UPPER(LEFT(E145,3)&LEFT(D145)&VALUE(N145))

    Instead of the AutoNumber? And do I use the 145 numbers too? I know I have to change the E, D & N to my table. How do I know what letters I need to use, I don’t see any in access? Only see names above each column.

    Thanks for those of you who ARE helping

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "MS Access is a great tool for databases but there's Nothing wrong with using MS Excel for this either."

    Dear Lord........

    You want an Address DATABASE? Then use a DATABASE product. Excel is no more a database than is a stack of 3x5 cards.

    MS Office templates for Access address databases:
    http://office.microsoft.com/en-us/re...dress+database
    Last edited by blindman; 01-21-05 at 18:18.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Jan 2005
    Posts
    4

    Every board has one.... WHY?

    Blindman... your not helping much. I already said I found that on microsoft website. Just because you know how to use this stuff doesnt mean you have to rub it in my face. I'm looking for some one to help me, not rub my nose in it.

  10. #10
    Join Date
    Feb 2004
    Posts
    533
    The MS Sample Members DB is excellent to use for your purpose. Access does not use Cells, Columns and Rows exactly the same as Excel so the example formula will not work as is. The MemberID in the Access DB uses an auto number which is a Primary Key and an Indexed Field. You *can* change this... *But*... it requires some major Access brain surgury to do so on a database already setup with an auto number primary key. If you just like to display member codes based on the LastName, FirstInitial, and Date Serial, the easiest way would be to add an unbound field to your Members Form, with a formula to show the member code.

    1. Add a new Text Field (Copy the existing 'MemberID' Field and Paste)
    2. Right Click, Select Properties, Data Tab
    3. Add the following formula to the 'Control Source' item
    =UCase(Left([LastName],3) & Left([FirstName],1) & DateDiff("d","1/1/1900",[DateJoined]))

    Note: Access does not have the 'Value' or 'DateValue' functions available in Excel. A similar Date Value can be produced with the DatDiff function using 1/1/1900 as the first date.

    For some reason there is a 3 day delta between Excel's 'Value' function and 'DateDiff' using the same date. I'm not sure why. The idea behind using the date value is to produce a unique Member ID for each member so the difference does not matter.

    FYI: if you REALLY want to change the primary key to use a text format generated key, these are the steps...

    1. Open the Database Relationships View
    2. Remove Relationship joins between the Members, Payments, and CommitteeMembers Tables.
    3. Change the data type in the tables design view to Text format in the Members, Payments, and CommitteeMembers Tables.
    4. Return to the Relationships view and re-add the relationsips to the prementioned tables, check cascade updates and deletes in the join properties.
    5. Enter Code to genereate primary keys, like:
    Code:
    Private Sub DateJoined_AfterUpdate()
    Dim strMemCode As String
       Dim bReqFields As Boolean
       
       bReqFields = (Not IsNull([FirstName]) And Not IsNull([LastName]) And Not IsNull([DateJoined]))
       If IsNull(Me.MemberID) Then
          If bReqFields Then
             strMemCode = GenMemberCode()
             Me.MemberID = strMemCode
          End If
       End If
    End Sub
    
    Private Function GenMemberCode()
       Dim lName As String
       Dim fName As String
       Dim strDateCode As String
       lName = Left(Me.LastName, 3)
       fName = Left(Me.FirstName, 1)
       strDateCode = DateDiff("d", "1/1/1900", Me.DateJoined)
       GenMemberCode = UCase(lName & fName & strDateCode)
    End Function
    6. Debug, Debug, Debug



    /
    ~

    Bill

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'm not trying to rub your face in anything. I'm trying to give you the benefit of more than 10 years experience working with databases. Access is not that difficult to learn, and the time you invest in it will be amply repaid.

    I can imagine a post on www.handymanforums.com:

    Poster: "Hey all. How can I keep my screws from falling out after I hammer them into the wall?"
    Responder: "Don't use a hammer. Use a screwdriver."
    Poster: "I don't know how to use a screwdriver. I only know how to use a hammer."
    Responder: "Then learn how to use a screwdriver."
    Poster: "Listen jackass, are you going to show me how to hammer in screws or not?"
    Responder: "Outa here..."


    Outa here...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •