locked
Copy field from one table to another table RRS feed

  • Question

  • I have a text file that contains the position and matrix code, which I import into access. The table will then look like this:

    Position  Matrix Code
    A01   14782641410
    B01   14782641411
    C01   14782641412
    D01   14782641413
    A02   14782641414
    B02   14782641415
    C02   14782641416
    D02   14782641417

    How can I get access to copy all fields starting with 01 + matrixcode next to (A01 + matrixcode, B01 + matrixcode, etc.)
    to a new table with field names and Posisjon01 matrixkode01. Those who are 02, I will another table with field names and Posisjon02 matrixkode02

    for example
    Tabel1: Position01 Matrixcode01  Tabell2: Position02 Matrixcode02
     A01  4782641410    A02  4782641414
     B01  4782641411    B02  4782641415
     C01  4782641412    C02  4782641416
     D01  4782641413    D02  4782641417

    Is there anyone who can help me with this?

    best regards

    Friday, June 29, 2012 8:13 AM

Answers

  • Hi Geir Arne Hansen,

    This is a query that'll do the trick

    SELECT right([position],2) AS Expr2, position as position01, [Matrix Code] as matrixcode01 INTO tabel1
    FROM >>>the name of the table containing the source data<<<
    WHERE right([position],2)="01"

    This query get the data with 01 in the position field, create tabel1 and put them in. For the 02 it's the same with 02 in the where and changing the name of the target table.

    HTH Paolo

    • Proposed as answer by Quist Zhang Wednesday, July 4, 2012 12:25 PM
    • Marked as answer by Quist Zhang Monday, July 9, 2012 9:15 AM
    Friday, June 29, 2012 8:27 AM
  • It sounds like you're working on a bad database design. Having several tables with the same structure, one for 01, one for 02, etc. is a REALLY BAD IDEA. In a relational database you need to think narrow tables with many rows. In your case:

    tblAllData
    PositionLetter text(1) required PK
    PositionNumber number required PK
    MatrixCode text(20) required

    Now you can create a query that pulls the rows with PositionNumber = X. For example:
    select * from tblAllData where PositionNumber = [Give Position Number:]
    This essentially gives you each "table" you would ever need, but provides much more flexibility for other queries.

    Example:
    select PositionNumber, count(PositionNumber) as TheCount from tblAllData group by PositionNumber
    This gives you:
    PositionNumber  TheCount
    1                       4
    2                       4
    while in your original idea you would have to get the count of multiple tables, presumably by writing a loop in VBA.

    Note that I am not storing the leading zero for the PositionNumber; it is simply a display nicity can be formatted as such when needed:
    select PositionLetter & Format$(PositionNumber, "0#") as Position from tblAllData


    -Tom. Microsoft Access MVP

    • Proposed as answer by Quist Zhang Wednesday, July 4, 2012 12:24 PM
    • Marked as answer by Quist Zhang Monday, July 9, 2012 9:15 AM
    Friday, June 29, 2012 10:17 AM

All replies

  • Hi Geir Arne Hansen,

    This is a query that'll do the trick

    SELECT right([position],2) AS Expr2, position as position01, [Matrix Code] as matrixcode01 INTO tabel1
    FROM >>>the name of the table containing the source data<<<
    WHERE right([position],2)="01"

    This query get the data with 01 in the position field, create tabel1 and put them in. For the 02 it's the same with 02 in the where and changing the name of the target table.

    HTH Paolo

    • Proposed as answer by Quist Zhang Wednesday, July 4, 2012 12:25 PM
    • Marked as answer by Quist Zhang Monday, July 9, 2012 9:15 AM
    Friday, June 29, 2012 8:27 AM
  • It sounds like you're working on a bad database design. Having several tables with the same structure, one for 01, one for 02, etc. is a REALLY BAD IDEA. In a relational database you need to think narrow tables with many rows. In your case:

    tblAllData
    PositionLetter text(1) required PK
    PositionNumber number required PK
    MatrixCode text(20) required

    Now you can create a query that pulls the rows with PositionNumber = X. For example:
    select * from tblAllData where PositionNumber = [Give Position Number:]
    This essentially gives you each "table" you would ever need, but provides much more flexibility for other queries.

    Example:
    select PositionNumber, count(PositionNumber) as TheCount from tblAllData group by PositionNumber
    This gives you:
    PositionNumber  TheCount
    1                       4
    2                       4
    while in your original idea you would have to get the count of multiple tables, presumably by writing a loop in VBA.

    Note that I am not storing the leading zero for the PositionNumber; it is simply a display nicity can be formatted as such when needed:
    select PositionLetter & Format$(PositionNumber, "0#") as Position from tblAllData


    -Tom. Microsoft Access MVP

    • Proposed as answer by Quist Zhang Wednesday, July 4, 2012 12:24 PM
    • Marked as answer by Quist Zhang Monday, July 9, 2012 9:15 AM
    Friday, June 29, 2012 10:17 AM
  • Tom is right - you should store the records on one table UNLESS you are VERY experienced, have indexed your tables appropriately, and require these additional tables as lookups for some highly optimised task.

    Your time would be better spent learning how to construct queries/subqueries with the appropriate WHERE condition so that they run on the particular data you wish to evaluate/manipulate at any given point in time.

    You might find it beneficial to split some of your columns into multiple columns, and include them in the primary key/ other indexes.

    When you have mastered this technique, you will find it substantially easier to do what you are asking here (though I doubt you will still want to solve your problem in this way).  If you really want to do what you are asking, try searching the internet for "SELECT ... INSERT" syntax for Microsoft Access database.


    Matthew Slyman M.A. (Camb.)

    Friday, June 29, 2012 10:58 AM