need id field for detail records to always begin at 1

[muneco]

Hi! I am designing an Access 2003 database that includes movement_header (master) and movement_detail (detail) tables. The primary key for the master table is movement_id, type autoincrement; this related to the foreign key movement_detail.movement_id.

In the movement_detail table, I would like to have a detail_id field that always begins at 1 and autoincrements to 2,3, etc. So if I have movement_id equal, say, 2000, with three detail records, I would like to have detail_id values of 1,2 and 3.

I currently have this defined as type autoincrement, but I find that it does not start at 1; instead, it starts at one number higher than the latest detail_id for ANY movement_id. So with limited testing now, my detail_id might start at 30 and increment to 31 and 32.

Is there a way I can implement this so that the detail_id always starts at 1 and increments by one for each new detail record? Thanks in advance for any input you can offer.

 

[lespaul]

what if you could just do that in a query instead of using VBA to insert that information in the table....would that be a viable alternative?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[Crowley16]

you cannot do what you want using autonumbering, you will need to "work out" the id each time and insert it either through vba using recordsets or with a insert query.

the best approach depends on how you are currently inserting records into the details table...

--------------------
Procrastinate Now!

 

[muneco]

I am new to Access so I'm not sure how I would implement that...but I was really under the impression that it could be done in the actual table definition...Any suggestions are welcome.

 

[lespaul]

Assuming you have something like this:
[tt]
movement_Header
HeaderID OtherHeaderStuff
1
2
3

movement_Detial
DetailID HeaderID OtherDetailStuff
16 1
17 1
18 1
19 2
20 2
21 2
22 2
23 2
24 3
[/tt]
then you can write a query that will return:
[tt]
HeaderID DetailID DetailLineNumber
1 16 1
1 17 2
1 18 3
2 19 1
2 20 2
2 21 3
2 22 4
2 23 5
3 24 1
[/tt]
If that will work search the fora for 'rank query' and you should find a few examples of the query. I'm not very good at ranking queries, but maybe you'll get lucky and one of the MVPs that IS good at them will give you a starting point.

You'll get a much clearer response if you post some sample data as I did above and your expected results.

HTH
Leslie