Sequence number

[Pete Provencher]

I'm trying tjo write a query that will update the TMSSequence field. I would
like to sequence each row by TMS field and Date sold field. It has to be
able to see all the tms that are the same and then sequence them in
assending date sold order. I can't do this on input as the input table only
has what is being currently updated. I'm doing this in the history file
which contains each weekly update.

Thanks for any advice,


Query3 TMS TMSSequence DateSold
001-00-01-001 1
1/19/1989
001-00-01-001 2
11/23/2005
001-00-01-001 3
12/1/2005
001-00-01-001 4
4/27/2006
001-00-01-001 5
4/27/2006
001-00-01-001 6
1/18/2007
001-00-01-002 1
8/23/1994
001-00-01-003 1
11/22/2001
001-00-01-004 1
3/27/2001
001-00-01-007 1
7/16/2002
001-00-01-007 2
12/30/2005
001-00-01-008 1
2/26/1999
001-00-01-009 1
11/8/1994
001-00-01-010 1
2/21/2007
001-00-01-011 1
11/21/1995
001-00-01-011 2
10/27/2004
001-00-01-011 3
1/25/2008

 

[Ken Sheridan]

Do not include the TMSSequence column in the table, compute its value in a
query. To include the column in the base table introduces redundancy and the
risk of inconsistent data, whereas computing it on the fly eliminates any
risk and guarantees that the values will be correct.

Your sample data seems to assume that the dates per TMS will be distinct,
i.e. there can be no two rows with the same TMS and DateSold values. If so
then you can compute the values like so:

SELECT T1.TMS,
(SELECT COUNT(*)
FROM YourTable AS T2
WHERE T2.TMS = T1.TMS
AND T2.DateSold <= T1.DateSold) AS TMSSequence,
T1.DateSold
FROM YourTable AS T1
ORDER BY T1.TMS, T1.DateSold;

If more than one row can have the same TMS and DateSold values then you'll
need to distinguish between them. The simplest way is to add an autonumber
column to the table, SaleID say. You can then arbitrarily distinguish
between two or more rows with the same TMS and DateSold values:

SELECT T1.TMS,
(SELECT COUNT(*)
FROM YourTable AS T2
WHERE T2.TMS = T1.TMS
AND T2.DateSold <= T1.DateSold
AND ( T2.SaleID <= T1.SaleID
OR T2.DateSold <> T1.DateSold)) AS TMSSequence,
T1.DateSold
FROM YourTable AS T1
ORDER BY T1.TMS, T1.DateSold, T1.SaleID;

Ken Sheridan
Stafford, England

 

[Pete Provencher]

I used your second example as i do have occurences where two or more records
might have the same tms number and same sale date. It worked perfect. THe
sequence numbers it generated in the query worked perfect.

But, I'm afraid I have another question. How do I take the sequence numbers
that were generated in the query and update my table with that data?


Pete Provencher

 

[Ken Sheridan]

As I said in my reply, you should not include the sequential numbers in the
table at all. Doing so introduces redundancy, which is a bad thing not only
because its unnecessary, but more importantly because it leaves the table at
risk of inconsistent data being entered. Once a value is stored in a column
then it can be changed and become inconsistent with other data. The rule in
essence is that data whose values can time independently be computed from
other data should not be stored in a column in a table. The 'time
independence' criterion can be illustrated by reference to the products and
Order Details tables in the sample Northwind database. You'll see that both
contain UnitPrice column and when an order is created the unit price from
Order Details is computed from that in Products. However, the prices of
products will change over time, but you want each order to retain the prices
at the time the order was created, so the values are not 'time independently'
computed and its therefore necessary to have a column in Order Details as
well as in Orders.

In your case the sequential numbers are computed from the values in a number
of columns, rather than one as in the Northwind example, but the principle
remains the same.

If you really do need to store the values in a TMSSequence column then you
can't use a subquery to compute them, however, as an SQL aggregating function
is used and this makes the query non-updatable, so you have to use the VBA
DCount function like so:

UPDATE YourTable
SET TMSSequence = DCOUNT("*","YourTable","TMS = " & YourTable.TMS &
" AND DateSold <= #" & Format(YourTable.DateSold,"yyyy-mm-dd") &
"# AND (SaleID <= " & YourTable.SaleID & " OR DateSold <> #" &
Format(YourTable.DateSold,"yyyy-mm-dd") & "#)");

Ken Sheridan
Stafford, England

 

[Pete Provencher]

I hate to continually ask about this problem but this problem is completely
beyound my expertise. I tried your DCOUNT function and it returns only 0 for
each row. The reason I need to update the tmssequence d as I need that field
to make the record unique.

 

[Bob Barrows]

Now my mind is boggled.
Why wouldn't you simply add an autonumber field and be done with this
problem?

 

[Ken Sheridan]

If you simply switch from SQL view to datasheet view with the UPDATE query it
will return zero for every row, assuming that's the default value of the
TMSSequence column, as the query has not yet been executed. You need to
execute it, either from the Run button on the toolbar, or from the Query |
Run menu item (or whatever is the equivalent in the version of Access you are
using). I've tested it against a similarly structured table of my own and
can confirm that it does work correctly.

But if its simply a unique key for each row you need then you can use an
autonumber column. In fact you must already have a candidate key in whatever
is the equivalent to the SaleID column in my example. An autonumber column
is arbitrarily sequential, but the values have no intrinsic meaning; they are
solely to provide a unique key, but it does enable you to compute sequential
numbers by date per TMS and return these in a query if there is some reason
you need to be able to do this, without the redundancy which storing the
numbers entails.

Ken Sheridan
Stafford, England

 

[Pete Provencher]

I went from SQL view to design view, click on the run button, red
exclamatation mark, and I still get only 0 in every row. Here's the modified
code if you wouldn't mind looking at it to see if I did something wrong in
changing mly table. :

UPDATE SeqNbr SET SeqNbr.TMSSequence = DCount("*","SeqNbr","TMS = " &
SeqNbr.TMS & " AND DateSold <= #" & Format(SeqNbr.DateSold,"yyyy-mm-dd") &
"# AND (SaleID <= " & Seqnbr.SaleID & " OR DateSold <> #" &
Format(SeqNbr.DateSold,"yyyy-mm-dd") & "#)");

I will keep playing with it but my knowledge doesn't allow me to understand
exactly what your code is doing. If the code looks ok then I'll just drop
back to an autonumber and play around with your code and hopefully one day,
as I learn more, i can get it to work.

THanks for all of the help you have given. Us rookies really appreciate the
assistance you give evceryone.

Pete Provencher

 

[Ken Sheridan]

I've just noticed that your TMS values include dashes. Is the column text
data type rather than a number data type which is displayed formatted with
dashes? If so then you'll need to wrap the value in quotes characters when
calling the DCount function:

UPDATE SeqNbr SET SeqNbr.TMSSequence = DCount("*","SeqNbr","TMS = """ &
SeqNbr.TMS & """ AND DateSold <= #" & Format(SeqNbr.DateSold,"yyyy-mm-dd") &
"# AND (SaleID <= " & Seqnbr.SaleID & " OR DateSold <> #" &
Format(SeqNbr.DateSold,"yyyy-mm-dd") & "#)");

In the original SELECT query using a subquery its not necessary to do this,
but in this case you are building a string expression as the criterion for
the DCount function so literal quotes have to be incorporated in the string.
A literal quotes character is represented by including two contiguous quotes
characters when building the string.

Ken Sheridan
Stafford, England

 

[Pete Provencher]

Yes, the TMS is text and your new script worked!!!!! Thank you so very much.

Pete Provencher