We currently have a database that we use a work, while it does its purpose, the database design is not the best, nor does it follow best practices in my opinion.
Therefore, I am in the process of redesigning it from scratch. While I can do this, I need a little help here and there.
I should explain what we use the database for:
We provide interpreters to who ever requires them. We use the database to keep track of appointments whether they took place or not, payments to the interpreters, charges to the Service User (here on in referred to as SU). The Organisation and Departments that use our services. So for example, might have Organisation A, but only Department B will use us. Invoice address; sometimes addressed to a specific person, but usually only the Finance department.
The tables:
‘tblBookings’ <- Details of bookings, name, time, date etc etc
‘tblFinance’<- Details of payment incoming and outgoing.
‘tblInt’ <- List of all our interpreters, how much they get paid an hour, what langs they speak etc etc
‘tblInvoice_Addresses’ <- Details of the people we invoice, who use our services
‘tblInvoice_Dates’ <- To keep track of what invoices have been sent to Finance, for which week ending etc etc
‘tblService_Users’ <- List of all the SUs that use our services.
‘tblVenue’ <- A list of common venues (where the intepretation will take place)
The first major problem is that all payments incoming and outgoing is mish mashed within the ‘tblBookings’. I plan to separate this out in ‘tblFinance’. Then use a query to pull relevant information for Finance from ‘tblBookings’ and ‘tblFinance’ then combine into one form ‘frmFinance’.
Couple of things I would like to implement, but don’t know how:
Q1) I would like to have cascading combo boxes on ‘frmBookings’ (based directly off ‘tblBookings’. I think this is what they are called. So when you select Organisation A, you are only able to select Department X, Y and Z from the following combo box. I assume this is done somewhere on the form controls (maybe in row source and stuff like that?). Information required for this bit of magic is all contained in ‘tblService_Users’.
Q2) When you select a Language (lets say French) for a booking the only interpreters you can select for the booking (from the cboIntID) are the ones who speak French (would this be cascading combo boxes again or a fancy SQL statement with a WHERE clause?).
Q3) Regarding ‘frmFinance’, I would like some of the fields here to automatically populated such as payments for interpreters. What our interpreters are paid an hour will be listed in ‘tblInt’. How do I achieve this? Any agencies we use need to be exclude from this.
Q4) Some of the fields in ‘frmFinance’ are being calculated on the form, but no actual data is being inputted into ‘tblFinance’. An example of this would be the field ‘IntTotalFee’. This amount is obtained by adding together: =[IntFee] + [IntTravel] + [IntAddtionalCost]. While this is calculated on the form perfectly fine, I would like this data to be stored in the respective table.
Q5) Been thinking about creating a tblLang which will hold all the languages that we provide. I would also like to use a relationship of sorts to tie each interpreter with whatever languages they speak (many-to-many?). Perhaps this could be half of a solution to Q2.
It might be worth mentioning that while we have our own interpreters, we do use agencies. So anything that filters out our interpreters via a filter will need to exclude any agencies we use.
Thanks for any help!
Therefore, I am in the process of redesigning it from scratch. While I can do this, I need a little help here and there.
I should explain what we use the database for:
We provide interpreters to who ever requires them. We use the database to keep track of appointments whether they took place or not, payments to the interpreters, charges to the Service User (here on in referred to as SU). The Organisation and Departments that use our services. So for example, might have Organisation A, but only Department B will use us. Invoice address; sometimes addressed to a specific person, but usually only the Finance department.
The tables:
‘tblBookings’ <- Details of bookings, name, time, date etc etc
‘tblFinance’<- Details of payment incoming and outgoing.
‘tblInt’ <- List of all our interpreters, how much they get paid an hour, what langs they speak etc etc
‘tblInvoice_Addresses’ <- Details of the people we invoice, who use our services
‘tblInvoice_Dates’ <- To keep track of what invoices have been sent to Finance, for which week ending etc etc
‘tblService_Users’ <- List of all the SUs that use our services.
‘tblVenue’ <- A list of common venues (where the intepretation will take place)
The first major problem is that all payments incoming and outgoing is mish mashed within the ‘tblBookings’. I plan to separate this out in ‘tblFinance’. Then use a query to pull relevant information for Finance from ‘tblBookings’ and ‘tblFinance’ then combine into one form ‘frmFinance’.
Couple of things I would like to implement, but don’t know how:
Q1) I would like to have cascading combo boxes on ‘frmBookings’ (based directly off ‘tblBookings’. I think this is what they are called. So when you select Organisation A, you are only able to select Department X, Y and Z from the following combo box. I assume this is done somewhere on the form controls (maybe in row source and stuff like that?). Information required for this bit of magic is all contained in ‘tblService_Users’.
Q2) When you select a Language (lets say French) for a booking the only interpreters you can select for the booking (from the cboIntID) are the ones who speak French (would this be cascading combo boxes again or a fancy SQL statement with a WHERE clause?).
Q3) Regarding ‘frmFinance’, I would like some of the fields here to automatically populated such as payments for interpreters. What our interpreters are paid an hour will be listed in ‘tblInt’. How do I achieve this? Any agencies we use need to be exclude from this.
Q4) Some of the fields in ‘frmFinance’ are being calculated on the form, but no actual data is being inputted into ‘tblFinance’. An example of this would be the field ‘IntTotalFee’. This amount is obtained by adding together: =[IntFee] + [IntTravel] + [IntAddtionalCost]. While this is calculated on the form perfectly fine, I would like this data to be stored in the respective table.
Q5) Been thinking about creating a tblLang which will hold all the languages that we provide. I would also like to use a relationship of sorts to tie each interpreter with whatever languages they speak (many-to-many?). Perhaps this could be half of a solution to Q2.
It might be worth mentioning that while we have our own interpreters, we do use agencies. So anything that filters out our interpreters via a filter will need to exclude any agencies we use.
Thanks for any help!
