|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
June 7th, 2006, 11:37 AM
posted to microsoft.public.access.queries
|
|||
|
|||
List all Sum of every category
Hi, professional
I want to list the sum of each category but it fail to list those sum which has zero value. I have two tables in my database. A. tblType {Field: index, name} 1 Computer 2 Travel 3 Food 4 Others B. tblHistory {Field: date, amount, type} 1/5/2006 100 3 2/5/2006 800 1 3/5/2006 50 4 4/5/2006 10 3 5/5/2006 80 4 6/5/2006 250 1 What i want to do is to summarize the monthly amount of each category Like this.... Computer 1050 Travel 0 Food 110 Others 130 I try to joinly select those record with Year = 2006 and Month = 5 but it just returns sth like this...(which miss the Travel category since there is no Travel expense at that month) Computer 1050 Food 110 Others 130 How should i write the query in order to make it shows up? 
|
|
#2
June 7th, 2006, 02:06 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
List all Sum of every category
Try the following untested SQL statement
SELECT TblType.Name, Sum(H.Amount) FROM tblType LEFT JOIN (SELECT tblHistory.* FROM tblHistory WHERE tblHistory.Date Between #1/1/06# And #1/31/06#) as H ON tblType.Index = H.Type GROUP BY TblType.Name "Albert" wrote in message ... Hi, professional I want to list the sum of each category but it fail to list those sum which has zero value. I have two tables in my database. A. tblType {Field: index, name} 1 Computer 2 Travel 3 Food 4 Others B. tblHistory {Field: date, amount, type} 1/5/2006 100 3 2/5/2006 800 1 3/5/2006 50 4 4/5/2006 10 3 5/5/2006 80 4 6/5/2006 250 1 What i want to do is to summarize the monthly amount of each category Like this.... Computer 1050 Travel 0 Food 110 Others 130 I try to joinly select those record with Year = 2006 and Month = 5 but it just returns sth like this...(which miss the Travel category since there is no Travel expense at that month) Computer 1050 Food 110 Others 130 How should i write the query in order to make it shows up?
|
|
#3
June 10th, 2006, 05:23 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
List all Sum of every category
The SQL given is GREAT!!
In the Left Join operation, if there is no record in the Right Table, the field is null instead of a "0" How can I append 0 in the query? What i want is ... Computer 1050 Travel 0 Food 110 Others 130 Now the query gives me.... Computer 1050 Travel -----null in the second column Food 110 Others 130 "John Spencer" bl... Try the following untested SQL statement SELECT TblType.Name, Sum(H.Amount) FROM tblType LEFT JOIN (SELECT tblHistory.* FROM tblHistory WHERE tblHistory.Date Between #1/1/06# And #1/31/06#) as H ON tblType.Index = H.Type GROUP BY TblType.Name "Albert" wrote in message ... Hi, professional I want to list the sum of each category but it fail to list those sum which has zero value. I have two tables in my database. A. tblType {Field: index, name} 1 Computer 2 Travel 3 Food 4 Others B. tblHistory {Field: date, amount, type} 1/5/2006 100 3 2/5/2006 800 1 3/5/2006 50 4 4/5/2006 10 3 5/5/2006 80 4 6/5/2006 250 1 What i want to do is to summarize the monthly amount of each category Like this.... Computer 1050 Travel 0 Food 110 Others 130 I try to joinly select those record with Year = 2006 and Month = 5 but it just returns sth like this...(which miss the Travel category since there is no Travel expense at that month) Computer 1050 Food 110 Others 130 How should i write the query in order to make it shows up?
|
|
#4
June 10th, 2006, 06:43 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
List all Sum of every category
Use the NZ function around the sum of the field
SELECT TblType.Name, NZ(Sum(H.Amount),0) as Total FROM ... albert wrote: The SQL given is GREAT!! In the Left Join operation, if there is no record in the Right Table, the field is null instead of a "0" How can I append 0 in the query? What i want is ... Computer 1050 Travel 0 Food 110 Others 130 Now the query gives me.... Computer 1050 Travel -----null in the second column Food 110 Others 130 "John Spencer" bl... Try the following untested SQL statement SELECT TblType.Name, Sum(H.Amount) FROM tblType LEFT JOIN (SELECT tblHistory.* FROM tblHistory WHERE tblHistory.Date Between #1/1/06# And #1/31/06#) as H ON tblType.Index = H.Type GROUP BY TblType.Name "Albert" wrote in message ... Hi, professional I want to list the sum of each category but it fail to list those sum which has zero value. I have two tables in my database. A. tblType {Field: index, name} 1 Computer 2 Travel 3 Food 4 Others B. tblHistory {Field: date, amount, type} 1/5/2006 100 3 2/5/2006 800 1 3/5/2006 50 4 4/5/2006 10 3 5/5/2006 80 4 6/5/2006 250 1 What i want to do is to summarize the monthly amount of each category Like this.... Computer 1050 Travel 0 Food 110 Others 130 I try to joinly select those record with Year = 2006 and Month = 5 but it just returns sth like this...(which miss the Travel category since there is no Travel expense at that month) Computer 1050 Food 110 Others 130 How should i write the query in order to make it shows up?
|
|
#5
June 10th, 2006, 07:39 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
List all Sum of every category
GREAT!!!Thank you very much.
"John Spencer" ... Use the NZ function around the sum of the field SELECT TblType.Name, NZ(Sum(H.Amount),0) as Total FROM ... albert wrote: The SQL given is GREAT!! In the Left Join operation, if there is no record in the Right Table, the field is null instead of a "0" How can I append 0 in the query? What i want is ... Computer 1050 Travel 0 Food 110 Others 130 Now the query gives me.... Computer 1050 Travel -----null in the second column Food 110 Others 130 "John Spencer" bl... Try the following untested SQL statement SELECT TblType.Name, Sum(H.Amount) FROM tblType LEFT JOIN (SELECT tblHistory.* FROM tblHistory WHERE tblHistory.Date Between #1/1/06# And #1/31/06#) as H ON tblType.Index = H.Type GROUP BY TblType.Name "Albert" wrote in message ... Hi, professional I want to list the sum of each category but it fail to list those sum which has zero value. I have two tables in my database. A. tblType {Field: index, name} 1 Computer 2 Travel 3 Food 4 Others B. tblHistory {Field: date, amount, type} 1/5/2006 100 3 2/5/2006 800 1 3/5/2006 50 4 4/5/2006 10 3 5/5/2006 80 4 6/5/2006 250 1 What i want to do is to summarize the monthly amount of each category Like this.... Computer 1050 Travel 0 Food 110 Others 130 I try to joinly select those record with Year = 2006 and Month = 5 but it just returns sth like this...(which miss the Travel category since there is no Travel expense at that month) Computer 1050 Food 110 Others 130 How should i write the query in order to make it shows up?
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Count in a query | Ben | Running & Setting Up Queries | 21 | April 4th, 2006 10:12 PM |
| Contact assigned to category doesn't appear in that category list | Froggy | Contacts | 3 | November 14th, 2005 01:27 AM |
| Category list | Sue Mosher [MVP-Outlook] | Contacts | 1 | November 10th, 2005 06:16 PM |
| Master Category List | Kevin | General Discussion | 2 | October 31st, 2005 11:09 PM |
| synchronizing form and list box | Deb Smith | Using Forms | 8 | June 21st, 2004 08:15 PM |
Linear Mode
