Splitting +/- values into separate columns

[rob.lyles]

Hey folks,
Need help with the following. I have a table that list usage fields such as as Date, part #, Order #, Desc, Qty, etc. I append usage reports to this every so many days. Moreover, I have a 2nd table that I use to convert multiple part #'s into one generic description. This is necessary as my customer does not care that I have multiple part #'s, and they just want to see total usage for 5" pipe. I have multiple part #'s because our pipe part #/desc. are vendor specific. So I make a relationship with these 2 tables in query and only need to view what is equal as I have the chosen the specific part #'s that will roll into the generic description. Usually I summarize the qty from the usage table. I have attached my sample conversion table. I would like to create a query based on this relationship that will show me the positives and negatives in separate columns, and a blank value in its corresponding column. So if its 3, then I would like to see a 3 in the positive column and nothing in the other. Does anyone know how to help?

Thanks,

 

[pbaldy]

I'm not sure I see the connection to the attachment, but you could do this type of thing in a query:

Positives: IIf(AmountField >= 0, AmountField, 0)
Negatives: IIf(AmountField < 0, AmountField, 0)

You can use the Abs() function if you want the negative amounts displayed as positive numbers.

 

[rob.lyles]

Ok I'm not get anything when I run the query. Let me explain what I have done, and see if I did anything wrong. Forget about the previous attachment and relationship I was talking about. We are going to go with just the main usage table from which this data derives from, and a sample of it is also attached to this reply.

Ok, I have created a select query, and for simplistic terms I have included the attached table and then displayed Report Date, Description, and Quantity twice. I use the IIF statements and get zero results. If I remove one of the Quantity fields from the query, say the negative one, when I run the query all I get are positive values, not even a 0 where there would have been a negative. It just completely omits it, so if I have both Quantity fields in the query with the statements above, I get nothing. Thanks for any help you can give me on what I missed.

 

[DCrake]

Ok so thats what is happening with the data at present. Can you simulate what the expected outcome should be. and post that.

 

[rob.lyles]

Ok, I've uploaded both the usage table and the end result query. Again, by nature, the positives and negatives are in the same quantity field on the usage table. I'd like to split the +/- values into their own fields.

Thanks.

 

[pbaldy]

What was your query that you said didn't work? The examples I posted earlier should enable you to do this.

 

[rob.lyles]

It returns with no results. I see the field names but no records.

 

[pbaldy]

I understood that it returned no results. What was the SQL of it?

 

[rob.lyles]

Here you go. Thanks.


-------------


SELECT [Conversion Table].Description, [Append Table].[Report Date], [Append Table].Well, [Append Table].Quantity, [Append Table].Quantity
FROM [Append Table] INNER JOIN [Conversion Table] ON [Append Table].[Part Number] = [Conversion Table].[Part Number]
WHERE ((([Append Table].Quantity)=IIf([Quantity]>=0,[Quantity],0)) AND (([Append Table].Quantity)=IIf([Quantity]<0,[Quantity],0)));

 

[pbaldy]

Those were intended to be fields. Try this:

SELECT [Conversion Table].Description, [Append Table].[Report Date], [Append Table].Well, IIf([Quantity]>=0,[Quantity],0) AS QuantityP, IIf([Quantity]<0,[Quantity],0) AS QuantityN
FROM [Append Table] INNER JOIN [Conversion Table] ON [Append Table].[Part Number] = [Conversion Table].[Part Number]

 

[rob.lyles]

Excellent! By chance would you know how to create a blank column. I forgot to mention that I need a space between the Quantity columns.

Thanks.

 

[pbaldy]

Typically you wouldn't worry about that in the query, you'd create the desired presentation on a form or report. I suppose something like this would work:

IIf([Quantity]>=0,[Quantity],0) AS QuantityP, " " AS Spacer, IIf([Quantity]<0,[Quantity],0) AS QuantityN

Edit: The website took out my spaces; there can be any number in there. Though again, I'd just do it on a form/report.

 

[rob.lyles]

Great!! Everything I needed to know has been answered

 

[pbaldy]

Happy to help!