Duane Hookom - Crosstab Question

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

Guest

Duane and other professionals:

Thank you for your post on the crosstab. I was a little too excited out of
the gate and it is not working exactly the way I want it to.

My tables:

Company - Company ID, Company Name

Officers - Company ID, Name ID, Title ID, Term Start Date, Term End Date

Names - Names ID, First Name, Last Name, Email Address

Titles - Title ID, Title

I attempted to use the company and officers tables as the foundation for the
crosstab query but it seems to be trying to add the ID numbers to give me a
sum.

My end report needs to look like this:

Company ABC

President: Joe Smith
Email: (e-mail address removed)
Term Begins: 1/1/05
Term Ends: 12/31/05

Vice President: John Doe
Email: (e-mail address removed)
Term Begins: 2/1/05
Term Ends: 1/31/05

And so on for each company.

I do plan to have the data for each individual officer on a single line but
I did what I did above for cosmetic purposes.

Thanks in advance.
 
You would need to include all tables in the crosstab. Your Column Heading
field would be Title and the Value could be:
first of [First Name] & " " & [Last Name] & chr(13) & chr(10) & "Email: " &
[Email Address] & ...

Since you added the email and other fields, I think I would approach this in
a different manner. Possibly with a cartesian query and/or subreports.
 
I can successfully pull all the fields into the crosstab query however when
it comes to the report I need to break them out into separate fields. I am
not formatting the fields so they are vertical but rather horizontal. When I
concatenate all of the fields in the crosstab then I do not have the option
of having all the fields (name, email, title) available to me but rather I
have them all in one field. Anyone have Advil?
 
Duane, thank you for your post, I tried a subreport to begin with. The
subreport consisted of all of the company personnel and their titles.

The link was company ID.

I then created several text boxes with IIF statements.

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

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

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

No matter what I do with this avenue, the detail either doesn't appear or it
appears in different positions on the page.

I am at a loss with this.


Duane Hookom said:
You would need to include all tables in the crosstab. Your Column Heading
field would be Title and the Value could be:
first of [First Name] & " " & [Last Name] & chr(13) & chr(10) & "Email: " &
[Email Address] & ...

Since you added the email and other fields, I think I would approach this in
a different manner. Possibly with a cartesian query and/or subreports.

--
Duane Hookom
MS Access MVP
--

TinleyParkILGal said:
Duane and other professionals:

Thank you for your post on the crosstab. I was a little too excited out of
the gate and it is not working exactly the way I want it to.

My tables:

Company - Company ID, Company Name

Officers - Company ID, Name ID, Title ID, Term Start Date, Term End Date

Names - Names ID, First Name, Last Name, Email Address

Titles - Title ID, Title

I attempted to use the company and officers tables as the foundation for
the
crosstab query but it seems to be trying to add the ID numbers to give me
a
sum.

My end report needs to look like this:

Company ABC

President: Joe Smith
Email: (e-mail address removed)
Term Begins: 1/1/05
Term Ends: 12/31/05

Vice President: John Doe
Email: (e-mail address removed)
Term Begins: 2/1/05
Term Ends: 1/31/05

And so on for each company.

I do plan to have the data for each individual officer on a single line
but
I did what I did above for cosmetic purposes.

Thanks in advance.
Click to expand...
Click to expand...
 
I would create a cartesian query so that all Titles are combined with all
companies. Then use this cartesian query to join with the officers and
names. Make sure that your final query selects all records from the
cartesian query.

--
Duane Hookom
MS Access MVP
--

TinleyParkILGal said:
Duane, thank you for your post, I tried a subreport to begin with. The
subreport consisted of all of the company personnel and their titles.

The link was company ID.

I then created several text boxes with IIF statements.

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

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

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

No matter what I do with this avenue, the detail either doesn't appear or
it
appears in different positions on the page.

I am at a loss with this.


Duane Hookom said:
You would need to include all tables in the crosstab. Your Column Heading
field would be Title and the Value could be:
first of [First Name] & " " & [Last Name] & chr(13) & chr(10) & "Email: "
&
[Email Address] & ...

Since you added the email and other fields, I think I would approach this
in
a different manner. Possibly with a cartesian query and/or subreports.

--
Duane Hookom
MS Access MVP
--

TinleyParkILGal said:
Duane and other professionals:

Thank you for your post on the crosstab. I was a little too excited out
of
the gate and it is not working exactly the way I want it to.

My tables:

Company - Company ID, Company Name

Officers - Company ID, Name ID, Title ID, Term Start Date, Term End
Date

Names - Names ID, First Name, Last Name, Email Address

Titles - Title ID, Title

I attempted to use the company and officers tables as the foundation
for
the
crosstab query but it seems to be trying to add the ID numbers to give
me
a
sum.

My end report needs to look like this:

Company ABC

President: Joe Smith
Email: (e-mail address removed)
Term Begins: 1/1/05
Term Ends: 12/31/05

Vice President: John Doe
Email: (e-mail address removed)
Term Begins: 2/1/05
Term Ends: 1/31/05

And so on for each company.

I do plan to have the data for each individual officer on a single line
but
I did what I did above for cosmetic purposes.

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top