db planning the foundation

[jtwilber]

I am starting a new project and could use some direction.

I am familiar with FM "tutorial" basics and have built databases in the old DOS days. So I'm not totally new to the basic concepts

The project is for the company I work for. I am a regional sales manager and am trying to develop some tools to manage business better.

We are a very large plumbing company and our database is an old old green screen type program.

I get a weekly excel sheet with the commercial jobs we did that week. About 200 records per week.

Here are the critical fields:

Ticket number : The job number for that one job ( we replaced a faucet at the Dunkin Donuts on 12, Main St, Appleville, PA)

Account number : A number for that one location ( e.g. Dunkin Donuts, 12 Main St, Appleville, PA )

Customer name : The name of that location ( Dunkin Donuts #12 )

Address

Total cost

Company branch Which of our branches did the work

Parent name: The company or individual who owns this dunlin donuts, they may own 10 of them (e.g. Appleville Coffee LTD )

------------------------------------

The customer numbers are always there and accurate ( altho they do duplicate occasionaly)

The Parent Name is hit or miss, sometimes accurate sometimes missing, sometimes wrong

-----------------------------------

I have created a table for: Jobs, Accounts, Parents and a new table for what I call "BroadGroup". It is the widest marketing label for this group regardless of ownership. For example Wendys accounts can have different "parents" such as "Corporate store" or "Smiths Franchise group" etc however the "BroadGroup" for each would be "Wendys"

At the end of the day I want to be able to run month over month with Year to date sales by 1) Broad group 2) Company branch 3) Parent

Step one is to have a way to "clean up" the inbound weekly excel spreadsheets. That process would do things such as:

1) Is this a new account number. If yes we need to add the account to the accounts table as well as its parent and broad group.

2. It is an existing account number, so bring in the correct Parent and Broad group from the accounts table, discard what is there.

It seems like I need a "cleaning" function for the data each week as well as some "maintenance" functions to identify accounts without parents etc. Once parents are identified then I need to be able to group update the tables.

Lots of stuff here… sorry for the scope.

Anybody able to start me in the right direction ? I really appreciate input.

Jonathan