count at 0 when no value

[haerion]

Hi all, was wondering if there is a way to get a count of zero to appear in a qeury like this:

SELECT [PO Query].VendorNo, Count([PO Query].VendorNo) AS CountOfVendorNo
FROM [PO Query]
GROUP BY [PO Query].VendorNo, [PO Query].[Store#]
HAVING ((([PO Query].[Store#])=[forms]![VendorOrder]![chkstore]));

Right now, if there is no VendorNo in the store# I select, the VendorNo doesnt appear at all

Thanks,

Haerion

 

[traingamer]

One way is to use the NZ function:

Nz([PO Query].VendorNo,0)

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[Golom]

You would need to do a left join with some table that does have the vendor code.

Obviously, if the vendor code doesn't exist in the data, then there's nothing to report and nothing will appear (zero or anything else.)

 

[traingamer]

Sorry, misread the question. I think.

 

[haerion]

I never did a left join, could you write how I should write it to work?

Thanks

 

[Golom]

Something like

SELECT V.VendorNo, Count(Q.VendorNo) AS CountOfVendorNo

FROM Vendors V LEFT JOIN [PO Query] Q 
     ON V.VendorNo = Q.VendorNo

WHERE Q.[Store#]=[forms]![VendorOrder]![chkstore]

GROUP BY Q.VendorNo, Q.[Store#];

Where "Vendors" is some table containing all vendor numbers.

 

[lespaul]

Alternatively, in the query design grid, you can modify the join type by right clicking on the relationship between PO Query and Vendors (if there isn't one you can add it) and then you'll have choices like:

Only include records where both fields are equal
Include all the records from PO Query and only the matches in Vendor
Include all the records from Vendor and only the matches in PO Query.

 

[haerion]

seem like when I do that the one without record dissapear anyway, thats weird

 

[Golom]

Small error in the SQL

SELECT V.VendorNo, Count(Q.VendorNo) AS CountOfVendorNo

FROM Vendors V LEFT JOIN [PO Query] Q 
     ON V.VendorNo = Q.VendorNo

WHERE Q.[Store#]=[forms]![VendorOrder]![chkstore]

GROUP BY [red]V[/red].VendorNo