Relationships & Queries

[vman92]

I am trying to run a query to combine 2 tables. The relationship is one that includes all values from one table and only the values from the other table that match. This is the problem. I want the values that do not have an exact match from the first table to show the next higher value from the second table.

ie.

1st table 2nd table Current Query Output

10000 20000 20000 25000 10000 20000
15000 20000 35000 40000 15000 20000
20000 25000 20000 25000 20000 25000
25000 30000 25000 30000
35000 40000 35000 40000 35000 40000


Desired Query Output

10000 20000 20000 25000
15000 20000 20000 25000
20000 25000 20000 25000
25000 30000 35000 40000
35000 40000 35000 40000

Any Ideas??

 

[PHV]

Any chance you could post the 2 tables schema and your actual SQL code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[vman92]

(UMUIMPDwDED)

Field1 Field2
* -1
* 250
* *
0 -1
0 *
-1 -1
-1 *
10000 -1
10000 250
10000 *
100000 -1
100000 250
100000 *
1000000 -1
1000000 250
1000000 *
15000 -1
15000 250
15000 *
150000 -1
150000 250
150000 *
20000 -1
20000 250
20000 *
200000 -1
200000 250
200000 *
25000 -1
25000 250
25000 *
250000 -1
250000 250
250000 *
300000 -1
300000 250
300000 *
3500 -1
3500 250
3500 *
5000 -1
5000 250
5000 *
50000 -1
50000 250
50000 *
500000 -1
500000 250
500000 *
7500 -1
7500 250
7500 *
750000 -1
750000 250
750000 *


(UMUIMPDwDED IMPORT)

Field1 Ded Type Field3
0 1 495200
-1 1 495200
100000 250 495237
15000 250 495201
25000 250 495213
50000 250 495225



Query (UMUIMPDwDED Query)

SELECT UMUIMPDwDED.Field1 AS UMUIMPDwDED_Field1, UMUIMPDwDED.Field2 AS UMUIMPDwDED_Field2, [UMUIMPDwDED IMPORT].Field3, IIf([UMUIMPDwDED IMPORT]!Field1=UMUIMPDwDED!Field1,[UMUIMPDwDED IMPORT]!Field1,IIf(UMUIMPDwDED!Field1<[UMUIMPDwDED IMPORT]!Field1,[UMUIMPDwDED_Field1],[UMUIMPDwDED_Field1])) AS Expr3, [UMUIMPDwDED IMPORT].[Ded Type] AS [UMUIMPDwDED IMPORT_Field2], IIf(UMUIMPDwDED!Field1=[UMUIMPDwDED IMPORT]!Field1,"N","") AS Expr1, IIf(UMUIMPDwDED!Field2=[UMUIMPDwDED IMPORT]![Ded Type],"N",IIf(UMUIMPDwDED!Field2="-1","A","M")) AS Expr2
FROM UMUIMPDwDED LEFT JOIN [UMUIMPDwDED IMPORT] ON UMUIMPDwDED.Field1 = [UMUIMPDwDED IMPORT].Field1;


Hope this helps

 

[Golom]

Your example is a bit confusing. You have three headings (1st table 2nd table Current Query Output) and six values. In your Desired Output you have four values.

Are these different fields? All the same field?

I can't tell from the example in which rows a value is missing from "2^nd Table" or in which rows it isn't.

Assuming that you are dealing with a single field from each source table, you may try something like

Select a.fld1 As t1fld , 
       IIF(IsNull(b.fld1), 
           (Select fld1 From tbl2 Where tbl2.fld1 >= a.fld1),
            b.Fld1 ) As t2fld

From tbl1 As a LEFT JOIN tbl2 As b ON a.Fld1 = b.Fld1

The possible problem with this is that it is possible that there will be an instance where tbl2.fld1 >= a.fld1) and the value for t2fld will be NULL.

 

[vman92]

Sorry, I understand it was confusing. There are two tables within the previous past. within the first table (UMUIMPDwDED) I have two columns of information. In the second table (UMUIMPDwDED IMPORT) I have three columns of information. And the last item is a query named (UMUIMPDwDED Query) and what I listed was the SQL view of the query. I hope this cleared up the confusion.

(UMUIMPDwDED IMPORT)

Field1 Ded Type Field3
0 1 495200
-1 1 495200
100000 250 495237
15000 250 495201
25000 250 495213
50000 250 495225

 

[PHV]

And what is the expected result vs the actual ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[vman92]

Actual results:

UMUIMPDwDED_Field1 UMUIMPDwDED_Field2 Field3 Expr3 UMUIMPDwDED IMPORT_Field2 Expr1 Expr2
-1 -1 495200 -1 1 N A
-1 * 495200 -1 1 N M
0 -1 495200 0 1 N A
0 * 495200 0 1 N M
3500 -1 3500 A
3500 250 3500 M
3500 * 3500 M
5000 -1 5000 A
5000 250 5000 M
5000 * 5000 M
7500 -1 7500 A
7500 250 7500 M
7500 * 7500 M
10000 -1 10000 A
10000 250 10000 M
10000 * 10000 M
15000 -1 495201 15000 250 N A
15000 250 495201 15000 250 N N
15000 * 495201 15000 250 N M
20000 -1 20000 A
20000 250 20000 M
20000 * 20000 M
25000 -1 495213 25000 250 N A
25000 250 495213 25000 250 N N
25000 * 495213 25000 250 N M
50000 -1 495225 50000 250 N A
50000 250 495225 50000 250 N N
50000 * 495225 50000 250 N M
100000 -1 495237 100000 250 N A
100000 250 495237 100000 250 N N
100000 * 495237 100000 250 N M
150000 -1 150000 A
150000 250 150000 M
150000 * 150000 M
200000 -1 200000 A
200000 250 200000 M
200000 * 200000 M
250000 -1 250000 A
250000 250 250000 M
250000 * 250000 M
300000 -1 300000 A
300000 250 300000 M
300000 * 300000 M
500000 -1 500000 A
500000 250 500000 M
500000 * 500000 M
750000 -1 750000 A
750000 250 750000 M
750000 * 750000 M
1000000 -1 1000000 A
1000000 250 1000000 M
1000000 * 1000000 M
* -1 * A
* 250 * M
* * * M

 

[vman92]

Desired Results would have the values 3500, 5000, 7500 & 10000 have the fourth & fifth columns as:

15000 -1 495201 15000 250 N A
15000 250 495201 15000 250 N N


I wish there were someway to post the DB itself as these are the only tables & query in it it is pretty small.

 

[PHV]

You'll have a sort problem as field1 seems to be text, and so 3500, 5000 and 7500 are GREATER than 10000

Anyway:
FROM UMUIMPDwDED U INNER JOIN [UMUIMPDwDED IMPORT] I ON U.Field1 <= I.Field1
WHERE I.Field1 = (SELECT Min(Field1) FROM [UMUIMPDwDED IMPORT] J WHERE J.Field1>=U.Field1)


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[vman92]

Would I just replace the FROM statement in my SQL with your from statement? I really don't care about the sorting as long as the report contains only the values from the UMUIMPDwDED table matched to the corresponding values from the other table and the values that do not have a match be matched to the next higher value.