Creating multiple, sequential crosstab-esque columns from one field.

[davidgoliath]

Hi all -

This is probably a simple question for some.

I have data that looks like this:

Number of Operators per Permit	Permit	Operator Name
6	065-24088	EOG RESOURCES INC
6	065-24088	PDC MOUNTAINEER LLC
6	065-24088	PETRO DEV CORP
6	065-24088	PETROLEUM DEV CO
6	065-24088	SOMERSET OIL & GAS CO INC
6	065-24088	EXCO RESOURCES PA INC
6	063-27221	LINN OPR LLC
6	063-27221	MTN V OIL & GAS INC
6	063-27221	LINN OPR INC
6	063-27221	DOMINION EXPLORATION & PROD INC
6	063-27221	CONSOL GAS CO
6	063-27221	CNX GAS CO LLC
5	129-24355	REX ENERGY OPERATING CORPORATION
5	129-24355	DOUGLAS OIL & GAS LP
5	129-24355	REX ENERGY OPERATING CORP
5	129-24355	STD STEEL
5	129-24355	STD STEEL LLC

I want to take this data and create six new columns (operator1; operator2; operator3; operator4; operator5; operator6) for each permit number so that each unique operator name will be housed in each of those six columns, with the rows being defined by the permit number. So, I want to produce something that looks like this...

Permit	operator1	operator2	operator3	operator4	operator5	operator6
065-24088	EOG RESOURCES INC	PDC MOUNTAINEER LLC	PETRO DEV CORP	PETROLEUM DEV CO	SOMERSET OIL & GAS CO INC	EXCO RESOURCES PA INC
063-27221	LINN OPR LLC	MTN V OIL & GAS INC	LINN OPR INC	DOMINION EXPLORATION & PROD INC	CONSOL GAS CO	CNX GAS CO LLC
129-24355	REX ENERGY OPERATING CORPORATION	DOUGLAS OIL & GAS LP	REX ENERGY OPERATING CORP	STD STEEL	STD STEEL LLC

At present, there are as many as 6 operators per permit, but the number of operators per permit varies from 1 to 6. I included an example that has 5 operators, to show that I want the sixth column to be null in this instance. So if a permit had only one operator, only the operator1 field would take on a non-null value in the new table/query.

I know how to do this with 2 operators per permit by using min/max or first/last functions in a query, but I don't know how to deal with more than two operators per permit.

Figuring this out would really help with my analyses, so any tips will be much appreciated!

Thanks!

[alansidman]

If you are able to change the field "Number of Operators...." to Operators and number them one up for each permit then you could do something like what is shown in this video

http://www.datapigtechnologies.com/f.../crosstab.html

[davidgoliath]

Hmm... yeah, that would work.

I'll have to dig on an efficient way to create sequential numbers based on the permits...

Thanks.

[June7]

Sequential numbering by group can be done by:

1. manually enter the next number

2. VBA code to determine next available number for group and save value to table. This is a common topic in forum.
https://www.accessforums.net/access/...ers-15787.html
https://www.accessforums.net/forms/a...ing-23329.html

[davidgoliath]

Thanks June7 and alansidman. I did the sequential numbering in Excel rather clunkily, but effectively. Snap after that.

=) Gavid Doliath