Creating a customer database incorporating HQ and branches.

[CarysW]

OK.....I will outline what I need and what I have and hopefully someone will be able to help me with any aspect of it.

What I need:
I'm creating a database of customers(retailers) for a large retail supplier - some customers have one branch, some customers have more than one, some have hundreds!

My database will consist of a 'back-end' which will be all of my data and a data entry type form which will show all of the information(branch name, address, contact etc etc). This will be facilitated by a switchboard which will also contain buttons for a number of reports.

What I need help with first of all is showing the branches....all of the customers have a 'HQ' listing and then possibly subsequent branches, when shown in the data form I just want it to show the HQ with an option of being able to see all of the branches in a table.

Second of all, and I'm sure this is really simple, I need to set up several users with different permissions. Basically I will be the only one able to add or edit information but several other people will need to access it and export the reports.

What I have at the moment:
A HUGE excel workbook with several hundred sheets! The first sheet shows all customers HQs and branches(i.e. every site where our products are sold) the subsequent sheets are split by each customer(they all have a unique account number).

I really hoping that someone will be able to help me as I'm struggling to get my head round it at the moment.

Thanks in advance.

I forgot to add, I'm using Access 2007.

 

[DCrake]

First of all get yourself a large piece of white paper, a pencil, ruler and a rubber.

Physically draw out the tables you think you need and the relationships between each table. Need help on this then look up Normalisation. Next read up on naming conventions and reserved words. This will help you naming your tables and fields within each table.

When you think you have done that, create the same in access and ask Access to analyse it. When you have reached this point then let us know and we will continue from there.

David

 

[CarysW]

Thanks David. I will have a go at doing this.

 

[Atomic Shrimp]

I've had to deal with the head office/branches thing before - I know of three common approaches:

1. Let them all have an account number and a branch number, and decree that branch '0' (for the sake of argument) is the head office. Branches under that head office have the same account number, but a different branch number (unique within the group with the same account number).
Pro: Easy to set up and understand.
Con: Can be restrictive if you run out of space in the branch number field, and problems such as duplication of branch numbers, and orphan branches, are possible

2. Let them all have a field in which to store a reference (key) linking to their parent account - use the same table twice in a query (self-join) to work with head offices and branches.
Pro: No limitation on the number of branches belonging to a head office. Acount numbers are arbitrary.
Con: Without safeguards, it is possible for a branch to be a head office to more branches, and so on - creating a complex tree of relationships (although this might be desirable)

3. Store all of the accounts in one place and describe their relationships in a separate junction table
Pro: flexible
Con: more complex to work with when designing queries, etc.

One thing I would definitely recommend - whichever method you use, above or otherwise - is to treat all entities similarly -
-So you might have a customer whose business is just one location, but you still might want to give them three different account records - an HQ record describing the billing address and contacts, a branch record describing the dretail address and contacts and a delivery record describing the goods delivery address (which might be in a different street, behind the shop) and contacts that way, you only need store one address per record (I could tell you some horror stories about different ways I've seen it attempted)

 

[CarysW]

Thanks Mike, the idea of giving them all a branch number sounds good. I'm going to have a good crack at this this afternoon!

 

[DCrake]

Remember to plan for future development and increased workloads. If you are going down the Head Office and Branch route think about the following.

I wrote a system for a national company and in the early development stage we always went to their head off for briefings. We did the structure design for the head office and all its Franchisees, and everything was fine, unitl I stumbled on the fact that Yes there was a head office, infact there were 10 head offices within the organisation. It was in fact a three tier structure.

Organisation
Many Head Offices
Many Franchisees

So we actually need a 3 part PK

CG was the Organisation Code
CGH01, CGH02, etc were the Head Office codes
CGH01001,CGH01002,CGH01003,CGH02001,CGH02002,CGH02003 were my franchisee codes

So using this scope it allowed for up to 99 head offices and 999 franchisees per head office.

David

 

[CarysW]

That's a very good point, I will have a look into that!

 

[Atomic Shrimp]

Thanks Mike, the idea of giving them all a branch number sounds good. I'm going to have a good crack at this this afternoon!

A few potential pitfalls to be aware of with this method:

-Make sure your branch numbering scheme is sufficiently extensible - I worked on one system where a three digit text field was allocated (but intended only ever to contain numbers between 000 and 999). Eventually, a customer came along with more than 1000 branches and it was a big problem.

-If you try to get your system to follow the customer's branch numbering system (where they have one), it will cause confusion when they decide to renumber their stores - by all means include a field to store their reference numbers, but don't make your own system dependent on them - implement your own independent numbering scheme

 

[CarysW]

A few potential pitfalls to be aware of with this method:

-Make sure your branch numbering scheme is sufficiently extensible - I worked on one system where a three digit text field was allocated (but intended only ever to contain numbers between 000 and 999). Eventually, a customer came along with more than 1000 branches and it was a big problem.

-If you try to get your system to follow the customer's branch numbering system (where they have one), it will cause confusion when they decide to renumber their stores - by all means include a field to store their reference numbers, but don't make your own system dependent on them - implement your own independent numbering scheme

Most of our customers just have 1 or 2 branches but the ones with many are very changeable so I will definitely implement my own numbering system.

My brain hurts trying to get my head around Access!! I think I shall go and study my Access for Dummies book tonight!

 

[David Eagar]

Persevere (I'm sure you don't spell it like that!) My first database was along the lines of what you are attempting - after a long struggle with good help, got it up & running.

Looking back at it now, with what I have learned from here, it's a dog's breakfast, but if nothing else, it helped me to really learn Access & more importantly, table structures

 

[CarysW]

Thanks for the encouragement David.

Does anyone know if there is a template in any way similar to what I need so I can try to understand it more by seeing it?

 

[DCrake]

SMS

What you want is a feasability report. This is a report that looks at three things

1. 
   [*]Technical
   Can it be done? Does the present technology allow you to reach the objectives or the application?
   [*]Financial
   Can we afford it? Does the cost of the application justify the need for it and how long will it take to repay itself.
   [*]Social
   Will people use it? Many bosses come up with fantastic ideas about improving their workers efficiency and put together a system spec without any consultation with the end users. Then when presented with the "finished article" the users often throw scorn over it as it does not equate to what is being done now, and does not fulfill the proposed objective.

You first need to draw up a system specification, speak to your intended users. Ask them for a "Wish List"... What it does now that you don't like, what you would like it to do that it currently doesn't. Include and thoughts on possible expansions in the future. Think about the envioronment it is going to be used in. Will it be a stand alone system, will it be running on a LAN or a WAN. The number of users. How many records do you expect it to have in 3 years time. Have you got the current infrastructure in place to implement it.

Once you have done all that and you are satisfied that the project is feasable, that when the developer steps in. However, whilst drawing up the system specification it is advisable to include the developer as they may have ideas and examples of the type of work you are looking for, and offer suggestions to work arounds and common practices.

Many software builders usually charge for feasability reports upfront, as in the past companies have been know to get a report and a spec from a software house and tout this round to other developers looking for better offers. However, the initial cost is deducted from the final price.

Its the old adage Fail to prepare and prepare to fail.

David

 

[CarysW]

OK, after tearing my hair out a bit I've thought of what might be a tidier approach, but I'm not sure if it's possible:-
To have a separate section for Branches with a button for each company which would then show a report of all branches under the account number. I know it'll be long winded for me but it'll be tidier for the end user. But is this possible? Do I need to create a new table for each account number and link each button to a report from it or can I put them all in one table and simply ask the report to pick off the right ones?

 

[David Eagar]

If it were me (and aren't you glad you aren't)

I would start with a Companies table, linked one to many with an Offices table (with a check box to indicate Head Office)

As this db will eventually go global, I would include a lookup table of countries that can be selected in the Companies table

So, the primary table (Companies)can be filtered by country and for each Country/Company, the offices can be selected by Head Office, or not

 

[David Eagar]

Persevere (I'm sure you don't spell it like that!) My first database was along the lines of what you are attempting - after a long struggle with good help, got it up & running.

Looking back at it now, with what I have learned from here, it's a dog's breakfast, but if nothing else, it helped me to really learn Access & more importantly, table structures

With this type of database, what I most importantly learnt was that as a supplier / provider, it came as quite a shock that my company was NOT the primary driver of the database. If you are to provide service to the customer (and after all, that is the alleged purpose of the database) THEY come first.

How are they structured? How do you fit into their structure?

Your table setup should become clearer once this has been resolved

 

[CarysW]

The branch information will only be needed for reference and probably quite rarely, it's just something we want to have as it will provide good Customer Service in the long run.

The most important thing will be the HQ, the place were the buyer sits, in order for us to send Price Lists and special offer flyers periodically. The database will really only be used for this but needs to be accurate and clear which is why I think it may be easier to have the branch information in a different place as this will be used for a different purpose.

If that makes sense!!

 

[neileg]

I doesn't make sense. There's no advantage in separating branches and HQ information and lots of disadvantages.

You're also making an assumption that may not be true, i.e. that there is only one buyer and that they are located in the HQ. It's not difficult to imagine a company that has buying authority at its branches, or that has a buyer remote from the HQ.

 

[CarysW]

I doesn't make sense. There's no advantage in separating branches and HQ information and lots of disadvantages.

You're also making an assumption that may not be true, i.e. that there is only one buyer and that they are located in the HQ. It's not difficult to imagine a company that has buying authority at its branches, or that has a buyer remote from the HQ.

Let's say I'm not making assumptions, I work for the company I'm making the DB for, I will be the primary user and I have spent a long time cleansing the data. The reason for separating HQ's from branches is as detailed above, we only want to send information out to the HQ but will have the branch info for reference.

 

[neileg]

It's your database, you do what you want. All I'm doing is pointing out that you may find that you get a customer that doesn't fit with the model you are using so you are designing in a possible future issue.

To select the HQ locations for mailing is simply a matter of using a query to pick them out.

You're getting sound advice from some experienced developers (for free!). You don't have to follow it, but it's worth taking it seriously.

Good luck, whatever you choose to do.

 

[CarysW]

I'm sorry, I didn't mean to upset you.

I have taken on board all of the advice here and it has made my decisions much easier - you must realise I am just a beginner and am mostly self taught - although creating relationships etc etc might be more proffesional, in the long run, for me, and for what the DB will be used for I think the way I have decided will be much tidier. This isn't something I'm taking into lightly and it isn't something I've just started. I know what is needed of the DB and how it will need to be used. In the end the 'customers' will only be seeing a front end so the back end, for me, doesn't need to be pretty, as long as I can understand what's going on.

I do appreciate everyone's help but I don't appreciate being berated for making a different decision. But then, I guess it is just a forum. If someone knows a real problem with what i plan to do then please let me know.