Combine two fields?

[sjl]

Hi,

Each project in my database has (up to) two managers: MgrI and MgrII.

We need a report that is to be grouped by Manager.

How could I create reports based on a manager's name...regardless if he/she is MgrI or MgrII.

E.g. ............Mgr I ................. Mgr II
Project 1: .........Jane Doe.............John Smith
Project 2: .........Ken Lopp ............Jane Doe
Project 3: .........John Smith...........Ken Lopp

So the report on Jane Doe needs to report on her Project 1 (where she is Mgr I), and her Project 2 (where she is Mgr II). ...

thanks in advance,
Sarah

 

[mhartman]

Sarah, run the report wizard to build your report. When the wizard asks if you to have any grouping, this where you would select your managers.

Regards
Mark

 

[sjl]

Mark,
Thank you.
This is what I did, but that pulls in both fields separately--i.e., MgrI and MgrII.

Then, e.g., when Jane Doe is MgrII, it does not come up in separate MgrII report, nor with the MgrI report of Jane Doe.

Is there an Expression I can enter in the Sorting and Grouping window perhaps that would group on each name? I tried "Mgr1 or MgrII" as the Expression, but this did not work as want.

thanks
sjl

 

[mhartman]

Sarah, what you do then is base a query on your table that returns only the records that are managers.

Then group on the individual names. Also, if this does not work, if your post a sample of your database, I will attempt to fix it.

Regards
Mark

 

[WayneRyan]

Sarah,

You really shouldn't have repeating fields, it leads to this kind of problems.

Your workaround is:

Select MgrI As Manager, SomeField, SomeOtherField, LastField
From   YourTable

UNION

Select MgrII As Manager, SomeField, SomeOtherField, LastField
From   YourTable

The resulting query will return only one manager column and should give you
what you want.

hth,
Wayne

 

[sjl]

Sarah, what you do then is base a query on your table that returns only the records that are managers.

Then group on the individual names. Also, if this does not work, if your post a sample of your database, I will attempt to fix it.

Regards
Mark

Mark, I set up a select query which returns only the records that are managers (all records have managers...some have both MgrI, some with both MgrI and MgrII). So, the result is two columns: Mgr I (all rows have names), Mgr II (some rows have names).

Where do I "group on the individual names"? In a crosstab query?

thanks,
Sarah

 

[mhartman]

Hello Sarah

I have enclosed an example of one way to do this: The database is of the relational type. IE: One job may have many managers. This is what Wayne was referring to above.

Take a look at the report and see if this is what your after.
This was made in Access 2003
Regards
Mark