Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Forms and/or Reports Question

Discussion in 'Information Technology' started by TAWise, Jul 28, 2006.

  1. TAWise

    TAWise
    Expand Collapse
    Guest

    My database lists the 700 employees in a particular division. There are 48
    organizations within that division and an employee could support one, two,
    three, or four organizations. I have set up an org table for the 48 orgs and
    created a roster table and form using the orgs table as four separate
    dropdown boxes to be able to choose choose the one, two, three, or four orgs
    supported for each employee. All of this works fine. Problem: I would like
    to add a field on the roster form or roster report that captures the one,
    two, three, or four selections made for the orgs. However, all I get is the
    ID numbers and not the actual orgs that were selected. I am using control
    =[Org1]&[Org2]...etc. I have tried changing column counts, bound column, and
    even tried changing fields from numbers to text and text to numbers. Any
    help would be appreciated.
     
  2. Loading...

    Similar Threads Forum Date
    USA Akaal Takhat Forms Committee to Represent US Sikhs Breaking News Sep 13, 2013
    India Poll reforms on crash course Breaking News Sep 7, 2013
    General A Conversation about the Grammatical Forms of Raam and Simran Hard Talk Dec 1, 2011
    India Anna Says Fast Only "Suspended", Wants Electoral Reforms Breaking News Aug 28, 2011
    India India Announces $5 bn Aid for Africa, Seeks Support for UN Reforms Breaking News May 24, 2011

  3. Arvin Meyer [MVP]

    Arvin Meyer [MVP]
    Expand Collapse
    Guest

    Your design is not relational. You need 3 tables, not 2.

    tblEmployees
    EmployeeID
    LastName
    etc.

    tblOrganizations
    OrganizationID
    OrganizationName
    etc.

    tblEmployeeOrgs
    EmployeeID
    OrganizationID

    Now your forms will be Mainform/Subform design and can be view either as the
    Employee with Organizations. or as an Organization with Employees.

    The suborm will have a combo box (dropdown) with 2 or more columns, the
    first of which will be the ID value and its width should be set to 0 (zero)
    to hide it.
    --
    Arvin Meyer, MCP, MVP
    Microsoft Access
    Free Access downloads
    http://www.datastrat.com
    http://www.mvps.org/access


    "TAWise" <TAWise@discussions.microsoft.com> wrote in message
    news:06DB0EA0-288A-4D56-99D9-04BA3A6DD81B@microsoft.com...
    > My database lists the 700 employees in a particular division. There are
    > 48
    > organizations within that division and an employee could support one, two,
    > three, or four organizations. I have set up an org table for the 48 orgs
    > and
    > created a roster table and form using the orgs table as four separate
    > dropdown boxes to be able to choose choose the one, two, three, or four
    > orgs
    > supported for each employee. All of this works fine. Problem: I would
    > like
    > to add a field on the roster form or roster report that captures the one,
    > two, three, or four selections made for the orgs. However, all I get is
    > the
    > ID numbers and not the actual orgs that were selected. I am using control
    > =[Org1]&[Org2]...etc. I have tried changing column counts, bound column,
    > and
    > even tried changing fields from numbers to text and text to numbers. Any
    > help would be appreciated.
     
  4. TAWise

    TAWise
    Expand Collapse
    Guest

    Thank you. I will re-design.

    "Arvin Meyer [MVP]" wrote:

    > Your design is not relational. You need 3 tables, not 2.
    >
    > tblEmployees
    > EmployeeID
    > LastName
    > etc.
    >
    > tblOrganizations
    > OrganizationID
    > OrganizationName
    > etc.
    >
    > tblEmployeeOrgs
    > EmployeeID
    > OrganizationID
    >
    > Now your forms will be Mainform/Subform design and can be view either as the
    > Employee with Organizations. or as an Organization with Employees.
    >
    > The suborm will have a combo box (dropdown) with 2 or more columns, the
    > first of which will be the ID value and its width should be set to 0 (zero)
    > to hide it.
    > --
    > Arvin Meyer, MCP, MVP
    > Microsoft Access
    > Free Access downloads
    > http://www.datastrat.com
    > http://www.mvps.org/access
    >
    >
    > "TAWise" <TAWise@discussions.microsoft.com> wrote in message
    > news:06DB0EA0-288A-4D56-99D9-04BA3A6DD81B@microsoft.com...
    > > My database lists the 700 employees in a particular division. There are
    > > 48
    > > organizations within that division and an employee could support one, two,
    > > three, or four organizations. I have set up an org table for the 48 orgs
    > > and
    > > created a roster table and form using the orgs table as four separate
    > > dropdown boxes to be able to choose choose the one, two, three, or four
    > > orgs
    > > supported for each employee. All of this works fine. Problem: I would
    > > like
    > > to add a field on the roster form or roster report that captures the one,
    > > two, three, or four selections made for the orgs. However, all I get is
    > > the
    > > ID numbers and not the actual orgs that were selected. I am using control
    > > =[Org1]&[Org2]...etc. I have tried changing column counts, bound column,
    > > and
    > > even tried changing fields from numbers to text and text to numbers. Any
    > > help would be appreciated.

    >
    >
    >
     
  5. TAWise

    TAWise
    Expand Collapse
    Guest

    I made the recommended changes, but I still have the same type of problem.
    If an employee supports two or more orgs, the Organization Form with the
    Employees subform only recognizes the first org that was selected for that
    employee. It does not capture the additional orgs (2, 3, or 4) that were
    selected for that employee. Also, I still cannot get a report to accept the
    actual org numbers...it still will only give me the ID numbers.

    Any advice is appreciated.



    "TAWise" wrote:

    > My database lists the 700 employees in a particular division. There are 48
    > organizations within that division and an employee could support one, two,
    > three, or four organizations. I have set up an org table for the 48 orgs and
    > created a roster table and form using the orgs table as four separate
    > dropdown boxes to be able to choose choose the one, two, three, or four orgs
    > supported for each employee. All of this works fine. Problem: I would like
    > to add a field on the roster form or roster report that captures the one,
    > two, three, or four selections made for the orgs. However, all I get is the
    > ID numbers and not the actual orgs that were selected. I am using control
    > =[Org1]&[Org2]...etc. I have tried changing column counts, bound column, and
    > even tried changing fields from numbers to text and text to numbers. Any
    > help would be appreciated.
     
  6. Arvin Meyer [MVP]

    Arvin Meyer [MVP]
    Expand Collapse
    Guest

    Use a query, to supply the values for the report.

    In the join table, the one I identified as:

    tblEmployeeOrgs
    EmployeeID
    OrganizationID

    you need 1 record for each combination of Employee and Organization. There
    is a many-to-many relationship between the 2 primary tables (which is the
    reason you need the join table). I made a small demo that will show you how
    it works and put it up on the Access MVP website:

    http://www.accessmvp.com/Arvin/ManyToMany.zip
    --
    Arvin Meyer, MCP, MVP
    Microsoft Access
    Free Access downloads
    http://www.datastrat.com
    http://www.mvps.org/access

    "TAWise" <TAWise@discussions.microsoft.com> wrote in message
    news:0722228E-A570-4CEB-9CC6-8C7B2146EEEA@microsoft.com...
    >I made the recommended changes, but I still have the same type of problem.
    > If an employee supports two or more orgs, the Organization Form with the
    > Employees subform only recognizes the first org that was selected for that
    > employee. It does not capture the additional orgs (2, 3, or 4) that were
    > selected for that employee. Also, I still cannot get a report to accept
    > the
    > actual org numbers...it still will only give me the ID numbers.
    >
    > Any advice is appreciated.
    >
    >
    >
    > "TAWise" wrote:
    >
    >> My database lists the 700 employees in a particular division. There are
    >> 48
    >> organizations within that division and an employee could support one,
    >> two,
    >> three, or four organizations. I have set up an org table for the 48 orgs
    >> and
    >> created a roster table and form using the orgs table as four separate
    >> dropdown boxes to be able to choose choose the one, two, three, or four
    >> orgs
    >> supported for each employee. All of this works fine. Problem: I would
    >> like
    >> to add a field on the roster form or roster report that captures the one,
    >> two, three, or four selections made for the orgs. However, all I get is
    >> the
    >> ID numbers and not the actual orgs that were selected. I am using
    >> control
    >> =[Org1]&[Org2]...etc. I have tried changing column counts, bound column,
    >> and
    >> even tried changing fields from numbers to text and text to numbers. Any
    >> help would be appreciated.
     
  7. TAWise

    TAWise
    Expand Collapse
    Guest

    Thank you again for your assistance. I have made the necessary changes and
    my database works like a charm and my sanity is restored.

    "Arvin Meyer [MVP]" wrote:

    > Use a query, to supply the values for the report.
    >
    > In the join table, the one I identified as:
    >
    > tblEmployeeOrgs
    > EmployeeID
    > OrganizationID
    >
    > you need 1 record for each combination of Employee and Organization. There
    > is a many-to-many relationship between the 2 primary tables (which is the
    > reason you need the join table). I made a small demo that will show you how
    > it works and put it up on the Access MVP website:
    >
    > http://www.accessmvp.com/Arvin/ManyToMany.zip
    > --
    > Arvin Meyer, MCP, MVP
    > Microsoft Access
    > Free Access downloads
    > http://www.datastrat.com
    > http://www.mvps.org/access
    >
    > "TAWise" <TAWise@discussions.microsoft.com> wrote in message
    > news:0722228E-A570-4CEB-9CC6-8C7B2146EEEA@microsoft.com...
    > >I made the recommended changes, but I still have the same type of problem.
    > > If an employee supports two or more orgs, the Organization Form with the
    > > Employees subform only recognizes the first org that was selected for that
    > > employee. It does not capture the additional orgs (2, 3, or 4) that were
    > > selected for that employee. Also, I still cannot get a report to accept
    > > the
    > > actual org numbers...it still will only give me the ID numbers.
    > >
    > > Any advice is appreciated.
    > >
    > >
    > >
    > > "TAWise" wrote:
    > >
    > >> My database lists the 700 employees in a particular division. There are
    > >> 48
    > >> organizations within that division and an employee could support one,
    > >> two,
    > >> three, or four organizations. I have set up an org table for the 48 orgs
    > >> and
    > >> created a roster table and form using the orgs table as four separate
    > >> dropdown boxes to be able to choose choose the one, two, three, or four
    > >> orgs
    > >> supported for each employee. All of this works fine. Problem: I would
    > >> like
    > >> to add a field on the roster form or roster report that captures the one,
    > >> two, three, or four selections made for the orgs. However, all I get is
    > >> the
    > >> ID numbers and not the actual orgs that were selected. I am using
    > >> control
    > >> =[Org1]&[Org2]...etc. I have tried changing column counts, bound column,
    > >> and
    > >> even tried changing fields from numbers to text and text to numbers. Any
    > >> help would be appreciated.

    >
    >
    >
     

Share This Page