I'm trying to use Access 2000 to create a database for a small newspaper operation. I'm creating a database to store classifieds from customers calling in, but I could use some help with certain aspects of the database. This is how I planned on the database functioning:
1. Customer calls in and relays classified information to phone operator.
2. The classified information is entered directly into the database as it's relayed.
3. The database calculates the number of lines and multiplies that by the price per line to give the customer a quote.
4. The classified is stored in the database and later will be pulled out by a layout technician to be placed in the paper.
Here are the items that confuse me:
1. Each customer needs to be assigned a unique account number. I need a way to add a "search function" to the Order Entry form to determine whether or not a customer already exists in the database. If they do, the operator needs to enter a new classified. If not, a new account needs to be set up. What is the best way to accomplish this?
2. Like account numbers, each classified needs a unique identifying number based on when it's entered. Classifieds 012 and 026 won't necessarily be for the same customer. How do I accomplish this?
3. In terms of the quoting based on line numbers, I know approximately how many characters I can get on each line. I need to take this number and multiply it by $1.50 and have the final answer appear in a box in the same form. The problem is, the characters are coming from two different input boxes. (1 is for the "classified subject", which is to appear bold in the paper and 2 is for the "classified body", which appears standard in the paper.) I need it to take the "subject" and add it to the "body" and multiply that sum by the $1.50 for the quote. How the heck do I do this?
4. In terms of the classified "subject" vs. "body"... I need a way to take these two fields and merge them into one classified. To better explain, our currently system involves an operator entering the classifieds into a Word document and manually formatting the classified subject to make it bold. Our layout technician then takes the Word document at the end of the week and copies/pastes the classifieds into the paper. I need the database to merge the individual "subject" and "body" fields into 1 classified and then compile all classifieds for that week into a document that my layout technician can use. Can this be done?
5. When the classified is entered, the date of the entry needs to be recorded. The operator then needs to select the number of weeks for the classified to run. (1 week, 2 weeks, 3 weeks, or 4 weeks.) I want the database to take the current date and add the appropriate number of weeks to it. For example, if I enter a classified on 9/19/04 and the customer wants to run for 2 weeks, I want the database to automatically calculate and output the date 10/3/04 in a new box. This of course would update the quote information to multiply that number by 2, for the number of weeks.
6. If the customer sells the item and they call in to discountinue the running of the classified, I need a way to show that the classified only ran for a certain period of time. Let's say after 1 week, they call back and don't want to run the following week. I need a way to change their run time from 2 weeks to 1 week and update their quoted information. (This is important because it needs to be used for billing)
7. In terms of billing, I need a way at the end of the day, week, month, or whatever, to compile a list of all customers with outstanding bills. I want to be able to create an invoice that will be mailed to the customer that has the correct quoted information on it along with any run dates. Additionally, it would be nice to be able to print ALL invoices based on all outstanding bills.
8. How do I store the backend information in one file and the front end information in another file? I want to be able to modify the interface of the database without having to worry about destroying data.
If there are some tutorials available that might be able to explain these items to me, please give me a hand. I know it's a lot of information that I'm requesting, so I'm grateful for ANY help I receive. Thanks a bunch.
You're taking on quite a job for a first database. If this is really your first database attempt with Access then I highly suggest you try to start with something a little easier and then work your way up to the task at hand once you've gained a little experience.
I understand you most likely require this now...but realisticly, short of someone creating the forms and writing the modules for you, you'll be at this for a lng time and it wil end up riddled with faults.
Start your post with the first item....small steps.
I would do as cyberlynx says. Try building a database that deals with each point individually (where applicable). once you have it running, try combining the databases until you have the functionality that you're looking for.