Not sure if I should separate fields into a new table...

[AliG]

Hi guys,

I am getting some help from plog and ginawhipp regarding my database design structure in another thread, and realized that I might need to restructure some tables too. I've attached a sample copy of my database in case anyone wants to see it in action or wants to skip the details below....here is the database setup:

This database is for a tech consulting company, and needs to keep track of their Consultants, Vendors, Task Orders, Projects, and Invoices. The company has Projects that it is working on, and each Project is subdivided into specific Task Orders. Because Consultants often continue contracting for the company once a Task Order or Project is completed and are simply assigned to a new Task Order/Project, I have separated the dynamic information (like the consultant's rate of pay) into an Assignment table so that the company can keep records of old assignments and to avoid having to constantly change Consultant records.

Since they are contractors with the company, Consultants send in invoices every month to bill for their labor and expenses. Each Task Order is allocated a certain amount of funding for labor (based off the rates of the Consultants working on that Task Order) and expenses by the client, and then the Consultants are paid from the funding pool of whichever Task Order they are assigned to. I already know that I will need to run reports which pull up how much is funded total vs how much funding has already been spent on a Consultant, Task Order, or Project basis.

Okay, so here's my dilemma: I can't figure out whether I should have a separate funding table, or if having the funding split between TaskOrderT and AssignmentT (the way it is now) is fine. TaskOrderT has 2 fields regarding funding; TOLaborFundingAmount (the total amount funded to the Task Order as a whole for labor costs) and TOTravelFundingAmount (the total amount funded to the Task Order as a whole for travel costs). AssignmentT also has 2 fields regarding funding; LaborFundingAmount (the total amount funded to the Consultant for labor on the current Task Order they're assigned to--represents a portion of TOLaborFundingAmount) and TravelFundingAmount (the total amount funded to the Consultant for travel on the current Task Order they're assigned to--represents a portion of TOTravelFundingAmount). I keep trying to think whether putting these 4 fields in their own Funding table and linking it to TaskOrderT and AssignmentT will do me any good in the end, and I just don't know. Thanks for any advice!

 

[Uncle Gizmo]

I'm working my way through it and it looks fine so far. I've got to table Contact Info, I would be in two minds on this... you could leave it as it is or you might find the method I describe on my website here could be applied and might offer you a much better interface. http://www.niftyaccess.com/excel-in-access-parts-1-2-and-3-as-video-instructions/

 

[Uncle Gizmo]

Regarding the invoice table the usual way of tackling invoices is to have a main form which carries the basic invoice information, like the invoice number and date. Then you have a subform linked to another table which shows what are normally described as "line items" these are the individual items purchased on that particular invoice.

 

[Uncle Gizmo]

Regarding the project table and I saw something similar in an earlier table but didn't mention it, but now raise it as a possible issue. You have 4 date Fields. I'm on the fence - possibly inclined to use the same technique as I suggested for the contacts to pull the dates into another table. Basically you would use the same technique as with the contacts. The dates would then be displayed in a subform on the main form. You would also need some VBA code to prefill the description of each date field which makes it slightly difficult to do, and possibly not worth doing. It comes into its own if you have to do any calculations based on those dates. If they're in the same table it's usually a lot easier to extract useful information.

 

[Uncle Gizmo]

Same as I said above but for the task order table

 

[Uncle Gizmo]

The address F form doesn't open saying that address t form is missing

 

[Uncle Gizmo]

The email F form says the email T form is missing

 

[Uncle Gizmo]

The phone number f form Says the phone number t form is missing

 

[Uncle Gizmo]

Otherwise I thought it was a very nice looking database with some nicely laid out forms. I didn't inspect them specifically but I got the feeling that some of the forms were essentially the same looking. If that's the case then it is usually a good idea to have ONE form. If you duplicate a form, then consider reducing it back to one form and add VBA to modify it so that it serves the two purposes. But I wouldn't worry about that for now, you will come to the decision that it's necessary when you find yourself changing one of the duplicate forms, and then going over and change the other duplicate form. Eventually you will get frustrated with this process and will consider the possibility of reducing duplicates into one form with which you stimulate two (or more) forms by changing it with VBA. I'm not sure if that's the case here, as I expressed earlier but it's a general principle you can employ.

 

[Uncle Gizmo]

With regard to the funding... It appears that you have the same item in separate tables and separate forms. Now... I realised to your way of thinking they might not be the same, or they might be the same thing but at different times. As a general principle if it's got the same name in one place and the same name in another place then there's probably a good case to bring the information together somewhere. You may even find it advantageous to bring what you consider separate figures which you store in adjacent columns in a table into rows in a new table. For example in the assignment table, you have rate in and rate out, this could be represented in a separate table as the assignmentID, the description and then the amount:-

AsignID ------------ Rate ---------- Amount
101 ---------------- RateIn ----------£100
101 ---------------- RateOut ---------£10
102 ---------------- RateIn ----------£1000
102 ---------------- RateOut ---------£100
103 ---------------- RateIn ----------£11
103 ---------------- RateOut ---------£1

 

[AliG]

Thank you for going into such detail Uncle Gizmo! I checked out your link in regards to the ContactInfo table, and it's a great tutorial. I don't know if I am advanced enough to make it work for this database though...some Consultants are also their own Vendors (meaning information like the email address, phone number, and address are the same for both), then there are also some Consultants with different Vendor info but the company still only has one Consultant for a given Vendor, and also there are Vendors that provide multiple Consultants to the company.

My Invoice table is keeping track of the invoices sent in to the company from Consultants who are billing for their hours worked, not invoices the company is sending out. There are no line items etc. as nothing is being sold.

In regards to the 4 date fields in my Project and TaskOrder tables, does separating those into separate tables have any advantage? I don't foresee needing to do any calculations with these dates, they are really just for reference.

The address/email/phone number forms are left over from a previous design attempt...after getting advice on rethinking my overall design, I ended up deleting their source tables and just forgot to delete the linked forms before posting, oops!

I realize the fields regarding funding have very similar names, but they will hold different values in the database. The funding-related fields in the TaskOrder table hold the total funds for a given task order, while the funding-related fields in the Assignment table hold the part of the Task Order's funding that is allocated to an individual Consultant. So, the individual funding for all of the Consultants who are working on any one Task Order should equal the Task Order's total funding when added together. Because the funding is determined by the Prime Contractor as opposed to the company itself though, I need to store both types of funding so that the company can identify any funding discrepancies easily and rectify them with the Prime Contractor. Right now, TaskOrderT and AssignmentT are related with TaskOrderID as the primary key in TaskOrderT and a foreign key in AssignmentT. I'm just not sure if I would gain any advantages by putting the funding fields in their own table and relating that back to both TaskOrderT and AssignmentT, or if it works just as well to leave them where they are.