Complex IIF function

  • Thread starter Thread starter alex
  • Start date Start date
A

alex

I have a table that look like this:

ID Opcode Date
123 Align 01/jan/06
Oil
Break

569 Align 05/jan/06
Break
Transmission
Inspection

425 Oil 04/Jan/06
etc...

I want to modifiy it with automatic macros to this:
ID Opcode Date
123 Align 01/jan/06
123 Oil 01/jan/06
123 Break 01/jan/06

569 Align 05/jan/06
569 Break 05/jan/06
569 Transmission 05/jan/06
569 Inspection 05/jan/06

425 Oil 04/Jan/06


This would be easy in excel. For examble, If ID is Colomn A, Opcode
Colomn B, i would create a C colomn and have this simple formula.

A B C
ID Opcode New ID Date
123 Align =if(A2="",C1,A2) ----) A2 = 123
Oil =if(A3="",C2,A3) -----) C2 =
123
Break =if(A4="",C3,A4) ----) C3 =
123

etc..

So it's easy in excel but excel cannot manage my database. I need to
do this in access. How could I do this?
 
Are you saying that "Break" is all by itself in its own row OR that it's in
the same record as Align and Oil? To use Excel terminology, Align Oil Break
are all in the same cell?

If "Break" is by itself in an Access table record, you have a real mess on
your had UNLESS there is another field in the table that can help link the
appropriate records together.

Also ID and Date should be in one table. In a second table you should have
Id and Opcode. This ID field will be the foriegn key linked to the first
table. That way you don't need to keep repeating the Date field for every
Opcode record.
 
It did not came out correctly but yes! it a mess this table. it`s
actualy a from a text file and I am trying to restructure the table
properly.

Do mind the 2 databases, it`s not my issue. My issue to to fixe my
"break" that is all by itself. in excel, aling oil and break are all
in diffrencent cell. on under each other. that is why the formula
works well in excel. I was wondering if access could manage something
like this.

I retyped the data here, hope this work better.

Row A B C
ID Opcode New ID
1 123 Align =if(A2="",C1,A2) ----) A2 = 123
2 Oil =if(A3="",C2,A3) ----) C2 =123
3 Break =if(A4="",C3,A4) ----) C3 =123

so I should get:
Row A B C
ID Opcode New ID
1 123 Align 123
2 Oil 123
3 Break 123
 
Access won't work like that. In fact you can't even guarentee that the
records will show up in the same order each time that you open up a table.
Break could be where it is now; above the ID; or even at the end of the table.

That leaves you a couple of choices. One would be to fix it in Excel then
import into Access. Another is some fancy code to parse out the text file and
import it. Here's something that might help from Roger Carlson.

http://www.rogersaccesslibrary.com/download3.asp?SampleName=ImportLineInput.mdb
 
Back
Top