Adding a numbered field in a query

[jeanulrich00]

Hi

I need to automate the creation of a table

For that I am using a make table query.

Right now I have created a query that gives me 4 of the 5 fields I
need

Let say this query gives me 12 rows (It can be 5 or 37)
..What I need is adding another field to this query and this field
should give me like an auto number 1, 2, 3, 4, 5, 6....until 12.

Each time I use this make table query, the new field should start all
the time at 1

Example

Country Stores Cost

Australia Big 150
Canada Small 50
Denmark Medium 100
France Medium 125
United States Big 300

and the new query should give me

Sequence Country Stores
Cost

1 Australia
Big 150
2 Canada
Small 50
3 Denmark
Medium 100
4 France
Medium 125
5 United States
Big 300

Thanks for helping

 

[John W. Vinson]

Hi

I need to automate the creation of a table

For that I am using a make table query.

Right now I have created a query that gives me 4 of the 5 fields I
need

Let say this query gives me 12 rows (It can be 5 or 37)
.What I need is adding another field to this query and this field
should give me like an auto number 1, 2, 3, 4, 5, 6....until 12.

Each time I use this make table query, the new field should start all
the time at 1

Example

Country Stores Cost

Australia Big 150
Canada Small 50
Denmark Medium 100
France Medium 125
United States Big 300

If the sequential number needs to be in the same order as the alphabetical
order of Country, and if Country is unique within the table (so there are no
ties), you can use Select query with the same criteria as the MakeTable query;
include a calculated field

Seq: DCount("*", "[yourqueryname]", "[Country] <= '" & [Country] & "*")

Then base your MakeTable query on this query.

If you're doing all this rigamarole in order to get a sequentially numbered
report, you don't need either a make-table query OR a sequential number - just
base a Report on the select query, and put a textbox on the report with its
Control Source set to =1, and its Running Sum property to Over All.