Crosstab query help

  • Thread starter Thread starter SR
  • Start date Start date
S

SR

Hi people,

need help to figure out how to get the right crosstab
report.
I have the table where the values that indicate the
contact type has to become colum names. Well, one group
could have several people of the same contact type (more
than one), but if you run the crosstab report, Access will
report only one value, but I need to have all of them in
one row.
Example:
table
GroupName ContactName ContactType
Sales John PerContact
Sales Paula PerContact
Sales Kerry OPContact
Sales Lopez OPContact

How to get this:
GroupName PerContact1 PerContact2 OPContact1 OPContact2
sales John Paula Kerry Lopez

Please help,
Million of thanks,

Sergei
 
Your table/query would need to have/calculate a sequence of 1, 2, 3,... so
that your column heading expression can include the sequence
ColHead: [ContactType] & [Sequence]
A sequence column could be created with a column in a query
Sequence: DCount("*","YourTable","[Group]=""" & [Group] & """ AND
ContactType=""" & [ContactType] & """ AND ContactName <=""" & [ContactName]
& """")
 
Thank you Duane very much,

It work great!!!!

Sergei
-----Original Message-----
Your table/query would need to have/calculate a sequence of 1, 2, 3,... so
that your column heading expression can include the sequence
ColHead: [ContactType] & [Sequence]
A sequence column could be created with a column in a query
Sequence: DCount("*","YourTable","[Group]=""" & [Group] & """ AND
ContactType=""" & [ContactType] & """ AND ContactName <=""" & [ContactName]
& """")

--
Duane Hookom
MS Access MVP
--

SR said:
Hi people,

need help to figure out how to get the right crosstab
report.
I have the table where the values that indicate the
contact type has to become colum names. Well, one group
could have several people of the same contact type (more
than one), but if you run the crosstab report, Access will
report only one value, but I need to have all of them in
one row.
Example:
table
GroupName ContactName ContactType
Sales John PerContact
Sales Paula PerContact
Sales Kerry OPContact
Sales Lopez OPContact

How to get this:
GroupName PerContact1 PerContact2 OPContact1 OPContact2
sales John Paula Kerry Lopez

Please help,
Million of thanks,

Sergei
Click to expand...


.
Click to expand...
 
If you have lots of records then by "great", I assume you also mean "slow"
:-)

--
Duane Hookom
MS Access MVP
--

Sergei said:
Thank you Duane very much,

It work great!!!!

Sergei
-----Original Message-----
Your table/query would need to have/calculate a sequence of 1, 2, 3,... so
that your column heading expression can include the sequence
ColHead: [ContactType] & [Sequence]
A sequence column could be created with a column in a query
Sequence: DCount("*","YourTable","[Group]=""" & [Group] & """ AND
ContactType=""" & [ContactType] & """ AND ContactName <=""" & [ContactName]
& """")

--
Duane Hookom
MS Access MVP
--

SR said:
Hi people,

need help to figure out how to get the right crosstab
report.
I have the table where the values that indicate the
contact type has to become colum names. Well, one group
could have several people of the same contact type (more
than one), but if you run the crosstab report, Access will
report only one value, but I need to have all of them in
one row.
Example:
table
GroupName ContactName ContactType
Sales John PerContact
Sales Paula PerContact
Sales Kerry OPContact
Sales Lopez OPContact

How to get this:
GroupName PerContact1 PerContact2 OPContact1 OPContact2
sales John Paula Kerry Lopez

Please help,
Million of thanks,

Sergei
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top