My first "real" database

[KimHuff]

I have been playing around with Access for over ten years but I have never really created a true relational database.

I have been wanting to create a database for a non-profit group I am involved with but I just dont know how to begin. I was curious if I could get some pointers on what is the best way to begin and if there is a topic that I should post in that best fits what I am looking for?

To give you a little background... I am running MS Access 2007. I am doing this for a local Star Trek convention. Things we need to track would include customer info (address, phone, email, etc), order info (tickets ordered, badge name, panels that require a sign-up, etc), dealers info, volunteer info, etc. It is crucial that the database will allow us to take certain fields into a mail merge to print badges.

One of the things that has me very confused is how to separate each year while at the same time show an order history. For example, if a person ordered tickets in 2009, there would be a drop down showing the tickets available. Well, in 2010 those same ticket items would not be the same. I could add them to the TicketType, but then it will show all of the previous years when I only want to show 2010. I cant delete the previous years because I want to be able to see the customer's order history.

My biggest concern about doing this is my lack of VBA knowledge. If we had the cash I would just hire someone but I know how expensive it is and to be honest, I am never going to learn if I just keep hiring people to do the work for me.

Any suggestions you could give is greatly appreciated. Locations of where to learn. Suggestions on how to get started. Anything that would help us get on our way.

Thank you in advance.

Kim Huff

 

[PNGBill]

Hi Kim, Some more experienced members will be able to help you over time but just one comment on your list for tickets.

Often this list is a query result rather than the actual table, in which case you can restrict the data to just this year.

 

[KimHuff]

PNG,

I had not thought about doing it that way. It has been so long since I have played with Access and unfortunately, I feel like I am starting all over again with 2007.

I think the part that always stops me dead in my tracks is figuring out how to begin. I know it is crucial to have data normalization but I think that is one of my biggest stumbling blocks. So it is difficult to know how to begin, if you are not sure which data belongs in which table.

I am hoping I can get some help here.

Kim

 

[PNGBill]

Then put aside your ticket issue as this is not related to setting up your tables and normalising them. This is just a simple 2 min query.

On your table structure..

Post main table Names and fields and you will get advice on this.
also, keep the starting database with only a few sample records so you can compact and repair, then post same on the forum for critique.

 

[KimHuff]

Should I remain in this topic (General) or should I move to another location?

For the last several years we have been struggling with doing this in Excel. Not that Excel is a bad program, but there was a great deal of double entry and typos. I know that Access is what we need, but I never have much time to attempt it. We are less than 13 weeks until the convention and we still need to start data entry. Granted I can type fairly fast, but who knows how long it will take me to do this.

Let me know what section I should post these kinds of questions and I will start typing up something. I actually might already have something typed up.. I will have to look.

Thanks again for your advice.

Kim

 

[PNGBill]

Tables is probably the best place to start until you have specific questions regarding Forms, Reports or Queries later.

Also, check the Sample Database section.

 

[KimHuff]

Time to put on my big girl pants

Well, I have been using Access for nearly 10 years now but I have yet to fully build a database. I have made databases but they were very basic that didnt really utilize a true relational database structure. I have had a couple built for me so I understand a great deal of the inner workings of access, but I have never been able to understand how to get started or how data normalization works. Another concern is my limited to no visual basics knowledge.

For the last several years I have wanted to create a database for a non-profit group I am involved with. I have debated hiring someone but I will never learn this stuff unless I actually do it. Unfortunately, this means you will all probably hear a great deal from me over the next several weeks.

Here is what I am trying to accomplish. First, I am using MS Access 2007. The database is to keep track of people that have purchased tickets for a Star Trek Convention. The goal is to keep a record of past attendees, create a mail merge to print convention badges, print reports of people that have not bought tickets for over a year so we can send postcards, email confirmation for order and I am sure there is more.

We have been using an Excel spreadsheet over the last several years but we struggle when it comes to data entry. We also are not able to get out of it what we want and we have to start over every year. Which means we are re-entering the same data EVERY YEAR!!

A few of the fields I can think about include:

FirstName
LastName
Address1
Address2
City/Town
State/County
Zip/Postal Code
Country
Phone Number
Email Address
Customer Type (Staff, Volunteer, Attendee, Media)

Ticket (There are a few options and they change each year)
Extras (T-Shirt, Dinner, Photo Op, Workshop, etc)
Dinner Seating (Need a seating chart to pick from)
Badge Name
Mundane Name
Badge Number (starts at 001 and resets after each convention)
Total Amount
Discount
Discount Reason

Keep in mind that most of the time one person will order several tickets. This is fine but we have to have information for each person buying a ticket. For example, if I buy a ticket for my daughter, we need to gather her badge information (badge number & name, real name, all the ticket details, etc... but we want to know that I am the one that bought the tickets.

I am attaching the spreadsheet we used last year. We have brought back the Dinner with the Stars so that is not on this spreadsheet. I also had a friend write an access database for me and it was very close to what we needed but we had problems getting the information out for the mail merges and it seems to have some debugging errors now. My friend lives in the UK and is no longer coming to the conventions due to University so he is really hard to get in touch with.

I just dont know where to begin but we need to start entering data soon. Any suggestions on how I should start? If you think this is going to take weeks, then maybe we should just use Excel again this year and I can continue to work on the database.

Thank you in advance for any advice.

Kim

 

[Uncle Gizmo]

Re: Time to put on my big girl pants

I am attaching the spreadsheet we used last year

I can't see it?

 

[stopher]

Re: Time to put on my big girl pants

Hi Kim

This looks like a fairly straight forward model so the actual design in Access could anything from a couple of hours to a couple of days or more depending on the experience of the designer and how much is required beyond your brief description e.g. forms, report, vba etc. But a complete noob could easily spend weeks on this.

You say that you already have an Access database. I would have though that this would be a good place to start? Maybe you can upload it?

As Uncle Gizmo said, we can't see your Excel upload.

Chris

 

[KimHuff]

Re: Time to put on my big girl pants

WELL, looks like I shouldn't pull those big girl pants out so soon... sorry, I forgot the attachment.

I am attaching the Excel Spreadsheet we used last year with some fake data in there. I also have attached the Access database a friend created. It doesnt seem to work due to debug errors, but the idea I think is there.

Thank you again for trying to help. If I can get past the beginning stages.. maybe the rest will come to me. I do know that Event Procedures are a big part of a good database and since I dont know VB maybe that means I will never be able to create a detailed database. I just hate to give up. I have taught myself a great deal and would know nothing about computers if I gave up.

Let me know what you all think. It is nearly 7am here in Indianapolis so I am going to try and get some shut eye. Maybe some sleep will help me think better.

Kim

 

[AccessBlaster]

Re: Time to put on my big girl pants

Just a reminder using symbols like Invoice # may cause unexpected results. Also I would close all field names i.e. Badge Name to BadgeName to simplify coding.

Good luck with your project.

 

[KimHuff]

Well, as I stated in the beginning, I just dont know how to start. Here is what I have done already but I dont know how to tie things together. (See attached file)

I have the Attendee (customer) info together and I made a form, but I need to add the ordering aspect of the database and I am stuck.

First, I am assuming I will need a table for the different types of tickets. But this changes every year. How do I create a table that allows for that kind of flexibility. Its not as though I can change the amount each year because that will affect the previous ordered tickets. Plus, If I just put in new ticket options each year, I will have to fumble through a growing list to find the correct year. How do I get passed this? What are my next steps to building an order section that will collect the following information?

Order Number
Ticket Type
Discount
Discount Reason
Mundane Name
Badge Name

And its not just tickets they are buying.. other purchases include...

T-Shirt
Dinner with the Stars
Dinner Seating
Photo Ops
Autograph Tickets
Screening
Panel with Gary Wood

These items are all extra so there is no guarantee someone will purchase this. Plus, each year the extras might change.

I hope someone can figure out how to dumb this down enough so I can figure it out.

Thanks,

Kim

 

[KimHuff]

I am not really sure what level of help to expect from this forum, but I have less than 10 weeks until my event and it's been 16 days since my last post. I do not feel I can do this without assistance so if I am asking too much... does anyone have any other ideas on how I can learn this.

It seems like I need to have a way of entering the year and then that fine tunes what options I have to pick from .. but I dont know how to do that.

Any suggestions?

Thank you...

Kim

 

[PNGBill]

Kim, Some concept ideas.
You would have a table with your items for sale - TblSKU if it was a selling business like a shop.
This table would consist of records of items for sale eg T Shits and would hold the current price of each item, selling unit(each, pair etc)
If your seats are known you could hold these in this table, maybe show the sections ie price options.
I don't know how you would handle the seat numbers.
If you have 600 seats then I guess you could have 600 records each with it's own seat number and area name and price etc.
No big deal as a supermarket may have 100,000 or more sku records.

When you sell a seat or a T shirt this is recorded in another table commonly called a Transaction Table (TblSales, TblTrans etc)
Here is where you record, for posterity, the sales data.
This table would hold the SaleDate, SalePrice, plus other data.
This means that next year, when the price changes in your TblSKU, only the records for this years sales in your TblTrans will have this new price. All previous sales records will show whatever sale price was used at the time.

Trust this assists you in your thinking.
I won't open your database as quite busy and I use access 2000.