Multiple criteria to get data from other table?

[wes]

I’ve got another situation with filling in fields in one table based on fields in another.



First I think I have a structural problem. In my production tracking database, I have the query FundingByCategoryAndSource, which shows only the projects for which I actually need funding. Fields are ProjectName, FundingFY, FundType, FundingSource, and FundingAmount, all drawn from tblFunding. To plan and track funding obligations, I need to be able to see all of this information, PLUS the following: ObligationNeededBy; DateObligated; RQ#; AmountBilled; and DatePaid.

Initially I included these fields in the table tblFunding, but now I realize that this would capture only a single obligation for each project, whereas I can have up to 4 different obligations, which may or may not need different dates depending on the type of funding. So I think I need to handle the underlying structure differently, but I don’t know how. Separate table? Query? Report?

I need to end up with a new ObligationTracking table (or report, or whatever it ends up being) in which I can see the date each particular type of funding needs to be obligated, and from which I can run other reports and queries. The ObligationNeededBy date will come from one of four fields in tblContentproduction (PlannedDateToDesigner/Indexer/Printer/Purchase), based on a combination of the values in FundingFY, FundType, and FundingSource.

General business rules:
^Funds can be obligated for 1Design, 2Index, 3Printing, or 4Purchase (identified in tblFundType).
^Each project could have any or all of the four funding types, but only one instance of each type.
^Each funding type comes from a single funding source, either 1Pubs, 2Contract, or 3CreditCard (identified in tblFundingSource).
^A project can have any or all of the three funding sources, and may have more than one type of obligation from the same funding source.
^Three of the fund types (index, printing, and purchase) can only be funded from a specific source. One of the fund types (design) may be funded from either of two sources.
^Different funding types may be obligated in the same FundingFY, or in different FundingFYs.

How funding types and sources will feed the ObligationNeededBy field:

1) Projects with FundType=1Design could come from either FundingSource=2Contract or FundingSource=1Pubs. Either way, I need to see the date from tblContentProduction.PlannedDateToDesigner.

2) Projects with FundType=2Index will only ever come from FundingSource=2Contract. I need to see the date from tblContentProduction.PlannedDateToIndexer.

3) Projects with FundType=4Purchase will only ever come from FundingSource=3CreditCard. I need to see the date from tblContentProduction.PlannedDateToPurchase.

4) The most squirrely one: FundType=3Printing will only ever come from FundingSource=1Pubs, BUT design and printing may or may not be obligated concurrently, depending on the FundingSource and the FundingFY. Specifically:

##If a project with FundType=3Printing has no related entry with FundType=1Design, printing will be a unique obligation so I only need to see tblContentProduction.PlannedDateToPrinter.

##If a project with FundType=3Printing also has an entry for FundType=1Design which has FundingSource=2Contract, printing will be a unique obligation so I need to see the date from tblContentProduction.PlannedDateToPrinter. (See ProjectC)

##If a project with FundType=3Printing also has FundType=1Design with FundingSource=1Pubs in a different FundingFY, printing will be a unique obligation so I need to see the date from tblContentProduction.PlannedDateToPrinter. (See ProjectA)

##If a project with FundType=3Printing also has an entry for FundType=1Design which has FundingSource=1Pubs, and the two fund types occur in the same FundingFY, there will be a single obligation. So while the project will still have a PlannnedDateToPrinter in tblContentProduction, the obligation is triggered by the earlier of the two dates, which will always be design. So I’m not sure if we should still show DateToPrinter as the ObligationNeededBy for these cases and then run another query to test for this situation, or what. Note that I will also need to see the total obligation needed on that date, so would need to add the amounts in Design and Printing. (See ProjectB)

Sorry this is so long, I can see where I want to go but am not sure if I’m explaining it in a way that makes sense to anyone else. ;-) I’m attaching a sample db in 2003 as well as screenshots of the query and the relationships in case that helps.

Thanks for your help!

[jzwp11]

First I think I have a structural problem

Yes, you do and as such a structural problem lies with the tables.

FundType=1Design could come from either FundingSource=2Contract or FundingSource=1Pubs
FundType=2Index will only ever come from FundingSource=2Contract
FundType=4Purchase will only ever come from FundingSource=3CreditCard

Because of the above rules, you have to define the relationship between fund types and fund sources before even assigning it/them to the project.

tblFundTypeSources
-pkFundTypeSourceID primary key, autonumber
-FundTypeID foreign key to tblFundType
-FundSourceID foreign key to tblFundingSource

Different funding types may be obligated in the same FundingFY, or in different FundingFYs.

Can the funding mix change within a FY?
Can the funding mix change one FY to another?

[wes]

Can the funding mix change within a FY?
Can the funding mix change one FY to another?

Yes, the funding mix could change within an FY, and it could change to a different FY. We might have a project budgeted over 2 FYs and decide to combine the funds; or we may plan to pay for design through a contract and printing through Pubs, but change our minds and use Pubs funds for both design and printing. Design and Printing are really our biggest variables. Indexing and purchasing might move to another FY but they cannot ever be paid by anything other than their assigned sources.

[jzwp11]

OK, I'm not really sure how to handle that, I was thinking of perhaps a transaction like table. Each project will have many transaction events and each event might involve multiple fundtypes/sources & their corresponding amounts.

tblProjectTransEvent
-pkProjTransEventID primary key, autonumber
-fkProjectID foreign key to tblProjects
-dteEvent (date of the event)

tblProjTransEventDetail
-pkProjTransEventDetailID primary key autonumber
-fkProjTransEventID foreign key to tblProjectTransEvent
-fkFundTypeSourceID foreign key to tblFundTypeSources
-TransAmount

(from my previous post):
tblFundTypeSources
-pkFundTypeSourceID primary key, autonumber
-FundTypeID foreign key to tblFundType
-FundSourceID foreign key to tblFundingSource


I'm a little unclear as to what the obligationsneededby/dateobligated field is. Does it refer to certain planned events for which monies must be spent (i.e. a transaction that causes a reduction in the amount in the fund)?

BTW, I looked at your tblContentCreation and tblContentProduction, both tables are not normalized. It appears that both are used to track actions related to a project. If that is the case, then you have a one(project)-to-many(actions) relationship which would be handled in a separate but related table. The fields indicate that you have both a planned date and an actual (i.e. 2 dates) for each action

So a table to hold the basic actions (to Editorial, draft, review, edit, to designer, to indexer etc.)

tblActions
-pkActionID primary key, autonumber
-txtAction


tblProjectActions
-pkProjActionID primary key, autonumber
-fkProjectID foreign key to tbl project
-fkActionID foreign key to tblActions

Since an action has muliple dates associated with it we have another one-to-many relationship

tblProjectActionDates
-pkProjActDateID primary key, autonumber
-fkProjActionID foreign key to tblProjectActions
-field to denote planned or actual
-dteAction (date for the planned or actual action)

[wes]

Wow, thanks for putting so much throught into this!

I'll have to play the transaction-based approach, I had figured I could capture things like the difference between the date we planned to do something and the date we actually do it with queries, but is it better to do it based on transaction tables?

While the sources or FYs _could_ change, it would be rare, like maybe only 1 in 20 projects. I'm not concerned about when they change, I just need a snapshot of the dates obligations are needed for which projects and for which types of actions, which I figured I could do somehow with an Obligations table or query but was getting stuck. This afternoon I've been playing around with the ObligationNeededBy field and realized I don't need it as a field in a table, I can answer my "when do we need it" question through a query. Sorry I posted too soon...

I'm a little unclear as to what the obligationsneededby/dateobligated field is. Does it refer to certain planned events for which monies must be spent (i.e. a transaction that causes a reduction in the amount in the fund)?

The reason I need to see when Obligations are needed by is that I need to be able to go to my comptroller with a list of dates by which they need to their paperwork mumbo-jumbo to "obligate" or make available the funds. I can't move foward with various steps in a project until the Comptroller has actually obligated the funds, and they may or may not obligate them on time. I don't drive the obligation process but I'm beholden to it. So I have to plan when we need funds obligated, and that date is driven by the date I have planned for Design, Indexing, Printing, or Purchasing, in relationship to the source and FY. Then, I need to capture the date funds were actually obligated, so then I can run queries to find overdue obligation requests among other things.

I looked at your tblContentCreation and tblContentProduction, both tables are not normalized.

I initially had the ContentCreation and ContentProduction items combined in one table but thought it might be too cumbersome, but you're right, since they are all types of milestones it makes sense to have them together.

For those milestones that only have one action, a completed date, rather than a planned and actual, would the transaction-based approach still work? Only a few of the milestones have a planned and actual date. While we could set a target for each one I'll have enough of a problem getting the other supervisors to be attentive enough to data-entry to keep up with things as they are.

Phew, I have a lot to think about now! I appreciate you taking time for a novice who knows just enough to get into trouble.

[jzwp11]

For those milestones that only have one action, a completed date, rather than a planned and actual, would the transaction-based approach still work?

I think it will still work with a little modification. In tblProjectActionDates instead of using the words planned or actual how about planned or completed. Alternatively, you could have a table that holds all possible status for an action (planned, promised, actual, completed etc.) and just use a foreign key in tblProjectActionDates. I would not matter whether you had 1 record or many records in tblProjectActionDates related to the action.


tblProjectActionDates
-pkProjActDateID primary key, autonumber
-fkProjActionID foreign key to tblProjectActions
-fkStatusID foreign key to a table that holds the status names
-dteAction (date for the planned or actual action)

It sounds like making sure funding is available (i.e. obligating) is just another action and therefore should be tracked with the action tables I have discussed with the obligation needed by date just another date related to the action, but the funding thing is still unclear for me. Since you say you do not need to track changes in how the funding might be manipulated over the course of the project, then perhaps we can simplify that aspect by not using a transaction approach for the funding.

Since a project has multiple funding types/sources, it could be simplified to this

tblProjectFunding
-pkProjectFundingID primary key, autonumber
-fkProjectID foreign key to tblProject
-fkFundTypeSourceID foreign key to tblFundTypeSources
-AmountFunded


Of course we still need this table:
tblFundTypeSources
-pkFundTypeSourceID primary key, autonumber
-FundTypeID foreign key to tblFundType
-FundSourceID foreign key to tblFundingSource


Then again, I assume that you will actually track the monies that have been obligated, but do these also have to be tracked against each funding types/source?

[wes]

Wonderful, thanks! I do indeed have a lot to think about. I think I like where you're going, I just have to set it up with some sample data to make sure I really "get" it.

I assume that you will actually track the monies that have been obligated, but do these also have to be tracked against each funding types/source?

I need to know about the type and source for planning purposes. Once the funds have been obligated, I won't care about the type or source. Once obligated, I only care about tracking when the contractor has submitted a bill, and for how much, so I can figure out if there is a discrepancy.

I'm looking forward to playing around with all these new possibilities now! Unfortunately I may not be able to log back in to the forum for a while--if I disappear I'm not trying to be rude, I just happen to work for an employer whose employees will be legally prohibited from working for an indeterminate amount of time starting tomorrow...

Thanks for all the help, I really appreciate it!

[jzwp11]

Hopefully you won't be prevented from working for too long.

[wes]

Thanks! It's the uncertainty that makes it so exciting.

[wes]

I'm back and finally able to put a few more brain cells towards this. I'm still thinking about the new structure and have some more questions before I play with it as a sample.

In the new structure, would I be able to get the "Planned" fields to populate based on the type of project it is and the time required by each step as listed in another table? This way if we change our definition of how long it takes to do one of the steps for a product of a certain type, it will change the following steps accordingly. Could we identify certain product types for which we would do this auto calculation of the intervals for each step? Not every project will have every step; specifically, some products may not be printed, and would not need all of the steps. I created the field tblProjects.PrintedProduct (Y/N) thinking that would help, but am sure I'm not using it properly. Then again, certain product types are by nature always printed, so just by seeing project type "Large Scholarly Book" we know it will be printed, so maybe we don't need the PrintedProduct field.

Also if we do this auto calculation would users still be able to manually input a date in a Planned field? I'm thinking I would mostly need that in the PlannedDateToPurchase field, but I may find others as I move on.

What would this Planned data look like? Would it be a table? I learned the first time around that it is dangerous to use drop-downs in a table; does the same thing apply here, as the value in subsequent fields will basically have to calculate off of previous fields? Or would the Planned part of the equation be handled in a query?

I'm sorry this is taking the long way around the lake, I'm still trying to make sure I grasp the structural concepts...

Thanks for your patience!

[jzwp11]

It sounds like you are thinking about milestones for a project and associated with each milestone are a series of actions. It sounds like for each milestone there is a prescribed time in which to complete it, but you want the option to adjust that time for a specific project. Am I correct in my thinking? What are some examples of milestones and typical actions that you would use to complete them?

[wes]

It sounds like you are thinking about milestones for a project and associated with each milestone are a series of actions. It sounds like for each milestone there is a prescribed time in which to complete it, but you want the option to adjust that time for a specific project. Am I correct in my thinking? What are some examples of milestones and typical actions that you would use to complete them?

You've got it! Here's more info:

The main projects that will have milestones based on prescribed intervals for planning purposes are printed projects. Other milestones for other projects will be user-defined.

The only data that _every_ project would have are DeliverableDue, DateStarted, and Delivered. Some of the other milestones would be DraftDue, DraftComplete, SupervisorReview, PeerReview, RevisionsCompleted, ToEditorial, ToDesigner, ToIndexer, To Printer, Delivered, and PurchaseMade. (Purchase is actually an outlier; we will have very few purchases made by credit card, but I need to capture them in my overall Obligations table so just put that in as a milestone for now.) Some projects will only have a few milestones; others will have many. But our printed products are the only ones with definable intervals for the milestones; for non-printed products we need to be able to choose which milestones are relevant and enter their planned dates on a case-by-case basis. I hope to make this more automated in the future but we're taking baby steps right now.

To plan the schedule for printed products, I'd like the database to automatically calculate "Planned" dates for the various steps based on the intervals we set up for the different project types in a PubsProcessDuration table. For example, for a scholarly book we estimate it should take 11 weeks from DatetoEditorial to EditComplete; then 21 weeks from DatetoDesigner to Design Complete; then 4 weeks from DateToIndexer to IndexComplete; then 13 weeks from DateToPrinter to PrintingComplete; then 1 week from PrintingComplete to Delivered.

I would like to be able to adjust a date manually once it's entered, however, so that if we decide it's not realistic to plan for a particular date due to other workloads, I can change the one date and the other dates "downstream" will still use the same interval calculation. However, once there is an "Actual" date I'd like the downstream "Planned" dates to calculate off of the actual date, so my future planned dates reflect the real world.

For example, once we have a PlannedDateToEditorial, all of the other planned dates would show the date relative to that one. For the scholarly book example above, the PlannedDateToDesigner would be 11 weeks later; PlannedDateToIndexer 21 weeks after that; PlannedDateToPrinter 4 weeks after that; PlannedPrintingComplete 13 weeks after that; PlannedDelivery 1 week after that. Then, once we have entered a date into the ActualDateToEditorial, the PlannedDateToDesigner/Indexer/Printer/ etc would calculate from that date using the same intervals. (However if I realize we are swamped and I need more time in Editorial, I want to be able to manually change the date for PlannedDateToDesigner and see where the remaining steps fall based on the normal interval.) Once we have a date in ActualDateToDesigner, the remaining Planned fields would calculate from that.

I'm thinking this will be much easier with the transaction-based approach you are recommending, but don't quite know where to start. Clearly, I know just enough to be dangerous . . .

[jzwp11]

OK, after your explanation, I think I would approach this a little differently. I think a table to hold all possible milestones would be best and in that table you would have a field to hold the number of weeks that milestone will need. Not all milestones will have a value in that field. When you add a milestone to a project, you would carry over the value of that number of weeks field as well as the foreign key value of the milestone. This will give you the option to input the time requirements those milestones for other projects on a project by project basis.

The planned date will be calculated from the actual date + required time field, since the planned date will always be calculated, it will not need to be stored and that way you would not need to do any update queries to changed planned dates as things change.

Since all of the following are milestones, they will be records (not fields) in the milestone table: DeliverableDue, DateStarted,Delivered, DraftDue, DraftComplete, SupervisorReview, PeerReview, RevisionsCompleted, ToEditorial, ToDesigner, ToIndexer, To Printer, Delivered, and PurchaseMade.

tblMilestones
-pkMileID primary key, autonumber
-txtMilestone
-requiredtime a number field either long integer or single precision number depending on your requirement.
-fkUnitOfMeasureID foreign key to tblUnitsOfMeasure, so that you can define the requiretime value in terms of days, weeks, months etc. if needed

tblProject
-pkProjectID primary key, autonumber


tblProjectMilestones
-pkProjMileID primary key, autonumber
-fkMilestoneID foreign key to tblMilestones
-requiredmilestonetime (will be the same value if so defined in the milestone table or entered manually for other projects
-dteMilestoneComplete date the milestone was completed

tblProjectMilestoneActions
-pkProjMileActionsID primary key, autonumber
-fkProjMileID foreign key to tblProjectMilestones
other fields related to the action

[wes]

Thanks for the thoughtful reply! I am intrigued by this approach.

Since all of the following are milestones, they will be records (not fields) in the milestone table: DeliverableDue, DateStarted,Delivered, DraftDue, DraftComplete, SupervisorReview, PeerReview, RevisionsCompleted, ToEditorial, ToDesigner, ToIndexer, To Printer, Delivered, and PurchaseMade.

tblMilestones
-pkMileID primary key, autonumber
-txtMilestone
-requiredtime a number field either long integer or single precision number depending on your requirement.
-fkUnitOfMeasureID foreign key to tblUnitsOfMeasure, so that you can define the requiretime value in terms of days, weeks, months etc. if needed

I can definitely see the value of having Milestones as its own table, thanks for outlining the specific structures! But wouldn't the above structure make it so that the time interval for each milestone is always the same? My problem is that different project types would have different intervals for TimeRequired. For example, a ScholarlyPub would need 11 weeks for editing, 21 weeks for design, 4 weeks for indexing, and 13 weeks for printing. Whereas a PopularBook would need 8 weeks for editing, 13 weeks for design, 0 weeks for indexing, and 10 weeks for printing.

I can easily set up a table that defines the baseline intervals for each milestone for each project type, but then what I do with it?

Also what would tblUnitsOfMeasure look like? Would it have pkMeasureID, and the units I define as records within that table, e.g., 1 for days, 2 for weeks, 3 for months, etc? I may just leave everything in days but I'll have to play with it to see how it works.

Thanks so much for your patience, and persistence!

[jzwp11]

Since the requiredtime field will be copied from the tblMilestones to the tblProjectMilestones it will be the same but can be edited in tblProjectMilestones as needed, but now that you mention that you have project types then we can use a different approach.

For example, if a milestone can be applied to multiple project types and the timerequired is different for various combinations of milestone/project type, then we can handle that as follows:

tblMilestones
-pkMilestoneID primary key, autonumber
-txtMilestone

tblProjectTypes
-pkProjectTypeID primary key, autonumber
-txtProjectType

tblProjectTypeMilestones
-pkProjTypeMilestoneID primary key, autonumber
-fkProjectTypeID foreign key to tblProjectTypes
-fkMilestoneID foreign key to tblMilestone
-requiredtime
-fkUnitOfMeasureID foreign key to tblUnitsOfMeasure

tblUnitsOfMeasure (records for days, weeks, months etc. as you described)
-pkUnitsOfMeasureID primary key, autonumber
-txtUnitOfMeasure

Of course, you would have to define the type for each project.

tblProjects
-pkProjectID primary key, autonumber
-txtProjectName
-fkProjectTypeID foreign key to tblProjectTypes

You would still have your tblProjectMilestones, but now you relate it to the already defined tblProjectTypeMilestones

tblProjectMilestones
-pkProjectMilestonesID primary key, autonumber
-fkProjectID foreign key to tblProjects
-fkProjectTypeMilestonesID foreign key to tblProjectTypeMilestones
-dteActual

With this setup, all of your project types will have defined times for each milestone and thus for each project. Now, if you still want the ability to modify the time for a milestone, then you will need to add the requiredtime field to tblProjectMilestones above and when you bring in the fkProjectMilestoneID you will also need to bring in the requiretime. Once the value is populated, you can then change it if necessary or leave as is for those project where it should not be changed. You would control this at the form level.