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

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

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)

What is the logic behind this?

You could also use the Abs() function:

SortField: Abs(YourFieldName)

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.

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.

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.

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.

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?

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

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."

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?

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;

http://static-digitalvault.cox.net/static/kdpetersen/storage/d54552d1521aaa46099472aeb552dd9e/4c34d305/lRawNS.png/absQry.png?save

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)

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?

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.

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.

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.

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.

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. :confused:
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.

Here is the final query that works.
SELECT Ap.VenNum AS VendorNumber, Ap.GrossAmt, ApVenTest.VenName, Abs([Ap].[GrossAmt])
FROM ApTest AS Ap INNER JOIN ApVenTest ON Ap.VenNum = ApVenTest.VenNum
WHERE (((Abs([Ap].[GrossAmt])) In (SELECT ABS(Ap.GrossAmt) AS ABSOLUTEGross
FROM ApTest Ap
GROUP
BY ABS(Ap.GrossAmt)
HAVING COUNT(*) > 1 )))
ORDER BY Ap.VenNum, Abs([Ap].[GrossAmt]);
THE OUTPUT
VendorNumber GrossAmt VenName Expr1003
47801 $174,240.00 SUMRAM 174240
47801 $174,240.00 SUMRAM 174240
47801 $178,200.00 SUMRAM 174240
47801 $178,200.00 SUMRAM 174240
47801 ($182,880.00) SUMRAM 174240
47801 $182,880.00 SUMRAM 174240
47801 $187,920.00 SUMRAM 174240
47801 $187,920.00 SUMRAM 174240
47801 $189,750.00 SUMRAM 174240
47801 ($189,750.00) SUMRAM 174240
47801 $189,750.00 SUMRAM 174240
47862 $144,867.00 OCEAN SALES 144867
47862 $144,867.00 OCEAN SALES 144867
47862 ($144,867.00) OCEAN SALES 144867
47862 $144,867.00 OCEAN SALES 144867
47862 $145,800.00 OCEAN SALES 144800
47862 $145,800.00 OCEAN SALES 144800
47862 $170,100.00 OCEAN SALES 170100
47862 ($170,100.00) OCEAN SALES 170100
47862 $171,700.00 OCEAN SALES 171700
47862 $171,700.00 OCEAN SALES 171700
47862 ($171,700.00) OCEAN SALES 171700
47862 $171,700.00 OCEAN SALES 171700
47862 $171,700.00 OCEAN SALES 171700
47862 $171,700.00 OCEAN SALES 171700

I have never done anything this complex before so this was not easy for me.
I actually found another forum that helped me figure out what I was doing wrong.

Sometimes its better to throw a life vest instead of laughing about the boat they are sinking in.