Results 1 to 6 of 6
  1. #1
    Dixi23228 is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2015
    Posts
    4

    Customer Database HELP!! :)

    I am new to Access 2007. I have completed an array of tutorials, but I can't get my brain to "click".....
    I have the following information and I want to pull it together:
    Customers: Company, Name, address, phone numbers and email address
    There are various services offered and each customer has one or more of them.
    I have the following:
    Type of service
    Occurance of service (monthly, annually, etc)
    Month service is due
    Type of equipment used
    Number of men assigned
    Number of hours spent on job
    I would like to be able to click on a customer/company to find out all of the services they have, the occurrance of each service, the month each service is due, the type of equipment used, the number of men assigned and the number of hours spent on the service, all on one page.

    How would I make this happen? I have input the information in the database, but I don't know how to get it out like I want it. Not even sure I have put it in the correct way for it to work.


    Any help would be greatly appreciated!

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Have you got a sample database available. If you don't you can create one by making a copy of your database, putting in some garbage data (or masking what you have if it's small) with 'dummy' names. Then zip it up and use the 'go advanced' button to upload the file.

  3. #3
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    along with tCustomer table, you need a reference table tServices
    this has all the services you offer to pick from. (costs?)
    and a tCustService table, a child table to the tCustomer parent , to show what services each customer gets.

    tCustService
    -----------------
    [CustSvcID] autonumber
    [CustID] long integer (ID from the tCustomer table)
    [Service]
    [StartDate]
    [duration]
    [ExpDate]
    etc....

    1 client can have many services.
    maybe a tCustWork table, to track work on a service/ customer
    tCustWork
    --------------
    [
    CustSvcID] or [CustID]
    [work done]
    [hours]
    ...

  4. #4
    Dixi23228 is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2015
    Posts
    4
    I will do that and attach it shortly. Just have to "mask" some info.
    Thanks!

  5. #5
    Dixi23228 is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2015
    Posts
    4

    "Masked" Database

    This is a sample of what I have.
    Thank you so very much!
    Attached Files Attached Files

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Ok I thought you had something more complete than this. It looks like you just have a table for each service type (electrical, hvac or plumbing) and a customer table and your service tables (plumbing and electrical) don't appear to be normalized. So let's be more generic, can you describe your contracting process?

    So for instance if you have a customer with whom you contract for electrical services, what electrical services do you offer. The individual services (or service packages) need to be identified.

    Let's say you have 10 electrical services

    Code:
    tblSvcType
    ST_ID  ST_Desc
    1      Electrical
    2      Plumbing
    3      HVAC
    
    tblSvc
    SV_ID  ST_ID  SV_Description
    1      1      Electrical Service 1
    2      1      Electrical Service 2
    3      1      Electrical Service 3
    4      1      Electrical Service 4
    5      1      Electrical Service 5
    6      1      Electrical Service 6
    7      1      Electrical Service 7
    8      1      Electrical Service 8
    9      1      Electrical Service 9
    10     1      Electrical Service 10
    11     2      Plumbing Service 1
    Now let's say you have three 'packages' to offer on a contract package 1 obligates service 1 through 3, package 2 obligates 1 through 6 and package 3 obligates all of them you would have a junction table to indicate the 'packages' like

    Code:
    tblPackages
    PA_ID  ST_ID  PA_Desc  
    1      1      Electrical Bronze Package
    2      1      Electrical Silver Package
    3      1      Electrical Gold Package
    
    tblPackageSvc
    PS_ID  PA_ID  SV_ID
    1      1      1
    2      1      3
    3      1      2
    4      2      1 
    5      2      2
    6      2      3
    7      2      4
    8      2      5  
    9      2      6 
    10     3       1
    11     3       2
    12     3       3
    13     3       4 
    14     3       5 
    15     3       6 
    16     3       7 
    17     3       8 
    18     3       9
    19     3       10
    So that when you went to build a contract you could choose the service package and know which particular services were supposed to be rendered. If you don't have 'packages' then you don't need this step.

    From there when you build a schedule you would have to have some sort of mechanism for figuring service dates by the start date of the contract. i.e. if the contract is start date is january 2015 and the period is quarterly you could have your database build the schedule of creating 4 scheduled maintenance visits.

    If electrical service 1 needs to be done once per quarter regardless of customer that would be set up on the services table, if electrical service 1 could be performed monthly, quarterly or annually, that would be on the scheduling side.

    You can also do things like have the 'required' number of men for each service rendered so you can estimate how many people you should send to the job when the time comes. Or estimate the number of hours required by service so if you sum the hours of service required and you know how long you want to be at the site you can figure how many employees to send.

    The payment/service rendered side would be another table that records who went on the job (individually rather than as a group would be my preferred method so you could track individual employee hours) and the number of hours they were there.


    If you can explain more clearly what your business actually does (process wise, not service wise) in building a contract and delivering the contract that would be quite helpful.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Invoice/Customer Database
    By gebmiller1984 in forum Access
    Replies: 5
    Last Post: 01-15-2015, 04:17 PM
  2. Replies: 3
    Last Post: 03-19-2014, 05:35 PM
  3. Replies: 6
    Last Post: 12-11-2013, 09:27 PM
  4. New Database on Customer Quotes
    By rkalapura in forum Access
    Replies: 2
    Last Post: 07-13-2012, 07:15 PM
  5. Customer database problem
    By paasan in forum Access
    Replies: 2
    Last Post: 04-21-2011, 11:05 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums