Need help fomatting spreadsheet for import into access

[Fatboymedic]

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

 

[Tom Wickerath]

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
__________________________________________

:

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

 

[Douglas J Steele]

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