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. 
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"alex" wrote:

> 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?
> 
> 

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


On Mar 23, 2:45 pm, Jerry Whittle
<JerryWhit...@discussions.microsoft.com> wrote:
> 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.
> --
> Jerry Whittle, Microsoft Access MVP
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
>
>
>
> "alex" wrote:
> > 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?- Hide quoted text -
>
> - Show quoted text -

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
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"alex" wrote:

> 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
> 
> 
> On Mar 23, 2:45 pm, Jerry Whittle
> <JerryWhit...@discussions.microsoft.com> wrote:
> > 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.
> > --
> > Jerry Whittle, Microsoft Access MVP
> > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> >
> >
> >
> > "alex" wrote:
> > > 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?- Hide quoted text -
> >
> > - Show quoted text -
> 
> 
>