Customer Orders Database

[Lillian Chan]

I am fairly new to Access and am seeking direction as to what topics I
should read more on to design the following database:

At work, we maintain a master list of customers who have ever placed
orders with us. It's basically one column of names and another column
indicating whether or not a customer has placed an order (marked
active) or not (marked inactive) for the current month. Where
customers have greater than one order, the name appears more than
once. Each month, we get an updated list of all the current orders for
that month, which is another column of names. With the new data, we
manually update the active/inactive column, delete names that dropped
orders and add names that added orders. With 3,600 orders every month,
this is quite the task! I've looked at other customer database setups,
but what stumps me is that we don't get any order details, beyond a
list of names. I'm also not sure how to direct Access to update the
data each month.

Eventually, I would like to create a form, which allows someone not
familiar with Access to generate a total of all monthly orders/
customers and keep the data updated with the file we get every month.
We are using Access 2003, but will likely upgrade in the
future...which maybe complicate this project?

Any general direction and/or helpful reading who be greatly
appreciated as I'm a little overwhelmed with all the websites,
tutorials and books I've looked at thus far. Thanks in advance!

 

[Allen Browne]

Since you are only interested in *when* a customer ordered (you don't record
details of *what* they ordered), can I suggest you create 2 tables:

a) Customer table, with fields like this:
CustomerID AutoNumber primary key
CustomerName Text
...

b) Orders table, with fields like this:
OrderID AutoNumber primary key
CustomerID Number relates to Customer table
OrderDate Date/Time when this order was placed.
...

Now create a main form bound to the Customers table, with a subform bound to
the Orders table. Show the subform in Continuous or Datasheet view, so it
displays one order per row.

When an order comes in, you find the customer (if it's an existing one) or
enter a new one. Then in the subform, enter the date of the order on a new
row. Over time, this gives you a simple listing of when a customer ordered.

To see the active customers for any month, just create a query using both
tables, and enter your criteria under the OrderDate field, e.g.:

= #12/1/2008# AND < #1/1/2009#

To see how many orders a customer had in that month, change the query into a
Totals query (big sigma icon on the toolbar/ribbon.) Access adds a Total row
to the query design grid. Choose Group By under CustomerID, and Where under
OrderDate. Then add CustomerID a second time, and choose Count under this
one.

 

[Lillian Chan]

Thank you! That was really helpful, especially the part about the
queries.

I modified the tables a bit so I could import the Excel names file for
each month to update our total numbers. I made FirstName & LastName
columns that are the primary keys for the Customer table. Each month's
Excel file can then be imported into that table without worrying about
double entering anyone. Then, I import the same data, now with an
OrderDate column, into the Orders table and do the queries like you
suggested so I can get the total number of active employees for each
month. I hope that's a legitimate work around b/c it gets me the
numbers I want.

Sincerely,
Grateful Office Worker