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?
5  5578 
On 14 Feb 2005 04:36:45 -0800, "?????" <al**********@h otmail.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.
????? 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.PartI D AND [Date] <= MainQuery.[Date]
--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQhpFn4echKq OuFEgEQLJ8ACeOJ ZvJf68qtBZ9XjLz N+fD28XJPUAoNNe
TrBxaIKCo6NIid/l2DvvyW21
=YAYY
-----END PGP SIGNATURE-----
MGFoster <me@privacy.com > wrote in message news:<lA******* **********@news read3.news.pas. earthlink.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.PartI D AND [Date] <= MainQuery.[Date]
--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQhpFn4echKq OuFEgEQLJ8ACeOJ ZvJf68qtBZ9XjLz N+fD28XJPUAoNNe
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","T S","
[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)>=Dat eSerial(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];
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: wil smiths |
last post by:
int i,j,val=4;
int arr={4,7,3,9};
int temp=-1;
int iptr=-1;
for(i=0; i<=val-2;i++)
{
iptr=i;
for(j=i+1;j<=val-1;j++)
|
by: Rich N |
last post by:
When I open my form, I want the records already in it to be in
Ascending Order based on one field. Though I keep putting them in
Ascending order and then saving the form, each time I re-open the
form, they're out of order. What am I missing here?
|
by: sunilkes |
last post by:
I have a to only show date. I set the date to be in ascending order,
but still 01/11/2006 shows first, and 31/10/2006 shows last..It is only
ascending the date and not the complete date including month and
year..please help
Thanks
|
by: dwelden |
last post by:
I have successfully used the sort lambda construct described in
http://mail.python.org/pipermail/python-list/2006-April/377443.html.
However, how do I take it one step further such that some values can be
sorted ascending and others descending? Easy enough if the sort values
are numeric (just negate the value), but what about text?
Is the only option to define an external sorting function to loop
through the list and perform the...
|
by: askmatlab |
last post by:
Hello all:
I would like to insert a number into a linked list in ascending order.
Is the following function correct?
void insert(Node **node, int v)
{
Node *tmp = (Node *)malloc(sizeof(Node));
while(*node && (*node)->value < v) node = &(*node)->next;
| | |
by: Eric Lilja |
last post by:
Hello, consider the following assignment and my code for it:
/*
Write a program that does the following:
* Integer numbers shall be read from a textfile and stored in a
std::vector.
The name of the input file is to be given on the command line.
* All negative values in the vector shall then be replaced with
their
positive counterpart, using the standard algorithm for_each.
|
by: reon |
last post by:
Here is my source code....
And any one pls help me how can we find the output integers sort by ascending and find there average...
#include<iostream.h>
#include<conio.h>
void main() {
int num;
clrscr();
cout<<"ENTER NUMBERS TO SORT THEM IN ASCENDING ORDER & FIND THE AVERAGE"<<endl;
for(int times=0;times<=10;times++)
|
by: cess |
last post by:
If the user input three numbers, say (1,3,2), how it will become 1,2,3 or in ascending order?? there is something wrong/lacking with my code, help plzzzz!!
import java.io.*;
public class sign {
public static void main (String args)throws Exception
{
String number;
int numberInteger;
int temp;
|
by: mpasschi |
last post by:
MS Access 2002, in Windows XP.
Hello all, could you please have a look at this problem description?
I have a Query that shows in 'Design view' two fields in which the "Ascending" option is selected.
When opening the Query it shows exactly these selections, and both field are running ascending, so till now no problem.
Out of this Query I have a Form attached which shows one Field as in the linked query (so ascending) but the second Field...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
