Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2012
    Location
    West Mids, England, UK
    Posts
    2

    Confused about how to create a view based on current structure

    Edit: Okay so it appears my post perhaps isn't very clear or just to much to read...I did rush it a little. So I've attached my tables from my MySQL database for all to see and these are what I want to JOIN together.

    Hello,

    As you can see I'm very new here. In fact I've registered as I could do with the help! I'm not a database designer but have dealt with databases throughout my last 15 years in IT both academically and personally.

    I now run my own small IT business (me and my brother) doing repairs, cabling and server installations. To try and help the business run as smoothly as possible I created my own database with an MSAccess front-end and a MySQL back-end.

    I've recently purchased what proves to be a fantastic product that will allow me to sync my database contacts to a mailbox on my Exchange server, this will help my business a great deal and save lots of time searching for customer telephone numbers etc.!

    I hope you appreciate the background info. I've given and why I've come here.

    So...the problem I have is with the design of my database. The 3rd party product requires a flat file style of supplying the contact details to sync with Exchange. The problem is I have this information spread over 3 tables.

    Here are ALL the tables in my database:

    accounts*
    addressbook*
    contacts*
    income
    invoicejobs
    invoices
    jobs
    jobsrecurring
    nomicalcodes
    products
    visitproducts
    visits

    The 3 that contain the info I need to extract are the first 3 marked with asterisk's.

    The system currently works okay and stores almost everything I need. Here's a summary of the function of these 3 tables:

    -An 'account' represents a customer or supplier and holds basic info. no addresses and no contact details.
    -A customer 'account' can be a business or residential customer. If it is a residential customer there will be at least one 'contact' associated with it more if there are other family members details stored.
    -Each 'account' can have many addresses ('addressbook') or 'contacts' associated with it.
    -An address can be:
    --the main company address
    --the directors home address
    --another site address
    -A contact represents a person

    For the 3rd party software I have to create a view (this I can do) but it has to output ALL contact details. So, for example, if in my database I have:

    Acme Inc. account header
    3 different address: 2 site address and the directors home address
    5 contacts: 1 person at each site, the directors wife for home visits and an IT contact.

    I know I will end up with more records than I desire in Exchange because I have no relationship between the addressbook and the contact, I am okay to accept that. So based on the above example, in Microsoft Outlook I would expect there to be 3 contacts items stored under the company name each having one of the 3 site addresses and a further 5 contact items under the company name with each of the contacts details.

    The only way to do this I believe is to use a union and run a query based on a join of, first the addressbook table and then the contacts table - but this is where I am unsure!

    If anyone else can think of a better way of doing this or if I'm doing this in a really awkard way I would be greateful.

    Thanks for your time and patience and let me know if theres a major bit of info I've missed!

    Regards,

    Tim
    Attached Files Attached Files
    Last edited by TechyTim; 02-20-12 at 10:38.

  2. #2
    Join Date
    Feb 2012
    Location
    West Mids, England, UK
    Posts
    2
    Okay, so I've re-read this a couple of times and I've seen a lot of people viewing but not answering. I guess I perhaps made things more complicated for myself than they needed to be.

    My tables:

    accounts: Id, Name, DateCreation, Email, Url, LastModified

    addressbook: Id, Department, Line1, Line2, Street, Area, Town, County, Postcode, Tel1, Tel2, Fax, Accounts_Id(fk)

    contacts: Id, Title, FirstName, LastName, Email, Tel1, Ext1, Tel2, Accounts_Id(fk)

    I need to join all 3 together as discussed previously.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •