Autonumber

[astrevens]

A really simple one, i want to have an autonumber generated in my Make table query. Or is there anyway to do a running count?
so if I have:

Ident Cost Count
ashur001 £10 1
ashur001 £20 2
ashur001 £30 3

How do i get the count field?

 

[Shirley]

Why done you design a table and set up the autonumber field first, then just append the records.

 

[astrevens]

because the autonumber seems to remember where it left off, so if you delete out and then append data it remembers the last number.

what im looking to do is get the max number in the field then multiply that by the target amount so i can get a running target

 

[Maquis]

If all you need is a count of the records, then you don't even need an extra field. Just use the Dcount function on that table after you have created it. (Or appended the records)

See Access's help file for the syntax on the Dcount function. Maq B-)
<insert witty signature here>

 

[astrevens]

how do i get that to work?
ive got:
ident date value cost
ashur001 01/10/01 £10 £5
ashur001 02/10/01 £20 £10

 

[Shirley]

Do you need it in the table or for a report?

 

[Maquis]

What are you trying to do? Dcount will simply count the number of records in your table.

If you don't feel comfortable using VB code, then you can create a group-by query which will count the number of records for you. It can also sum your number fields if that is what you need. Maq B-)
<insert witty signature here>

 

[astrevens]

what i want in the end is a line graph that shows a running total against a running target.
so i tried doing this in a report but i cant base a graph on a report.
Next plan was to have a make table query which does a running sum on the cost for each day, then i will be able to base the graph on that.

each rep has their own daily target (ie, £100 a day) so i need to have a table that looks like this:

ident rep date cost daily target Count
ash 02 01/10/01 £10 £10 1
ash 02 02/10/01 £20 £20 2

to calculate the target i could just multiply the daily against the count next to it?

 

[Shirley]

I came across this article in the MSDN Library see if it helps any:

However, you can force Microsoft Access to number an AutoNumber field with a number you choose by following these general steps:

Copy the design of the original table in which you want to set the starting AutoNumber value to a new table.
Change the AutoNumber field in the new table to a Number field with a FieldSize property of Long Integer.
Add a record to the new table, and set the Number field to a value that is one less than the starting number you want for your original table. For example, if you want the AutoNumber field to start at 100, type 99 in the Number field of the new table.
Use an append query to add a this new record to your original table. This action forces Microsoft Access to number any new AutoNumber fields with your number plus 1.

NOTE: Do not compact the database before you add a new record to the original table. If you do, Microsoft Access will reset the AutoNumber field value to the number 1.
The following example uses the sample database Northwind.mdb (or NWIND.MDB in version 1.x or 2.0) to set a new starting value for an AutoNumber field in the Employees table:

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb(or NWIND.MDB in version 1.x or 2.0). You may want to back up the Northwind.mdb (or NWIND.MDB) file and perform these steps on a copy of the database.


1. Open the sample database Northwind.mdb (or NWIND.MDB in version 1.x or
2.0).

2. Select the Employees table, and on the Edit menu, click Copy.

3. On the Edit menu, click Paste.

4. In the Paste Table As dialog box, type EmployeesTemp in the Table Name
box, and then click the Structure Only option. Click OK.

5. Open the EmployeesTemp table in Design view.

6. Change the data type of the EmployeeID field to Number, and set its
FieldSize property to Long Integer.

NOTE: In Microsoft Access 1.x and 2.0, the Employee ID field contains
a space.

7. Save the EmployeesTemp table and close it.

8. Open the EmployeesTemp table in Datasheet view and add the following
new record:

Employee ID: 99
Last Name: <Type your last name>
First Name: <Type your first name>
Birth Date: <Type your birth date>

9. Create a new query in Design view based on the EmployeesTemp table.

Add all fields from the table to the QBE grid.

On the Query menu, click Append Query (or Append in Microsoft Access
1.x, 2.0, and 7.0).

Select Employees in the Append To Table Name box, and then click OK.

On the Query menu, click Run. Microsoft Access displays the following
message:

In Microsoft Access 7.0 and 97
------------------------------

You are about to append 1 row(s).

In Microsoft Access 1.x and 2.0
-------------------------------

1 Row(s) will be appended.

Click Yes (or OK) to append the record to the Employees table.

Close the query and do not save it.

Delete the EmployeesTemp table, and then delete the newly appended
record from the Employees table.

Open the Employees table in Datasheet view and add a new record. Note
that the Employee ID field starts numbering at 100.


REFERENCES

For more information about AutoNumber fields, type &quot;starting value AutoNumber&quot; in the Office Assistant, click Search, and then click to view &quot;Change the starting value of an incrementing AutoNumber field.&quot;