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!

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Lillian Chan" <(E-Mail Removed)> wrote in message
news:57198784-a296-44ee-a665-(E-Mail Removed)...
> 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!

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

On Dec 16, 6:53*pm, "Allen Browne" <AllenBro...@SeeSig.Invalid> wrote:
> Since you are only interested in *when* acustomerordered (you don't record
> details of *what* they ordered), can I suggest you create 2 tables:
>
> a)Customertable, with fields like this:
> * * CustomerID * *AutoNumber * *primary key
> * * CustomerName * *Text
> * * ...
>
> b) Orders table, with fields like this:
> * * OrderID * * * *AutoNumber * *primary key
> * * CustomerID * Number * * * * *relates toCustomertable
> * * OrderDate * *Date/Time * * * when this order was placed..
> * * ...
>
> Now create a main form bound to the Customers table, with a subform boundto
> 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 thecustomer(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 acustomerordered.
>
> 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 acustomerhad 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.
>
> --
> Allen Browne - Microsoft MVP. *Perth, Western Australia
> Tips for Access users -http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Lillian Chan" <californiu...@gmail.com> wrote in message
>
> news:57198784-a296-44ee-a665-(E-Mail Removed)...
>
>
>
> > I am fairly new to Access and am seeking direction as to what topics I
> > should read more on to design the followingdatabase:
>
> > 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 acustomerhas 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 othercustomerdatabasesetups,
> > 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!- Hide quoted text -
>
> - Show quoted text -