I am trying to build a client database for marketing and ordering purposes. I am fining that the further I go in the more problems I am having with the fundamental design, if anyone is willing to take some time to help me sort it out I would appreciate it. I am not going to write it all down now as it may take a few back and forth posts to explain fully (maybe some screen shots of the relationships will be in order?) and I don't want to waste too much of anybodies time.

Hi,
the best thing to do right now would be to post the structure of your db in 1 of 3 ways:

1. Simply list the tables, Columns and datatypes:

tblName
col1 autonumber pk
col2 text
col3tbl2id number fk to tbl2

etc,

2. Create a jpeg of the rlationships window and attach it to a post.

best option would be
3. Zip your database and attach it to a post.

A bit of an explanation on the purpose of the db and basic relationships between entities.

Someone will pipe up and help.

Good luck

Jim

Hi
Well sounds like you haven't done any of the base work and have simply dived into the actual database. Have you done an Entity - Relationship diagram?

If you don't have an E-R diagram then you really need a set of Business Rules and Report requirements.

It's hard to help without one of these two...

@Starmage,
did you use the 'quick reply' window rather than click in the 'Reply' link next to NNotHard's post. If so, then I'm the one that is getting replied to not NNotHard.

Jim

Yeah I did Jim...
Sorry.

No probs, it's just that the OP might not realise that they have had another reply No apologies necessary

Hi I have attached the db as a zip file.

The db will have a few purposes: It is a list of all the clients related my company. These clients fall into 2 catagories, private and company. This split is what is causing me problems.

The purpose of this is to create a mailing list of all the clients for marketing. Each client will be sent an 'advantagecard' which will have a unique number (the cards have been printed with numbers on - simple numeric) This will entitle them to discounts on training. Both companies and private clients will recieve cards and members within a company will use their company card.

So I need a mailing list once all the data is in - which I am not worried about, I am ok with mail merge etc.

The other function wil be to record information and display details of clients when they get in contact to use their cards. I need to be able to search using card number or name to bring up the record, and then have a facility to record calls, qoutes given and orders taken.

Data entry is the final function - to record new client details. This role will be limited to a single person and so it does not need to be as user friendly as the rest.

The crux of my confusion lies with 2 issues: 1. The split between the types of clients. 2 Importing the existing data. The data only needs to be imported once, as once this is done all new contacts/clients will be recorded in the database. I have imported the various list into tables in my database but am struggling to put the data into the tables I have designed to put them in.

Problem 1 is the most important as I think I may need to start again. I have dealt with the problem by splitting the database into two section Company clients and Private clients. If you look at the relationships table it will give you a good idea of the structure I have attempted.
You can see there why I had to split it: A company client has an address and contact details, as does a private client. But a company also has contacts within it that have their own contact details but for whom we don't need an address.

I did put some thought into the structure before I started, but now I am thinking about how I will be able to make it user friendly so that people can search for the client and bring up the correct form.
I have two main forms, both with a similar layout. Each form is realed to the key table(client or private) and has subforms which show data from related tables.

I have yet to create the orders page and table. Of the data imported, ImportedPrivate ImportSuppliesClients have been entered into the correct table, those field not relating to the main table I had to do manually.

Sorry for the long post, but I wanted to give you a good idea of what is happening. Can I keep the existing structure, or do I need to combine all the clients into one table and work from there? Can I get the data I have imported into the existing structure, or do I need to do some manual data entry?

Edited by: ghubbell on Thu Jun 22 11:01:46 EDT 2006.

I have figured out how to import the data by creating a dummy table and then using an append query. Cool

Now it is just the question of how to create an ordering system and whether the fundamental design is flawed!!