Default value return

D

Dragon

I have a query that counts my trailers loaded in a given day for a given
warehouse. I have the data broken down by types (ie, export, truck, can) and
in a particular order.

The problem is: I need the query to return a default value of 0 for each
possiblity when there is no trailers of a particular type loaded in the given
day. (SQL below)
ie: if there were 3 non export, no export trucks and 2 export cans I need an
output of:

Non export: 3
Export Trucks: 0
Export Cans: 2

Thanks,
Dragon

SQL:
SELECT [PCS / Bulk Ship].Warehouse, [PCS / Bulk Ship].ShipDate, [PCS / Bulk
Ship].Export, [PCS / Bulk Ship].Can, Count([PCS / Bulk Ship].[Unit #]) AS
[CountOfUnit #]
FROM [PCS / Bulk Ship]
WHERE ((([PCS / Bulk Ship].[Unit #])>"0.1") AND (([PCS / Bulk Ship].Rail)=0))
GROUP BY [PCS / Bulk Ship].Warehouse, [PCS / Bulk Ship].ShipDate, [PCS /
Bulk Ship].Export, [PCS / Bulk Ship].Can
HAVING ((([PCS / Bulk Ship].Warehouse)='Bulk') AND (([PCS / Bulk
Ship].ShipDate)=[What is the date]))
ORDER BY [PCS / Bulk Ship].Export DESC , [PCS / Bulk Ship].Can DESC;
 
P

pietlinden

I have a query that counts my trailers loaded in a given day for a given
warehouse.  I have the data broken down by types (ie, export, truck, can) and
in a particular order.

The problem is: I need the query to return a default value of 0 for each
possiblity when there is no trailers of a particular type loaded in the given
day.  (SQL below)
ie: if there were 3 non export, no export trucks and 2 export cans I needan
output of:

Non export: 3
Export Trucks: 0
Export Cans: 2

Thanks,
Dragon

SQL:
SELECT [PCS / Bulk Ship].Warehouse, [PCS / Bulk Ship].ShipDate, [PCS / Bulk
Ship].Export, [PCS / Bulk Ship].Can, Count([PCS / Bulk Ship].[Unit #]) AS
[CountOfUnit #]
FROM [PCS / Bulk Ship]
WHERE ((([PCS / Bulk Ship].[Unit #])>"0.1") AND (([PCS / Bulk Ship].Rail)=0))
GROUP BY [PCS / Bulk Ship].Warehouse, [PCS / Bulk Ship].ShipDate, [PCS /
Bulk Ship].Export, [PCS / Bulk Ship].Can
HAVING ((([PCS / Bulk Ship].Warehouse)='Bulk') AND (([PCS / Bulk
Ship].ShipDate)=[What is the date]))
ORDER BY [PCS / Bulk Ship].Export DESC , [PCS / Bulk Ship].Can DESC;
Click to expand...

are you getting nulls? Use Nz() to convert the nulls to zeroes.
 
D

Dragon

When there are no records it simply doesn't display anything for that option.
I'm not familiar with the Nz() command recommended.
How would it fit into my query?

Thanks,
Dragon

[email protected] said:
I have a query that counts my trailers loaded in a given day for a given
warehouse. I have the data broken down by types (ie, export, truck, can) and
in a particular order.

The problem is: I need the query to return a default value of 0 for each
possiblity when there is no trailers of a particular type loaded in the given
day. (SQL below)
ie: if there were 3 non export, no export trucks and 2 export cans I need an
output of:

Non export: 3
Export Trucks: 0
Export Cans: 2

Thanks,
Dragon

SQL:
SELECT [PCS / Bulk Ship].Warehouse, [PCS / Bulk Ship].ShipDate, [PCS / Bulk
Ship].Export, [PCS / Bulk Ship].Can, Count([PCS / Bulk Ship].[Unit #]) AS
[CountOfUnit #]
FROM [PCS / Bulk Ship]
WHERE ((([PCS / Bulk Ship].[Unit #])>"0.1") AND (([PCS / Bulk Ship].Rail)=0))
GROUP BY [PCS / Bulk Ship].Warehouse, [PCS / Bulk Ship].ShipDate, [PCS /
Bulk Ship].Export, [PCS / Bulk Ship].Can
HAVING ((([PCS / Bulk Ship].Warehouse)='Bulk') AND (([PCS / Bulk
Ship].ShipDate)=[What is the date]))
ORDER BY [PCS / Bulk Ship].Export DESC , [PCS / Bulk Ship].Can DESC;
Click to expand...

are you getting nulls? Use Nz() to convert the nulls to zeroes.
Click to expand...
 
J

John Spencer (MVP)

Would you care to explain which field returns the values of
Non Export, Export Trucks and Export Cans? Or is that a combination of fields?

Do you have a separate table that has those values in it?

And NZ has to have a field/object to work on to return anything. If there is
no row, then NZ has no field to work with. So NZ is probably not the solution
for your problem.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
When there are no records it simply doesn't display anything for that option.
I'm not familiar with the Nz() command recommended.
How would it fit into my query?

Thanks,
Dragon

[email protected] said:
I have a query that counts my trailers loaded in a given day for a given
warehouse. I have the data broken down by types (ie, export, truck, can) and
in a particular order.

The problem is: I need the query to return a default value of 0 for each
possiblity when there is no trailers of a particular type loaded in the given
day. (SQL below)
ie: if there were 3 non export, no export trucks and 2 export cans I need an
output of:

Non export: 3
Export Trucks: 0
Export Cans: 2

Thanks,
Dragon

SQL:
SELECT [PCS / Bulk Ship].Warehouse, [PCS / Bulk Ship].ShipDate, [PCS / Bulk
Ship].Export, [PCS / Bulk Ship].Can, Count([PCS / Bulk Ship].[Unit #]) AS
[CountOfUnit #]
FROM [PCS / Bulk Ship]
WHERE ((([PCS / Bulk Ship].[Unit #])>"0.1") AND (([PCS / Bulk Ship].Rail)=0))
GROUP BY [PCS / Bulk Ship].Warehouse, [PCS / Bulk Ship].ShipDate, [PCS /
Bulk Ship].Export, [PCS / Bulk Ship].Can
HAVING ((([PCS / Bulk Ship].Warehouse)='Bulk') AND (([PCS / Bulk
Ship].ShipDate)=[What is the date]))
ORDER BY [PCS / Bulk Ship].Export DESC , [PCS / Bulk Ship].Can DESC;
Click to expand...
are you getting nulls? Use Nz() to convert the nulls to zeroes.
Click to expand...
Click to expand...
 
D

Dragon

It’s a combination of fields. I have check boxes (all in one table) for
export, truck, and can. There is also one for rail but I don’t want any
records for rail included in these results.

I need the query to return a value of zero for time frames when there aren’t
any loads of a particular kind shipped (ex. Export trucks). This way I can
import the query results into a report and have it displaying in a uniform
order time after time even when there’s nothing shipped.

I hope this makes it all clearer.

Thanks,
Dragon

John Spencer (MVP) said:
Would you care to explain which field returns the values of
Non Export, Export Trucks and Export Cans? Or is that a combination of fields?

Do you have a separate table that has those values in it?

And NZ has to have a field/object to work on to return anything. If there is
no row, then NZ has no field to work with. So NZ is probably not the solution
for your problem.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
When there are no records it simply doesn't display anything for that option.
I'm not familiar with the Nz() command recommended.
How would it fit into my query?

Thanks,
Dragon

[email protected] said:
I have a query that counts my trailers loaded in a given day for a given
warehouse. I have the data broken down by types (ie, export, truck, can) and
in a particular order.

The problem is: I need the query to return a default value of 0 for each
possiblity when there is no trailers of a particular type loaded in the given
day. (SQL below)
ie: if there were 3 non export, no export trucks and 2 export cans I need an
output of:

Non export: 3
Export Trucks: 0
Export Cans: 2

Thanks,
Dragon

SQL:
SELECT [PCS / Bulk Ship].Warehouse, [PCS / Bulk Ship].ShipDate, [PCS / Bulk
Ship].Export, [PCS / Bulk Ship].Can, Count([PCS / Bulk Ship].[Unit #]) AS
[CountOfUnit #]
FROM [PCS / Bulk Ship]
WHERE ((([PCS / Bulk Ship].[Unit #])>"0.1") AND (([PCS / Bulk Ship].Rail)=0))
GROUP BY [PCS / Bulk Ship].Warehouse, [PCS / Bulk Ship].ShipDate, [PCS /
Bulk Ship].Export, [PCS / Bulk Ship].Can
HAVING ((([PCS / Bulk Ship].Warehouse)='Bulk') AND (([PCS / Bulk
Ship].ShipDate)=[What is the date]))
ORDER BY [PCS / Bulk Ship].Export DESC , [PCS / Bulk Ship].Can DESC;
are you getting nulls? Use Nz() to convert the nulls to zeroes.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Counting trailer numbers 2
Database problem 1
Trouble with a query 1
LITEON's dark secret? 50
query to return counts of all categories per day 3
trying to lookup a value and return if true or fals 1
SQL bit column default value 4
AMD Tech Day at Develop 2007 1

Top