AutoNumber that changes with a value

[Frank]

Ineed to create a Make Table quey that will apply an auto numbertha resets
with a change of value in a speciffic field. The end result would be to
create a cross tab with the AutoNumber values as the column heading.

For example I have a database of circuits that feed customers. These
circuits are broken up into sections. I have he number of downstream
customers for each section, the section with larget quantity would be closest
to the source, so I can sort my cisuits by downstream customers. Now I need
to be able to number each of the sections so i can list the circuits (Row)
with the customers by section (Column). Not every circuit has the saem
number of sections.

Ckt Sct1 Sect 2 Sect3
CKT1 100 50 10
CKT 2 500 100


Cn this be done in a simple Make Table query?

 

[akphidelt]

What would changing the autonumber do that is different then adding a field
that you change and the query sorts based off that field?

 

[Frank]

I need to be ablt to number the sections. It would give me column headings
for the cross tab. Section 1, section 2 section 3.

 

[John Spencer]

Sounds to me as if you need to rank the sections by the number of customers.
I do see a problem if two sections have the same number of customers - since
that will result in ties. If you have some other information in your table
that you can use to resolve the ties that could help.

You might be able to use a query that looked something like the following as
the basis of a crosstab query.
SELECT Ckt, Section, CustomerCount,
(SELECT Count(*)
FROM [TheTable] as Temp
WHERE Temp.Ckt = MainCkt
AND Temp.CustomerCount <Main.CustomerCount) +1 as SectionPosition
FROM [TheTable] as Main

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..