Odd numbering situation

J

Joel

I have a table that stores repair information for every building and it has
an autonumber.
Now I need someway of making another autonumber inside this table for each
individual building. ( I have thought about making a repair table for each
building but that would be thousands of tables!)

EX: Building 001
Repair 1 autonumber 23
Repair 2 autonumber 63

Building 002
Repair 1 autonumber 24
Repair 2 autonumber 73

I need each buildings repair # to start at 1 and keep going, this needs to
be done automaticly to avoid human error.

Thanks
 
J

Jackie L

Joel,
Try this: Create a Totals query (qryLastRepair) with two fields, Building
and RepairNo. Have the Building field set up as GroupBy and the RepairNo as
Max (rename the field to something like LastRepair). In the criteria of the
building field, point to the form you are working on or where ever the
building number would be indicated.

Then, on the event that would create your new repair record, put something
like

Me.RepairNo = DMax("LastRepair","qryLastRepair")+1

In rereading your note, the example I showed above is for numbering the
repairs on a record by record basis. Are you looking to number the entire
table at once?
 
J

Joel

Thanks for the responce.

No im not trying to number the whole table at once (the table is blank atm)
so by each record is what i need.

I followed your directions and im up to this part

Me.RepairNo = DMax("LastRepair","qryLastRepair")+1
But when i run the form and try to add a record I just get 1 in the box, its
not adding or anything =(
 
J

Joel

nvm about that, i forgot it was just linked to a textbox and not saving in
the database.

Now its working great!

Thank You very much Jackie.
 
Top