MS-Access Database, Forms and report generating RRS feed

  • Question

  • I am a beginner and new to this forum, need help to create form and report.

    My task is to create database with forms and reports, where, we open New Jobs and each jobs remains with us for 1 year. Same times, we invoice it on monthly basis where we maintain the every day stock reporting for it coupled with outstanding sheet where all unpaid invoices shall be shown.

    I have already created a Database with the name of "tblMucMainDB" and a form with the name of "frmMucJob" to fetch the data for each job along with a field name Service Charges which I shall use for Billing....

    Now ,I need to generate monthly invoices/bills (Reports) for each job along with stock reports on daily basis and I don't know how may I do this... 

    In next step, I will be needing an outstanding sheet where all unpaid data would be shown.. 

    If any one help me, I will highly appreciate it and will be very thankful...

    Friday, January 24, 2020 9:00 AM

All replies

  • It is going to be difficult for any of us to help you because we don't know anything about your business or how it works, but based on what little you have told us, let be give you a suggestion on how I believe your database should be designed so you have a proper start from the beginning.

    It sounds like you have employees who do jobs for clients and you wish to keep track of their hours so you can invoice those hours to your clients. Is that correct? If this is the case, your database design should look something like this:

    You should have a Clients table, a Jobs table and an Employees table since each client may have multiple Jobs and each Job may have multiple Employee hours. The clients table has a one-to-one relationship to the Jobs table with the ClientID field and the Jobs table has a one-to-one relationship with the Employees table with the JobID field. Referential Integrity is enforced in both cases. That means as you add or delete records in any of the tables, then the related tables are also updated.

    This is just a suggested design to get you started in the right direction. You will need to add fields to each table as your needs require. I cannot tell you here how to design your input forms or invoicing reports, but once you have input Client, Job and Employee data, you will be able to design an invoice report based upon that data. Build your tables and create your relationships first. Then design input forms for adding/deleting Clients, Jobs and Employees. Once you have input some test data and are satisfied it works, you can create invoicing data based upon how many hours each employee worked for each client job.

    Saturday, January 25, 2020 4:14 PM