A Report Question

[mattfox]

I created a inventory database and I have a total inventory report that shows everything that we have in stock. The way I have it setup is one table with the customer information and item descriptions and a second table with the recieved and shipped data. In the total inventory report I show the item information and the total inventory which is the difference of the qty received and the qty shipped. This calculation is done in the report. Some of my items show a zero balance on the floor and this of course shows on the report. What I want to do, but do not know how to do is have the items that have a zero balance not show on the report. Does anybody have any ideas?

Thanks,
Matt [sig][/sig]

 

[TheInsider]

Hi,
Simply place the "Not 0" or ">0" criteria into the Report's record source query for the appropriate field. You may need to do the calculations in the query instead of the report if the result of a calculation is what the criteria is based on.
CCTC1
Rob Marriott
rob@career-connections.net [sig][/sig]

 

[mattfox]

Okay, this change created another problem. I added my calculations into the query. Now remember that I have two tables setup. The error I now get is:
You tried to execute a query that doesn't include the specified expresion "Customer Name" as a part of an aggregate function.

Now if I delete the Customer Name field, then it says the next field in this same error. [sig][/sig]

 

[TheInsider]

Hi,
Try creating a calculated field in the query like this:

TotalInventory: [Qty Received] - [Qty Shipped]

Then set the criteria of this new field to "Not 0" or ">0" (one or the other). You can then use the "TotalInventory" field as a field source in your report. The amount of tables in the query does not matter. You may however, need to include the table names in the calculation ie

TotalInventory: [Table1].[Qty Received] - [Table2].[Qty Recieved]

Make sure that your calculated field name is unique to the field names that are in the tables.
CCTC1
Rob Marriott
rob@career-connections.net [sig][/sig]

 

[mattfox]

Well,
I did that and it still gives me the same error. [sig][/sig]

 

[TheInsider]

I have tried everything that I can think of to get that error message to come up, but no luck. Is your query using Totals? [sig][/sig]

 

[mattfox]

The query figures all of the totals. [sig][/sig]

 

[TheInsider]

Try making a backup copy of the query and turn the "Totals" button off. If this doesn't solve your problem then post the SQL statement as you have it now (with the Totals) in this thread, so that I can see what you are doing.
[sig][/sig]

 

[mattfox]

SELECT DISTINCTROW Customer1.[Customer Name], Customer1.Contact, Customer1.[Item #], Customer1.[Item Description], Inventory1.[Qty Rec], Inventory1.[Qty Shipped], [Inventory1].[Total Qty Rec]-[Inventory1].[Total Qty Shipped] AS [Total Floor Qty], Sum([Inventory1].[Qty Shipped]) AS [Total Qty Shipped], Sum([Inventory1].[Qty Rec]) AS [Total Qty Rec], Inventory1.[Now Date], Customer1.[Unit Of Measure]
FROM Customer1 RIGHT JOIN Inventory1 ON Customer1.[Item #] = Inventory1.[Item #];

Here you go. [sig][/sig]

 

[TheInsider]

Hi,
Ok, I see what is going on. You are summing up the information, thus creating the field "Total Qty Rec" while trying to use that same field in another calculation in the same query. You need break this down. Use your original query as a subquery. Create a new query based on your original one, create the calculation with the criteria that I suggested, but in the new query, then set your report's record source query to the new query.
CCTC1
Rob Marriott
rob@career-connections.net [sig][/sig]