Forms and/or Reports Question

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/13748-forms-and-or-reports-question.html
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13748
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.

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

Your design is not relational. You need 3 tables, not 2.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13748

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" 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13748
> help would be appreciated.

Thank you. I will re-design.

"Arvin Meyer [MVP]" wrote:

> Your design is not relational. You need 3 tables, not 2.
>
> tblEmployees
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13748
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13748
> http://www.mvps.org/access
>
>
> "TAWise" 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.
>
>
>

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13748
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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13748

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13748
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" 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13748
>> help would be appreciated.

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13748
> Microsoft Access
> Free Access downloads
> http://www.datastrat.com
> http://www.mvps.org/access
>
> "TAWise" 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13748
> >> 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.
>
>
>