duplicate records problem 2

[mmaz]

Hi,

I have a query that returns results like this:

Name of Company Phone Type_Phone
CompanyA 334-5554 admin
CompanyA 334-7786 fax
CompanyB 335-7765 admin
CompanyB 335-7634 fax

My question is, how can I have one record for each company with this query? Is it possible to do that in straigh SQL? If so, how? And if not, what else could I do (without building a report)?

Thanks,

Marie

 

[swampBoogie]

Which of the two lines for each company do you want returned?

 

[mmaz]

Sorry, I should have written the desired output.

This is what I want:

Name of Company Phone admin Phone fax
CompanyA 334-5554 334-7786
CompanyB 335-7765 335-7634

 

[swampBoogie]

Maybe a transform statement would be apropriate. It is described briefly on this page.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acintsql.asp

 

[lameid]

Transform is right but in the QBE it is called a crosstab query as opposed to an insert, append, etc query.

Once you select crosstab query, in the crostab row under the fields listed below, select the values.

Name of Company: Row Heading
Type_Phone: Column Heading
Phone: Value

In the total row, I think the default group by will work all the way accross. You may need to select something different for phone like first/min/max. As long as there is only Phone type does not duplicate with in a company, it doesn't matter.

 

[mmaz]

Thanks to both of you. It worked!