Ascending number in query

[?????]

I have an access query which gets data from a number of different
tables. Although the tables have primary key fields, the order in which
the records are returned means that none of these are in ascending
order in the query result set. I need to include in the query a field
that numbers the records in the order that they are returned. The
numbers must be unique and ascending but do not necessarily have to be
consecutive.

For Example:

Field ¦Date from¦No. from¦Name from¦ID from¦ID from¦ID from
I need¦table 1 ¦table 2 ¦table 3 ¦table 1¦table 2¦table 3
to add¦ ¦ ¦ ¦ ¦ ¦
¦ ¦ ¦ ¦ ¦ ¦
1 ¦1/1/1997 ¦ 24 ¦ Boris ¦ 28 ¦ 34 ¦ 68
2 ¦8/3/1998 ¦ 48 ¦ Tom ¦ 45 ¦ 12 ¦ 45
3 ¦8/3/1998 ¦ 1 ¦ Lucy ¦ 1 ¦ 51 ¦ 54
4 ¦4/9/1999 ¦ 15 ¦ Pam ¦ 29 ¦ 97 ¦ 83
5 ¦5/1/2000 ¦ 34 ¦ Marcel ¦ 14 ¦ 11 ¦ 87

Can this be done?

[Tom van Stiphout]

On 14 Feb 2005 04:36:45 -0800, "?????" <al**********@hotmail.com>
wrote:

How do you know that "...that none of these are in ascending
order in the query result set..."? Because of the values in SomeField?
Then order by SomeField!

-Tom.

I have an access query which gets data from a number of different
tables. Although the tables have primary key fields, the order in which
the records are returned means that none of these are in ascending
order in the query result set. I need to include in the query a field
that numbers the records in the order that they are returned. The
numbers must be unique and ascending but do not necessarily have to be
consecutive.

For Example:

Field ¦Date from¦No. from¦Name from¦ID from¦ID from¦ID from
I need¦table 1 ¦table 2 ¦table 3 ¦table 1¦table 2¦table 3
to add¦ ¦ ¦ ¦ ¦ ¦
¦ ¦ ¦ ¦ ¦ ¦
1 ¦1/1/1997 ¦ 24 ¦ Boris ¦ 28 ¦ 34 ¦ 68
2 ¦8/3/1998 ¦ 48 ¦ Tom ¦ 45 ¦ 12 ¦ 45
3 ¦8/3/1998 ¦ 1 ¦ Lucy ¦ 1 ¦ 51 ¦ 54
4 ¦4/9/1999 ¦ 15 ¦ Pam ¦ 29 ¦ 97 ¦ 83
5 ¦5/1/2000 ¦ 34 ¦ Marcel ¦ 14 ¦ 11 ¦ 87

Can this be done?

[?????]

The query set is ordered by date and this can be identical for a number
of records. I need a field that I can use to calculate a running total
down the result set which does not start from zero:

Total brought forward = 878

Date Value Running Total
01/01/05 35 913
02/01/05 12 925
02/01/05 48 973
03/01/05 5 978

The only way I know of to do this is to use DSum. For this I need to be
able to identify the current record and all records which come before
it. I cannot use the date as it would include records with the same
date even if they come after the current record in the result set.

[MGFoster]

????? wrote:

The query set is ordered by date and this can be identical for a number
of records. I need a field that I can use to calculate a running total
down the result set which does not start from zero:

Total brought forward = 878

Date Value Running Total
01/01/05 35 913
02/01/05 12 925
02/01/05 48 973
03/01/05 5 978

The only way I know of to do this is to use DSum. For this I need to be
able to identify the current record and all records which come before
it. I cannot use the date as it would include records with the same
date even if they come after the current record in the result set.

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

You need more data per row that uniquely identifies the row. E.g.:

PartID Date Value Running Total
25 01/01/05 35 913
30 02/01/05 12 925

The natural key for the above data appears to be PartID and [Date].
You'd use that in the running total subquery's WHERE clause:

WHERE PartID = MainQuery.PartID AND [Date] <= MainQuery.[Date]
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQhpFn4echKqOuFEgEQLJ8ACeOJZvJf68qtBZ9XjLzN+fD2 8XJPUAoNNe
TrBxaIKCo6NIid/l2DvvyW21
=YAYY
-----END PGP SIGNATURE-----

[?????]

MGFoster <[e-mail]> wrote in message news:<lA*****************@newsread3.news.pas.earth link.net>...

????? wrote:

The query set is ordered by date and this can be identical for a number
of records. I need a field that I can use to calculate a running total
down the result set which does not start from zero:

Total brought forward = 878

Date Value Running Total
01/01/05 35 913
02/01/05 12 925
02/01/05 48 973
03/01/05 5 978

The only way I know of to do this is to use DSum. For this I need to be
able to identify the current record and all records which come before
it. I cannot use the date as it would include records with the same
date even if they come after the current record in the result set.

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

You need more data per row that uniquely identifies the row. E.g.:

PartID Date Value Running Total
25 01/01/05 35 913
30 02/01/05 12 925

The natural key for the above data appears to be PartID and [Date].
You'd use that in the running total subquery's WHERE clause:

WHERE PartID = MainQuery.PartID AND [Date] <= MainQuery.[Date]
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQhpFn4echKqOuFEgEQLJ8ACeOJZvJf68qtBZ9XjLzN+fD2 8XJPUAoNNe
TrBxaIKCo6NIid/l2DvvyW21
=YAYY
-----END PGP SIGNATURE-----

The problem is that the records may not be unique. The part ID, date
and value could all be exactly the same. If this cannot be done in the
query is it possible to do it in the report? I cannot use the
RunningSum setting on the report field as I need to include the total
brought forward in the running total.

[?????]

I have tried to add a running total field to my query but when I run
the query I get the error "Syntax error (missing operator) in query
expression" and every row in the running total column contains
"#Error". I have included the entire query below in case this helps.
Can anyone see where the problem is?

SELECT CL.CODE, CL.CLIENT, CL.COMPANY, [CBFWD query].BFWD, [YTD
query].[THIS YEAR], [YTD query].[WIP TOTAL], Year([DATE]) AS [YEAR],
TS.DATE, TS.[STAFF INITIALS], TS.WORK, [hours]*[rate] AS [value],
TS.HOURS, TS.RATE, TS.DESCRIPTION, TS.[Unique Ref], DSum("Value","TS","
[DATE] < " & [DATE] & " or ( [DATE] = " & [DATE] & " and [STAFF
INITIALS] < " & [STAFF INITIALS] & " ) or ( [DATE] = " & [DATE] & " and
[STAFF INITIALS] = " & [STAFF INITIALS] & " and [WORK] < " & [WORK] & "
) or ( [DATE] = " & [DATE] & " and [STAFF INITIALS] = " & [STAFF
INITIALS] & " and [WORK] = " & [WORK] & " and ([hours]*[rate]) < " &
[value] & " ) or ( [DATE] = " & [DATE] & " and [STAFF INITIALS] = " &
[STAFF INITIALS] & " and [WORK] = " & [WORK] & " and ([hours]*[rate]) =
" & [value] & " and [DESCRIPTION] < " & [DESCRIPTION] & " ) or ( [DATE]
= " & [DATE] & " and [STAFF INITIALS] = " & [STAFF INITIALS] & " and
[WORK] = " & [WORK] & " and ([hours]*[rate]) = " & [value] & " and
[DESCRIPTION] = " & [DESCRIPTION] & " and [Unique Ref] < " & [Unique
Ref] & " ) ") AS RunTot
FROM ((CL LEFT JOIN TS ON CL.CODE = TS.[CLIENT CODE]) INNER JOIN [YTD
query] ON CL.CODE = [YTD query].[CLIENT CODE]) INNER JOIN [CBFWD query]
ON CL.CODE = [CBFWD query].[CLIENT CODE]
GROUP BY CL.CODE, CL.CLIENT, CL.COMPANY, [CBFWD query].BFWD, [YTD
query].[THIS YEAR], [YTD query].[WIP TOTAL], Year([DATE]), TS.DATE,
TS.[STAFF INITIALS], TS.WORK, [hours]*[rate], TS.HOURS, TS.RATE,
TS.DESCRIPTION, TS.[Unique Ref]
HAVING (((CL.CODE)=[Please enter Client Code]) AND
((TS.DATE)>=DateSerial(3,3,31)) AND ((TS.WORK) Like [Enter optional
works code or * for all:]))
ORDER BY TS.DATE, TS.[STAFF INITIALS], TS.WORK, [hours]*[rate],
TS.DESCRIPTION, TS.[Unique Ref];