SQL question - finding same number either + or -

[voidcranium]

I have a database that has numerical data in it.
I'm in need of a query that will list all like numbered data even if it is Positive or Negative.
I just need to know if the numbers match.

Example:
1025.00
(1025.00)

Have any idea how I would get this?

Thanks

 

[DCrake]

Create a query that converts the numbers to string and use like


NumToStr:CStr([Numberfield])

Like "*1024*"


Or

Alias:IIF(Numberfield<0,NumberField*-1,NumberField)

= 1024

 

[voidcranium]

How would that work if I don't have a set number I am looking for?

I want to list ALL values that are the same not a specific number.

Lets say I have 3 values that are 1000.00 and 2 that are (1000.00)
and I have 2 values that are 5000.00 and 3 that are (5000.00)
I want them listed like this.

1000.00
1000.00
1000.00
(1000.00)
(1000.00)

5000.00
5000.00
(5000.00)
(5000.00)
(5000.00)

 

[DCrake]

What is the logic behind this?

 

[pbaldy]

You could also use the Abs() function:

SortField: Abs(YourFieldName)

 

[voidcranium]

You could also use the Abs() function:

SortField: Abs(YourFieldName)

I was thinking that I might have to use the ABSOLUTE math function but had no idea on how or where to use it.

Would this work?
WHERE "((Abs[Amount])=[Amount])";

I am going to try it tonight when I get home.

 

[pbaldy]

Well, your parentheses are misplaced, but that wouldn't really accomplish anything. Unless I'm misreading it, that would simply return all positive values. Based on your earlier reply to David, it appeared you didn't want to restrict records but keep records together, hence both our suggestions of a calculated field you could sort on.

 

[voidcranium]

What is the logic behind this?

I applied for an Access position at a company and they gave me this task to test my skills.
I am past the time-line to give it back so right now I am just wanting to understand how to do this since it is something I've never tried.
Here are the instructions:

"Write a report that groups payment of equal amounts (Use GrossAmt) for each vendor together.
Ignore the sign (positive or negative) of the amount. Ignore groups with only one record.
For each vendor show a group header containing the vendor number and name.
For example if the GrossAmt is $100 in one record and the GrossAmt is $-100 in another record treat that as a match."

I created a report that does sort it via vendor but I can't get the Negative and Positive amounts to group together and I have not figured out how to leave out the single records.

Like I said I have never tried anything this elaborate so I am at a loss on exactly where to start.

If you decide to help then thanks for your patience.

AND I am not sure this is the right forum for this, since it has to do with SQL statements I put it here. It might need to be moved to the REPORT forum.

 

[DCrake]

The exercise they gave you sems to be to be unrealistic. I can not see where you would use this in the real world. Could you ever see a reason to do this. Pity they did not give you a more real world test.

My solution would be to to create a query that groups by vendor and counts records and put a condition on where count >1.

Then create a new query that displays vendor id and amount with any neg values transposed to positive. and bring in the above query and linkk on vendor id.

This is if I read the task correctly.

 

[voidcranium]

The exercise they gave you sems to be to be unrealistic. I can not see where you would use this in the real world. Could you ever see a reason to do this. Pity they did not give you a more real world test.

I agree with you. When I try to learn something new I try to think of it as a real world application and what I would use it for but this example leaves me mind-boggled.
********************************************************

My solution would be to to create a query that groups by vendor and counts records and put a condition on where count >1.

Here is the qry I made to do this part.

qryGroupByCount
SELECT Count(ApTest.VenNum) AS VendorNumber
FROM ApTest
GROUP BY ApTest.VenNum
HAVING (((ApTest.VenNum)>1));

This produces one column with different numbers in the fields.
VendorNumber
3
10
8
25
33
ect.

Is this what you had in mind?

*********************************************************

Then create a new query that displays vendor id and amount with any neg values transposed to positive. and bring in the above query and linkk on vendor id.

Now this part.
Is this where you would use the code you gave me earlier?

 

[DCrake]

Yes

Basically

Select VendorId, Vendor Name, Amt, AmtTemp:IIF(Amt <0,Amt*-1,Amt) From VendorsPayments Order by VendorID By Amt

Then make a join between the vendor id in the above to the vendor id in the earlier query. This is so you only see vendors with more that one amount, as part of test.

If the vendor name is not in the vendor payments table bring that down as well so you can display the vendor name. Also untick the AmtTemp field to hide it.

did they give you a sample table to work from or did you have to create your own?

Code is for brevity format only

 

[voidcranium]

did they give you a sample table to work from or did you have to create your own?

Yeah they sent 2 tables. One has the Vendor Number and Vendor Name
while the other has the Vendor Number and all the rest of the data.

What do you mean by uptick? "Also untick the AmtTemp field to hide it."

 

[voidcranium]

Yeah they sent 2 tables. One has the Vendor Number and Vendor Name
while the other has the Vendor Number and all the rest of the data.

What do you mean by uptick? "Also untick the AmtTemp field to hide it."

OK, do you mean to UNCHECK the SHOW box in Query Design window for the temp field?

 

[voidcranium]

I think I have the other query working.

SELECT ApTest.VenNum, ApTest.GrossAmt, IIF(ApTest.GrossAmt<0,ApTest.GrossAmt*-1,ApTest.GrossAmt) AS ABSOLUTEGross
FROM ApTest
ORDER BY ApTest.VenNum, ApTest.GrossAmt;

I tried this but I kept getting a Syntax error: I am using Access2000, think that would make a difference?

ABSOLUTEGross:IIF(ApTest.GrossAmt<0,ApTest.GrossAmt*-1,ApTest.GrossAmt)

 

[pbaldy]

Probably some combination of dinner/pub/bedtime for David. Not sure why you're reinventing the wheel instead of using the Abs() function, but whatever floats your boat. Unless I'm blind, the "working" function posted above is identical to the "failing" function posted below. Has there been a typo?

 

[voidcranium]

I don't think it is reinventing the wheel if you have never invented the wheel in the first place. My boat is not floating that's why I am asking questions.

I don't know why that code won't work. It just doesn't if I move 'ABSOLUTEGross' from the end to the front with Colon's. I have tried it several times.

 

[pbaldy]

I would submit that the Abs() function is the wheel, and rather than use it you're reinventing it with an IIf() that does the same thing. Where is it that it doesn't work? As I said, the formula is the same in your working and not working examples. These are also the same thing:

Whatever: IIf(...)
IIf(...) AS Whatever

The first is how you would see it in query design view, which is a visual presentation of the actual SQL. The second is the actual SQL view.

 

[voidcranium]

I would submit that the Abs() function is the wheel, and rather than use it you're reinventing it with an IIf() that does the same thing. Where is it that it doesn't work? As I said, the formula is the same in your working and not working examples. These are also the same thing:

Whatever: IIf(...)
IIf(...) AS Whatever

The first is how you would see it in query design view, which is a visual presentation of the actual SQL. The second is the actual SQL view.

That's what it is. I was only looking at it in SQL view and trying to make the changes there. Now when I look at it in Design view I can see the difference between it and SQl view.

Thanks for that.

 

[voidcranium]

I would submit that the Abs() function is the wheel, and rather than use it you're reinventing it with an IIf() that does the same thing. Where is it that it doesn't work? As I said, the formula is the same in your working and not working examples. These are also the same thing:

Whatever: IIf(...)
IIf(...) AS Whatever

The first is how you would see it in query design view, which is a visual presentation of the actual SQL. The second is the actual SQL view.

OK.
I now know what you are talking about.
I changed this - ABSOLUTEGross: IIf(ApTest.GrossAmt<0,ApTest.GrossAmt*-1,ApTest.GrossAmt)
To this - Expr1: Abs([ApTest].[GrossAmt])

Aside from the format it is the same data.

Thanks.

 

[voidcranium]

I'm BACKK.

I have a couple questions.
Using this query:

SELECT Count(ApTest.VenNum) AS VendorTotals, ApVenTest.VenName AS VendorName, ApTest.VenNum AS VendorNumber
FROM ApTest INNER JOIN ApVenTest ON ApTest.VenNum = ApVenTest.VenNum
WHERE (((ApTest.VenNum)>1))
GROUP BY ApVenTest.VenName, ApTest.VenNum;

I get the following results:

VendorTotals	VendorName	VendorNumber
21	APPLIED AIR ENGINEERING, INC.	64409
20	AQUA STAR	                            68454
2	BEAVER STREET FISHERIES, INC.	31184
2	BEM SALES AND MARKETING	        65296
2	CANNON EQUIPMENT COMPANY	32466
5	COX'S WHOLESALE SEAFOOD, INC.	52729
11	DSC SALES INC.	                        23863
4	DURACELL	                                13041
2	EVEREADY BATTERY COMPANY, INC.26082
8	GILLETTE COMPANY	                1039
2	GREAT DANE TRAILERS, INC.	        24580
20	IBM CORPORATION	                30374
2	JACK MORTON CO.	                69546
2	JOHNSON & JOHNSON SALES &	61781
11	KRAFT FOODS, INC.	                42752
2	L'EGGS PRODUCTS	                7175
2	MAZZETTA COMPANY	                66097
21	OCEAN TO OCEAN SEAFOOD SALES	47862
9	ORION SEAFOOD INTL, INC.	        70580
2	PHILIP MORRIS INC.	                10547
5	PLAYTEX PRODUCTS, INC.	        28983
33	PROCTER & GAMBLE DISTRIBUTING	2065
4	SOUTH GEORGIA PECAN COMPANY	52420
2	SUNDOWN VITAMINS, INC.	        31176
13	SURAM TRADING	                        47801
2	VERCIL & HELENE SENSEMAN TRUST46530
25	WHITEHALL -ROBINS HEALTHCARE	2748
2	YOUNG PECAN COMPANY	        29687

QUESTION 1:
This basically just weeded out the Vendors that ONLY had ONE record. RIGHT?
This takes care of this part of the problem "Ignore groups with only one record."

Using this query:

SELECT ApTest.VenNum AS VendorNumber, ApTest.GrossAmt, IIf(ApTest.GrossAmt<0,ApTest.GrossAmt*-1,ApTest.GrossAmt) AS ABSOLUTEGross
FROM ApTest 
ORDER BY ApTest.VenNum, ApTest.GrossAmt;

I get these results:

VendorNumber	GrossAmt	ABSOLUTEGross
2065 ($123,846.10)	$123,846.10
2065	$105,441.72	$105,441.72
2065	$105,441.72	$105,441.72
46530	$122,500.00	$122,500.00
46530	$122,600.00	$122,600.00
47801	($189,750.00)	$189,750.00
47801	($182,880.00)	$182,880.00
47801	$169,950.00	$169,950.00
47801	$169,950.00	$169,950.00
47801	$174,240.00	$174,240.00
47801	$187,920.00	$187,920.00
47801	$189,750.00	$189,750.00
47801	$189,750.00	$189,750.00
47862	($171,700.00)	$171,700.00
47862	($170,100.00)	$170,100.00
47862	($144,867.00)	$144,867.00
47862	$195,500.00	$195,500.00
47862	$195,500.00	$195,500.00
47862	$195,500.00	$195,500.00
47862	$195,500.00	$195,500.00
47862	$223,348.17	$223,348.17
47862	$223,348.17	$223,348.17
52420	$114,998.40	$114,998.40
52420	$114,998.40	$114,998.40
52420	$114,998.40	$114,998.40
52729	$37.00	        $37.00
52729	$237.00	        $237.00
52729	$102,625.00	$102,625.00
52729	$105,625.00	$105,625.00
52729	$105,625.00	$105,625.00

QUESTION 2:
How do I "group payment of equal amounts" if there is NO EQUAL AMOUNT to group with?
Like these two records:

52729	$37.00	$37.00
52729	$237.00	$237.00

They don't have an equal amount so they should not even be on this report RIGHT?

If I need to GROUP on the EQUAL Payment Amounts AND I need to Ignore groups with only one record then this won't work.

Write a report that groups payment of equal amounts (Use GrossAmt) for each vendor together.....Ignore groups with only one record.

The first query got rid of the SINGLE Vendor Records but it did not get rid of the AMOUNTS that had no matching Amounts.
Did that make sense?
*********************************************************
*********************************************************
By the Way.
Here is my attempt at the JOIN:

SELECT Q2.GrossAmt, Q2.ABSOLUTEGross, Q1.VendorNumber
FROM qryAbsoluteGross AS Q2, qryGroupByCount AS Q1
WHERE Q1.VendorNumber = Q2.VendorNumber
AND VendorTotals >2;

It returns these results: A Total of 210 records returned instead of the 236 I started with.

GrossAmt	ABSOLUTEGross	VendorNumber
$156,891.00	$156,891.00	64409
$137,479.84	$137,479.84	64409
$152,767.20	$152,767.20	64409
$156,212.20	$156,212.20	64409
$156,212.20	$156,212.20	64409
$153,085.20	$153,085.20	64409
$152,767.20	$152,767.20	64409
$156,891.00	$156,891.00	64409
$106,560.00	$106,560.00	68454
$179,850.00	$179,850.00	68454
$179,850.00	$179,850.00	68454
$179,850.00	$179,850.00	68454
$179,850.00	$179,850.00	68454
$171,000.00	$171,000.00	68454
$192,276.00	$192,276.00	68454
$37.00	$37.00	52729
$105,625.00	$105,625.00	52729
$237.00	$237.00	52729
$105,625.00	$105,625.00	52729

OK. I know my boat is probably floating in the wrong stream so I am asking for a little guidance on how to get to the stream I need to be floating on.
Thanks.