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 said:
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:
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.
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?
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), ...?
I still don't see the connection between customers, agents and meters, let
along what meter "details" you are referring to.
Processes? ?Check-off, calculated, sent out for confirmation, ...?
How do you/Access know the difference? Do you handle these two types any
differently in Access?
I don't understand -- how is a sale a service?
I thought a sale was a service?
??What does this mean, and how is it related to the rest?
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
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
