Report Detail

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
 
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],""))
 
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.


Duane Hookom said:
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
--

TinleyParkILGal said:
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.
Click to expand...
Click to expand...
 
Duane, I was able to design the crosstab query and this worked beautifully in
my report. Thanks so much for your help!
 
Back
Top