how to get query ot show data

[robcarr]

Hi,

can you advise how I can get this to show even if one of the results are blank,

SELECT qryinsideSLA.[# inside of SLA], qryOutsideSLA.[# outside of SLA]
FROM qryOutsideSLA, qryinsideSLA;

qryoutsideSLA is 0 today and queryinsideSLA is 19, when ran it shows both as blank, how do I get around this.

thanks

Hope this is of use, Rob.

 

[ZmrAbdulla]

Better a Union Query !?

________________________________________________________
Zameer Abdulla
Help to find Missing people
My father was a realistic father; not a vending machine dispense everything I demanded for!!

 

[PHV]

SELECT Sum([# inside of SLA]), Sum([# outside of SLA])
FROM (
SELECT [# inside of SLA], 0 As [# outside of SLA] FROM qryinsideSLA
UNION ALL SELECT 0, [# outside of SLA] FROM qryOutsideSLA
) As U

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ZmrAbdulla]

Also to note that avoid reserved words as field name/part of field name

http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

scroll to the bottom of the page.

________________________________________________________
Zameer Abdulla
Help to find Missing people
My father was a realistic father; not a vending machine dispense everything I demanded for!!

 

[VhbuiA]

I have a related question, I think...how do you get a query to show a value of 0 especially if that value doesn't exist yet in the table? I have a table used to document awards given but I want the query results to show all the award types in all quarters even if there are no awards given. Here's my query so far:

SELECT
TBL_ORG.ORG_DIVSUP, TBL_EMPAWARD.AWARD_ID, TBL_QTR.QTR_NAME, SUM(TBL_EMPAWARD_AMT)AS [TOTAL AWARD]
FROM
TBL_QTR INNER JOIN ((TBL_ORG INNER JOIN TBL_EMPDATA ON (TBL_ORG.ORG_ABB=TBL_EMPDATA.ORG_ABB) AND (TBL_ORG.ORG_ID=TBL_EMPDATA.ORG_ID)) INNER JOIN (TBL_AWARD INNER JOIN TBL_EMPAWARD ON TBL_AWARD.AWARD_ID=TBL_EMPAWARD.AWARD_ID) ON (TBL_EMPDATA.EMP_SSN=TBL_EMPAWARD.EMP_SSN) AND (TBL_EMPDATA.EMP_MPSN=TBL_EMPAWARD.EMP_MPSN)) ON TBL_QTR.QTR_ID=TBL_QTR.QTR_NAME, TBL_EMPAWARD.EMPAWARD_FY
HAVING (((TBL_EMPAWARD.EMPAWARD_FY)="2006"));

The reason for why I need to do this is because I need to include it in a report as a subreport.

Thanks!

 

[ZmrAbdulla]

I didn't understant what you are asking..
Do you need to find the count of records?
Use "DCount()"

or if you need to replace null to zero then
use "Nz()" function.

or anything else?



________________________________________________________
Zameer Abdulla
Help to find Missing people
My father was a realistic father; not a vending machine dispense everything I demanded for!!

 

[VhbuiA]

Sorry for not explaining better, I think that I need to use the Nz() function (but I'm not very familiar with it) but maybe if I explain to you the table structure you can point me in the right direction?

Table X
EmpID
OrgID
AwardID
QtrID
AwardAmt

I basically need to create a query that looks like this:
qryAwardsbyType:
OrgID
QtrID
AwardID
SumAwardAmt

Results:
ORGID AwardID QTRID SumAwardAmt
ORG1 Award1 QTR1 0
ORG2 Award1 QTR1 0
ORG3 Award1 QTR1 0
...all orgs, award, quarters listed

The problem is there may or may not be any data associated with all the awards (if a particular award is not given). Therefore, I would like to have the query show all the award types in all qtrs for all orgs. The purpose is to ultimately have a report that enables the end user to see the awards given as it is updated.

Any suggestion would be greatly appreciated!

 

[ZmrAbdulla]

No.. I don't think you need a Nz function here.(for more on this see the help file)
Your problem is the master table is having data and subtable has nothing for that quarter.

I am unable to manipulate the SQL because I am not that expert.
In the design veiw of the query rightclick on the join line between the tables and change it's join type to see the result.

________________________________________________________
Zameer Abdulla
Help to find Missing people
My father was a realistic father; not a vending machine dispense everything I demanded for!!