Novice need advice on simple calculation

[TougToug]

Hi, i need to do a simple calculation on a field in my db for auto-entry, but i'm getting confuse between what i can do and NOT do and between the sripts step that i cant use (or don't know how) and functions.

Here it is: I am listing in 4 different fields the assembly or part of the assembly that needs to be tested.

the 4 fields are:

Part No

Joint ID

Tube No

Connector No

for a bit more information, i can test a full assy (part no) or a tube on its own (tube no) or a connector on its own (connector no) or and that's where my problem is, a connector and a tube (Joint ID = tube no + connector no)

Now i have an existing list of "Tube no" and "Connector No" but i need a calculation that creates automatically a joint id when i select a tube + a connector, and checks as well if the couple alreadyy exist or not (so that next time i use the same tube+connector the same joint id is used)

So far i have a table for connectors and one for tubes, and one for joint with "joint" "tube" and "connector" fields in.

few, i hope i've been clear enough, if not let me know and i'll try to explain it better...

Thanks for any help,

GREAT FORUMS ! ! !

[stanley]

TougToug:

What I think you mean is that if you've got an entry in both "tube no" and "Connector No", then you want it to automatically create a "Joint ID", right? In that case, you could have a calculation field which would be defined as:

Case ( IsEmpty(tube no) & IsEmpty (Connector No); "" ; tube no & Connector No)

As far as checking to see if the resulting number has already been used, I'm afraid I don't understand why you'd do that. However. You could script that Case() statement up there, instead of defining the field with it. You'd check to see if both fields are empty first, and if not, take the combination of the two and search your Joint ID field for that; if Get(Found Count) = 0, you'd then set your Joint ID field to the combination of the two fields.

Hope that makes sense; I'm still on my first cup of tea.

-Stanley

[-Queue-]

The ampersand in the test should be replaced with AND.

You can also do the inverse and simplify the calc.

Case( not (IsEmpty(Tube No) or IsEmpty(Connector No)); Tube No & "_" & Connector No )

[TougToug]

Hi, thanks for your answers, but i thought about something like that, unfortunately it's the bit where you check the joint ID i wasn't sure about...

Let me explain, the purpose of checkin if the Joint ID has been used already is that every time i'm using the same couple "tube+connector", it allocate it the same Joint ID, so that when i go back to my test results later i can compare the same Joint by choosing a particular Joint ID and looking all the results for that specific Joint

hope it helps understanding what i want;)

THANKS AGAIN

[stanley]

TougToug:

Queue is of course right - my Case() was in error, though my heart was in the right place.

However, I think we're all missing some important data, in that we can't see what you're trying to do. If these are all test results, then you want to see all of the identical "tube+connector" results, as they're all the same tests (but perhaps with different results?) In which case, you actually want multiples of the same JointID, which is the same as "tube+connector".

I, for one, need more clarification.

-Stanley

[TougToug]

Yes, your last suggestion is the right one!!

Let me try to clarify a bit more:

in one of the layer i do sort of a simple BOM (bill of material) = i am listing the "part no", the "tube no", the "connector no", the "joint" (joint = tube + connector) to be tested

So what i need the calculation to do is when i list these items for a specific "project", it tests from a Joint Table if the Joint already exist (then display) or if it doesnt, create the next one (next serial number sort of thing)

Hope that helps,

THANKS FOR THE QUICK ANSWER!!!

GREAT FORUM

[TougToug]

Still need help though