Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Need help fomatting spreadsheet for import into access

Discussion in 'Information Technology' started by Fatboymedic, Nov 15, 2005.

  1. Fatboymedic

    Fatboymedic
    Expand Collapse
    Guest

    I have a spreadsheet that i exported from an old database program, in CSV
    format. I have it saved as a xls now, and my data is laid out like:

    lastname firstname dob date01 date 02 date 03 ticket01 ticket02
    ticket03 (etc)
    lastname firstname dob date01 date 02 date 03 ticket01 ticket02
    ticket03

    I NEED (for importing into an access database)

    lastname firstname dob date01 ticket01
    lastname firstname dob date02 ticket02
    lastname firstname dob date03 ticket03

    I have 2365 individual names... some with one date/ticket entry, some with
    10....

    HELP!?
     
  2. Loading...

    Similar Threads Forum Date
    Interracial And Interfaith Marriage, Help Needed To Confront/convince My Parents Love & Marriage Aug 16, 2016
    17 yr old Baljit needs your help Get Involved Aug 19, 2015
    Learn Punjabi Need help Language, Arts & Culture May 20, 2014
    United Sikhs From United Sikhs. Phillippines Needs Your Help Sikh Organisations Nov 22, 2013
    Need Help... (Keeping Hair) Questions and Answers Sep 2, 2013

  3. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    Create a temporary table in Access with the indicated fieldnames and
    datatypes. For purposes of illustration, let's name it tblImport. Import your
    data into this table. Then create a union query as follows:

    1.) Create a new query, and add tblImport
    2.) In query design view, click on View > SQL View
    3.) Enter the following SQL statement:

    SELECT lastname, firstname, dob, date01, ticket01
    FROM tblImport
    UNION
    SELECT lastname, firstname, dob, date02, ticket02
    FROM tblImport
    UNION
    SELECT lastname, firstname, dob, date03, ticket03
    FROM tblImport;

    Run the query by clicking on the maroon colored exclaimation mark. You
    should see the data in the format that you described. Save this query as
    quniImport.

    4.) Create a new query. Select quniImport as the source of data. In query
    design view, click on Query > Make-Table Query... or Query > Append Query...
    (if you already have a destination table that you wish to append (add) the
    records to).

    5.) Run your new make-table or append query. Presto.

    6.) Continue normalizing this table, as it's still contains lots of repeated
    data.


    Tom

    http://www.access.qbuilt.com/html/expert_contributors.html
    __________________________________________

    "Fatboymedic" wrote:

    I have a spreadsheet that i exported from an old database program, in CSV
    format. I have it saved as a xls now, and my data is laid out like:

    lastname firstname dob date01 date 02 date 03 ticket01 ticket02
    ticket03 (etc)
    lastname firstname dob date01 date 02 date 03 ticket01 ticket02
    ticket03

    I NEED (for importing into an access database)

    lastname firstname dob date01 ticket01
    lastname firstname dob date02 ticket02
    lastname firstname dob date03 ticket03

    I have 2365 individual names... some with one date/ticket entry, some with
    10....

    HELP!?
     
  4. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    <picky>

    SELECT lastname, firstname, dob, date01 As TicketDate, ticket01 As Ticket
    FROM tblImport
    UNION
    SELECT lastname, firstname, dob, date02, ticket02
    WHERE ticket02 IS NOT NULL
    FROM tblImport
    UNION
    SELECT lastname, firstname, dob, date03, ticket03
    FROM tblImport
    WHERE ticket03 IS NOT NULL

    </picky>

    Otherwise, your table fields are going to be lastname, firstname, dob,
    date01 and ticket01 (which I'll agree isn't the end of the world), and
    you'll have one row where (presumably) date01 and ticket01 are Null (to be
    honest, I've never tested whether UNION eliminates duplicate rows where some
    of the fields are Null)

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Tom Wickerath" <AOS168 AT @comcast DOT net> wrote in message
    news:EEEA42F4-DF46-4794-8057-C68B7E171508@microsoft.com...
    > Create a temporary table in Access with the indicated fieldnames and
    > datatypes. For purposes of illustration, let's name it tblImport. Import

    your
    > data into this table. Then create a union query as follows:
    >
    > 1.) Create a new query, and add tblImport
    > 2.) In query design view, click on View > SQL View
    > 3.) Enter the following SQL statement:
    >
    > SELECT lastname, firstname, dob, date01, ticket01
    > FROM tblImport
    > UNION
    > SELECT lastname, firstname, dob, date02, ticket02
    > FROM tblImport
    > UNION
    > SELECT lastname, firstname, dob, date03, ticket03
    > FROM tblImport;
    >
    > Run the query by clicking on the maroon colored exclaimation mark. You
    > should see the data in the format that you described. Save this query as
    > quniImport.
    >
    > 4.) Create a new query. Select quniImport as the source of data. In query
    > design view, click on Query > Make-Table Query... or Query > Append

    Query...
    > (if you already have a destination table that you wish to append (add) the
    > records to).
    >
    > 5.) Run your new make-table or append query. Presto.
    >
    > 6.) Continue normalizing this table, as it's still contains lots of

    repeated
    > data.
    >
    >
    > Tom
    >
    > http://www.access.qbuilt.com/html/expert_contributors.html
    > __________________________________________
    >
    > "Fatboymedic" wrote:
    >
    > I have a spreadsheet that i exported from an old database program, in CSV
    > format. I have it saved as a xls now, and my data is laid out like:
    >
    > lastname firstname dob date01 date 02 date 03 ticket01 ticket02
    > ticket03 (etc)
    > lastname firstname dob date01 date 02 date 03 ticket01 ticket02
    > ticket03
    >
    > I NEED (for importing into an access database)
    >
    > lastname firstname dob date01 ticket01
    > lastname firstname dob date02 ticket02
    > lastname firstname dob date03 ticket03
    >
    > I have 2365 individual names... some with one date/ticket entry, some

    with
    > 10....
    >
    > HELP!?
    >
     

Share This Page