Counting in a Make Table Query

  • Thread starter Thread starter Centaur
  • Start date Start date
C

Centaur

Hi all

I have a Make Table that selects according to a criteria of a yes/no field.

I want it to Add a field that is not in the source table and put a
incremental number in it starting at 1

So if it selects 50 records, it will put 1 in the first record 2 in the next
and so on

When I re-run the query it will of course delete the table it made for a new
one, and again I want it to start at 1. In other words, I don't want it to
carry on where it left off.

What would I have to add to

SELECT Names.RegisteredName INTO PrintNames
FROM [Names]
WHERE (((Names.NamesSelect)=-1));

Many thanks to all who reply

Centaur
 
Centaur had uiteengezet :
Hi all

I have a Make Table that selects according to a criteria of a yes/no field.

I want it to Add a field that is not in the source table and put a
incremental number in it starting at 1

So if it selects 50 records, it will put 1 in the first record 2 in the next
and so on

When I re-run the query it will of course delete the table it made for a new
one, and again I want it to start at 1. In other words, I don't want it to
carry on where it left off.

What would I have to add to

SELECT Names.RegisteredName INTO PrintNames
FROM [Names]
WHERE (((Names.NamesSelect)=-1));

Many thanks to all who reply

Centaur
Click to expand...

You'll have to write a function that returns the counter. And add that
function to the select statement. Reset the counter to zero before
starting the select

Public iCounter as Integer 'Max 32K, change to Double if you need
mroe

Function IncrementValue() as integer (or as double)

iCounter = iCounter + 1
IncrementValue = iCounter

End Function

And (from top of my head, this could be syntaxed a bit differently),
change the select to :
Select Names.RegisteredName, IncrementValue() as IdentificationNumber
INTO <etc>
 
Can I ask the reason you need to do this in a query? If you are using this for
a report, there is a much simpler method available to do it in a report.

In a report, simply add a control to the detail section and set its properties as:

Source: =1
Running Sum: OverAll

This will automatically give you a one up number for each detail row.

Otherwise post back. Do your records have a primary key of some type? That can
be used to generate a one-up number in the query. If you do post back please
tell use what the primary key is in the Names tables.
 
Thanks John for your help

I have a table of names each one having a Primary Key Number of NamedID
which is fine, and naturally if you delete a name the sequence of numbers is
broken.

The Names are to be included in a roster. And suppose there are 100 Names
listed. I want to be able to print the Roster 100 times but each time it is
printed for a different person on the list, and in the Roster, whenever that
name occurs it will be printed in a different color. So each print out will
be for a different person highlighting their name.

I was going down the route of a make table of all names to be printed. That
an PrintNameID is given to each in the new table starting at No 1. Then to
print the report use a For Next loop, For PrintList = 1 to
NoOfNamesToBePrinted So each number will tell the report which one it is on
and so print the name in the appropriate color.

I have been able to use colors in reports before in a similar way to show
the status of orders i.e. - not ready; on time; overdue; etc So I think that
I may be OK on the color thing. What I am concerned with is identifying
which record is being printed

I thought that using the original NameIDs was inappropriate since over time
there would be many gaps. And determining the highest number; and making a
for next loop to skip any number not there would be an untidy way, and
possibly slow way of doing this, especially in time.

Many thanks for your help

Centaur


John Spencer (MVP) said:
Can I ask the reason you need to do this in a query? If you are using this for
a report, there is a much simpler method available to do it in a report.

In a report, simply add a control to the detail section and set its properties as:

Source: =1
Running Sum: OverAll

This will automatically give you a one up number for each detail row.

Otherwise post back. Do your records have a primary key of some type? That can
be used to generate a one-up number in the query. If you do post back please
tell use what the primary key is in the Names tables.
Hi all

I have a Make Table that selects according to a criteria of a yes/no field.

I want it to Add a field that is not in the source table and put a
incremental number in it starting at 1

So if it selects 50 records, it will put 1 in the first record 2 in the next
and so on

When I re-run the query it will of course delete the table it made for a new
one, and again I want it to start at 1. In other words, I don't want it to
carry on where it left off.

What would I have to add to

SELECT Names.RegisteredName INTO PrintNames
FROM [Names]
WHERE (((Names.NamesSelect)=-1));

Many thanks to all who reply

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