Need to Show Value of Zero

[bassstuf via AccessMonster.com]

I want to get a query to show a value of zero, if there are no records.

The fields im using are:

RMA Number: 5-7 digit number, to reference the return
Part Number: A description of the part (has both text and letters in it)
Location: There are 4 Locations and I want to be able to tell how many Part
Numbers are at each location, if there are none, then I would like it to show
"0".

The basic SQL is:

SELECT DISTINCTROW Return.[RMA Number], Return.[Part Number], Return.Location,
Count(*) AS [Count Of Return]
FROM Return
GROUP BY Return.[RMA Number], Return.[Part Number], Return.Location;


Please help, this is driving me crazy

 

[Ryan]

Try
SELECT DISTINCTROW Return.[RMA Number], Return.[Part Number], Return.Location,
0 + Count(*) AS [Count Of Return]
FROM Return
GROUP BY Return.[RMA Number], Return.[Part Number], Return.Location;

 

[John Spencer]

If there are no parts at a location then there is no row to return.
Do you have a parts table and a location table that are separate from the
Return table.

If not, then you need to this in several steps
qLocations
SELECT DISTINCT Location
FROM Return

qParts
SELECT Distinct [Part Number]
FROM Return

qAllLocationsAllParts
SELECT Location, [Part Number]
FROM qLocations, qParts

NOW
SELECT q.[Part Number], q.Location, Return.[RMA Number]
, Count(Return.[Part Number]) as Returns
FROM qAllLocationsAllParts as Q LEFT JOIN Return
ON Q.Location = Return.Location
AND Q.[Part Number] = Return.[part number]
GROUP BY q.[Part Number], q.Location, Return.[RMA Number]


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[bassstuf via AccessMonster.com]

Its still only showing the 7 part numbers that are at that location, and not
zero for the other 14 I have

Try
SELECT DISTINCTROW Return.[RMA Number], Return.[Part Number], Return.Location,
0 + Count(*) AS [Count Of Return]
FROM Return
GROUP BY Return.[RMA Number], Return.[Part Number], Return.Location;

I want to get a query to show a value of zero, if there are no records.

[quoted text clipped - 14 lines]

Please help, this is driving me crazy

 

[bassstuf via AccessMonster.com]

I do have a table for part numbers and location, since they are comboboxes on
my main table

Main Table - Return
Location Table - [Options]![Location]
Part Num. Table - [Items]![View]

if these are not what will help to work, can you explain what you wrote last
message

If there are no parts at a location then there is no row to return.
Do you have a parts table and a location table that are separate from the
Return table.

If not, then you need to this in several steps
qLocations
SELECT DISTINCT Location
FROM Return

qParts
SELECT Distinct [Part Number]
FROM Return

qAllLocationsAllParts
SELECT Location, [Part Number]
FROM qLocations, qParts

NOW
SELECT q.[Part Number], q.Location, Return.[RMA Number]
, Count(Return.[Part Number]) as Returns
FROM qAllLocationsAllParts as Q LEFT JOIN Return
ON Q.Location = Return.Location
AND Q.[Part Number] = Return.[part number]
GROUP BY q.[Part Number], q.Location, Return.[RMA Number]

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I want to get a query to show a value of zero, if there are no records.

[quoted text clipped - 14 lines]

Please help, this is driving me crazy

 

[John Spencer]

Ok, then I'm not quite sure about what fields you have in those two tables


first query.
-- Add your Part num table and location table into a query with no join
-- Put the Location and the Part number into the query
-- Save the query as qAllPartsAllLocations

NOW, build a new query that looks like this

SELECT q.[Part Number], q.Location, Return.[RMA Number]
, Count(Return.[Part Number]) as Returns
FROM qAllPartsAllLocations as Q LEFT JOIN Return
ON Q.Location = Return.Location
AND Q.[Part Number] = Return.[part number]
GROUP BY q.[Part Number], q.Location, Return.[RMA Number]

In query design view, you need to do the following
-- add the saved query and the return table to a new query
-- join the saved query to the return table on the location and part number
feilds (drag from location to location and then from part number to part number)
-- Double-click on each join line and change the join to show all records in
the query and matching records in the table
-- Select View: Totals from the menu
-- Add part number and location from the saved query
-- add part number from the table
-- Change GROUP BY to COUNT for this field
-- add any other fields you want to display



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I do have a table for part numbers and location, since they are comboboxes on
my main table

Main Table - Return
Location Table - [Options]![Location]
Part Num. Table - [Items]![View]

if these are not what will help to work, can you explain what you wrote last
message

If there are no parts at a location then there is no row to return.
Do you have a parts table and a location table that are separate from the
Return table.

If not, then you need to this in several steps
qLocations
SELECT DISTINCT Location
FROM Return
qParts
SELECT Distinct [Part Number]
FROM Return
qAllLocationsAllParts
SELECT Location, [Part Number]
FROM qLocations, qParts
NOW
SELECT q.[Part Number], q.Location, Return.[RMA Number]
, Count(Return.[Part Number]) as Returns
FROM qAllLocationsAllParts as Q LEFT JOIN Return
ON Q.Location = Return.Location
AND Q.[Part Number] = Return.[part number]
GROUP BY q.[Part Number], q.Location, Return.[RMA Number]

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I want to get a query to show a value of zero, if there are no records.

[quoted text clipped - 14 lines]

Please help, this is driving me crazy

 

[bassstuf via AccessMonster.com]

Thanks Alot, Ive been trying to get this to work for awhile now

Dave

Ok, then I'm not quite sure about what fields you have in those two tables

first query.
-- Add your Part num table and location table into a query with no join
-- Put the Location and the Part number into the query
-- Save the query as qAllPartsAllLocations

NOW, build a new query that looks like this

SELECT q.[Part Number], q.Location, Return.[RMA Number]
, Count(Return.[Part Number]) as Returns
FROM qAllPartsAllLocations as Q LEFT JOIN Return
ON Q.Location = Return.Location
AND Q.[Part Number] = Return.[part number]
GROUP BY q.[Part Number], q.Location, Return.[RMA Number]

In query design view, you need to do the following
-- add the saved query and the return table to a new query
-- join the saved query to the return table on the location and part number
feilds (drag from location to location and then from part number to part number)
-- Double-click on each join line and change the join to show all records in
the query and matching records in the table
-- Select View: Totals from the menu
-- Add part number and location from the saved query
-- add part number from the table
-- Change GROUP BY to COUNT for this field
-- add any other fields you want to display

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I do have a table for part numbers and location, since they are comboboxes on
my main table

[quoted text clipped - 37 lines]

[quoted text clipped - 14 lines]
Please help, this is driving me crazy

 

[John Spencer]

Sorry, your response was ambiguous.

Do you mean that you are having trouble with the proposed solution or do you
mean that you had been seeking a solution for a long time and now have a solution?

In other words, do you need further help on this.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[bassstuf via AccessMonster.com]

nope, i dont need any more help with this, ive just been working on it for
awhile