Need help with company database and form setup (1 Viewer)

flyers123

Registered User.
Local time
Today, 16:20
Joined
Aug 20, 2016
Messages
19
Hi all,

Here's the idea that I have and need your help with execution

I'm a contractor providing services to multiple companies

Company A has 20 employees

I have to send the invoice (no quoting required) to company A but the invoice has the following in the service section

Main form

Invoice number, my details, bill to company details

Sub Form

Date of service, employee name, service description, price,qty, total amount, discount per line item etc

When I create new invoice it should be like creating new page all the fields should be blank

Is this achievable at all.

Thank you for your time and help in advance.
 

jdraw

Super Moderator
Staff member
Local time
Today, 02:50
Joined
Jan 23, 2006
Messages
13,624
I suggest you describe your business in plain English--- lead us through
1)a typical day or
2)a service cycle.....customer requests service, order created, service performed, invoice created, ....

By reviewing/understanding your business readers can get an appreciation of your business and the tables and relationships relevant to your database/application. Tables and relationships are the heart of database --- forms and subforms are important but dependent on your underlying structure.

Good luck.
 

flyers123

Registered User.
Local time
Today, 16:20
Joined
Aug 20, 2016
Messages
19
Ok let me try better

I am an individual contractor and have sub contractors as employees

The services we provide include physiotherapy, massage, podiatry, acupuncture, yoga etc

For example: Company A would call and request to provide services to their 20 employees.

A date of service is fixed

Then my subcontractors would visit the place and provide requested services

One subcontractor may provide multiple services to one employee or only one service depends on the request

At the end of the day subcontractors would report me the services they provided

Then a invoice is issued to Company A under my name

The invoice should include the following

Main form

Invoice number, my details(business name, business registration number) bill to company details

Sub Form

Date of service, employee name, service description, price,qty, total amount, discount per line item etc

Let me know if this makes any better sense
 

CJ_London

Super Moderator
Staff member
Local time
Today, 07:50
Joined
Feb 19, 2013
Messages
12,926
When I create new invoice it should be like creating new page all the fields should be blank

You have a main form and subform, they should both be bound to their respective underlying tables (and their controls bound to the respective fields) - so when entering a new invoice, the fields should be blank ready for you to enter details.

If you don't want to see previous invoices, set the form data entry property to true. And the allowadditions property should also be set to true

Alternatively navigate to the last record - in vba you might have this code in the form load event

docmd.GoToRecord,,acNewRec
 

George-Bowyer

Registered User.
Local time
Today, 07:50
Joined
Dec 21, 2012
Messages
157
Before you can get on to forms, you need to work out what tables you're going to need to store your information. You'll need a table for your client companies. You'll need a table for your client companies' employees. You'll need a table for your contractors. A table for treatments offered (possibly more than one, if you have a really wide variety of times and prices).

You might want a table to store your bookings and allocated contractors.

Then your main business table will be the "work report" table - where you store the information about what treatments which contractor does for which employee of which company on which date.

From this, you can then generate your invoices table.

Then you need to work out your table relationships, and the queries to link them. Forms for data entry into the data tables and bookings table; then your "main" form, which will be where you enter the work reports, with drop down lists for contractor, company, employee, and treatment. Then you'll want a report to print your invoices and a form to note when they've been paid.

That's how I'd do it, anyway...
 

flyers123

Registered User.
Local time
Today, 16:20
Joined
Aug 20, 2016
Messages
19
Hey Guys,

Here's the list of relationship tables that I was able to put together as in attached screenshot

Company Table - represents my company let's say "MyCompany"
Employee Table - represents my employees or contractors
Customer Table - represents my customer "the other company or business" seeking services from MyCompany
Invoice Table - represents the invoice that will be issues to other business seeking services
Service Table - represents the services MyCompany provides i.e. Physio, Chiro, Yoga etc.
Payment Option Table - represents the payment option i.e. cash, cheque, eftpos
Payment Status Table - represents the status of payment i.e. due, paid, overdue

Invoice should include:
  • invoice number
  • invoice date
  • customer id
  • customer's business or company name or person in-charge name
  • Payment type i.e. cash, cheque, eftpos
  • Payment term i.e. "30 days"
Services provided to each employee on subform of invoice

Service sub-form should include:

  • service date
  • the other business (client's) employee name receiving service in combobox
  • service amount
  • qty
  • total amount
  • discount
Note: One person can receive multiple services i.e. Physio and Chiro on two different dates

Hopefully it makes sense

Look forward to your input.

Relationship table attached.
 

Attachments

  • Screenshot 2021-01-23 113104.jpg
    Screenshot 2021-01-23 113104.jpg
    72.7 KB · Views: 17

George-Bowyer

Registered User.
Local time
Today, 07:50
Joined
Dec 21, 2012
Messages
157
Hi, from the way I’ve interpreted what you said, I don’t think you’ve got the structure quite right yet (although you, of course, know your business better than me).

I would be looking at a structure more like the attached:

You will need tables for: your contractors, the services they offer (including prices), your client companies, your client companies’ employees, discount options, payment options, invoices, payment status and your business details. I suspect that you will also want a table to keep track of the payments that you need to make to your contractors.

The element that I think you are missing is a work report table and, in fact, I think this would actually be the main table of your database.

You will want a work report entry form so that each time a contractor tells you where they have been what they have done, you can easily enter this onto the database. The form should have drop down lists for the contractor, the date, the company visited, the service delivered and the employee treated.

Each record in the work report table should be one service delivered by one contractor to one employee of one company at one price on one date – only. This way it doesn’t matter whether a work report is one treatment to one employee, or multiple treatments to multiple employees, every treatment gets stored in the same way as a unique record in the table.

This will enable you to search your data in as many ways as you may wish. You can find all employees treated by one contractor, all employees of one company treated by all contractors, all treatments given on a certain date, all treatments of a certain price, etc, etc, etc.

From this, you can then generate your invoices, in as many formats as you or your clients wish, because you will easily be able to search which treatments were delivered on which dates by which contractors at which prices for the specified client.

Likewise, you will also easily be able to search the work reports for which treatments delivered to which clients your contractors need to be paid for and use that information to generate records in your contractor payments table.

Your work report records will want a field to record when they've been invoiced and your invoice records to show when they've been paid.

Hope this helps,

Regards,

George
 

Attachments

  • db plan 21-01-23.png
    db plan 21-01-23.png
    91.2 KB · Views: 21
Last edited:

Rene vK

Member
Local time
Today, 08:50
Joined
Mar 3, 2013
Messages
117
Create a Table 'Request' which creates a unique number, and combines, contractor, client, service, etc.
George-Bowyer's solution is looking like that.

I have an application that administers my hours worked in a project, it takes me three clicks to have an invoice: Choose Project and Month then click Make Invoice.
 

flyers123

Registered User.
Local time
Today, 16:20
Joined
Aug 20, 2016
Messages
19
Hi George,

Thank you for your time and efforts to explain the structure.

I have started something, a sample just the base structure based on what i had in my previous message.

Your attached image seems to be perfect equation.

However, I've got next to none experience other than what i have attached.

Please have a look into the attached database and the form invoice.

That is what i'm trying to achieve.

There are some combo boxes on the main form (invoice) and sub form (service list).

Perhaps not the best way to put it across to you and others with tons of experience.

However, I'm hoping it gives me something to start with your and others guidance.

Look forward to your valuable input.
 

Attachments

  • Database5.accdb
    1.3 MB · Views: 31

Rene vK

Member
Local time
Today, 08:50
Joined
Mar 3, 2013
Messages
117
Sorry if it looks like I am jumping like a frog in hot water.

I think you are trying real hard to give your first idea a chance. You are focussing at the end stage of your proces, the invoice!
Take a step back and overlook your complete process; 1. Customer places order for a service and date, 2. you choose an employee, 3. Job done, 4. Invoicing, 5. Trouble in your payments (maybe pick a pencil, a blank piece of paper and sketch the proces)

I think you have a journal with appointments as your base, make that your start. The setup of your form Invoice is a reasonable start for that.
Create tables with the name of their contents like: Customers, Employees, Services etc., consider it’s usage and keep it clean and simple.
Your ordertable can look like : Ordernumber, ClientID, ClientEmpID, yourEmployeeID, ServiceID, Date, jobDone

For consideration: (what I can think of)
Does every employee have a specialisation, it’s own service?
Does every client have a specified paymentplan/term, or do you just have a couple fixed ones?
is there any repetition involved (Employees and services, discount etc...)

If you create you other tables towards an order situation, your invoicing would be a ‘click on the button’

Btw you don’t put an employeeID tot he customers Table. It is the other way around!
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 07:50
Joined
Sep 12, 2006
Messages
14,413
@OP On your first post you say.
When I create new invoice it should be like creating new page all the fields should be blank

I don't think this is what you should be aiming for. You don't want to "edit" an invoice, and fill it in as you go. You want to just record the work you have done, and the supporting details in your data structure. You then click the button that says "produce the invoice", or probably "all the invoices", and the invoice(s) gets generated based on the data you entered.

eg.
Maybe you set up new customers, and new jobs. Maybe you include the selling price for the job.
You record the hours worked by different employees on these jobs.
You record the parts used on the jobs.
When the job is complete, you mark it as "complete"
Now your invoicing process has all the information it needs to raise an invoice for the jobs marked as "completed".

This is similar to what @Rene vK was just suggesting, I think.
 

mike60smart

Registered User.
Local time
Today, 07:50
Joined
Aug 6, 2017
Messages
727
Hi George,

Thank you for your time and efforts to explain the structure.

I have started something, a sample just the base structure based on what i had in my previous message.

Your attached image seems to be perfect equation.

However, I've got next to none experience other than what i have attached.

Please have a look into the attached database and the form invoice.

That is what i'm trying to achieve.

There are some combo boxes on the main form (invoice) and sub form (service list).

Perhaps not the best way to put it across to you and others with tons of experience.

However, I'm hoping it gives me something to start with your and others guidance.

Look forward to your valuable input.
Hi

I think you need a Form for Data Input like the example attached.

I think the ER Diagram covers your process.
 

Attachments

  • Contractor ER Diagram.JPG
    Contractor ER Diagram.JPG
    51.5 KB · Views: 23
  • Contractor.JPG
    Contractor.JPG
    72.4 KB · Views: 24

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:50
Joined
Feb 19, 2002
Messages
31,476
I would add an additional table to define your services. This allows you to define a standard price rather than having to input the price each time the service is sold. Your Invoice form can allow the price to be overwritten or even be charged as 0 or you might offer a discount of 5% for some customers so 5% could be deducted from the price as it is stored.

The invoice itself will be created as a report and printed directly to a pdf which is then saved. The report can be printed and hard-copy sent to the client or you can send the pdf via email. When the invoice is printed, the invoice table should be marked as DatePrinted. When DatePrinted is not null, your invoice form should NOT allow changes. You should be able to void the invoice and recreate it by writing queries to copy the old invoice data and make a new one.

Invoicing can be complicated and requires carefully tracking what you sent as well as what they paid. In the real world you probably also need a payments table with a child table that allows you to allocate a payment to multiple invoices. But we can start with a more simple approach.

I would actually look into QuickBooks. QuickBooks sells for a couple of hundred dollars depending on which version you buy but it is a fully capable accounting application and I'm pretty sure it can create invoices with this level of detail. It will also do far more than I think you are envisioning for this application.

QuickBooks is extensible if you are willing to pay more. It has multi-user versions and it also has an ODBC driver you can purchase. That allows you to add functionality with Access buy linking to QuickBooks data using the ODBC driver and create the reports or whatever you need that QuickBooks doesn't provide.

If you have an Accountant he will be very happy if you get QuickBooks because it will make his job easier:)
 

flyers123

Registered User.
Local time
Today, 16:20
Joined
Aug 20, 2016
Messages
19
Hi

I think you need a Form for Data Input like the example attached.

I think the ER Diagram covers your process.
Hi Mike,

Thank you for sharing the screenshots.

Is it possible to see live example of the same?

In your example if I delete an service I.e. acupuncture because it is no longer offered by my company, will it delete the historical data from previous invoices or will I be able to retain the old data unaffected.

Thank you in advance.
 
Last edited:

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 07:50
Joined
Sep 12, 2006
Messages
14,413
@flyers123

I never like deleting stuff that took you a long while to do, and you might need again.
Have a "not available" flag in your "services table", and just set this to true for "acupuncture"

Now all you need is a way to either include or exclude the "not available" services at different points of your database.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:50
Joined
Feb 19, 2002
Messages
31,476
This is where RI is your friend. If you enforce RI but not specify Cascade delete, the database engine will allow you to delete any service that was never used but will not allow you to delete services that were sold at some point. You also need a flag in your services table to indicate discontinued. I would use a date rather than just a flag though since it gives additional information but still serves the purpose of giving you a way to not allow anyone to create new sales of a discontinued service.
 

flyers123

Registered User.
Local time
Today, 16:20
Joined
Aug 20, 2016
Messages
19
@flyers123

I never like deleting stuff that took you a long while to do, and you might need again.
Have a "not available" flag in your "services table", and just set this to true for "acupuncture"

Now all you need is a way to either include or exclude the "not available" services at different points of your database.
I like your idea of not deleting and retaining history.

Now the solution I will be looking for is

For example:

The service combobox that show value from service table, how can i hide the services from new invoice and keep the outdated ones in the old invoice.

So when I update the service it should not delete from previous invoices
 

mike60smart

Registered User.
Local time
Today, 07:50
Joined
Aug 6, 2017
Messages
727
I like your idea of not deleting and retaining history.

Now the solution I will be looking for is

For example:

The service combobox that show value from service table, how can i hide the services from new invoice and keep the outdated ones in the old invoice.

So when I update the service it should not delete from previous invoices
Hi
The example attached is just a suggestion and there may be areas that need further work.

The Combobox for services only shows those services which are currently Active.
 

Attachments

  • Contractor.zip
    66.7 KB · Views: 20

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 07:50
Joined
Sep 12, 2006
Messages
14,413
You can also sort the active services to the top, and the inactive ones at the bottom, and then use code to stop a user selecting an inactive service for a new job. It's a pain with a continuous form particularly, as if you don't show the inactive options, you get blanks on certain rows.
 

Rene vK

Member
Local time
Today, 08:50
Joined
Mar 3, 2013
Messages
117
You can also sort the active services to the top, and the inactive ones at the bottom, and then use code to stop a user selecting an inactive service for a new job. It's a pain with a continuous form particularly, as if you don't show the inactive options, you get blanks on certain rows.
maybe you can use this remark in a SQL query as Rowsource for the combo? Active = 1, notActive = 0
Code:
SELECT Services.Service, Services.Price, Services.ServiceID, Services.Active
FROM Services ORDER BY Services.Active DESC, Services.ServiceID
 

Users who are viewing this thread

Top Bottom