My Access 2007 database tracks projects which get obligated onto contracts. Projects and obligations have a many-to-many relationship, since one obligation can obligate multiple projects and one project may require multiple obligation actions to be fully obligated. The projects and obligations tables are connected through a juncture table.
Here are my tables:
Tbl_Contracts (lookup table for contracts)
ContractID
ContractNumber
Tbl_FiscalYear (lookup table for fiscal years)
FiscalYearID
FiscalYear
Tbl_Projects
ProjectID
ProjectName
FiscalYearFK
ContractNumberFK
Tbl_Obligations
ObligationID
ObligationAmount (currency type field)
ContractNumberFK
Tbl_ProjectsObligations (juncture table)
ProjectsObligationsID
ProjectsIDFK
ObligationIDFK
My end goal: for a given fiscal year chosen by the user, I need a sum of the ObligationAmount field for each contract.
To accomplish this, I first ran a totals query on ProjectName from tbl_Projects, ObligationAmount from tbl_Obligations, and ContractNumber from tbl_Contracts. I put “Sum” in the totals row for ObligationAmount, which returned the sum of ObligationAmount for each project. It worked. I called this query “qry_Obligation Totals.”
I then created a new query with ProjectName from tbl_Projects, ContractNumber from tbl_Contracts, FiscalYear from tbl_FiscalYear, and SumOfObligationAmount from qry_ObligationTotals. I put a parameter in the Criteria row of FiscalYear so the user could enter the desired fiscal year.
When I run the query, ObligationAmount still returns sums by each project. I created a report and tried to maneuver between the different grouping levels, but I still cannot get it to display a sum of ObligationAmounts across all projects for ONE contract in ONE fiscal year.
I suspect that my issue lies in the FiscalYear being connected to projects, and that the only way for Obligations to be queried by fiscal year is to include projects. So I tried adding a FiscalYearFK to my tbl_Obligations, adding that to the query, and putting a parameter in the Criteria row of this new FiscalYearFK field. This obviously doesn’t work since both FiscalYearFK fields draw from tbl_FiscalYear, and setting parameter criteria for two of the same field doesn’t work. More importantly, it’s sloppy.
(You may ask, why did I link everything back to the tbl_Projects? I did this because our office mainly uses project numbers to track the data, and I wanted the table structure to reflect this).
(I have worked on this issue for so long that I have lost objectivity and would welcome any ideas or nudges in the right direction. I am also happy to clarify anything since my loss of objectivity has likely impacted my ability to explain the problem).
Thank you all!
Here are my tables:
Tbl_Contracts (lookup table for contracts)
ContractID
ContractNumber
Tbl_FiscalYear (lookup table for fiscal years)
FiscalYearID
FiscalYear
Tbl_Projects
ProjectID
ProjectName
FiscalYearFK
ContractNumberFK
Tbl_Obligations
ObligationID
ObligationAmount (currency type field)
ContractNumberFK
Tbl_ProjectsObligations (juncture table)
ProjectsObligationsID
ProjectsIDFK
ObligationIDFK
My end goal: for a given fiscal year chosen by the user, I need a sum of the ObligationAmount field for each contract.
To accomplish this, I first ran a totals query on ProjectName from tbl_Projects, ObligationAmount from tbl_Obligations, and ContractNumber from tbl_Contracts. I put “Sum” in the totals row for ObligationAmount, which returned the sum of ObligationAmount for each project. It worked. I called this query “qry_Obligation Totals.”
I then created a new query with ProjectName from tbl_Projects, ContractNumber from tbl_Contracts, FiscalYear from tbl_FiscalYear, and SumOfObligationAmount from qry_ObligationTotals. I put a parameter in the Criteria row of FiscalYear so the user could enter the desired fiscal year.
When I run the query, ObligationAmount still returns sums by each project. I created a report and tried to maneuver between the different grouping levels, but I still cannot get it to display a sum of ObligationAmounts across all projects for ONE contract in ONE fiscal year.
I suspect that my issue lies in the FiscalYear being connected to projects, and that the only way for Obligations to be queried by fiscal year is to include projects. So I tried adding a FiscalYearFK to my tbl_Obligations, adding that to the query, and putting a parameter in the Criteria row of this new FiscalYearFK field. This obviously doesn’t work since both FiscalYearFK fields draw from tbl_FiscalYear, and setting parameter criteria for two of the same field doesn’t work. More importantly, it’s sloppy.
(You may ask, why did I link everything back to the tbl_Projects? I did this because our office mainly uses project numbers to track the data, and I wanted the table structure to reflect this).
(I have worked on this issue for so long that I have lost objectivity and would welcome any ideas or nudges in the right direction. I am also happy to clarify anything since my loss of objectivity has likely impacted my ability to explain the problem).
Thank you all!
