DSum in a query

A

AT

I have seen similar post regarding this, but I can't seem to make mine work.
I'm sure it's something little. Hope someone can help me.

This is what my query is like: (without the total column)
Ticket # Amt Total
1000 5.55 5.55
1001 6.23 11.78
1002 4.78 16.56

The total column is what I want to get. I'm thinking I need to use a DSum
in the total field in the query, but not sure how to set it up.

Thanks for any help you can give me.
 
D

Duane Hookom

Try this sql and your field and query names:
SELECT TicketNum, Amt,
(SELECT Sum(Amt)
FROM qryA A
WHERE A.TicketNum<=qryA.TicketNum) as Total
FROM qryA;
 
A

AT

Okay, I put this in the field row in my query:

TonsUsedSubtotal: (SELECT "[Ticket #]","[Amt]", (SELECT Sum([Amt]) FROM
"qryInvoice Blend Totals" WHERE [qryInvoice Blend Totals].[Ticket #] <=
[qryInvoice Blend Totals].[Ticket #]) as Total FROM "qryInvoice Blend Totals")

and I am getting a syntax error. I have never done anything like this
before so please forgive my ignorance.

Any ideas?
 
D

Duane Hookom

Try place just this in the field row:
TonsUsedSubtotal: (SELECT Sum([Amt]) FROM [qryInvoice Blend Totals] A WHERE
A.[Ticket #] <=
[qryInvoice Blend Totals].[Ticket #])

--
Duane Hookom
MS Access MVP
--

AT said:
Okay, I put this in the field row in my query:

TonsUsedSubtotal: (SELECT "[Ticket #]","[Amt]", (SELECT Sum([Amt]) FROM
"qryInvoice Blend Totals" WHERE [qryInvoice Blend Totals].[Ticket #] <=
[qryInvoice Blend Totals].[Ticket #]) as Total FROM "qryInvoice Blend
Totals")

and I am getting a syntax error. I have never done anything like this
before so please forgive my ignorance.

Any ideas?





Duane Hookom said:
Try this sql and your field and query names:
SELECT TicketNum, Amt,
(SELECT Sum(Amt)
FROM qryA A
WHERE A.TicketNum<=qryA.TicketNum) as Total
FROM qryA;
Click to expand...
Click to expand...
 
A

AT

Good news, I've got a number finally instead of the error message, but the
number is a sum of the whole column instead of the running sum. Here is what
I have.

TonsUsedSubtotal: (SELECT Sum([Amt]) FROM [qryInvoice Blend Totals] WHERE
[Ticket #] <=[qryInvoice Blend Totals].[Ticket #])

I noticed you had an "A" before the WHERE statement, what is supposed to be
in there?

thanks again!

Duane Hookom said:
Try place just this in the field row:
TonsUsedSubtotal: (SELECT Sum([Amt]) FROM [qryInvoice Blend Totals] A WHERE
A.[Ticket #] <=
[qryInvoice Blend Totals].[Ticket #])

--
Duane Hookom
MS Access MVP
--

AT said:
Okay, I put this in the field row in my query:

TonsUsedSubtotal: (SELECT "[Ticket #]","[Amt]", (SELECT Sum([Amt]) FROM
"qryInvoice Blend Totals" WHERE [qryInvoice Blend Totals].[Ticket #] <=
[qryInvoice Blend Totals].[Ticket #]) as Total FROM "qryInvoice Blend
Totals")

and I am getting a syntax error. I have never done anything like this
before so please forgive my ignorance.

Any ideas?





Duane Hookom said:
Try this sql and your field and query names:
SELECT TicketNum, Amt,
(SELECT Sum(Amt)
FROM qryA A
WHERE A.TicketNum<=qryA.TicketNum) as Total
FROM qryA;

--
Duane Hookom
MS Access MVP
--

I have seen similar post regarding this, but I can't seem to make mine
work.
I'm sure it's something little. Hope someone can help me.

This is what my query is like: (without the total column)
Ticket # Amt Total
1000 5.55 5.55
1001 6.23 11.78
1002 4.78 16.56

The total column is what I want to get. I'm thinking I need to use a
DSum
in the total field in the query, but not sure how to set it up.

Thanks for any help you can give me.
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

Thanks for noticing the "A". Please put it back in the two places you
removed it from. You might not return the "sum of the whole column".

--
Duane Hookom
MS Access MVP


AT said:
Good news, I've got a number finally instead of the error message, but the
number is a sum of the whole column instead of the running sum. Here is what
I have.

TonsUsedSubtotal: (SELECT Sum([Amt]) FROM [qryInvoice Blend Totals] WHERE
[Ticket #] <=[qryInvoice Blend Totals].[Ticket #])

I noticed you had an "A" before the WHERE statement, what is supposed to be
in there?

thanks again!

Duane Hookom said:
Try place just this in the field row:
TonsUsedSubtotal: (SELECT Sum([Amt]) FROM [qryInvoice Blend Totals] A WHERE
A.[Ticket #] <=
[qryInvoice Blend Totals].[Ticket #])

--
Duane Hookom
MS Access MVP
--

AT said:
Okay, I put this in the field row in my query:

TonsUsedSubtotal: (SELECT "[Ticket #]","[Amt]", (SELECT Sum([Amt]) FROM
"qryInvoice Blend Totals" WHERE [qryInvoice Blend Totals].[Ticket #] <=
[qryInvoice Blend Totals].[Ticket #]) as Total FROM "qryInvoice Blend
Totals")

and I am getting a syntax error. I have never done anything like this
before so please forgive my ignorance.

Any ideas?





:

Try this sql and your field and query names:
SELECT TicketNum, Amt,
(SELECT Sum(Amt)
FROM qryA A
WHERE A.TicketNum<=qryA.TicketNum) as Total
FROM qryA;

--
Duane Hookom
MS Access MVP
--

I have seen similar post regarding this, but I can't seem to make mine
work.
I'm sure it's something little. Hope someone can help me.

This is what my query is like: (without the total column)
Ticket # Amt Total
1000 5.55 5.55
1001 6.23 11.78
1002 4.78 16.56

The total column is what I want to get. I'm thinking I need to use a
DSum
in the total field in the query, but not sure how to set it up.

Thanks for any help you can give me.
Click to expand...
Click to expand...
Click to expand...
 
A

AT

Here's what I have now:

TotalTonsUsed: (SELECT Sum([Amt]) FROM [qryInvoice Blend Totals] A
WHERE A.[Ticket #] <= [qryInvoice Blend Totals].[Ticket #])

and it's asking me for the ticket #.



Duane Hookom said:
Thanks for noticing the "A". Please put it back in the two places you
removed it from. You might not return the "sum of the whole column".

--
Duane Hookom
MS Access MVP


AT said:
Good news, I've got a number finally instead of the error message, but the
number is a sum of the whole column instead of the running sum. Here is what
I have.

TonsUsedSubtotal: (SELECT Sum([Amt]) FROM [qryInvoice Blend Totals] WHERE
[Ticket #] <=[qryInvoice Blend Totals].[Ticket #])

I noticed you had an "A" before the WHERE statement, what is supposed to be
in there?

thanks again!

Duane Hookom said:
Try place just this in the field row:
TonsUsedSubtotal: (SELECT Sum([Amt]) FROM [qryInvoice Blend Totals] A WHERE
A.[Ticket #] <=
[qryInvoice Blend Totals].[Ticket #])

--
Duane Hookom
MS Access MVP
--

Okay, I put this in the field row in my query:

TonsUsedSubtotal: (SELECT "[Ticket #]","[Amt]", (SELECT Sum([Amt]) FROM
"qryInvoice Blend Totals" WHERE [qryInvoice Blend Totals].[Ticket #] <=
[qryInvoice Blend Totals].[Ticket #]) as Total FROM "qryInvoice Blend
Totals")

and I am getting a syntax error. I have never done anything like this
before so please forgive my ignorance.

Any ideas?





:

Try this sql and your field and query names:
SELECT TicketNum, Amt,
(SELECT Sum(Amt)
FROM qryA A
WHERE A.TicketNum<=qryA.TicketNum) as Total
FROM qryA;

--
Duane Hookom
MS Access MVP
--

I have seen similar post regarding this, but I can't seem to make mine
work.
I'm sure it's something little. Hope someone can help me.

This is what my query is like: (without the total column)
Ticket # Amt Total
1000 5.55 5.55
1001 6.23 11.78
1002 4.78 16.56

The total column is what I want to get. I'm thinking I need to use a
DSum
in the total field in the query, but not sure how to set it up.

Thanks for any help you can give me.
Click to expand...
Click to expand...
Click to expand...
 
A

AT

I got it, finally. I didn't have a primary key in the table. I just had a
field that was indexed with no duplicates. So it's working now. Thanks for
your help!

AT said:
Here's what I have now:

TotalTonsUsed: (SELECT Sum([Amt]) FROM [qryInvoice Blend Totals] A
WHERE A.[Ticket #] <= [qryInvoice Blend Totals].[Ticket #])

and it's asking me for the ticket #.



Duane Hookom said:
Thanks for noticing the "A". Please put it back in the two places you
removed it from. You might not return the "sum of the whole column".

--
Duane Hookom
MS Access MVP


AT said:
Good news, I've got a number finally instead of the error message, but the
number is a sum of the whole column instead of the running sum. Here is what
I have.

TonsUsedSubtotal: (SELECT Sum([Amt]) FROM [qryInvoice Blend Totals] WHERE
[Ticket #] <=[qryInvoice Blend Totals].[Ticket #])

I noticed you had an "A" before the WHERE statement, what is supposed to be
in there?

thanks again!

:

Try place just this in the field row:
TonsUsedSubtotal: (SELECT Sum([Amt]) FROM [qryInvoice Blend Totals] A WHERE
A.[Ticket #] <=
[qryInvoice Blend Totals].[Ticket #])

--
Duane Hookom
MS Access MVP
--

Okay, I put this in the field row in my query:

TonsUsedSubtotal: (SELECT "[Ticket #]","[Amt]", (SELECT Sum([Amt]) FROM
"qryInvoice Blend Totals" WHERE [qryInvoice Blend Totals].[Ticket #] <=
[qryInvoice Blend Totals].[Ticket #]) as Total FROM "qryInvoice Blend
Totals")

and I am getting a syntax error. I have never done anything like this
before so please forgive my ignorance.

Any ideas?





:

Try this sql and your field and query names:
SELECT TicketNum, Amt,
(SELECT Sum(Amt)
FROM qryA A
WHERE A.TicketNum<=qryA.TicketNum) as Total
FROM qryA;

--
Duane Hookom
MS Access MVP
--

I have seen similar post regarding this, but I can't seem to make mine
work.
I'm sure it's something little. Hope someone can help me.

This is what my query is like: (without the total column)
Ticket # Amt Total
1000 5.55 5.55
1001 6.23 11.78
1002 4.78 16.56

The total column is what I want to get. I'm thinking I need to use a
DSum
in the total field in the query, but not sure how to set it up.

Thanks for any help you can give me.
Click to expand...
Click to expand...
Click to expand...
 
Top