Report Detail

[TinleyParkILGal]

Professionals:

I am trying to design a report from 2 tables. My report is based on a query
of the two tables.

Table 1: Company

Table 2: Company Officers

What I am attempting to do is create a report that shows the company name
and its officers, however, I need a position for all officers and for the
name to appear where there is one and a blank space to appear where there is
not.

I have each company on a separate page and this is an example of what I
need. On each of the company reports, the titles of the positions will remain
in the same order. A name will appear where we have one.

ABC Company

President: Bob Smith

Vice President: Joe Schmoe

Treasurer:

Secretary: Harry Larry

We do not have a treasurer listed for the ABC company but the position is
there without a name.

What I have tried to do is the following in the detail section of the
report. I have created a text box for each of the company titles:

=(IIf([T_Title]="President","President:" & [Name],""))

=(IIf([T_Title]="Vice President","President:" & [Name],""))

=(IIf([T_Title]="Treasurer","President:" & [Name],""))

=(IIf([T_Title]="Secretary","President:" & [Name],""))

I have tried using a subreport but no matter what I do, depending on how
many officers are listed for the company, the detail either doesn't appear or
it appears in different positions on the page.

I am at a loss.

Thanks in advance for your help.

 

[Duane Hookom]

It would help if you provided your table structures and some sample data. I
expect you could use a crosstab query where all the titles are the Column
Headings with the first of Officer Name as the Value. You should enter all
possible titles into the Column Headings property.

 

[TinleyParkILGal]

Folks, I did a quick job of copying and pasting:

My text boxes really look like this:

=(IIf([Title]="President","President:" & [Name],""))

=(IIf([Title]="Vice President","VP:" & [Name],""))

=(IIf([Title]="Treasurer","Treasurer:" & [Name],""))

=(IIf([Title]="Secretary","Secretary:" & [Name],""))

 

[TinleyParkILGal]

Duane, thank you. I have never done a crosstab query but I will attempt it now.

My data is:

Company Table: Company ID, Company Name (and other detail like address,
etc.)

Officers: Company ID, Officer's Title ID (which comes from the titles
table, and Officer's name.

It would help if you provided your table structures and some sample data. I
expect you could use a crosstab query where all the titles are the Column
Headings with the first of Officer Name as the Value. You should enter all
possible titles into the Column Headings property.

--
Duane Hookom
MS Access MVP
--

Professionals:

I am trying to design a report from 2 tables. My report is based on a
query
of the two tables.

Table 1: Company

Table 2: Company Officers

What I am attempting to do is create a report that shows the company name
and its officers, however, I need a position for all officers and for the
name to appear where there is one and a blank space to appear where there
is
not.

I have each company on a separate page and this is an example of what I
need. On each of the company reports, the titles of the positions will
remain
in the same order. A name will appear where we have one.

ABC Company

President: Bob Smith

Vice President: Joe Schmoe

Treasurer:

Secretary: Harry Larry

We do not have a treasurer listed for the ABC company but the position is
there without a name.

What I have tried to do is the following in the detail section of the
report. I have created a text box for each of the company titles:

=(IIf([T_Title]="President","President:" & [Name],""))

=(IIf([T_Title]="Vice President","President:" & [Name],""))

=(IIf([T_Title]="Treasurer","President:" & [Name],""))

=(IIf([T_Title]="Secretary","President:" & [Name],""))

I have tried using a subreport but no matter what I do, depending on how
many officers are listed for the company, the detail either doesn't appear
or
it appears in different positions on the page.

I am at a loss.

Thanks in advance for your help.

 

[TinleyParkILGal]

Duane, I was able to design the crosstab query and this worked beautifully in
my report. Thanks so much for your help!