Supplier Accounts Case Study

[jimmy321go]

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

[orange]

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.