Autonumbering Query
in [General]
|
From: Reva on 10 May 2010 15:39 I have 2 tables. Table 1 contains an indexed autonumber field named QID. Table 2 contains the same name field - it is a number field. The two fields are linked in the relationships as 1 to many and the form is working as expected. What I am trying to do is insert a secondary autonumber field in Table 2 which will restart at #1 everytime a new record is created in Table 1. Eg. Table 1 will have record 1, 2, 3....etc. I want Table 2 to have record 1.1, 1.2, 1.3....etc. (the 1 coming from the QID field in Table 1). The .1 etc. from the autonumber field in Table 2. Therefore when Record 2 is created in Table 1, Table 2 will be Record 2.1, 2.2, 2.3...etc. Is this possible?
From: Allen Browne on 10 May 2010 22:55 This article provides some options: http://allenbrowne.com/ranking.html#query There's not a really simple version. Essentially, you use a subquery to count the number of preceding values for the particular foreign key value, or use a DCount() expression if you really need the query to be updatable and don't mind it slowing to a crawl. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Reva" <Reva(a)discussions.microsoft.com> wrote in message news:08728B91-C703-4FC1-A86D-8BF8E8BCD30C(a)microsoft.com... > I have 2 tables. Table 1 contains an indexed autonumber field named QID. > Table 2 contains the same name field - it is a number field. The two > fields > are linked in the relationships as 1 to many and the form is working as > expected. What I am trying to do is insert a secondary autonumber field > in > Table 2 which will restart at #1 everytime a new record is created in > Table > 1. Eg. Table 1 will have record 1, 2, 3....etc. I want Table 2 to have > record 1.1, 1.2, 1.3....etc. (the 1 coming from the QID field in Table 1). > The .1 etc. from the autonumber field in Table 2. Therefore when Record 2 > is > created in Table 1, Table 2 will be Record 2.1, 2.2, 2.3...etc. > > Is this possible?
|
Pages: 1 Prev: Uppercase notation in access 2007 Next: Corruption in access 2003 form |