[AccessD] A2K:Loop Syntax for Update SQL

With respect, IMO there is something fundamentally wrong with this design.
Any column that contains multiply-meaningful subColumns is a candidate for
revision. Each of these subColumns ought IMO to be its own column. How you
present the columns' values is a UI issue, not related to how you store the
data.

You can do what you want to very easily if each subColumn is an actual
Column. For example, "ABC" need not be stored in the database at all, since
it is common to every row. You can achieve this with a simple format, and
not store the data at all.

You can write a function to assemble N columns into whatever format you wish
and present that as the identifier to the users. How you present the data
has almost nothing to do with how you store the data. To obtain part 3 of
your key you can Dmax() with a criteria argument equal to the first two
parts of your key -- actually the first, since the "ABC" part is not stored.
I.e. 

	Dmax("BookingItemNumber", "MyTable", "BookingID = '123'") + 1

will increment the itemnumber part of your key, and you can throw away the
first part (ABC) and concentrate on the second part.

HTH,
Arthur

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darren DICK
Sent: April 21, 2003 9:42 PM
To: AccessD List
Subject: [AccessD] A2K:Loop Syntax for Update SQL


Hello all
When I create a record in an app I am making
I record a combination Alpha/Numeric code (stored
as Text)
Something like ABC:123. The next booking would be
recorded
as ABC:124 then ABC:125 etc etc
(The ABC bit is constant, because it's our Company
initials)

We also allow for block bookings (recurring
bookings really) based on the details
for ABC:123 to occur also.

What I want to do is once the original booking
details for ABC:123 has been made
If someone wants to make a recurring booking based
on ABC:123, I want
all the recurring bookings to get the same
Alphanumeric number (This bit I can do) but
my real problem is I want to append stuff to the
end of each AlphaNumeric in the group

EG the first booking for a recurring booking based
on ABC:123 booking will be ABC:123-1 the second
ABC:123-2
the 3rd ABC:123-3 and so on and so on

Any suggestions?

Many thanks in advance

Darren