A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Help with design



 
 
Thread Tools Display Modes
  #1  
Old March 8th, 2005, 07:13 PM
Ronnie
external usenet poster
 
Posts: n/a
Default Help with design

I have a database which I have constructed which has many tables. It needs
redesigning as I don’t think it’s an effective database design, as when
trying to achieve tasks its always giving me trouble. The db was originally
designed with jus 9 tables. I now have to make amendments to accommodate
expansion.

What I am trying to get the system to do.

The db holds a list of customers. The customers can enter the system via an
agent, or via the client making an appointment with a client. Whether the
customer enters the system via an agent or via the client, I need to record
all the meters details, and renewal dates. I then need to keep a status of
the new clients which are coming into the system. When a new client has been
successful, I will update the system to allow the full meter details to be
entered into the system.

The flow of the system is as follows.

Customer into system (agent = non appointment, client = appointment)
Record all preliminary meter details
Keep a status of all the clients.
If successful with client record all meter details. Also allow to record
special meter details.

An employee can enter any customer into system and also make appointments,
for customers. They can also enter agents and agent personnel into the
system.

I was wandering if someone could help point out weakness in design if I
emailed it, or point me to a somewhere with good design solutions. Any help
on a more efficient design would be appreciated. I do know I have to change
the customer table, to accommodate many address.

Thanks

Ronnie

  #2  
Old March 9th, 2005, 01:22 PM
Jeff Boyce
external usenet poster
 
Posts: n/a
Default

Ronnie

Not really enough information to go on...

What if I said "I have a vehicle with 3 features but it needs more, what
should I do?"?

I don't understand the relationships among customers, clients and agents.

I don't understand how meters and meter details are involved.

I don't understand renewal dates -- what's being renewed, for whom?

I don't understand agent personnel.

?!Status?!

?!Many addresses?!

I don't understand!

--
More info, please ...

Jeff Boyce
Access MVP

"Ronnie" wrote in message
...
I have a database which I have constructed which has many tables. It

needs
redesigning as I don’t think it’s an effective database design, as when
trying to achieve tasks its always giving me trouble. The db was

originally
designed with jus 9 tables. I now have to make amendments to accommodate
expansion.

What I am trying to get the system to do.

The db holds a list of customers. The customers can enter the system via

an
agent, or via the client making an appointment with a client. Whether the
customer enters the system via an agent or via the client, I need to

record
all the meters details, and renewal dates. I then need to keep a status

of
the new clients which are coming into the system. When a new client has

been
successful, I will update the system to allow the full meter details to be
entered into the system.

The flow of the system is as follows.

Customer into system (agent = non appointment, client = appointment)
Record all preliminary meter details
Keep a status of all the clients.
If successful with client record all meter details. Also allow to record
special meter details.

An employee can enter any customer into system and also make appointments,
for customers. They can also enter agents and agent personnel into the
system.

I was wandering if someone could help point out weakness in design if I
emailed it, or point me to a somewhere with good design solutions. Any

help
on a more efficient design would be appreciated. I do know I have to

change
the customer table, to accommodate many address.

Thanks

Ronnie


  #3  
Old March 9th, 2005, 10:15 PM
Ronnie
external usenet poster
 
Posts: n/a
Default

Sorry Jeff

As there are many tables I could not fit all the info in. To clarify though
I have the following tables as they stand, where the 1 donates the 1 side and
the M, the many side.

Tables Relationships

Suppliers 1 Services M
Services 1 Sales Details M
Sales 1 Sales details M
Agent 1 Reps M
Rep 1 RepCustomers M
Customers 1 RepCustomers M
Customers 1 Appointments M
Appointments 1 Status M
Employee 1 Appointments M
Customers 1 Meters M
Customers 1 STD M

As I said I know I have to split my customer table to Customer has Many
Address’s, I would also like to relate some customer to others, E.G Head
Company, etc.

The db is based around customers who can have many meters. A customer can be
entered into system via an agent who supplies customer leads, or simply by
our own leads via an appointment. All meter details must be recorded.

All customer meters details entered into the system must be processed. Some
are processed immediately whereas others will depend on the customer giving
you a response.

If a customer accepts then this is processed as a sale. The sale will be a
service from a supplier. The customer can also have some special kind of
meters called STD. These are only recorded when this is agreed with the
customer as a sale. An employee can make many appointments as the amount of
appointments determines the employee commission, etc.

An agent can have many reps, and when a rep gets customer leads, then the
customer can belong to any agent/rep.

I hope this makes more sense.

Thanks Ronnie

"Jeff Boyce" wrote:

Ronnie

Not really enough information to go on...

What if I said "I have a vehicle with 3 features but it needs more, what
should I do?"?

I don't understand the relationships among customers, clients and agents.

I don't understand how meters and meter details are involved.

I don't understand renewal dates -- what's being renewed, for whom?

I don't understand agent personnel.

?!Status?!

?!Many addresses?!

I don't understand!

--
More info, please ...

Jeff Boyce
Access MVP

"Ronnie" wrote in message
...
I have a database which I have constructed which has many tables. It

needs
redesigning as I don’t think it’s an effective database design, as when
trying to achieve tasks its always giving me trouble. The db was

originally
designed with jus 9 tables. I now have to make amendments to accommodate
expansion.

What I am trying to get the system to do.

The db holds a list of customers. The customers can enter the system via

an
agent, or via the client making an appointment with a client. Whether the
customer enters the system via an agent or via the client, I need to

record
all the meters details, and renewal dates. I then need to keep a status

of
the new clients which are coming into the system. When a new client has

been
successful, I will update the system to allow the full meter details to be
entered into the system.

The flow of the system is as follows.

Customer into system (agent = non appointment, client = appointment)
Record all preliminary meter details
Keep a status of all the clients.
If successful with client record all meter details. Also allow to record
special meter details.

An employee can enter any customer into system and also make appointments,
for customers. They can also enter agents and agent personnel into the
system.

I was wandering if someone could help point out weakness in design if I
emailed it, or point me to a somewhere with good design solutions. Any

help
on a more efficient design would be appreciated. I do know I have to

change
the customer table, to accommodate many address.

Thanks

Ronnie



  #4  
Old March 10th, 2005, 02:49 PM
Jeff Boyce
external usenet poster
 
Posts: n/a
Default

Ronnie

The folks helping out here are almost all volunteers, so asking a specific
question rather than an overall inquiry is more likely to get you a specific
response.

Please see my questions/comments in-line below:

"Ronnie" wrote in message
...
Sorry Jeff

As there are many tables I could not fit all the info in. To clarify

though
I have the following tables as they stand, where the 1 donates the 1 side

and
the M, the many side.

Tables Relationships

Suppliers 1 Services M
Services 1 Sales Details M
Sales 1 Sales details M
Agent 1 Reps M
Rep 1 RepCustomers M
Customers 1 RepCustomers M
Customers 1 Appointments M
Appointments 1 Status M
Employee 1 Appointments M
Customers 1 Meters M
Customers 1 STD M


These are simply table names ... and I still don't have a very good idea of
what kinds of data you are storing in them. My definition of "Agent" and
yours may not match.


As I said I know I have to split my customer table to Customer has Many
Address’s, I would also like to relate some customer to others, E.G Head
Company, etc.


Why do you feel you have to split your Customer table? Are you looking for
a way to handle the fact that one Customer may have multiple addresses? Can
customers also have many phone numbers?


The db is based around customers who can have many meters.


We're not there, and since this seems to be central, could you describe what
kind of meters you are talking about -- meters (units of measure, larger
than a 'yard'), meters (Volt-Ohm meters), meters (electrical usage meters),
meters (a synonym for 'metrics', items being measured), ...?

A customer can be
entered into system via an agent who supplies customer leads, or simply by
our own leads via an appointment. All meter details must be recorded.



I still don't see the connection between customers, agents and meters, let
along what meter "details" you are referring to.


All customer meters details entered into the system must be processed.



Processes? ?Check-off, calculated, sent out for confirmation, ...?

Some
are processed immediately whereas others will depend on the customer

giving
you a response.


How do you/Access know the difference? Do you handle these two types any
differently in Access?


If a customer accepts then this is processed as a sale. The sale will be

a
service from a supplier.


I don't understand -- how is a sale a service?

The customer can also have some special kind of
meters called STD. These are only recorded when this is agreed with the
customer as a sale.


I thought a sale was a service?

An employee can make many appointments as the amount of
appointments determines the employee commission, etc.


??What does this mean, and how is it related to the rest?


An agent can have many reps, and when a rep gets customer leads, then the
customer can belong to any agent/rep.


??


I hope this makes more sense.


I'm sure if I were there, and watch what you were trying to model, I'd have
a better idea. Perhaps it is only me being dense, but I don't have a clear
picture yet.

Consider focusing in on a single aspect first, trying something, then
posting a specific question.

--
Good luck

Jeff Boyce
Access MVP


Thanks Ronnie

"Jeff Boyce" wrote:

Ronnie

Not really enough information to go on...

What if I said "I have a vehicle with 3 features but it needs more, what
should I do?"?

I don't understand the relationships among customers, clients and

agents.

I don't understand how meters and meter details are involved.

I don't understand renewal dates -- what's being renewed, for whom?

I don't understand agent personnel.

?!Status?!

?!Many addresses?!

I don't understand!

--
More info, please ...

Jeff Boyce
Access MVP

"Ronnie" wrote in message
...
I have a database which I have constructed which has many tables. It

needs
redesigning as I don’t think it’s an effective database design, as

when
trying to achieve tasks its always giving me trouble. The db was

originally
designed with jus 9 tables. I now have to make amendments to

accommodate
expansion.

What I am trying to get the system to do.

The db holds a list of customers. The customers can enter the system

via
an
agent, or via the client making an appointment with a client. Whether

the
customer enters the system via an agent or via the client, I need to

record
all the meters details, and renewal dates. I then need to keep a

status
of
the new clients which are coming into the system. When a new client

has
been
successful, I will update the system to allow the full meter details

to be
entered into the system.

The flow of the system is as follows.

Customer into system (agent = non appointment, client = appointment)
Record all preliminary meter details
Keep a status of all the clients.
If successful with client record all meter details. Also allow to

record
special meter details.

An employee can enter any customer into system and also make

appointments,
for customers. They can also enter agents and agent personnel into

the
system.

I was wandering if someone could help point out weakness in design if

I
emailed it, or point me to a somewhere with good design solutions. Any

help
on a more efficient design would be appreciated. I do know I have to

change
the customer table, to accommodate many address.

Thanks

Ronnie




  #5  
Old March 11th, 2005, 09:11 PM
Ronnie
external usenet poster
 
Posts: n/a
Default

Thanks Jeff

I have gone into more detail so you should get more of an understanding. I
know you are volunteers so I appreciate the response.

Std
STDID Autonumber
SalesID Number
CustomerID Number
STDDay Text
STD Night Text
Evening Weekend Text
Nov-Feb Text
Nov- Feb Peak Text
Dec-Jan Text
Dec-Jan-Peak Text

Agents
AgentID Autonumber
CompanyName Text
ContactFirstname Text
ContactName Text
ContactTitle Text
Address Text
Address2 Text
City Text
County Text
PostCode Text
Phone Text
Phone2 Text
Fax Text
Home Page Hyperlink
Notes Memo

AgentReps
RepID Autonumber
Agent Number
Surname Text
FirstName Text
Mobile Text
Notes Memo
EmailAddress Text

RepCustomers
RepCustomerid Autonumber
AgentRepID Number
CustomerID Number

HostRec
HostRecid Autonumber
HostRecName Text

Customers
CustomerID Autonumber
HostRecID Number
CompanyName Text
ContactFirstName Text
ContactSurname Text
ContactTitle Text
Address Text
Address2 Text
City Text
County Text
PostCode Text
Phone Text
Phone2 Text
Fax Text
HomePage Hyperlink
CompanyNumber Text
Notes Memo

Meters
MeterID Autonumber
CustomerID Number
MeterNumber Text
RenewalDate Date/Time
Type Text

Employee
EmployeeID Autonumber
Surname Text
ChristianName Text
Postion Text
Title Text
Rep Yes/No
BirthDate Date/Time
StartDate Date/Time
Address Text
Address2 Text
City Text
County Text
PostCode Text
HomePhone Hyperlink
Mobile Text
Notes Memo
EmailAddress Text

Appointments
Appointmentid Autonumber
CustomerID Number
DateBooked Date/Time
AppointmentWith Text
AppointmentTime Date/Time
AppointmentDate Date/Time
CurrentUsage Text
Amount Payable Currency
EmployeeID Number
SupplierID Number
RepSelection Text

Status
Statusid Autonumber
Appointmentid Number
Status Text
DateCompleted Date/Time

Sales
SalesID Autonumber
Customer Number
Date Signed Date/Time

SalesDetails
SalesID Number
Service Number
MPAN Text
StandingCharge Number
EAC Number
Day Number
Night Number
Rate Number
HalfHour Yes/No
RenewalDate Date/Time
MaxCapacity Number
WeekdayRate Number
Voltage Text
LoadFactor Text
RenewalStatus Yes/No

Service
ServiceID Autonumber
Supplier Number
ServiceName Text

Supplier
SupplierID Autonumber
CompanyName Text
ContactName Text
ContactTitle Text
Address Text
Address2 Text
City Text
County Text
PostCode Text
PhoneNo Text
Fax Text
HomePage Hyperlink

A Customer can have multiple addresses, and they can also have more than one
phone number.

By meters it is referring to an electrical meter or Gas meter, as you would
find in a home, or business. Any gas/electrical meter will have a renewal
date so you know when to contact customers with renewal price concerning
either there gas or electric meters.

By processed I mean all new meter details must be entered into the system
for a new customer. The customer can then either accept the price of the
service you are offering or not accept. If the service is accepted where the
service is supplied by a supplier, then a sales record is recorded, so an
updated list of all current customers is in the system. If the customer
rejects the services you are offering then the renewal date of a meter as
shown in the meters table, is important as this informs you when to contact
the customer again, and send out renewal invitation.

An agent is a kind of outside company who will offer you potential customers
for you. The agents will have more than 1 Rep/Personnel working for them.
It is possible that different agents will potentially offer you the same
customer. This is why I created the table repcustomers. After a rep has
offered you a potential customer, then an appointment may be required with
the customer. The rep from the agent would have potentially done most of the
work, so in most of the customers the rep has informed you about, it would be
a case of contacting the customer and see if they want to accept any of the
services you are offering.

The organisation for whom this database is designed for also have there own
employees/reps. The employees will also look for potential customers to sell
them a service. When an employee identifies a potential customer, an
appointment is generally required to visit the customer. Commission is paid
to employee‘s for arranging appointments, so the system need to recognize
which employee has made appointments.

After an appointment with a customer, then the client may not accept the
service immediately or not at all. The system needs to keep a track of
customers which a response is required.

A customer can also have specials deals on there meters. I have an STD
table which show the different type of data e.g. DecJan, from the standard
meter details. The sales details table holds all the field which a standard
meter would have, such as Load factor, Voltage, Day, Night, etc.

The hostrec table I have created is because a customer has to belong to one,
and only one hostrec. This is like an area from which the gas or electric
service is supplied.

Given the relationships I have indicated I was after a more effective design
than the one I currently have as I am sure this can be achieved. I know you
said try to focus on a single aspect the post a specific question. The
overall design of my database is my concern, as opposed to a specific aspect
of it.

Thanks

Ronnie



"Jeff Boyce" wrote:

Ronnie

The folks helping out here are almost all volunteers, so asking a specific
question rather than an overall inquiry is more likely to get you a specific
response.

Please see my questions/comments in-line below:

"Ronnie" wrote in message
...
Sorry Jeff

As there are many tables I could not fit all the info in. To clarify

though
I have the following tables as they stand, where the 1 donates the 1 side

and
the M, the many side.

Tables Relationships

Suppliers 1 Services M
Services 1 Sales Details M
Sales 1 Sales details M
Agent 1 Reps M
Rep 1 RepCustomers M
Customers 1 RepCustomers M
Customers 1 Appointments M
Appointments 1 Status M
Employee 1 Appointments M
Customers 1 Meters M
Customers 1 STD M


These are simply table names ... and I still don't have a very good idea of
what kinds of data you are storing in them. My definition of "Agent" and
yours may not match.


As I said I know I have to split my customer table to Customer has Many
Address’s, I would also like to relate some customer to others, E.G Head
Company, etc.


Why do you feel you have to split your Customer table? Are you looking for
a way to handle the fact that one Customer may have multiple addresses? Can
customers also have many phone numbers?


The db is based around customers who can have many meters.


We're not there, and since this seems to be central, could you describe what
kind of meters you are talking about -- meters (units of measure, larger
than a 'yard'), meters (Volt-Ohm meters), meters (electrical usage meters),
meters (a synonym for 'metrics', items being measured), ...?

A customer can be
entered into system via an agent who supplies customer leads, or simply by
our own leads via an appointment. All meter details must be recorded.



I still don't see the connection between customers, agents and meters, let
along what meter "details" you are referring to.


All customer meters details entered into the system must be processed.



Processes? ?Check-off, calculated, sent out for confirmation, ...?

Some
are processed immediately whereas others will depend on the customer

giving
you a response.


How do you/Access know the difference? Do you handle these two types any
differently in Access?


If a customer accepts then this is processed as a sale. The sale will be

a
service from a supplier.


I don't understand -- how is a sale a service?

The customer can also have some special kind of
meters called STD. These are only recorded when this is agreed with the
customer as a sale.


I thought a sale was a service?

An employee can make many appointments as the amount of
appointments determines the employee commission, etc.


??What does this mean, and how is it related to the rest?


An agent can have many reps, and when a rep gets customer leads, then the
customer can belong to any agent/rep.


??


I hope this makes more sense.


I'm sure if I were there, and watch what you were trying to model, I'd have
a better idea. Perhaps it is only me being dense, but I don't have a clear
picture yet.

Consider focusing in on a single aspect first, trying something, then
posting a specific question.

--
Good luck

Jeff Boyce
Access MVP


Thanks Ronnie

"Jeff Boyce" wrote:

Ronnie

Not really enough information to go on...

What if I said "I have a vehicle with 3 features but it needs more, what
should I do?"?

I don't understand the relationships among customers, clients and

agents.

I don't understand how meters and meter details are involved.

I don't understand renewal dates -- what's being renewed, for whom?

I don't understand agent personnel.

?!Status?!

?!Many addresses?!

I don't understand!

--
More info, please ...

Jeff Boyce
Access MVP

"Ronnie" wrote in message
...
I have a database which I have constructed which has many tables. It
needs
redesigning as I don’t think it’s an effective database design, as

when
trying to achieve tasks its always giving me trouble. The db was
originally
designed with jus 9 tables. I now have to make amendments to

accommodate
expansion.

What I am trying to get the system to do.

The db holds a list of customers. The customers can enter the system

via
an
agent, or via the client making an appointment with a client. Whether

the
customer enters the system via an agent or via the client, I need to
record
all the meters details, and renewal dates. I then need to keep a

status
of
the new clients which are coming into the system. When a new client

has
been
successful, I will update the system to allow the full meter details

to be
entered into the system.

The flow of the system is as follows.

Customer into system (agent = non appointment, client = appointment)
Record all preliminary meter details
Keep a status of all the clients.
If successful with client record all meter details. Also allow to

record
special meter details.

An employee can enter any customer into system and also make

appointments,
for customers. They can also enter agents and agent personnel into

the

system.

I was wandering if someone could help point out weakness in design if
I
emailed it, or point me to a somewhere with good design solutions. Any
help
on a more efficient design would be appreciated. I do know I have to
change
the customer table, to accommodate many address.

Thanks
Ronnie

  #6  
Old March 12th, 2005, 02:15 PM
Jeff Boyce
external usenet poster
 
Posts: n/a
Default

Ronnie

Thanks for the clarifications.

I'll stand on my recommendation... narrow down your focus to a single aspect
of what you are trying to do, and start a new thread on that one topic.

As you have laid it out, it sounds like you are seeking a comprehensive
review of your system/design. This is not something I have time to do as a
volunteer, if I am to also help other folks posting in the groups.

But perhaps one of the other 'group readers has time to?!

Good luck on your project

Jeff Boyce
Access MVP

"Ronnie" wrote in message
...
Thanks Jeff

I have gone into more detail so you should get more of an understanding.

I
know you are volunteers so I appreciate the response.

Std
STDID Autonumber
SalesID Number
CustomerID Number
STDDay Text
STD Night Text
Evening Weekend Text
Nov-Feb Text
Nov- Feb Peak Text
Dec-Jan Text
Dec-Jan-Peak Text

Agents
AgentID Autonumber
CompanyName Text
ContactFirstname Text
ContactName Text
ContactTitle Text
Address Text
Address2 Text
City Text
County Text
PostCode Text
Phone Text
Phone2 Text
Fax Text
Home Page Hyperlink
Notes Memo

AgentReps
RepID Autonumber
Agent Number
Surname Text
FirstName Text
Mobile Text
Notes Memo
EmailAddress Text

RepCustomers
RepCustomerid Autonumber
AgentRepID Number
CustomerID Number

HostRec
HostRecid Autonumber
HostRecName Text

Customers
CustomerID Autonumber
HostRecID Number
CompanyName Text
ContactFirstName Text
ContactSurname Text
ContactTitle Text
Address Text
Address2 Text
City Text
County Text
PostCode Text
Phone Text
Phone2 Text
Fax Text
HomePage Hyperlink
CompanyNumber Text
Notes Memo

Meters
MeterID Autonumber
CustomerID Number
MeterNumber Text
RenewalDate Date/Time
Type Text

Employee
EmployeeID Autonumber
Surname Text
ChristianName Text
Postion Text
Title Text
Rep Yes/No
BirthDate Date/Time
StartDate Date/Time
Address Text
Address2 Text
City Text
County Text
PostCode Text
HomePhone Hyperlink
Mobile Text
Notes Memo
EmailAddress Text

Appointments
Appointmentid Autonumber
CustomerID Number
DateBooked Date/Time
AppointmentWith Text
AppointmentTime Date/Time
AppointmentDate Date/Time
CurrentUsage Text
Amount Payable Currency
EmployeeID Number
SupplierID Number
RepSelection Text

Status
Statusid Autonumber
Appointmentid Number
Status Text
DateCompleted Date/Time

Sales
SalesID Autonumber
Customer Number
Date Signed Date/Time

SalesDetails
SalesID Number
Service Number
MPAN Text
StandingCharge Number
EAC Number
Day Number
Night Number
Rate Number
HalfHour Yes/No
RenewalDate Date/Time
MaxCapacity Number
WeekdayRate Number
Voltage Text
LoadFactor Text
RenewalStatus Yes/No

Service
ServiceID Autonumber
Supplier Number
ServiceName Text

Supplier
SupplierID Autonumber
CompanyName Text
ContactName Text
ContactTitle Text
Address Text
Address2 Text
City Text
County Text
PostCode Text
PhoneNo Text
Fax Text
HomePage Hyperlink

A Customer can have multiple addresses, and they can also have more than

one
phone number.

By meters it is referring to an electrical meter or Gas meter, as you

would
find in a home, or business. Any gas/electrical meter will have a renewal
date so you know when to contact customers with renewal price concerning
either there gas or electric meters.

By processed I mean all new meter details must be entered into the system
for a new customer. The customer can then either accept the price of the
service you are offering or not accept. If the service is accepted where

the
service is supplied by a supplier, then a sales record is recorded, so an
updated list of all current customers is in the system. If the customer
rejects the services you are offering then the renewal date of a meter as
shown in the meters table, is important as this informs you when to

contact
the customer again, and send out renewal invitation.

An agent is a kind of outside company who will offer you potential

customers
for you. The agents will have more than 1 Rep/Personnel working for them.
It is possible that different agents will potentially offer you the same
customer. This is why I created the table repcustomers. After a rep has
offered you a potential customer, then an appointment may be required with
the customer. The rep from the agent would have potentially done most of

the
work, so in most of the customers the rep has informed you about, it would

be
a case of contacting the customer and see if they want to accept any of

the
services you are offering.

The organisation for whom this database is designed for also have there

own
employees/reps. The employees will also look for potential customers to

sell
them a service. When an employee identifies a potential customer, an
appointment is generally required to visit the customer. Commission is

paid
to employee‘s for arranging appointments, so the system need to recognize
which employee has made appointments.

After an appointment with a customer, then the client may not accept the
service immediately or not at all. The system needs to keep a track of
customers which a response is required.

A customer can also have specials deals on there meters. I have an STD
table which show the different type of data e.g. DecJan, from the standard
meter details. The sales details table holds all the field which a

standard
meter would have, such as Load factor, Voltage, Day, Night, etc.

The hostrec table I have created is because a customer has to belong to

one,
and only one hostrec. This is like an area from which the gas or electric
service is supplied.

Given the relationships I have indicated I was after a more effective

design
than the one I currently have as I am sure this can be achieved. I know

you
said try to focus on a single aspect the post a specific question. The
overall design of my database is my concern, as opposed to a specific

aspect
of it.

Thanks

Ronnie



"Jeff Boyce" wrote:

Ronnie

The folks helping out here are almost all volunteers, so asking a

specific
question rather than an overall inquiry is more likely to get you a

specific
response.

Please see my questions/comments in-line below:

"Ronnie" wrote in message
...
Sorry Jeff

As there are many tables I could not fit all the info in. To clarify

though
I have the following tables as they stand, where the 1 donates the 1

side
and
the M, the many side.

Tables Relationships

Suppliers 1 Services M
Services 1 Sales Details M
Sales 1 Sales details M
Agent 1 Reps M
Rep 1 RepCustomers M
Customers 1 RepCustomers M
Customers 1 Appointments M
Appointments 1 Status M
Employee 1 Appointments M
Customers 1 Meters M
Customers 1 STD M


These are simply table names ... and I still don't have a very good idea

of
what kinds of data you are storing in them. My definition of "Agent"

and
yours may not match.


As I said I know I have to split my customer table to Customer has

Many
Address’s, I would also like to relate some customer to others, E.G

Head
Company, etc.


Why do you feel you have to split your Customer table? Are you looking

for
a way to handle the fact that one Customer may have multiple addresses?

Can
customers also have many phone numbers?


The db is based around customers who can have many meters.


We're not there, and since this seems to be central, could you describe

what
kind of meters you are talking about -- meters (units of measure, larger
than a 'yard'), meters (Volt-Ohm meters), meters (electrical usage

meters),
meters (a synonym for 'metrics', items being measured), ...?

A customer can be
entered into system via an agent who supplies customer leads, or

simply by
our own leads via an appointment. All meter details must be recorded.



I still don't see the connection between customers, agents and meters,

let
along what meter "details" you are referring to.


All customer meters details entered into the system must be processed.



Processes? ?Check-off, calculated, sent out for confirmation, ...?

Some
are processed immediately whereas others will depend on the customer

giving
you a response.


How do you/Access know the difference? Do you handle these two types

any
differently in Access?


If a customer accepts then this is processed as a sale. The sale will

be
a
service from a supplier.


I don't understand -- how is a sale a service?

The customer can also have some special kind of
meters called STD. These are only recorded when this is agreed with

the
customer as a sale.


I thought a sale was a service?

An employee can make many appointments as the amount of
appointments determines the employee commission, etc.


??What does this mean, and how is it related to the rest?


An agent can have many reps, and when a rep gets customer leads, then

the
customer can belong to any agent/rep.


??


I hope this makes more sense.


I'm sure if I were there, and watch what you were trying to model, I'd

have
a better idea. Perhaps it is only me being dense, but I don't have a

clear
picture yet.

Consider focusing in on a single aspect first, trying something, then
posting a specific question.

--
Good luck

Jeff Boyce
Access MVP


Thanks Ronnie

"Jeff Boyce" wrote:

Ronnie

Not really enough information to go on...

What if I said "I have a vehicle with 3 features but it needs more,

what
should I do?"?

I don't understand the relationships among customers, clients and

agents.

I don't understand how meters and meter details are involved.

I don't understand renewal dates -- what's being renewed, for whom?

I don't understand agent personnel.

?!Status?!

?!Many addresses?!

I don't understand!

--
More info, please ...

Jeff Boyce
Access MVP

"Ronnie" wrote in message
...
I have a database which I have constructed which has many tables.

It
needs
redesigning as I don’t think it’s an effective database design, as

when
trying to achieve tasks its always giving me trouble. The db was
originally
designed with jus 9 tables. I now have to make amendments to

accommodate
expansion.

What I am trying to get the system to do.

The db holds a list of customers. The customers can enter the

system
via
an
agent, or via the client making an appointment with a client.

Whether
the
customer enters the system via an agent or via the client, I need

to
record
all the meters details, and renewal dates. I then need to keep a

status
of
the new clients which are coming into the system. When a new

client
has
been
successful, I will update the system to allow the full meter

details
to be
entered into the system.

The flow of the system is as follows.

Customer into system (agent = non appointment, client =

appointment)
Record all preliminary meter details
Keep a status of all the clients.
If successful with client record all meter details. Also allow to

record
special meter details.

An employee can enter any customer into system and also make

appointments,
for customers. They can also enter agents and agent personnel

into
the

system.

I was wandering if someone could help point out weakness in design if
I
emailed it, or point me to a somewhere with good design solutions. Any
help
on a more efficient design would be appreciated. I do know I have to
change
the customer table, to accommodate many address.

Thanks
Ronnie


  #7  
Old March 12th, 2005, 03:53 PM
Ronnie
external usenet poster
 
Posts: n/a
Default

Thanks Jeff.

It is probably at a lot to ask, however there was no harm in trying


"Jeff Boyce" wrote:

Ronnie

Thanks for the clarifications.

I'll stand on my recommendation... narrow down your focus to a single aspect
of what you are trying to do, and start a new thread on that one topic.

As you have laid it out, it sounds like you are seeking a comprehensive
review of your system/design. This is not something I have time to do as a
volunteer, if I am to also help other folks posting in the groups.

But perhaps one of the other 'group readers has time to?!

Good luck on your project

Jeff Boyce
Access MVP

"Ronnie" wrote in message
...
Thanks Jeff

I have gone into more detail so you should get more of an understanding.

I
know you are volunteers so I appreciate the response.

Std
STDID Autonumber
SalesID Number
CustomerID Number
STDDay Text
STD Night Text
Evening Weekend Text
Nov-Feb Text
Nov- Feb Peak Text
Dec-Jan Text
Dec-Jan-Peak Text

Agents
AgentID Autonumber
CompanyName Text
ContactFirstname Text
ContactName Text
ContactTitle Text
Address Text
Address2 Text
City Text
County Text
PostCode Text
Phone Text
Phone2 Text
Fax Text
Home Page Hyperlink
Notes Memo

AgentReps
RepID Autonumber
Agent Number
Surname Text
FirstName Text
Mobile Text
Notes Memo
EmailAddress Text

RepCustomers
RepCustomerid Autonumber
AgentRepID Number
CustomerID Number

HostRec
HostRecid Autonumber
HostRecName Text

Customers
CustomerID Autonumber
HostRecID Number
CompanyName Text
ContactFirstName Text
ContactSurname Text
ContactTitle Text
Address Text
Address2 Text
City Text
County Text
PostCode Text
Phone Text
Phone2 Text
Fax Text
HomePage Hyperlink
CompanyNumber Text
Notes Memo

Meters
MeterID Autonumber
CustomerID Number
MeterNumber Text
RenewalDate Date/Time
Type Text

Employee
EmployeeID Autonumber
Surname Text
ChristianName Text
Postion Text
Title Text
Rep Yes/No
BirthDate Date/Time
StartDate Date/Time
Address Text
Address2 Text
City Text
County Text
PostCode Text
HomePhone Hyperlink
Mobile Text
Notes Memo
EmailAddress Text

Appointments
Appointmentid Autonumber
CustomerID Number
DateBooked Date/Time
AppointmentWith Text
AppointmentTime Date/Time
AppointmentDate Date/Time
CurrentUsage Text
Amount Payable Currency
EmployeeID Number
SupplierID Number
RepSelection Text

Status
Statusid Autonumber
Appointmentid Number
Status Text
DateCompleted Date/Time

Sales
SalesID Autonumber
Customer Number
Date Signed Date/Time

SalesDetails
SalesID Number
Service Number
MPAN Text
StandingCharge Number
EAC Number
Day Number
Night Number
Rate Number
HalfHour Yes/No
RenewalDate Date/Time
MaxCapacity Number
WeekdayRate Number
Voltage Text
LoadFactor Text
RenewalStatus Yes/No

Service
ServiceID Autonumber
Supplier Number
ServiceName Text

Supplier
SupplierID Autonumber
CompanyName Text
ContactName Text
ContactTitle Text
Address Text
Address2 Text
City Text
County Text
PostCode Text
PhoneNo Text
Fax Text
HomePage Hyperlink

A Customer can have multiple addresses, and they can also have more than

one
phone number.

By meters it is referring to an electrical meter or Gas meter, as you

would
find in a home, or business. Any gas/electrical meter will have a renewal
date so you know when to contact customers with renewal price concerning
either there gas or electric meters.

By processed I mean all new meter details must be entered into the system
for a new customer. The customer can then either accept the price of the
service you are offering or not accept. If the service is accepted where

the
service is supplied by a supplier, then a sales record is recorded, so an
updated list of all current customers is in the system. If the customer
rejects the services you are offering then the renewal date of a meter as
shown in the meters table, is important as this informs you when to

contact
the customer again, and send out renewal invitation.

An agent is a kind of outside company who will offer you potential

customers
for you. The agents will have more than 1 Rep/Personnel working for them.
It is possible that different agents will potentially offer you the same
customer. This is why I created the table repcustomers. After a rep has
offered you a potential customer, then an appointment may be required with
the customer. The rep from the agent would have potentially done most of

the
work, so in most of the customers the rep has informed you about, it would

be
a case of contacting the customer and see if they want to accept any of

the
services you are offering.

The organisation for whom this database is designed for also have there

own
employees/reps. The employees will also look for potential customers to

sell
them a service. When an employee identifies a potential customer, an
appointment is generally required to visit the customer. Commission is

paid
to employee‘s for arranging appointments, so the system need to recognize
which employee has made appointments.

After an appointment with a customer, then the client may not accept the
service immediately or not at all. The system needs to keep a track of
customers which a response is required.

A customer can also have specials deals on there meters. I have an STD
table which show the different type of data e.g. DecJan, from the standard
meter details. The sales details table holds all the field which a

standard
meter would have, such as Load factor, Voltage, Day, Night, etc.

The hostrec table I have created is because a customer has to belong to

one,
and only one hostrec. This is like an area from which the gas or electric
service is supplied.

Given the relationships I have indicated I was after a more effective

design
than the one I currently have as I am sure this can be achieved. I know

you
said try to focus on a single aspect the post a specific question. The
overall design of my database is my concern, as opposed to a specific

aspect
of it.

Thanks

Ronnie



"Jeff Boyce" wrote:

Ronnie

The folks helping out here are almost all volunteers, so asking a

specific
question rather than an overall inquiry is more likely to get you a

specific
response.

Please see my questions/comments in-line below:

"Ronnie" wrote in message
...
Sorry Jeff

As there are many tables I could not fit all the info in. To clarify
though
I have the following tables as they stand, where the 1 donates the 1

side
and
the M, the many side.

Tables Relationships

Suppliers 1 Services M
Services 1 Sales Details M
Sales 1 Sales details M
Agent 1 Reps M
Rep 1 RepCustomers M
Customers 1 RepCustomers M
Customers 1 Appointments M
Appointments 1 Status M
Employee 1 Appointments M
Customers 1 Meters M
Customers 1 STD M

These are simply table names ... and I still don't have a very good idea

of
what kinds of data you are storing in them. My definition of "Agent"

and
yours may not match.


As I said I know I have to split my customer table to Customer has

Many
Address’s, I would also like to relate some customer to others, E.G

Head
Company, etc.

Why do you feel you have to split your Customer table? Are you looking

for
a way to handle the fact that one Customer may have multiple addresses?

Can
customers also have many phone numbers?


The db is based around customers who can have many meters.

We're not there, and since this seems to be central, could you describe

what
kind of meters you are talking about -- meters (units of measure, larger
than a 'yard'), meters (Volt-Ohm meters), meters (electrical usage

meters),
meters (a synonym for 'metrics', items being measured), ...?

A customer can be
entered into system via an agent who supplies customer leads, or

simply by
our own leads via an appointment. All meter details must be recorded.


I still don't see the connection between customers, agents and meters,

let
along what meter "details" you are referring to.


All customer meters details entered into the system must be processed.


Processes? ?Check-off, calculated, sent out for confirmation, ...?

Some
are processed immediately whereas others will depend on the customer
giving
you a response.

How do you/Access know the difference? Do you handle these two types

any
differently in Access?


If a customer accepts then this is processed as a sale. The sale will

be
a
service from a supplier.

I don't understand -- how is a sale a service?

The customer can also have some special kind of
meters called STD. These are only recorded when this is agreed with

the
customer as a sale.

I thought a sale was a service?

An employee can make many appointments as the amount of
appointments determines the employee commission, etc.

??What does this mean, and how is it related to the rest?


An agent can have many reps, and when a rep gets customer leads, then

the
customer can belong to any agent/rep.

??


I hope this makes more sense.


I'm sure if I were there, and watch what you were trying to model, I'd

have
a better idea. Perhaps it is only me being dense, but I don't have a

clear
picture yet.

Consider focusing in on a single aspect first, trying something, then
posting a specific question.

--
Good luck

Jeff Boyce
Access MVP


Thanks Ronnie

"Jeff Boyce" wrote:

Ronnie

Not really enough information to go on...

What if I said "I have a vehicle with 3 features but it needs more,

what
should I do?"?

I don't understand the relationships among customers, clients and
agents.

I don't understand how meters and meter details are involved.

I don't understand renewal dates -- what's being renewed, for whom?

I don't understand agent personnel.

?!Status?!

?!Many addresses?!

I don't understand!

--
More info, please ...

Jeff Boyce
Access MVP

"Ronnie" wrote in message
...
I have a database which I have constructed which has many tables.

It
needs
redesigning as I don’t think it’s an effective database design, as
when
trying to achieve tasks its always giving me trouble. The db was
originally
designed with jus 9 tables. I now have to make amendments to
accommodate
expansion.

What I am trying to get the system to do.

The db holds a list of customers. The customers can enter the

system
via
an
agent, or via the client making an appointment with a client.

Whether
the
customer enters the system via an agent or via the client, I need

to
record
all the meters details, and renewal dates. I then need to keep a
status
of
the new clients which are coming into the system. When a new

client
has
been
successful, I will update the system to allow the full meter

details
to be
entered into the system.

The flow of the system is as follows.

Customer into system (agent = non appointment, client =

appointment)
Record all preliminary meter details
Keep a status of all the clients.
If successful with client record all meter details. Also allow to
record
special meter details.

An employee can enter any customer into system and also make
appointments,
for customers. They can also enter agents and agent personnel

into
the

system.

I was wandering if someone could help point out weakness in design if
I
emailed it, or point me to a somewhere with good design solutions. Any
help
on a more efficient design would be appreciated. I do know I have to
change
the customer table, to accommodate many address.

Thanks
Ronnie



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Who owns the copyright on graphic design layouts prepared in MS Wo Karen General Discussion 4 February 1st, 2005 08:01 AM
How can I customise an entire design set David Publisher 2 November 12th, 2004 10:43 AM
design master problem J. Vermeer General Discussion 0 September 8th, 2004 03:23 PM
Action queries changing when reopened in design view Kendra Running & Setting Up Queries 2 August 31st, 2004 12:34 AM
opening a document so it is NOT in design mode Brad Pears New Users 1 May 3rd, 2004 09:13 PM


All times are GMT +1. The time now is 11:44 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.