Hi All,
I have begun to explore Access so I am still understanding basics of a Database's design. And I am trying to create a database to store our Service and fees per customer. The problem I am facing is with its design. I am unable to figure out how to have a list of services available to different customers, but at different prices. The difference in prices is due to the amount of work done on each service and based on what prices are negotiated with each customer. I also want to be able to have a table/form/datasheet view of all customers, and the services each of them are provided. So to be able to check on/off a service so it shows up or does not show up for those specific customer.
Right now I have a table each for Company, Services and Prices.
Under Company I have:
CVR_Number (PK)
Company_Name
HR_Contact1
HR_Contact2
HR_Contact3
HR_Contact4
AssigneeID (FK)
Price ID (FK)
Service ID (FK)
Services
Under Services:
ID (PK)
Service (List of all services)
Comments
PriceID (FK)
Company ID (FK)
Under Prices:
ID_Prices (PK)
CVRNumber
Price
Service ID (FK)
There are also tables for Employees (for each Company), Dependents to the employees and Case related information per employee.
My final aim is to use the database to create a list of cases we have on each company. As each company will have several employees that we assist with but those employees are authorized with different services.
And then to finally have an Monthly orders view with list of cases and the respective services and fees and whether those cases were invoiced.
Please feel free to suggest a link to a standard template if that would be a better place to start with.
Thank you so much.