Forms and/or Reports Question

[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.

 

[Arvin Meyer [MVP]]

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

 

[Guest]

Thank you. I will re-design.

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

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.

 

[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.

 

[Arvin Meyer [MVP]]

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

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.

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.

 

[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.

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

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.

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.