Share via

How to make this database work so that I can view employees, their associated projects, the contracts for those projects, and the negotiation status of each project?

Anonymous
2024-08-09T12:29:42+00:00

I have the attached relational database.

I have tried countless forms and queries and cant quite get this to work. I want a system where I can log into a form and search by an employee and find all of the projects relating to them. I want to be able to select a project and see all of the contracts related to that project. Once I have selected a contract, I want to see the negotiation details.

I had imagined this would be a main form with employee details with a combo box to search by. And below that a view of their projects, and once selected a project the contract would appear on the left view and the appropriate negotiation on the right.

The queries I have Developed do not seem to work in displaying the correct data, and the forms do not seem to display appropriately. As such, I am wondering if my relationships are correct and or my queries just wrong?

Any help would be great.

Microsoft 365 and Office | Access | For education | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Duane Hookom 26,825 Reputation points Volunteer Moderator
    2024-08-09T15:37:52+00:00

    Your ERD looks fine. Anytime I want to show related data i use subforms. Since you seem to have multiple related tables and need to offer additional views you can place the subforms on a tab control. If you need to drill down to additional details, you can open other forms using the double click on a text box.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2024-08-09T23:38:04+00:00

    I's suggest the following:

    Create a Contracts form, in single form view, and embed a NegotiationDetails subform within it.  In the parent form's header section include the following correlated unbound controls:

    1. A combo box whose RowSource property is a query on the Employees table, which returns The EmployeeID column and a computed column which concatenates the Surname and ForeName columns, i.e. Surname & ", " & Forename, and is ordered by Surmame, ForeName, Hide the EmployeeID column by setting the control's ColumnWidths property to zero.
    2. A combo box whose RowSource property is a query which joins the ProjectEmployees and Project tables, which returns the ProjectID and Project Name columns, and which is restricted by a parameter on the EmployeeID column which references the employees combo box.  Hide the ProjectID column by setting the control's ColumnWidths property to zero.
    3. A combo box whose RowSource property is a query on the Contracts table which returns the ContractID and a computed column which concatenates whatever other columns values enable a specific contact to be selected.  Restrict the query by referencing the projects combo box as a parameter on the ProjectID column.  Hide the ContractID column by setting the control's ColumnWidths property to zero.

    In the AfterUpdate event procedures of the employees and projects combo boxes requery the combo box(es) below each in the hierarchy, and set the value of each to Null.

    In the AfterUpdate event procedure of the contacts combo box requery the form with Me.Requery.

    The RecordSource property of the parent form should be a query on the Contacts table whose WHERE clause references the contracts combo box as a parameter like this:

        WHERE (ContractID = Forms!frmContracts!cboContracts OR Forms!frmContracts!cboContracts IS NULL)

    For examples of correlated combo boxes by which a form's recordset is restricted  take a look at DatabaseBasics.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    In this little demo file the second form in the section on 'retrieving data from the database' includes three correlated unbound combo boxes in its header to restrict the form's recordset on Country, Region, and City.  The fourth form in the same section illustrates the use of a combo box which lists concatenated values from multiple columns.

    To clear the selections in the unbound combo boxes include a command button in the form which sets the value of each to Null and then requeries the form.

    Was this answer helpful?

    0 comments