asked on

total column in query

hi
i have ms access query
i know now we can select summary of the columns within the query
but is there any way to have conditional summary of those columns ?
for example
i have in my table
id
store_no
store_type
sales_amount

if i want to add 4 rows at the end of the query
1st row to have summary of columns for store_type= 1
2nd row to have summary of comuns for store_type= 2
3rd row to have summary of comuns for store_type= 3
4th row to have summary of comuns for store_type= 4

note :
i dont need this summary in ms access report , i need it in ms access query
the purpose is , i'm formatting one excell sheet in same order of the ms access output query
i want to copy the query output , paste it in the formatted excell sheet ( including the summary by types)
instead of formating the cells in excell after exporting it
thanx

Simon

This can be done by adding a sort column to your query output and using union queries for the summary lines, but please post your actual query SQL statement.

NiceMan331

ASKER

here is my sql statement

SELECT Basic_Date.Store, Basic_Date.B_Type, Basic_Date.City, Basic_Date.Address, Basic_Date.Op_Date, Basic_Date.Loc_Rent, Basic_Date.Land_Rent, Basic_Date.Owner, Basic_Date.Proj_Sales, Basic_Date.[1stDaySales], Basic_Date.[2ndDaySales], 

Basic_Date.CDbl(DSum("sales_amt","Yesterday_Sales","[store]= " & [store])) AS Yesterday_Sales, CDbl(DSum("sales_amt","Sales_trans","[store]= " & [store])) AS Total_Sales, CLng(DCount("sales_amt","Accepted_Trans","[store]= " & [store])) AS Days_Count, (Total_Sales/Days_Count) AS Daily_Avg, (nz([loc_rent],0)+nz([land_rent],0)+(nz([rent_percnt],0)*Daily_Avg*365))/365 AS Daily_Rent, CDbl(nz(DSum("asst_value","daily_depr","[rest] =" & [store]),0)) AS T_F_Asst, CDbl(nz(DSum("D_DEPR","daily_depr","[rest] =" & [store]),0)) AS Daily_Depr, Basic_Date.Rent_Percnt, [daily_depr]+[daily_rent] AS D_Rent_Dep, [d_rent_dep]/[daily_avg] AS Rent_Per_Sls
FROM Basic_Date
WHERE (((Basic_Date.IsOpen)=Yes))
ORDER BY Basic_Date.Store;

Open in new window

i need summry for those items

Basic_Date.CDbl(DSum("sales_amt","Yesterday_Sales","[store]= " & [store])) AS Yesterday_Sales, CDbl(DSum("sales_amt","Sales_trans","[store]= " & [store])) AS Total_Sales, CLng(DCount("sales_amt","Accepted_Trans","[store]= " & [store])) AS Days_Count, (Total_Sales/Days_Count) AS Daily_Avg, (nz([loc_rent],0)+nz([land_rent],0)+(nz([rent_percnt],0)*Daily_Avg*365))/365 AS Daily_Rent, CDbl(nz(DSum("asst_value","daily_depr","[rest] =" & [store]),0)) AS T_F_Asst, CDbl(nz(DSum("D_DEPR","daily_depr","[rest] =" & [store]),0)) AS Daily_Depr, Basic_Date.Rent_Percnt, [daily_depr]+[daily_rent] AS D_Rent_Dep, [d_rent_dep]/[daily_avg] AS Rent_Per_Sls

Open in new window

group by Basic_Date.B_Type

thanx

ASKER CERTIFIED SOLUTION

Simon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

NiceMan331

ASKER

ok thanx
any other suggestions from any experts

SOLUTION