Hello,
First of all sorry for my English I'm French and I'm completely new to Access but I have worked for a few years on Excel. I've finished reading a book on how to begin to design a database but it's the first time I have to design it for real!
To describe the scope of the project, here's the goal for each one in my company :
- Manager/Business developer (don’t want to work with Access) :
o Update their budget Excel file with all active contracts (an active contract during July 2015 can be counted as a potential contract for 2016)
- Suspension tech (can work with Access) :
o View all suspension (including historical) and add/modify them
- Assistant (can work with Access) :
o View all groups/clients (inc. historical) and their respective contracts (inc. historical) and their respective end-users. Maintain this information (can add and modify).
o Maintain information about contacts, distributors and business developer of those groups/clients
o Recap the invoices and have a global view about what is left to invoice
o Maintain information about overdue (for example historical comments for each overdue)
I have managed to design tables and relations for suspension, contact and overdue aspects. I hope that I have done correctly but now I’m facing a major problem: linking invoicing, contract (with price detail) and end-users!
I struggle to design correctly this part, maybe because of the many-to-many relationship when we start to talk about historical information!
How do this work at the moment ?
Open the Excel file enclosed –just ignore the budget sheet for now–. Each month the assistants copy the last existing sheet in the invoicing Excel file and update the invoice ID, the comments and change the color for the corresponding month. You have an extract of that file on the Invoicing sheet, as you can see it is the September invoice sheet as the September 2015 is already all green, it means that all the invoices were sent. The whole Excel file can retrace all historical invoices, we just have to switch sheets.
My initial task was linking within Excel the budget files with the invoicing files but to be honest it’s a real mess: each business developer has his own budget spreadsheet stored locally and each assistant has her own invoicing spreadsheet –because of writing permission issues, if we joined all files together they would be 3 working on the same excel file–… so I thought about creating a relational database! But now I’m struggling with the most important part I think…
The problem is: some clients want to be invoiced for several end-users, it depends on their contract, but we need to keep historical information about those contracts and even more important the price detail. It is a many-to-many relationship! As you can see on the Budget sheet, the manager needs another view of those contracts but it’s almost the same as the invoicing file.
ExcelFileExtract.zip
EDIT: Cross-posted on Acces-Programmers forum
Please feel free to correct my proposition for the tables design and give me advices for the missing part !
Thank you,
RaSk