Results 1 to 6 of 6
  1. #1
    dantnz is offline Novice
    Windows 7 Access 2007
    Join Date
    Nov 2010
    Posts
    4

    Building customer monthly program (access newbie)

    Hello Everyone,



    First off, sorry as this is all most likely going to seem very elementary, but I am having trouble with the access database that I am trying to build. I am reasonably computer savvy but also quite new to access. What I am trying to do is build a system for our (small) company, and making it in such a way that once it is finished, everything can be achieved using forms, for the people who might have more trouble. I have been reading some books, searching forums, doing tutorials etc, but am still stumped.

    We offer chemical products to our customers are to be used weekly, and what I want to produce is a way to be able to build them a program/report in access that tells them what to use when, something like this:

    JAN:
    Week 1:
    product 1
    product 2
    product 3

    Week 2:
    product 1

    Week 3:
    product 2
    product 3

    Week 4:
    product 1

    It would have this for each month for a year. I would like to create a form where I can first add a client to the database, and then have a second form that first has a dropdown list of the clients at the top, and once a client is selected, I can build and edit their list of products and yearly plan, and then print a report for them. There is also going to be some chemical calculations, prices, etc, but I think once I have figured this part out I should be good to go.

    Sorry again, I know this is incredibly long and most likely obvious, and thanks for your help in advance.

  2. #2
    Rixxe is offline Blessed Bear
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Location
    London (Essex ... yep!)
    Posts
    184
    Morning,

    What you are asking is kinda vague, as no one (unless they have an insane amount of time) will build the whole thing for you...

    The best idea is to create the 2-3(?) tables that you will need:
    1: Client details
    2: Product details(?), price etc
    3: (You mentioned seeing the months of the sales etc, probably dont need a table for this, can just work in queries i would assume)

    Once you have all the basics, try and create some queries, forms, reports as you described.

    Then, if at any point you get stuck, come back with a specific question. This makes it easier to learn, and you will probably get a reponse that is very helpful.

    Good luck.

    Rixxe

  3. #3
    A S MANN is offline Advanced System Analyst
    Windows XP Access 2007
    Join Date
    Oct 2010
    Location
    India
    Posts
    161
    Create the following Tables
    1:Customer_Table with following fields
    Customer_ID (Primery Key)
    Customer_Name
    Phone_No
    Other Details of Customer one by one
    2:Week_Table with following fields
    Week_No (Primery Key)
    Month Name
    Other Details of Week one by one
    3:Product_Table with following fields
    Product_ID (Primery Key)
    Product Name
    Other Details of Product one by one
    These need to be link by relationship
    Create the table and let me know i will guide the remainng part step by step

  4. #4
    dantnz is offline Novice
    Windows 7 Access 2007
    Join Date
    Nov 2010
    Posts
    4
    Currently I have a table for customers (and a form that allows the addition of new customers), a table for products, and a table for orders/dates. I'm not quite sure how to go about using a table for the week/month thing, as there would be multiple products used by multiple customers each week, so what fields would I have in that table? Sorry that it is still a bit vague.

    What I want to do is create a form where I first select the customer in the main form, and then show their schedule of products in a subform/datasheet, and be able to add and edit products on this subform.


    The fields in order form are:
    - product
    - date product will be used
    - customer who will be using the product

    So what I would like to achieve is this:
    - Combo box with all customers in the top of the main form.
    - Have another area (not sure if this should be in main form or a subform) which has a combo box of all products, a text box where you can enter the date which the product will be used, and an add button.
    - Once add button is pressed, I want it to use the selected customer from the customer combo box, the selected product from the products combo box, and the date that product will be used, and place these three things into the order form.
    - Once add button has been pressed, the products combo box and date text box will reset, but the customer combo box will not.
    -There will be a subform (datasheet?) showing the customer's list of products to be used weekly, pulled from the orders table, at the bottom of the form.

    I have managed to figure out how to make a form that you can select a customer from a combo box and view their orders/program from the orders table. I also figured out how to make a form to add products to the orders table, I just can’t figure out how to merge these two different forms into 1 usable form.

    Sorry about the long post again, hopefully that question now makes a bit more sense?

    Thanks

  5. #5
    A S MANN is offline Advanced System Analyst
    Windows XP Access 2007
    Join Date
    Oct 2010
    Location
    India
    Posts
    161
    You need to design the DB step by step and not all in one step and get confuse at the end of day. Form and Look up box are quite elementry thing and come much later .Follow the step and than apply it to your DB one by one
    Create the following Tables ou use existing table
    1:Customer_Table with following fields
    Customer_ID (Primery Key)
    Customer_Name
    Phone_No
    Other Details of Customer one by one
    2:Week_Table with following fields (table for orders/dates will have wrong inerpetation and may confuse you more)
    Week_No (Primery Key)
    Customer_ID ( Copy and paste Customer_ID from Customer_Table, Remove No Duplicate property by Allow Duplicate) Foreign Key
    Month Name
    Other Details of Week one by one
    3:Product_Table with following fields
    Product_ID (Primery Key)
    Week_No ( Copy and paste Week_No from Week_Table , Remove No Duplicate property by Allow Duplicate) Foreign Key
    Product Name
    Other Details of Product one by one
    These need to be link by relationship
    create realtionship.
    One [Customer_Table].[Customer_ID] = Many [Week_Table]. [Customer_ID] And
    One [Week_Table]. [Week_No] = Many [Product_Table]. [Week_No]
    Than create a Query with all the three Table as source
    add all the fields to Query.
    Than create a Form with Record source as Customer_Table
    Create the Above and let me know
    The process is lengthy and time consuming but correct procedure only will give correct result.

  6. #6
    A S MANN is offline Advanced System Analyst
    Windows XP Access 2007
    Join Date
    Oct 2010
    Location
    India
    Posts
    161
    Is is possible to send me a copy of your DB to my e mail Id. The table for orders/dates and Week_Table are contradictory and need to sort out I wish to see your Dummy data with DB. The above design may need to rework as per data need. my mail id is asm_sasmira@yahoo.com

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

Similar Threads

  1. Replies: 1
    Last Post: 09-09-2010, 04:40 PM
  2. Import Monthly Backlog - Excel
    By eww in forum Import/Export Data
    Replies: 2
    Last Post: 08-17-2010, 02:38 PM
  3. Is Access the right program for me?
    By Cole in forum Access
    Replies: 1
    Last Post: 08-07-2010, 08:47 AM
  4. Automated Email Reports - monthly no clicks
    By Bamber in forum Reports
    Replies: 1
    Last Post: 05-12-2010, 12:34 PM
  5. Access chat program
    By cuneyt in forum Programming
    Replies: 1
    Last Post: 09-08-2009, 02:44 PM

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