Results 1 to 2 of 2
  1. #1
    jimmy321go is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2012
    Posts
    4

    Supplier Accounts Case Study

    Could anyone help me do this question as I am new to database?




    Our company JKL Services Ltd. needs to keep track of its supplier accounts, and has initiated a MS-ACCESS database project to keep details of suppliers, the invoices we receive from them and the payments we have sent them. Each supplier may have sent us zero or more invoices and each invoice may have zero or more payments, each payment being allocated to one invoice (i.e. there is a 1:N relationship between invoices and payments).

    In a folder in your H: drive called H:\JCdatabaseTest2

    Database design


    1. Create a suitable database design including appropriate tables, fields, data types, keys, relationships and constraints. Use AutoNumber primary key fields where appropriate. Among the data we need to store about suppliers is the credit limit (maximum amount we can owe) and balance (amount we actually owe). Each invoice and payment should have a unique identifier, be dated in the range January to March 2012 and of course include the amount. Enforce referential integrity and cascade update and delete as appropriate. You might have to modify your database design slightly in light of the questions below.


    5 marks

    Forms


    1. Create a form called Q2Suppliers for adding, deleting and maintaining suppliers. Add three suppliers.

    1 mark


    1. Create a form called Q3Ivoices for adding, deleting and maintaining invoices. Add four invoices.

    1 mark


    1. Create a form called Q4Payments for adding, deleting and maintaining payments. Add five payments.

    1 mark


    1. Create a form/subform collection called Q5aSupplier/Q5bInvoice for adding, deleting and altering the invoices. Add some more invoices for each supplier. The supplier fields on this form should be read-only.

    2 marks
    VB Code


    1. Using a VB module or otherwise, add a button to the form Q2Suppliers that calculates the total of all the invoice amounts for the current supplier and displays the total in a label.

    2 marks



    SQL Queries


    1. By using MS-Access Query Design View or otherwise, create the following SQL queries:



    1. Query7a: List the name and telephone numbers of suppliers who have sent invoices dated February 2012.

    1 mark


    1. Query7b: List the total invoice amount for each supplier. The headings for the results should be ‘Supplier number’, ‘Name’, ‘Address’ and ‘Total invoice amount’.

    1 mark


    1. Query7c: List all January invoices that have so far received no payments.


    2 marks


    Switchboard Form and Macros


    1. Create suitable macros and a form called Q8Switchboard that contains command buttons to launch each of the four forms and the three queries created above.

    2 marks


    1. Create a macro that causes the switchboard to appear automatically when the database is opened.

    2 marks



    Total: 20 marks

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,847
    Do you have a specific question?
    We are here to advise, assist, suggest, recommend, review, consider......but we don't do student assignments.

    Feel free to ask questions on specific problems or issues.

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

Similar Threads

  1. Replies: 0
    Last Post: 01-19-2012, 11:36 PM
  2. Replies: 3
    Last Post: 10-13-2011, 04:42 PM
  3. Replies: 8
    Last Post: 10-13-2011, 04:34 AM
  4. How to create an accounts table?
    By Gman11 in forum Access
    Replies: 5
    Last Post: 10-12-2011, 06:43 AM
  5. profile each user accounts?
    By jun90 in forum Access
    Replies: 1
    Last Post: 01-18-2010, 03:30 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