Results 1 to 7 of 7

Thread: CRM help please

  1. #1
    Join Date
    Apr 2010
    Posts
    20

    Unanswered: CRM help please

    Hi to everyone , I don't quite understand how the CRM works, i 've done
    some research , but would'nt be able to fullfill my questions.
    So i think this is the right place for me .
    All i have to do is to develop a CRM system , with Customer data,
    Campaign data, Call data, Products data, Orders data, Transaction data,
    Can someone give me a brief idea how to go about the tables , to do the
    schema? Thank you very match

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Sure. What part are you having trouble with?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Apr 2010
    Posts
    20

    Thanks for your response

    All i want to do is connect the tables together.


    This is all the informations i have.

    Customer data: Has to have an address field, contact information for more than one contact.

    Campaign data: A campaign is simply a name given to a set of calls to make to certain customers which have been selected by a data mining algorithm. The campaign must store information on the campaign name,
    description of the campaign(purpose) and the starting and ending dates
    for the campaiggn. It also must include a status which will indicate if the campaign is deployed yet or not, or whether it is finished ("closed").

    Call data All calls allocated to a campaign must be stored. These records constitute calls that are going to be made for a campaign. Along with
    the campaign that each call is a part of, the customer that a call is to be made to, you must somehow store the time a call was attempted and the outcome of that attempt. Keep in mind that a call may be attempted multiply
    times before being suxxesfully completed. Each call attempt must contain the time and date that the call was attempted and the status of the outcome of that attempt. The status can be "Couldn't get through", "Positive Response",
    or "Negative Response".

    Products data The products ranges from raw building materials like steel nuts and bolts, concrete, to the assosiated equipment like drills, welders,cement mixers etc. To store all the product data in the CRM we need to record the product category (raw materials, equipment) along with the short name of the product and its full description. We also need to store the
    current cost price and purchase price for each product.

    Orders data: When a customer places an order, we retain information
    about which customer placed the order and when that order was placed, when it was filled(sent) and when payment for it it was received. Within an order a customer may have requested as many products as they like. These
    records will obviously need to be kept seperately.

    Transaction data Once an order is payed for and has been sent, we store summarised data in the transactions table. a transaction record simply
    contains the order, the date it was finalised, the total price and the profit made.


    All i want just a brief idea to develop the tables and if possible to connect them together

    Thanks to everyone who is going to help me on this.














    The following exercise is for an assigment , it's not going to work on a real world.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You may want to look at the Northwind.mdb that ships with MSAccess (I'm not sure about 2007 though but you can probably download it from MS). Look at the Relationships diagram and see how they've linked the tables together. The structure is similar to what you've described. After you've setup your structure, post it for recommendations.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Apr 2010
    Posts
    20

    Thank you SIR

    Thanks very match I will check this out right now.

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    My pleasure. Thank you for being honest and letting us know it's for an assignment (not that it would make any difference on giving you advice or not.) That's hefty requriements though for an assignment. Again, if you get stuck, just post where you're stuck and we'll do our best to help you (we often we get posters who want us to do the assignment work for them so it's appreciated you letting us know so we know how to best help you.) Of course, we can usually spot assignment type questions and those who pose as needing to do it for work, get advice such as "first try it yourself" before they are helped. We have to let you struggle a little on your own though so it sinks in a bit. But the Northwind is a good example on table structuring (and form designs) without having to buy a book. If you can't locate the Northwind.mdb, let me know. Sometimes it's buried in the folders so you may need to do some searching (or hopefully MS has it posted to download). There are also several different kinds of examples in the code bank to do different things: http://www.dbforums.com/microsoft-ac...code-bank.html (but these are mainly just coding snippets although I think I posted a few full type applications.)

    If you're using MSAccess 2007, Northwind.mdb may not be part of it. Instead, they have a whole bunch of template databases. Still, it should be downloadable somewhere on the web.

    Again, thanks for the honesty and let us know if you get stuck.
    Last edited by pkstormy; 04-08-10 at 00:35.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    To get you started though and answer your question on tables/relationships, once you've setup your table structure (which should be done first), edit the Relationships via the Relational diagram. Add your tables into the relational diagram. Then to link 2 tables together, try to keep the fields named the same in both tables, with the field in 1 of the tables being the autonumber field and the same named field in the other table an integer type. Then simply drag the appropriate field from your main table (which has the autonumber field) to the associated field in the other table (for example: the customer table might have an autonumber field called: CustomerID and then in your campaign table, you'd have a number type field also called CustomerID.) After joining these 2 fields, You'll see a line drawn between the 2 fields/tables and a popup where you can designate the Join Type, Cascade Updating, Cascade Deleting, etc... (or double-click on the joining line to edit it's properties.) When you edit the join line, and then click 'join type' button, you'll have 3 options. In your situation, you may want to return all customer records and only those that match in the campaign table (just as a quick example but you may want to set it up differently or experiment). Then Double-check that the arrow on the line between the 2 tables is pointing in the right direction.

    If done correctly, start testing the relationships by adding data manually to the tables. You'll notice you can't add any records in your relational table (ie. Campaign table in the example above) without first having a CustomerID (autonumber value) to match on first. Try out different join types to see how they work. Experiment with the cascade updating/deleting (cascade updating is usually done when joining text type fields.)

    Keep it simple though!! I mean don't join 3 fields in 1 table to 3 fields in another table. You CAN join 1 field in your main table (for example) to a relational table and then join another field in your main table to a different relational table.

    Typically, you have your 'info' type tables such as the customer table which holds all customer information. You then might have another info type table (say it's called businesses). Then you might have another table which holds just the CustomerID and BusinessID fields to put the info together (along with any other infor related to a transaction of some sort in that table.) This is a simple example but it sounds like you've got a good head on your shoulders to revise it and make it work for your situation.

    Also, don't forget to assign your primary key field to prevent duplication of data in the table design. You can select several fields and make them the primary key if need be (for example, on the Customer table, (but don't do this), you might make Firstname and Lastname both a primary key. That way, John Doe could never be entered twice (again don't do this on these fields since obviously there's more than 1 John Doe.) But you could also add City or State to the primary key so John Doe can be entered twice, just not at the same city or State. But that really isn't a good example for use of a primary key. You'll need to decide what field(s) you want to make your primary key within your tables to prevent duplication. And any data which would mean repeating several values (ie. requiring 2 or more of the same records) just to enter 1 or 2 fields should usually be separated into a relational table. In the customer example, you may decide you want multiple phone numbers for each customer and can either add them into the customer table (as phone1, phone2, etc...) or make it a relational table which has the CustomerID and phone numbers (along with phone type, extension, etc...) This is called normalizing the structure. Just be careful to not OVER-normalize the structure. Again, for example, you wouldn't want to separate City in another table and State in another table (joining them to the CustomerID). That would be a poor technique since typically you want to return the customer's full name along with their address and linking 3-4 tables together in a query just to return name/addresses is frowned upon (and not very efficient for queries.)

    I hope that helps.


    Relationships are extremely important so focus on setting them up correctly before you jump into form design (again, post what you setup and we'll help if you need it). If the table structure is setup correctly, form design is much easier.

    The BEST advice I can give you though is to NOT use any spaces in your field or table names! Also for field/table naming, don't use non-alpha characters such as: !@#$%^&*(. You'll have a much easier time coding if you don't use spaces (since then you don't need to [] every field named with a space.)

    Lastly, remember the golden rule to never use reserved words as field/table names. (such as calling a field: Date or Name or Field or Table or Join, etc...

    There's a listing of reserved words not to use in the code bank somewhere.
    Last edited by pkstormy; 04-08-10 at 01:37.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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