Access Query question

[vman92]

Anybody have any idea as to how I can get to the desired output? Currently I have the relationships set so that all ranges from table one are outputted and I get the current query output. I would like to have the 10000 & 20000 ranges show the next range higher in the 2nd table (See Desired results)

1st table

10000 20000
15000 20000
20000 25000
25000 30000
35000 40000

2nd table

20000 25000
25000 30000
35000 40000

Current Query Output

10000 20000
15000 20000
20000 25000 20000 25000
25000 30000 25000 30000
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

 

[Norie]

Which fields do you actually want to join?

In the first example (current) you seem to be joining the first fields in both tables.

In your desired output you seem to want to join field1 from table1 to field2 of table2 for the first 3 records in table1.

Then you seem to want to join field1 from both tables.

 

[vman92]

I would like to join the 1st two fields in each table. The problem is that the relationship is that any query would return all values from the first table and only those value from the second table that are equal. I would like to have a query return a value for each of the values in the first table. The trick is that since there is no corresponding value for the first two sets of values in the second table nothing is returned. I would like it to return the next highest value in table two. I hope this makes sense.

 

[Norie]

As far as I know you can't achieve what you want using one query.