V Lookup from Excel - Equivalent in Access within Query

[T1969]

In the example I have two numbers that could drive the change 5 & 20.

I want a list with the maximum amount of change against each of the numbers hence in this example you only see the 20 in what I want to end up with data..

The lines could have up to 25 entries in them on multiple lines. I can create this in Excel but it is very slow as I have thousands of lines.


My Child - Parent relationship is built up from two columns of data A & B, V Lookup to achieve the cascade to the right from Column C onward.

=VLOOKUP(C3,B1:C7,2,FALSE)
&
=VLOOKUP(D3,C17,2,FALSE)
&
So on for around twenty columns until no more parents.



1 How can the V lookup be done within a Query in access or cant it as I have been told ?


2 The second question only if the above can be achieved.

Can the below formula be performed in Access to turn the rows into two columns of data with the max amount against each number.




In B10:
=MIN($B$2:$G$7)

In B11:


=MIN(IF($B$2:$G$7>B10,$B$2:$G$7))
This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER
Copy down

In A10:
=MAX(IF($B$2:$G$7=B10,$A$2:$A$7))
This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER
Copy down

[CJ_London]

I think your problem is you do not store data like that in Access, so there is not an easy conversion. Access had a function called Dlookup which does something similar to Vlookup and although it can be used in queries, it is really intended to be used in forms and reports.

Probably better if you explain in simple English what your data is and what you are trying to achieve

[ranman256]

in access we use queries. your example looks like a crosstab query (aka pivot table)
instead of Vlookup, we have DLOOKUP. These are good for pulling 1 field on a form.
otherwise, we use queries.

[T1969]

Ajax,

Column A - Days I need to push a date to the right.
Column B - Child order No
Column C - Parent order No

Some orders are currently scheduled in the past and I want to schedule them offline to give a more realistic view of load & capacity. The only child parent relationship is built up in B & C. This can be a many to many relationship or one to one.

I can do this with the example in Excel but would rather if at all possible do in Access as Excel is slow due to the amount of data, 30,000 lines x 25 Columns.

If it is not achievable in Access then I will stick to Excel.

[CJ_London]

I don't think I can help you. It probably is achievable in Access, I've yet to come across anything that can't be done, but I don't understand your requirement and don't have the time to keep seeking clarification. It sounds like it is a scheduling issue, but cannot make sense of your data.

Good luck with your project