compare two columns and return result from third one with VBA

[sparky23]

Hi,


Short story. I'm trying to make small program that calculate cable lenght needed in data centers.


I need a data-base system that will help me to easily find a cable length between the racks and their patch panels.
So the program should contains table with different cable lengths between the points (one point is between two racks) - Check pircutre 2.png.


I would like to make a form where I give the points in text boxs, and program should deducate those values to variables and
find the row in the table where both variables are stored. The third column in the table conatins cable length value,
and this value should be deducated to third variable. (Picture 6.png third column, 0.6m in the red boarded case).



Depends of how high patch panel is positioned, on top of third variable program shuld add two constant values for each side of the cable (already gaved in Access form).
(Check the zipped pictures 2.png Variable and 3.png Constants).


I need some kind of solution in Access that will search through table and give me back a value.


In attached picture (No.6) there are 3 columns.
When I give two variables exp (AA2) and (AA1), Access should find the row in the table where AA1 and AA2 are stored and
give me back the value from the third column (in this scenario 0.6).


Note that variable should be text and the row with combination AA1 and AA2 is unique. There is no AA2 and AA1 row.
So the solution should actually compare both columns with both variables.




If this is the right way to go, can some one advice me how to do it, while I'm complete novice in Access.


Thank you in advance


Pictures.zip

[ssanfu]

I'm not real sure what you are looking for. How would you manually do the calculations??


Say you have Rack A, Con A. Walk me through the calculations.



Now you have Rack D, Con C. Walk me through the calculations.

[sparky23]

Hi Ssanfu,




let's say:
CON A = 0.6
CON C = 1.8


There are 3 variable: VAR A VAR B VAR C
(Check the draw)





In access table would be all potential combinations of connections between racks (points between racks).
Exp:
|point1|point2||0.8|
|point1|point3||1.6|
|point1|point4||2.4|
|point1|point5||3.2|
...
|point2|point5||2.4|
...


Exp.
I need right side of the rack A and left side of the rack E. So the points are point2 and point5.




VBA code should take values from text boxes in access form and dedicate them to variables VAR A and VAR B


Exp.


VAR A = point2
VAR B = point5




/Here I need a VBA code that will search trough the table and compare VAR A and VAR B in first two columns of the table in order to find the row where both values are there.(last row in my exp. table)
The value of third column is now dedicated to VAR C.
VAR C = 2.4




Calculation is:
VAR C + CON A + CON C


Hopefully I'm little bit more clear

[ssanfu]

For the connections, are these lengths correct?
CON A = 0.6
CON B = 1.2
CON C = 1.8


In your picture 6, the lengths between points on the racks are 0.6, but in Post #3 the lengths between points on the racks are 0.8.
What value are you going to use for the length between points?


I used 0.8

Is this close?

[sparky23]

Hi Ssanfu,

Thank you for your reply.
This is actually not what I looking for. The problem I described is only a part of one complex program, and I'm looking for a mechanism to solve this part.
I need a VBA code, a way where VBA code will search in Table for two entities in the same row, read the third column in that row, and add two constants to this value.
This table should contain different distances, (it is not just simple subsequent 0.8 or 0.6).

[Micron]

I suppose the distances between each consecutive point isn't the same, otherwise this would just be
abs(start - end)*0.8, e.g. abs(2-5)*.8 = 2.4
Besides not telling us if the distances between 2 adjacent points are the same, you haven't said if there are rows involved; i.e.
row 1A thru 1F
row 2A thru 2F
row 3A thru 3F
....

If there are no rows involved, then I must not be understanding the exact nature of the distances as my formula seems too simple.
If there are rows, then you'd need a table with a field for every horizontal point and a record for every row with a measurement for each intersection. Then you'd have to know the route a cable would take:
- direct makes the distance the hypotenuse
- over then down or vice versa makes the distance the sum

Probably what you need is a query, not vba. However, too much information missing at this point.

[ssanfu]

<snip> I need a VBA code, a way where VBA code will search in Table for two entities in the same row, read the third column in that row, and add two constants to this value.<snip>

OK, well you didn't give any values for the cable lengths between the points of the racks nor any points and the expected values.......

Try this. Right now the cable lengths between points are multiples of 0.8 as per your example in Post #3. Change the values in the table "tblCableLengths".
There are 21 records in the table - it doesn't make sense to have points 1,1, so those are not there. And you said point 2,5 is the same as 5,2 so the reverse value records are not there.

[sparky23]

OK, well you didn't give any values for the cable lengths between the points of the racks nor any points and the expected values.......

Try this. Right now the cable lengths between points are multiples of 0.8 as per your example in Post #3. Change the values in the table "tblCableLengths".
There are 21 records in the table - it doesn't make sense to have points 1,1, so those are not there. And you said point 2,5 is the same as 5,2 so the reverse value records are not there.

pictures.zip



Wow nice job. I would make this much complicated.
I have now another problem. I should explain you what I want from beginning. Because you solve my problem other way around, this is now not compatible
to what I needed at the first place (the way I wanted to make).


I'll explain my whole plan.


As I said, I'm trying to make a tool for easy identifying the cable length.
There are more rooms with racks, and more buildings with rooms.
Each room has many racks that need to be connected between each other.
Because the racks are beside each other, I wanted to make a points between racks so I need to add up to 4 times less entries in tblCableLengths.
Check Example.png| Rack ODF3 left and Rack ODF8 left /red lines represents front side of the racks/. The same path is good for ODF2 right and DDF1 right as well, and so on).


The main form should contain comboboxes to choose building, then room, and then Racks: A and B.
Each rack side has HU (height unit) and port number. (See Form.png)
So after all fields are full, after pressing calculate should get the length.


I have created some tables and relations between them but I think I did it too complicated.


-By default, panels have 24 ports. First 12 are on the left, and other 12 (13-24) are on the right side.
But, there are also some exceptions panels with 12 (1-6 left, and 7-12 right) and 48 ports (1-24 left and 25-48 right).
So it should be one general rule for 24 panels, and a separate table with all exceptions (tblExceptions contains: Building, Room, Rack, HU, and number of Ports (12 or 48) ).


-The table tblCableLengths should contain one more column with a pictures of the paths. (when everything is calculated this picture should be shown. Check Preview.png)


-There should be also one table with all available cable lengths in storage room. So the end result should round the number to the first bigger number in this table. (f.example result is 6.4 but in this table are cables with 2,3,4,5,8,10... meters, so the round code should pick 8 in this case.)


-The constants should be chosen depends of HU number. HU1-HU16 are CON C, HU17-HU32 are CON B, and HU33-HU48 are CON A.

-Points should be chosen depends of Racks name and the HU (default 24 or exceptions 12 and 48) and port. (p.example BuildingE10 Room:CPR1 Rack:ODF3 HU:32 Port:15, and in the exceptions table is one row with data - buildingE10, room:CPR1, rack:ODF3 HU:32 number of ports:48).
Because port 15 belong to the first half of the 48 panel, this means it is the left side of the rack ODF3 = respectively Point 4.


I would appreciate if you can help to with this project.


Thank you