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.
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.
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?
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.
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.
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:
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
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)
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.
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..."
If it's not practically useful, then it's practically useless.