Data Modelling for Car Dealership Data - New to Access

[mismag]

Hi,

First of all, sorry for the long message.
I am very new to Access database.
I work for a financial company that deals with Car dealerships data.


The data comes in on a monthly basis and currently we enter the data manually into excel and create reports in excel.
But with excel the problem is we can't store data.

So i am trying to create a database in access, so I can create quarterly and yearly reports as required.

My database contains Dealership information. In each dealership there are 2 types(Based on title) of contacts Finance managers and Sales Managers. For Finance Managers, I enter products(10-15) information like no.of deals per product a finance manager has sold and their corresponding gross amount. For Sales Managers it is only no. of new and used cars and their gross amounts.

Attached are my tables and their relationships and data entry form.
In the form when a title is selected, based on the title another sub-form will popup and i can enter data accordingly. But i need to select the reporting month,dealership name, and full name for every record insertion, which is not feasible all the time and there is room for error while selecting.

Can anybody help?
Hopefully somebody will understand what I am trying to acheive.

TIA,
Mismag

[Micron]

I advise you to step back and fix the tables design before proceeding. I often say that one needs to understand the entity/attribute relationship "theory" before getting started. There is no clear cut approach as what constitutes an entity - it is conducive to the business case. One clear indicator that you have it wrong is if you have to add table fields to accomodate "like" data, and I see that happening here. You should take a look at
normalization and entity relationships first to see if you spot any changes you might want to make. The rest of the links around naming and such are bonus.

Normalization is paramount. Diagramming maybe not so much for some people.

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.ca/...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.ca/...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.ca/...cation-in.html

Important for success:
One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html

[Micron]

After a few minutes and a quick look, here's where I see issues just from a "mechanical" point of view (i.e. based on what I see with no definitive idea as to whether or not it will even support the business):
dealership info - name,address and such; OK. What they sell, no; that's another table.

contact info - first, last names; don't combine anything that can be combined in a query or form. No to dealershipname in that table; you get that by linking to the dealer table by ID. Otherwise, you're repeating information unnecessarily. That is all about the relationship part of design.

MInput - seems you linked productID to ContactID - makes no sense, and if they are autonumbers, definitely a no-no. No spaces in names (as you will/have read in the links I provided). As a rule, don't store calculations or concatenations. If Gross and PenetrationPercent (goals/product) are calculated, then don't unless you have a specific valid reason to do so. If data changes, the calculations must be managed, and there's no need to store in a table what a query or form can calculate with the most up to date data.

ReportingMonth will probably make a poor field to join on, unless it's text type and not date/time type. It seems unusual to me, but I don't have a clear understanding of what this db is supposed to support.

Why ID and GoalID in goals/product?

FullName in dataEntry is (again) a) repetitive and b) calculated/concatenated.

That should be enough to (hopefully) induce a pause in your project. Sorry if that all seems cruel - not intended to be. Primary intent is to prevent problems, not solve them.

[mismag]

Sorry if that all seems cruel - not intended to be. Primary intent is to prevent problems, not solve them.

No worries...thanks for looking into the tables.

As I said I am very new to Access and anything and everything I have done is by googling around.
Primarily I only work on reports using excel.
This is a learning curve for me.

I have a question here. If a field is added to a form that is not tied to any object in the tables, where will the data entered in that field be stored in?

Thanks,
Mismag

[Micron]

Piece of advice - when it comes to Access, forget what you know about Excel; it doesn't jive. The former is row based, the later column based.

field is added to a form that is not tied to any object in the tables

Might as well get the vernacular right too. A control is bound to a table field (not object). If it's not bound, it's really just a control. Control and field is used interchangeably when referring to forms and we have to decipher the connotation when it's being used. If it's not bound and entered data isn't written to the table via code or macro (most of us here don't use macros) then it's lost if you do anything to commit the record, such as move off of the record.

This is a learning curve for me.

You have no idea how long and steep it can be!

[orange]

Micron has offered good advice. I'm going to suggest that you work through this tutorial from RogersAccessLibrary. It will take about 45 minutes to 1 hour. It has a problem description and leads you through a process that will identify tables and attributes. It will help you understand relationships and you will learn by actually going through the process. It does have an answer/solution - a normalized data structure with steps all the way along. It may be the best 45 minutes of database analysis and design you'll encounter. Once you have completed the tutorial, you can apply what you have experienced/learned with your own data.

Good luck with your project.

[mismag]

You have no idea how long and steep it can be!

You are absolutely right Micron...
After working for 2 months, i have come to this point and I am still no where near my goal.

Thanks,
Mismag

[mismag]

Thank you Orange...The tutorial is very helpful.

Thanks,
Mismag

[orange]

Perhaps you could show readers some of the Reports you'll produce from the data you receive. Even some mock ups would help.
Some sample input data along with a description of the who supplies this; what is it exactly; how is it processed/accumulated before you get it; when does it arrive; for how long is it good (best before date sort of thing).
Use the who, what, why, when, how, how much and/or how often to build a description that can provide some context of the business to the reader.
What differentiates Finance from Sales in simple terms? Who uses the outputs of your database? For what purpose(s)?
How often are these reports used? What do they do now --pre having your database? What are the expected benefits of the proposed database? Who will realize the benefits? Are there any plans or measurements to see what benefits actually materialize? Is that important to the project? Who sponsored the project?

Any of the above can be used to get some description/narrative of the business.

Good luck with your project.

See the links in my signature for other articles in different formats that may be helpful in getting your requirements together.

[Micron]

Thank you Orange...The tutorial is very helpful.
Thanks,
Mismag

Aaahh, but you already knew that because it's my 3rd link, right? You did/will follow at least some of them, I hope.