sum values in a field using criteria from another field

[schenell]

i have two tables (customer & Inv_Sales). The inv_sales table contains the
transaction type (trans_type), total net amt (tot_net_amt) and transaction
date (trans_date).

my two transaction types are DI and CM. in my report i need to generate a
customer list based on the sales for a period. i have the user entering the
beginning and end date for the period.

this is my code thus far:
PARAMETERS [Enter start date:] DateTime, [Enter end date:] DateTime;
SELECT DISTINCT customer.cust_num, customer.cust_name, inv_sales.trans_date,
inv_sales.trans_type, inv_sales.tot_net_amt
FROM customer, inv_sales
WHERE (((inv_sales.trans_date) Between [enter start date:] And [enter end
date:]));

i'm stuck from this point on. I want to get the sum of my DI and CM
trans_type for each cust_num and only have the cust_num, cust_name,
trans_date and sum(tot_net_amt) for each cust_num.

Any help would be appreciated.

 

[Duane Hookom]

If you don't want the details, change your query to a totals query.

If you want something different, please be more specific.

 

[schenell]

i'm new to access so i'm not sure exactly what u mean...but for eg.
cust trans_type trans_date Amt
1 DI 1 march 05 $50
DI 3 march 05 $80
CM 1 march 05 - $10

2 DI 1 march 05 $23
CM 4 march 05 - $50
DI 1 march 05 $67

in my report i want the following headings to show up:
cust num, trans date, Net amount

what i'm trying to do is add all the transactions for each cust num (i.e.
both DI & CM trans types) for the period specified by the user.

end result should be (if user specifies trans period 1 march 05):
cust trans date net amt
1 1 march 05 $40
2 1 march 05 $90

If you don't want the details, change your query to a totals query.

If you want something different, please be more specific.

--
Duane Hookom
MS Access MVP
--

i have two tables (customer & Inv_Sales). The inv_sales table contains the
transaction type (trans_type), total net amt (tot_net_amt) and transaction
date (trans_date).

my two transaction types are DI and CM. in my report i need to generate a
customer list based on the sales for a period. i have the user entering
the
beginning and end date for the period.

this is my code thus far:
PARAMETERS [Enter start date:] DateTime, [Enter end date:] DateTime;
SELECT DISTINCT customer.cust_num, customer.cust_name,
inv_sales.trans_date,
inv_sales.trans_type, inv_sales.tot_net_amt
FROM customer, inv_sales
WHERE (((inv_sales.trans_date) Between [enter start date:] And [enter end
date:]));

i'm stuck from this point on. I want to get the sum of my DI and CM
trans_type for each cust_num and only have the cust_num, cust_name,
trans_date and sum(tot_net_amt) for each cust_num.

Any help would be appreciated.

 

[Duane Hookom]

Change your query to:
SELECT Cust, TransDate, Sum(Amt) as NetAmt
FROM tblTransactions
WHERE Trans_Type IN ("DI","CM") AND
TransDate = #03/01/2005#
GROUP BY Cust, TransDate

--
Duane Hookom
MS Access MVP
--

i'm new to access so i'm not sure exactly what u mean...but for eg.
cust trans_type trans_date Amt
1 DI 1 march 05 $50
DI 3 march 05 $80
CM 1 march 05 - $10

2 DI 1 march 05 $23
CM 4 march 05 - $50
DI 1 march 05 $67

in my report i want the following headings to show up:
cust num, trans date, Net amount

what i'm trying to do is add all the transactions for each cust num (i.e.
both DI & CM trans types) for the period specified by the user.

end result should be (if user specifies trans period 1 march 05):
cust trans date net amt
1 1 march 05 $40
2 1 march 05 $90

If you don't want the details, change your query to a totals query.

If you want something different, please be more specific.

--
Duane Hookom
MS Access MVP
--

i have two tables (customer & Inv_Sales). The inv_sales table contains
the
transaction type (trans_type), total net amt (tot_net_amt) and
transaction
date (trans_date).

my two transaction types are DI and CM. in my report i need to
generate a
customer list based on the sales for a period. i have the user entering
the
beginning and end date for the period.

this is my code thus far:
PARAMETERS [Enter start date:] DateTime, [Enter end date:] DateTime;
SELECT DISTINCT customer.cust_num, customer.cust_name,
inv_sales.trans_date,
inv_sales.trans_type, inv_sales.tot_net_amt
FROM customer, inv_sales
WHERE (((inv_sales.trans_date) Between [enter start date:] And [enter
end
date:]));

i'm stuck from this point on. I want to get the sum of my DI and CM
trans_type for each cust_num and only have the cust_num, cust_name,
trans_date and sum(tot_net_amt) for each cust_num.

Any help would be appreciated.