Help with Design

[GaryS]

Hello,

I'm trying to help my office manager replace her existing flatfile database
with Access. Currently she uses an old program called Q&A which is all form
based, no tables. Every field is on the form that is used for data entry and
also for searches. The company we work for is a car dealership, and she uses
this database to keep track of sales commissions and sales tax outlay for
each car sale. On her data entry form she has fields which are labled like
this:

401D
402D
403D
424D
Etc, etc

These numbers represent different account numbers for each model that is
sold at the dealership, and she enters the taxable amount of proceeds into
the appropriate one for the model that was sold, the idea being that she
could have a report at the end of the month laid out like a spreadsheet
showing how many dollars went into each account. My first thought was I
would have a seperate table for Accouts and have a combo box on the data
entry form so that she could pick the appropriate account number for each
model and then a seperate textbox on the form for the dollar amount. That
textbox would be bound to the AccountAmount figure on the Sales Tax table
(which would be the main table in this database). The problem is I don't
know using this approach how I could give her a report at the end of the
month the way she wants it. The report would have to be layed out like this:

LastName 401D 402D 403D 404D



Totals $13131 $13331 etc etc

I hope this make sense to someone

 

[mnature]

I am assuming that each account is associated with one model.

tblAccount
AccountID (PK)
AccountName
ModelName

tblSales
SalesID (PK)
AccountID
SalesAmount

In the relationships window, link together AccountID from both tables.
Create a query that includes all the fields, then create a form based on that
query. When asked how to view data, select "by Account" and allow a subform
to be created. This form will allow you to create accounts and models, and
then enter sales amounts for those accounts. Just create a report that
summarizes sales by account numbers.

 

[Jerry Whittle]

One thing that I always ask a client before starting a new database project
is "Have you checked to see if there isn't already a commercial product
available? Check the trade magazines?" Unless you want to learn databases or
your business is so unique that someone hasn't already built one, chances are
it's going to be much cheaper and less painful to buy a product instead of
building one from scratch. I'd be shocked if there wasn't something out there
due to the large numbers of car dealerships.

 

[mnature]

Good point, Jerry.

http://office.microsoft.com/en-us/templates/CT011366681033.aspx

However, if you happen to have Access already, Microsoft has some simple
templates, which are probably overkill for what you need, Gary. I would
recommend looking at Customer Orders (works with Access 2000), or Orders
Management (works with Access 2003). Both of those have some built-in
reports.