Query field based upon dates in other fields

[Demiurgous]

I've got a DB that stores some budget planning information. Some lines would be for a single year, some would be up to 5 years at a time.



I've got the following fields: P1_Begin, P1_End, P1_Cost - these repeat for all 5 periods.

I'm curious if I can have a query to find planned costs for a given Fiscal Year. In other words, for each record in the table, find which Period starts 7/1/2015 and return the cost.

Conversely, should I simply redesign the table so that the P1_Cost is adjusted to FY15-16_Cost? I only need about 12 other fields, so I could theoretically have 243 remaining - more than enough to have what I'd consider more static columns.

Any insight is greatly appreciated.

[JoeM]

Assuming that P1_Begin and P1_End are both valid date fields, you would just need to add criteria to your query.

So for your example, under the P1_Begin field, you would add the Criteria:

<=#7/1/2015#

and under the P1_End field, you would add the Criteria (on the same Criteria line):

>=#7/1/2015#

[Demiurgous]

Assuming that P1_Begin and P1_End are both valid date fields, you would just need to add criteria to your query.

Joe, I do appreciate that greatly.

However, the issue is that some records need to return P1_Cost, while others would need to return P3_Cost, or P4_Cost, or whichever period.

I'm curious as to how to add all of those Criteria into a single query.

I hope this makes sense.

[JoeM]

The reason why it is so difficult is because your data is not normalized. You should have have multiple fields in a single table returning the same type of data (i.e. should not have 6 cost fields).

Here is a link on database design: http://sbuweb.tcu.edu/bjones/20263/A...sDB_Design.pdf

[Demiurgous]

The reason why it is so difficult is because your data is not normalized. You should have have multiple fields in a single table returning the same type of data (i.e. should not have 6 cost fields).

Appreciate the link and information. I will read through it now.

When budgeting costs by Fiscal Year for up to 5 years at a time, what would the best "normalized" layout be, then? Repeating rows isn't a way to normalize data. Having an ever-growing list tables for each Fiscal Year with a relational link to a Line Item of tables doesn't seem to be the best normalization approach.

I'm more than happy to adjust the layout of my table(s) if need be. I'm just curious how I should go about that. Right now it looks like:

ItemID	P1_Begin	P1_End	P1_Cost	P2_Begin	P2_End	P2_Cost	Etc
1	7/1/14	6/30/15	15000	7/1/15	6/30/16	16000
2	7/1/15	6/30/16	2000	7/1/16	6/30/17	4000

[JoeM]

Don't fall into the trap of thinking that more rows is bad. Often times in normalizing data, you will increase the number of data rows you have, but decrease the number of fields that you have.

In a nutshell, you will want to break each record down to its smallest unit, and not have any repeating type data fields within the same row.
In your case, I think it may be something like this:

ItemID, YearNumber, PeriodNumber, PeriodStart, PeriodEnd, PeriodCost

So basically, you will want to break it down so that you only have one Cost value per record.

[Demiurgous]

Interesting approach.

That would mean a new table for the cost data, so that I can keep various other fields related to describing/identifying each Line Item. Certainly wouldn't want to store all of that data multiple times.

I'm open to the idea and I'll play around with it.

[JoeM]

Interesting approach.

It is actually the recognized normalized approach. Many professional programmers don't want anything to do with data that is not normalized!

That would mean a new table for the cost data, so that I can keep various other fields related to describing/identifying each Line Item. Certainly wouldn't want to store all of that data multiple times.

Not sure what you mean. Care to post an example of that you mean here (just want to make sure that you don't have design issues with other tables that are causing you unnecessary work).

[Demiurgous]

I'm all for doing things according to accepted best practices.

Care to post an example

I can't upload due to the firewall at work. I'll see if I can strip stuff down and upload an example when I get home tonight.

Essentially, we're talking about Agreements that come with a cost. So I'd have an AgreementID, ProviderID, Term_Begin and End fields, then the Periods mentioned above, as well as an assigned Staff_Member, Services, whether this a SubAgreement (and what the Master would be, if a sub), and whether it has Subs under it (boolean).

Fields then would look like:
ID (Primary Key, unique)
ProviderID (Foreign Key)
Term_Begin (Date)
Term_End (Date)
Total_Cost (Currency)
Assigned Staff (Foreign Key)
Services (Foreign Key)
IsSub (Boolean)
Master (Foreign Key, only if IsSub=True)
HasSubs (Boolean)
Period Info for 5 periods (Begin, End, Cost for each)

Hope that makes sense.