Crosstab query possibly?

[Maver1ck666]

Ok, Im hoping you can help and that it is pretty straight forward so here goes.

I have a table that holds a customer reference number and a policy id. Now
Customers will have a unique id to identify them but can have multiple policy
numbers in the same tabe. For example, my table will look like this:

CustomerID CustomerPolicy
------------------------------------
123-99 12345
123-99 56789
345-88 98765
789-88 45667

What I need to do is to provide a query which shows the data as:

CustomerID Policy1 Policy2 Policy3
----------------------------------------------------------
123-99 12345 56789
345-88 98765
789-88 45667

Does anyone know how to do this please?

Kind regards,
Mav

 

[Jerry Whittle]

You need at least 3 fields in a table or query to create a crosstab. To get
what you want, you'd need a field with Policy1, Policy2, etc., it the table
for each record.

How about something like the insurance type like Auto, Life, Medical across
the top? Is there some way to find out what type of insurance each policy is?

 

[KARL DEWEY]

The only way I know of is to number the policies of the customer using a
Ranking in a group query to a temp table then run a crosstab on that.
SELECT [YourTable].[CustomerID, [YourTable].[CustomerPolicy], (SELECT
Count(*) FROM [YourTable] AS [XX] WHERE [YourTable].[CustomerID =
[XX].[CustomerID] AND [YourTable].[CustomerPolicy] < [XX].[CustomerPolicy])+1
AS PolicyNUM
FROM [YourTable]
ORDER BY [YourTable].[CustomerID, [YourTable].[CustomerPolicy];