Create Subtotals

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

General Problem:
I have a table with dollar fields. I want to take each
record in the table and create several new records: each
new record would essentially be a copy of the original
but add a new field to identify the subtotal I want the
original record to show-up (e.g., month, quarter, year).

Likely Solution:
Using VBA, read the first record from original table and
output the new records to a second table.

Can anybody provide some code to get me started?
 
Mark:

Could you be more specific?

I just did this yesterday with some daily production figures
and I used a subquery in the select statement

SELECT
Orders.OrderID
, Orders.SubTotal
, (SELECT Sum([Payment]) FROM ARDetails
WHERE ARDetails.OrderID = Orders.OrderID) AS Paid
FROM Orders
WHERE Orders.OrderDate> #12/01/2003#

In this case, each order has one SubTotal amount stored
in a field (I know, bad design ... not mine!). Anyhow, I
needed to compare the SubTotal amount with what the cust
had already prepaid, which is stored in multiple records in
the ARDetails table.

One OrderID record with a column that's a sum of multiple
ARDetail records. Is that what you're looking for?
 
Danny -

Thank you for the ideas. I think I need a more general
solution as I have about 15 dollar fields that need to
make it into the sub total.

Mark
-----Original Message-----
Mark:

Could you be more specific?

I just did this yesterday with some daily production figures
and I used a subquery in the select statement

SELECT
Orders.OrderID
, Orders.SubTotal
, (SELECT Sum([Payment]) FROM ARDetails
WHERE ARDetails.OrderID = Orders.OrderID) AS Paid
FROM Orders
WHERE Orders.OrderDate> #12/01/2003#

In this case, each order has one SubTotal amount stored
in a field (I know, bad design ... not mine!). Anyhow, I
needed to compare the SubTotal amount with what the cust
had already prepaid, which is stored in multiple records in
the ARDetails table.

One OrderID record with a column that's a sum of multiple
ARDetail records. Is that what you're looking for?
--

Danny J. Lesandrini
(e-mail address removed)
http://amazecreations.com/datafast


Mark said:
General Problem:
I have a table with dollar fields. I want to take each
record in the table and create several new records: each
new record would essentially be a copy of the original
but add a new field to identify the subtotal I want the
original record to show-up (e.g., month, quarter, year).

Likely Solution:
Using VBA, read the first record from original table and
output the new records to a second table.

Can anybody provide some code to get me started?
Click to expand...


.
Click to expand...
 
Ok, this is what I wasn't understanding. Are they in the same table ...
that is to say, are all data in the same table ... like this:

SELECT
fldID
, fldDate
, (fldDollar1 + fldDollar2 + fldDollar3) As SubTotal
FROM MyTable


Now, if some of your fields are NULL, you will have to
handle that. Use this syntax instead:

( Nz(fldDollar1,0) +Nz(fldDollar2,0) +Nz(fldDollar3,0) )
--

Danny J. Lesandrini
(e-mail address removed)
http://amazecreations.com/datafast


Mark said:
Danny -

Thank you for the ideas. I think I need a more general
solution as I have about 15 dollar fields that need to
make it into the sub total.

Mark
-----Original Message-----
Mark:

Could you be more specific?

I just did this yesterday with some daily production figures
and I used a subquery in the select statement

SELECT
Orders.OrderID
, Orders.SubTotal
, (SELECT Sum([Payment]) FROM ARDetails
WHERE ARDetails.OrderID = Orders.OrderID) AS Paid
FROM Orders
WHERE Orders.OrderDate> #12/01/2003#

In this case, each order has one SubTotal amount stored
in a field (I know, bad design ... not mine!). Anyhow, I
needed to compare the SubTotal amount with what the cust
had already prepaid, which is stored in multiple records in
the ARDetails table.

One OrderID record with a column that's a sum of multiple
ARDetail records. Is that what you're looking for?
--

Danny J. Lesandrini
(e-mail address removed)
http://amazecreations.com/datafast


Mark said:
General Problem:
I have a table with dollar fields. I want to take each
record in the table and create several new records: each
new record would essentially be a copy of the original
but add a new field to identify the subtotal I want the
original record to show-up (e.g., month, quarter, year).

Likely Solution:
Using VBA, read the first record from original table and
output the new records to a second table.

Can anybody provide some code to get me started?
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top