need help formatting spreadsheet for access import

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!?
 
You saybut also that some names have a single date/ticket entry while others
have up to 10.

Is the data actually like this

A: lastname firstname dob date01 ticket01 date02 ticket02 ...

or is it like this

B: lastname firstname dob date01 date02 ... ticket01 ticket02 ...

i.e. after dob there are 1 to 10 date fields followed by 1 to 10 ticket
fields?

If the answer is A,

1) If necessary, add a row at the top of the Excel sheet and type field
names into it. Make sure the field names are valid Access field names;
use Date01, Ticket01 and so on.

2) In Access, use File|Get External Data|Link to create a linked table
connected to the worksheet. If you have any problems with this, export
the data from Excel to CSV and link to the CSV file.

3) Create a table with fields Lastname, Firstname, DOB, TheDate, and
Ticket. (Calling a field "Date" causes confusion with the Date()
function so is best avoided).

3) Create an append query that gets its data from the linked table and
appends to the "real" table. Only use the first three fields and Date01
and Ticket01; the latter two of course append to TheDate and Ticket. In
the criteria row, under Ticket01, put the criterion Is Not Null.

4) Execute this query. Then modify it so it uses Date02 and Ticket02,
and execute that. Repeat till done.

IF the answer is B, some coding will be needed. Post back here.
 
Back
Top