how to find positive number

[adnancanada]

I have table has two column A and Column B

ColumA	ColumnB
-9	100
-36	3
-4	-36
25	-66
14	-74
96	60
-98	-25
-3	-2
-41	-1

I want to build query to show positive value in both column at the same time.

ColumA	ColumnB
25	100
14	3
96	60

[June7]

And how would you expect to achieve that output since 25 and 100 are not in same record? What relationship would they have? Same for 14 and 3. By coincidence, 96 and 60 are in the same record.

A query can easily show only positive values:

SELECT IIf(ColumnA>0, ColumnA, Null) AS C1, IIf(ColumnB>0, ColumnB, Null) AS C2 FROM tablename;

However, compacting to the example output will be complicated.

Query: ColumnASeq
SELECT DCount("*","Table2","ID<" & [ID] & " AND ColumnA>0 AND ColumnA<>" & [ColumnA]) AS Seq1, Table2.ColumnA
FROM Table2
WHERE (((Table2.ColumnA)>0));

Query: ColumnBSeq
SELECT DCount("*","Table2","ID<" & [ID] & " AND ColumnB>0 AND ColumnB<>" & [ColumnB]) AS Seq1, Table2.ColumnB
FROM Table2
WHERE (((Table2.ColumnB)>0));

Query:
SELECT ColumnASeq.ColumnA, ColumnBSeq.ColumnB
FROM ColumnBSeq INNER JOIN ColumnASeq ON ColumnBSeq.Seq1 = ColumnASeq.Seq1;

However, the final query will be inaccurate if the subqueries do not have the same number of records. By chance, your example has 3 positive values in each column.

And this will duplicate data:
SELECT ColumnASeq.ColumnA, ColumnBSeq.ColumnB FROM ColumnASeq, ColumnBSeq;

[adnancanada]

And how would you expect to achieve that output since 25 and 100 are not in same record? What relationship would they have? Same for 14 and 3. By coincidence, 96 and 60 are in the same record.

A query can easily show only positive values:

SELECT IIf(ColumnA>0, ColumnA, Null) AS C1, IIf(ColumnB>0, ColumnB, Null) AS C2 FROM tablename;

However, compacting to the example output will be complicated.

Query: ColumnASeq
SELECT DCount("*","Table2","ID<" & [ID] & " AND ColumnA>0 AND ColumnA<>" & [ColumnA]) AS Seq1, Table2.ColumnA
FROM Table2
WHERE (((Table2.ColumnA)>0));

Query: ColumnBSeq
SELECT DCount("*","Table2","ID<" & [ID] & " AND ColumnB>0 AND ColumnB<>" & [ColumnB]) AS Seq1, Table2.ColumnB
FROM Table2
WHERE (((Table2.ColumnB)>0));

Query:
SELECT ColumnASeq.ColumnA, ColumnBSeq.ColumnB
FROM ColumnBSeq INNER JOIN ColumnASeq ON ColumnBSeq.Seq1 = ColumnASeq.Seq1;

However, the final query will be inaccurate if the subqueries do not have the same number of records. By chance, your example has 3 positive values in each column.

And this will duplicate data:
SELECT ColumnASeq.ColumnA, ColumnBSeq.ColumnB FROM ColumnASeq, ColumnBSeq;

Thank you. Can we do in access ?

[Micron]

Please don't quote an entire answer in your follow up question (Reply with quote) unless you need the text as a reference. Instead use Reply.
So why not

ColumA	ColumnB
3	96
14	25
100	60

or any other similarly disconnected combination? As June7 is inferring, there is no logic to your combination. When you consider a "row" (record) in a data set, each data base table field (incorrectly referred to as a column by many) is related to every other field on that record in some way. In your case, no such relationship exists. Sorry to say that the goal is at least arbitrary and maybe even pointless. If you need your data to be assembled in this way, then explain the reason and the table design because the goal suggests there is something wrong with the design.

[June7]

Here's a way to get the example output that doesn't depend on same number of positive values in each column.

Build the first 2 queries as shown in post 2.

Then 2 more queries:

Query: ColumnUNION
SELECT Seq, ColumnA AS Data, "ColA" AS Source FROM ColumnASeq
UNION ALL SELECT Seq, ColumnB, "ColB" FROM ColumnBSeq;

Query:
TRANSFORM First(ColumnUNION.Data) AS FirstOfData
SELECT ColumnUNION.Seq
FROM ColumnUNION
GROUP BY ColumnUNION.Seq
PIVOT ColumnUNION.Source;