Subquery not integrating to query to display previous sum of quantities

[Wessam Fahmy]

I have a table of values which i need to summarize every payment. But
on this summary, I would like a list of the previous payment values as
well. I know I am suppose to use a subquery but every time I do, MS
Access keeps saying "At most one result can come in a subquery."

The idea of the query is this: SELECT Item No, Sum(Qty)., Payment FROM
Table GROUP BY Item No WHERE Payment <= [enter payment value here].

The subquery should be: (SELECT Item No, Sum(Qty)., Payment FROM Table
GROUP BY Item No WHERE Payment < [enter payment value here]) AS
PreviousQty.

Can anybody help? Thanks.


ID Item No Qty Payment
1 1 0 0
2 2 0 0
3 3 0 0
4 4 0 0
5 5 0 0
6 6 0 0
7 7 0 0
8 8 0 0
9 9 0 0
10 6 82 1
11 4 2 1
12 9 4 1
13 4 1 1
14 6 25 1
15 4 0.75 2
16 6 100 2
17 6 25 2
18 4 1 3
19 6 8 3
20 7 7 3
21 6 28 4
22 4 1 3
23 6 20 4
24 6 27 4
25 6 16 4
26 6 16 4
27 1 1 5
28 7 9 5
29 3 2 5
30 6 16 5
31 3 4 5
32 1 2 5
33 6 25 5
34 2 1 7
35 6 15 7
36 6 8 8
37 4 3 8
38 6 8 8
39 3 1 9
40 9 9 9


Item No SumOfQty PrevQty

 

[John W. Vinson]

I have a table of values which i need to summarize every payment. But
on this summary, I would like a list of the previous payment values as
well. I know I am suppose to use a subquery but every time I do, MS
Access keeps saying "At most one result can come in a subquery."

The idea of the query is this: SELECT Item No, Sum(Qty)., Payment FROM
Table GROUP BY Item No WHERE Payment <= [enter payment value here].

If you want to see the details and the summary, you'll do much better to
create a Report and use its Sorting and Grouping dialog to do so. Query
datasheets are very limited; they aren't really intended for data
presentation, but rather for debugging and testing.

If you insist on using a Query you'll need a simple Select query to get the
details, and a second Totals query to get the subtotals, with a UNION query to
string them together. It won't be trivial; the Report will be much easier.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Wessam Fahmy]

If you want to see the details and the summary, you'll do much better to
create a Report and use its Sorting and Grouping dialog to do so. Query
datasheets are very limited; they aren't really intended for data
presentation, but rather for debugging and testing.

If you insist on using a Query you'll need a simple Select query to get the
details, and a second Totals query to get the subtotals, with a UNION query to
string them together. It won't be trivial; the Report will be much easier..

I think my question hasn't been clear. Union queries are used to
append Rows from two different queries/tables. I am trying to combine
an entire Column.

The main query groups the "Item" together and sums their quantities to
output a single row of each item with the WHERE condition being the
payment number. The subquery should do the same exact thing but WHERE
condition is (payment number - 1).

The only way I see around this is to create a duplicate source that
outputs the same information and do an inner join on both of those
sources with one source adding up rows with (payment_number) < payment
and another source adding up rows with (payment_number) <= payment.
This would create two columns from two different WHERE clauses.

However, I want to do this right by using a subquery if for no other
purpose than to learn.

 

[Wessam Fahmy]

I think my question hasn't been clear. Union queries are used to
append Rows from two different queries/tables. I am trying to combine
an entire Column.

The main query groups the "Item" together and sums their quantities to
output a single row of each item with the WHERE condition being the
payment number.  The subquery should do the same exact thing but WHERE
condition is (payment number - 1).

The only way I see around this is to create a duplicate source that
outputs the same information and do an inner join on both of those
sources with one source adding up rows with (payment_number) < payment
and another source adding up rows with (payment_number) <= payment.
This would create two columns from two different WHERE clauses.

However, I want to do this right by using a subquery if for no other
purpose than to learn.

If it will help to add, the table I posted in the starting post may
easily be read in Notepad or fixed with font mode in Google Groups.
It is a sample of data with various quantities for various items
during each payment period.