Another Seqence Number Question - But With A Twist

  • Thread starter Thread starter James Cox
  • Start date Start date
J

James Cox

Problem: to generate ranking numbers for a subset of data in a table with a
SINGLE sql statement. The requirement for a single SQL statement is set by
contstraints of a third-party software package that will execute the SQL
against an Access database.

Background: Given a table All_Prod_Data with fields as shown below and with
many possible text values for the Department and UnitName fields

(AutoNumber) (Text) (Text) (Date/Time)

ReportID Department UnitName Date



The goal is to come up with a single sql select that will generate the
following

ReportID Department UnitName Date
SeqNumb

1235 Engines Block 12-Aug-04
12:23:45 1

589 Engines Block 12-Aug-04
16:02:19 2

2309 Engines Block 20-Aug-04
09:56:21 3

If I were starting with the above (except for the SeqNumb field) as a table
Some_Prod_Data, then this sql

SELECT PBDS1.ReportID, PBDS1.Department, PBDS1.UnitName, PBDS1.Date, (Select
Count(*) FROM Some_Prod_Data WHERE [Date] < [PBDS1].[Date]+1;) AS SeqNumb
FROM Some_Prod_Data AS PBDS1 ORDER BY PBDS1.Date;

Would generate what we need.

My attempt to extend the above to include a WHERE statement to return only
specified Departments and UnitNames from table All_Prod_Data , i.e.

SELECT PBDS1.ReportID, PBDS1.Department, PBDS1.UnitName, PBDS1.Date, (Select
Count(*) FROM All_Prod_Data WHERE [Date] < [PBDS1].[Date]+1;) AS SeqNumb
FROM All_Prod_Data AS PBDS1 WHERE ((PBDS1.Department = "Engines") AND
(PBDS1.UnitName = "Block")) ORDER BY PBDS1.Date;

Gave SeqNumb values that did not start with 1 and contained duplicate
values.

It's as if I need to do the SELECT for the particular Department and
UnitNames before the SELECT Count is implemented, but I don't know how to do
that. Any insights on how to modify the above to accomplish the goal?
Advance thanks for all help!

James Cox
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The criteria for both main query & subquery must be similar:

SELECT ReportID, Department, UnitName, [Date],
(SELECT Count(*)
FROM All_Prod_Data
WHERE Department = PBDS1.Department
AND UnitName = PBDS1.UnitName
AND [Date] < [PBDS1].[Date]+1
) AS SeqNumb
FROM All_Prod_Data AS PBDS1
WHERE Department = "Engines" AND UnitName = "Block"
ORDER BY [Date]

NB: Don't use the word "Date" as a column name, it is a reserved word.
If you insist on using it delimit it with square brackets - [Date].

BTW, the subquery is called a correlated subquery 'cuz it uses some
values of the main query in the WHERE clause.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQVBzPIechKqOuFEgEQLHgACgvssv7uhb+6TZTKMpwdDqYpxHjF8AoITe
lwtKj3ra5nYTgMtwJRG8K+rQ
=IK4a
-----END PGP SIGNATURE-----


James said:
Problem: to generate ranking numbers for a subset of data in a table with a
SINGLE sql statement. The requirement for a single SQL statement is set by
contstraints of a third-party software package that will execute the SQL
against an Access database.

Background: Given a table All_Prod_Data with fields as shown below and with
many possible text values for the Department and UnitName fields

(AutoNumber) (Text) (Text) (Date/Time)

ReportID Department UnitName Date



The goal is to come up with a single sql select that will generate the
following

ReportID Department UnitName Date
SeqNumb

1235 Engines Block 12-Aug-04
12:23:45 1

589 Engines Block 12-Aug-04
16:02:19 2

2309 Engines Block 20-Aug-04
09:56:21 3

If I were starting with the above (except for the SeqNumb field) as a table
Some_Prod_Data, then this sql

SELECT PBDS1.ReportID, PBDS1.Department, PBDS1.UnitName, PBDS1.Date, (Select
Count(*) FROM Some_Prod_Data WHERE [Date] < [PBDS1].[Date]+1;) AS SeqNumb
FROM Some_Prod_Data AS PBDS1 ORDER BY PBDS1.Date;

Would generate what we need.

My attempt to extend the above to include a WHERE statement to return only
specified Departments and UnitNames from table All_Prod_Data , i.e.

SELECT PBDS1.ReportID, PBDS1.Department, PBDS1.UnitName, PBDS1.Date, (Select
Count(*) FROM All_Prod_Data WHERE [Date] < [PBDS1].[Date]+1;) AS SeqNumb
FROM All_Prod_Data AS PBDS1 WHERE ((PBDS1.Department = "Engines") AND
(PBDS1.UnitName = "Block")) ORDER BY PBDS1.Date;

Gave SeqNumb values that did not start with 1 and contained duplicate
values.

It's as if I need to do the SELECT for the particular Department and
UnitNames before the SELECT Count is implemented, but I don't know how to do
that. Any insights on how to modify the above to accomplish the goal?
Advance thanks for all help!
Click to expand...
 
Thamks for the quick response, and your comment about the use of "Date" as a
field name is well taken - unfortunately, I'm a stranger in a strange
database on this one and can't get too frisky about changing the field
names!

Unfortately, there is still a bit of a problem somewhere - using the query
gave the following

ReportID Department UnitName Date SeqNumb


307 Engines Block 29-Jul-04 12:29:09 2
309 Engines Block 29-Jul-04 21:42:18 2
320 Engines Block 04-Aug-04 11:01:06 3
329 Engines Block 08-Aug-04 02:55:58 4
352 Engines Block 19-Aug-04 17:12:24 6
353 Engines Block 20-Aug-04 16:33:44 7
354 Engines Block 21-Aug-04 02:56:16 7
357 Engines Block 22-Aug-04 10:04:39 8
361 Engines Block 23-Aug-04 17:27:59 9
371 Engines Block 25-Aug-04 15:42:23 11
374 Engines Block 25-Aug-04 21:18:44 11
384 Engines Block 30-Aug-04 17:22:51 14
386 Engines Block 31-Aug-04 15:08:27 14
387 Engines Block 31-Aug-04 16:23:08 14
390 Engines Block 02-Sep-04 15:06:26 15
397 Engines Block 12-Sep-04 01:55:17 16
414 Engines Block 17-Sep-04 16:32:10 17
415 Engines Block 19-Sep-04 16:58:41 18



I'm totally clueless as to what the problem is or what a solution would
be...

James Cox



MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The criteria for both main query & subquery must be similar:

SELECT ReportID, Department, UnitName, [Date],
(SELECT Count(*)
FROM All_Prod_Data
WHERE Department = PBDS1.Department
AND UnitName = PBDS1.UnitName
AND [Date] < [PBDS1].[Date]+1
) AS SeqNumb
FROM All_Prod_Data AS PBDS1
WHERE Department = "Engines" AND UnitName = "Block"
ORDER BY [Date]

NB: Don't use the word "Date" as a column name, it is a reserved word.
If you insist on using it delimit it with square brackets - [Date].

BTW, the subquery is called a correlated subquery 'cuz it uses some
values of the main query in the WHERE clause.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQVBzPIechKqOuFEgEQLHgACgvssv7uhb+6TZTKMpwdDqYpxHjF8AoITe
lwtKj3ra5nYTgMtwJRG8K+rQ
=IK4a
-----END PGP SIGNATURE-----


James said:
Problem: to generate ranking numbers for a subset of data in a table with a
SINGLE sql statement. The requirement for a single SQL statement is set by
contstraints of a third-party software package that will execute the SQL
against an Access database.

Background: Given a table All_Prod_Data with fields as shown below and with
many possible text values for the Department and UnitName fields

(AutoNumber) (Text) (Text) (Date/Time)

ReportID Department UnitName Date



The goal is to come up with a single sql select that will generate the
following

ReportID Department UnitName Date
SeqNumb

1235 Engines Block 12-Aug-04
12:23:45 1

589 Engines Block 12-Aug-04
16:02:19 2

2309 Engines Block 20-Aug-04
09:56:21 3

If I were starting with the above (except for the SeqNumb field) as a table
Some_Prod_Data, then this sql

SELECT PBDS1.ReportID, PBDS1.Department, PBDS1.UnitName, PBDS1.Date, (Select
Count(*) FROM Some_Prod_Data WHERE [Date] < [PBDS1].[Date]+1;) AS SeqNumb
FROM Some_Prod_Data AS PBDS1 ORDER BY PBDS1.Date;

Would generate what we need.

My attempt to extend the above to include a WHERE statement to return only
specified Departments and UnitNames from table All_Prod_Data , i.e.

SELECT PBDS1.ReportID, PBDS1.Department, PBDS1.UnitName, PBDS1.Date, (Select
Count(*) FROM All_Prod_Data WHERE [Date] < [PBDS1].[Date]+1;) AS SeqNumb
FROM All_Prod_Data AS PBDS1 WHERE ((PBDS1.Department = "Engines") AND
(PBDS1.UnitName = "Block")) ORDER BY PBDS1.Date;

Gave SeqNumb values that did not start with 1 and contained duplicate
values.

It's as if I need to do the SELECT for the particular Department and
UnitNames before the SELECT Count is implemented, but I don't know how to do
that. Any insights on how to modify the above to accomplish the goal?
Advance thanks for all help!
Click to expand...
Click to expand...
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Do you just want a sequence number that follows the date value? If so,
I believe you will have to change the subquery's WHERE statement to
this:

WHERE Department = PBDS1.Department
AND UnitName = PBDS1.UnitName
AND [Date] < [PBDS1].[Date]
) AS SeqNumb

Don't include the +1 on the Date criteria.

But, the count is still going to just count the sequence for the
Department/UnitName/Date. If you don't want to count per Dept/UnitName
you'll have to take those columns out of the subquery's WHERE statement
& see what happens.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQVGvCYechKqOuFEgEQKV0ACfcEg66GL0uHErDzbWzplq6GYu0nAAoJjI
/w7OmOHx+29c+YKXEpg0zQsg
=PNH1
-----END PGP SIGNATURE-----


James said:
Thamks for the quick response, and your comment about the use of "Date" as a
field name is well taken - unfortunately, I'm a stranger in a strange
database on this one and can't get too frisky about changing the field
names!

Unfortately, there is still a bit of a problem somewhere - using the query
gave the following

ReportID Department UnitName Date SeqNumb


307 Engines Block 29-Jul-04 12:29:09 2
309 Engines Block 29-Jul-04 21:42:18 2
320 Engines Block 04-Aug-04 11:01:06 3
329 Engines Block 08-Aug-04 02:55:58 4
352 Engines Block 19-Aug-04 17:12:24 6
353 Engines Block 20-Aug-04 16:33:44 7
354 Engines Block 21-Aug-04 02:56:16 7
357 Engines Block 22-Aug-04 10:04:39 8
361 Engines Block 23-Aug-04 17:27:59 9
371 Engines Block 25-Aug-04 15:42:23 11
374 Engines Block 25-Aug-04 21:18:44 11
384 Engines Block 30-Aug-04 17:22:51 14
386 Engines Block 31-Aug-04 15:08:27 14
387 Engines Block 31-Aug-04 16:23:08 14
390 Engines Block 02-Sep-04 15:06:26 15
397 Engines Block 12-Sep-04 01:55:17 16
414 Engines Block 17-Sep-04 16:32:10 17
415 Engines Block 19-Sep-04 16:58:41 18



I'm totally clueless as to what the problem is or what a solution would
be...

James Cox



-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The criteria for both main query & subquery must be similar:

SELECT ReportID, Department, UnitName, [Date],
(SELECT Count(*)
FROM All_Prod_Data
WHERE Department = PBDS1.Department
AND UnitName = PBDS1.UnitName
AND [Date] < [PBDS1].[Date]+1
) AS SeqNumb
FROM All_Prod_Data AS PBDS1
WHERE Department = "Engines" AND UnitName = "Block"
ORDER BY [Date]

NB: Don't use the word "Date" as a column name, it is a reserved word.
If you insist on using it delimit it with square brackets - [Date].

BTW, the subquery is called a correlated subquery 'cuz it uses some
values of the main query in the WHERE clause.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQVBzPIechKqOuFEgEQLHgACgvssv7uhb+6TZTKMpwdDqYpxHjF8AoITe
lwtKj3ra5nYTgMtwJRG8K+rQ
=IK4a
-----END PGP SIGNATURE-----


James Cox wrote:

Problem: to generate ranking numbers for a subset of data in a table
Click to expand...
Click to expand...

with a
SINGLE sql statement. The requirement for a single SQL statement is set
Click to expand...
by
contstraints of a third-party software package that will execute the SQL
against an Access database.

Background: Given a table All_Prod_Data with fields as shown below and
Click to expand...
with
many possible text values for the Department and UnitName fields

(AutoNumber) (Text) (Text) (Date/Time)

ReportID Department UnitName Date



The goal is to come up with a single sql select that will generate the
following

ReportID Department UnitName Date
SeqNumb

1235 Engines Block 12-Aug-04
12:23:45 1

589 Engines Block
Click to expand...
12-Aug-04
16:02:19 2

2309 Engines Block
Click to expand...
20-Aug-04
09:56:21 3

If I were starting with the above (except for the SeqNumb field) as a
Click to expand...
table
Some_Prod_Data, then this sql

SELECT PBDS1.ReportID, PBDS1.Department, PBDS1.UnitName, PBDS1.Date,
Click to expand...
(Select
Count(*) FROM Some_Prod_Data WHERE [Date] < [PBDS1].[Date]+1;) AS
Click to expand...
SeqNumb
FROM Some_Prod_Data AS PBDS1 ORDER BY PBDS1.Date;

Would generate what we need.

My attempt to extend the above to include a WHERE statement to return
Click to expand...
only
specified Departments and UnitNames from table All_Prod_Data , i.e.

SELECT PBDS1.ReportID, PBDS1.Department, PBDS1.UnitName, PBDS1.Date,
Click to expand...
(Select
Count(*) FROM All_Prod_Data WHERE [Date] < [PBDS1].[Date]+1;) AS SeqNumb
FROM All_Prod_Data AS PBDS1 WHERE ((PBDS1.Department = "Engines") AND
(PBDS1.UnitName = "Block")) ORDER BY PBDS1.Date;

Gave SeqNumb values that did not start with 1 and contained duplicate
values.

It's as if I need to do the SELECT for the particular Department and
UnitNames before the SELECT Count is implemented, but I don't know how
Click to expand...
Click to expand...

to do
Click to expand...
 
That seems to have fixed the problem of having non-unique and non-sequential
SeqNumb values. The values start with zero not 1, but maybe we can live
with that.

I'm totally amazed by my lack of understanding how the +1 could have the
effect it did! : )

Thanks for your time on this.

Hold the presses! It had been sort of bothering me that we were using a
Date field to generate the sequence information, and it finally occured to
me that the autonumber ReportID in the table was sequential, so that it
could be used instead of the Date. Going back to your original suggestion
for the subquery syntax and substituting ReportID gives

(SELECT Count(*)
FROM All_Prod_Data
WHERE Department = PBDS1.Department
AND UnitName = PBDS1.UnitName
AND ReportID < PBDS1.ReportID +1
) AS SeqNumb

This works, giving a starting value for SeqNumb of 1 with unique and
sequential other values.

The key may lie in what [Date] +1 represents when there can be mutiple
records with the same date, but different times. Reviewing the SeqNumb
values in the table below, it seems there are problems when two or more
dates are within 24 hours of each other - and that's 1 day.

Another experiment with the following subquery

(SELECT Count(*)
FROM All_Prod_Data
WHERE Department = PBDS1.Department
AND UnitName = PBDS1.UnitName
AND [Date] < [PBDS1].[Date] +0.000694
) AS SeqNumb

(where the 0.000694 is the fraction of 24 hours that is one minute) also
gave SeqNumb's that started with 1, were sequential and unique. I suppose
there is no reason not to go to 0.00001157 (one second) if there was a
chance that entries could be that close in time (that's not a possibility
for this application).

Now we're cooking!!!

James Cox


MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Do you just want a sequence number that follows the date value? If so,
I believe you will have to change the subquery's WHERE statement to
this:

WHERE Department = PBDS1.Department
AND UnitName = PBDS1.UnitName
AND [Date] < [PBDS1].[Date]
) AS SeqNumb

Don't include the +1 on the Date criteria.

But, the count is still going to just count the sequence for the
Department/UnitName/Date. If you don't want to count per Dept/UnitName
you'll have to take those columns out of the subquery's WHERE statement
& see what happens.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQVGvCYechKqOuFEgEQKV0ACfcEg66GL0uHErDzbWzplq6GYu0nAAoJjI
/w7OmOHx+29c+YKXEpg0zQsg
=PNH1
-----END PGP SIGNATURE-----


James said:
Thamks for the quick response, and your comment about the use of "Date" as a
field name is well taken - unfortunately, I'm a stranger in a strange
database on this one and can't get too frisky about changing the field
names!

Unfortately, there is still a bit of a problem somewhere - using the query
gave the following

ReportID Department UnitName Date SeqNumb


307 Engines Block 29-Jul-04 12:29:09 2
309 Engines Block 29-Jul-04 21:42:18 2
320 Engines Block 04-Aug-04 11:01:06 3
329 Engines Block 08-Aug-04 02:55:58 4
352 Engines Block 19-Aug-04 17:12:24 6
353 Engines Block 20-Aug-04 16:33:44 7
354 Engines Block 21-Aug-04 02:56:16 7
357 Engines Block 22-Aug-04 10:04:39 8
361 Engines Block 23-Aug-04 17:27:59 9
371 Engines Block 25-Aug-04 15:42:23 11
374 Engines Block 25-Aug-04 21:18:44 11
384 Engines Block 30-Aug-04 17:22:51 14
386 Engines Block 31-Aug-04 15:08:27 14
387 Engines Block 31-Aug-04 16:23:08 14
390 Engines Block 02-Sep-04 15:06:26 15
397 Engines Block 12-Sep-04 01:55:17 16
414 Engines Block 17-Sep-04 16:32:10 17
415 Engines Block 19-Sep-04 16:58:41 18



I'm totally clueless as to what the problem is or what a solution would
be...

James Cox



-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The criteria for both main query & subquery must be similar:

SELECT ReportID, Department, UnitName, [Date],
(SELECT Count(*)
FROM All_Prod_Data
WHERE Department = PBDS1.Department
AND UnitName = PBDS1.UnitName
AND [Date] < [PBDS1].[Date]+1
) AS SeqNumb
FROM All_Prod_Data AS PBDS1
WHERE Department = "Engines" AND UnitName = "Block"
ORDER BY [Date]

NB: Don't use the word "Date" as a column name, it is a reserved word.
If you insist on using it delimit it with square brackets - [Date].

BTW, the subquery is called a correlated subquery 'cuz it uses some
values of the main query in the WHERE clause.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQVBzPIechKqOuFEgEQLHgACgvssv7uhb+6TZTKMpwdDqYpxHjF8AoITe
lwtKj3ra5nYTgMtwJRG8K+rQ
=IK4a
-----END PGP SIGNATURE-----


James Cox wrote:


Problem: to generate ranking numbers for a subset of data in a table
Click to expand...

with a
SINGLE sql statement. The requirement for a single SQL statement is set
by

contstraints of a third-party software package that will execute the SQL
against an Access database.

Background: Given a table All_Prod_Data with fields as shown below and
with

many possible text values for the Department and UnitName fields

(AutoNumber) (Text) (Text) (Date/Time)

ReportID Department UnitName Date



The goal is to come up with a single sql select that will generate the
following

ReportID Department UnitName Date
SeqNumb

1235 Engines Block 12-Aug-04
12:23:45 1

589 Engines Block
12-Aug-04

16:02:19 2

2309 Engines Block
20-Aug-04

09:56:21 3

If I were starting with the above (except for the SeqNumb field) as a
table

Some_Prod_Data, then this sql

SELECT PBDS1.ReportID, PBDS1.Department, PBDS1.UnitName, PBDS1.Date,
(Select

Count(*) FROM Some_Prod_Data WHERE [Date] < [PBDS1].[Date]+1;) AS
SeqNumb

FROM Some_Prod_Data AS PBDS1 ORDER BY PBDS1.Date;

Would generate what we need.

My attempt to extend the above to include a WHERE statement to return
only

specified Departments and UnitNames from table All_Prod_Data , i.e.

SELECT PBDS1.ReportID, PBDS1.Department, PBDS1.UnitName, PBDS1.Date,
(Select

Count(*) FROM All_Prod_Data WHERE [Date] < [PBDS1].[Date]+1;) AS SeqNumb
FROM All_Prod_Data AS PBDS1 WHERE ((PBDS1.Department = "Engines") AND
(PBDS1.UnitName = "Block")) ORDER BY PBDS1.Date;

Gave SeqNumb values that did not start with 1 and contained duplicate
values.

It's as if I need to do the SELECT for the particular Department and
UnitNames before the SELECT Count is implemented, but I don't know how
Click to expand...

to do
that. Any insights on how to modify the above to accomplish the goal?
Advance thanks for all help!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top