Help with how to set up access db

[bmccartney]

We run a property preservation company and need a way to track our orders.



We have three types of orders.
New orders - recently acquired
Old orders - have been completed
Pending orders - orders we have received but are either in the field being worked on or for a future date

We receive our bulk orders 300-750 orders at time in one master excel file that is an open orders list. When we submit an order, it falls off the list. New orders are generated AND pending orders also stay on the list.

Each order has its own row. Each order has a unique order number but multiple orders over the course of the year have duplicate addresses.

In the past, I've just used access to sort our completed orders from uncompleted. I had a yes/no column that I would check once an order was completed or assigned. I would import a current excel file that had both new and current orders on it and new order numbers would be added and current order numbers (and all data associated with it) would not be touched.

We would then keep a separate excel sheet with accounting. It worked back when we had 400 orders per month and manually did everything. We are starting our grass season on April 1st and will have 3k-5k orders per month.

Excel doesn't work to track orders because if we pasted all of the open orders we would have duplicates of the pending orders (on the previous open order list and the current open order list) and I haven't been able to find a way to select which duplicates get deleted and I have a 50% chance the order that we've already manipulated gets deleted instead of the new order coming in.

I also need to be able to bulk update 20-30 orders at a time. We assign out 150 orders per day and I need to know which orders go to which of my crews. This makes the most sense to do in excel because i can put crew#1 and drag it down to all of the orders in crew#1 has done and the same with crew#2 and crew#3. I can also put yes in the cell and drag it through 150 orders instead of checking 150 little boxes in access.

I would like to be able to use access to manage all of the orders. Then I could also track payments by order number as well. I don't know if this makes sense to someone who hasn't been dealing with this but if it does make sense and you can help I would greatly appreciate it. Thanks!!

[June7]

You should understand that we are mostly volunteers here to share our experience and knowledge. Your post covers a lot of requirements and is more than can be dealt with in one thread. I am sure all you want can be done. What I see in store for you is a long and complicated effort. You need to analyse your business operations, identify data entities and relationships, design a relational database model to fit, learn Access and VBA, then start development of your app. When you encounter specific issues post question and hopefully someone will have a response to resolve. There are templates available that might meet your needs or at least serve as a learning tool. Take a look at http://office.microsoft.com/en-us/te...001228997.aspx