Converting from Excel to Access - HELP!

[sherry1]

I am developing a database which tracks Victims and Offenders. The original data is in Excel and I want to convert it to Access. The existing data in Excel has a column for the family's last name (ie. SMITH), the next column has the first child in the family's name (ie. Jennifer), the third column has the second child's name (ie. John), etc. There can be up to a dozen first name columns, depending on how many children this family has had. On the same row you have the Offender's Last Name and First Name (in separate columns). This is followed by the Actual victim from that family. See example below:

SMITH Jennifer John ACOBY Melvin SMITH John

If there is more than one offender who has offended against this family there would be a duplicate Row with a different offender and possibly a different victim name at the end. Also, If one offender has offended against say both John and Jennifer they both of their names would appear at the end of that row.

In the database that I have created, there are 4 tables, Family, Children, Offenders, Offenses. Each tracks the pertinent information. My problem is that when I try to import the document I need to have each child on their on line with their own ID number, but the same FamilyID number. How can I do this? There are over 4000 entries, so doing this manually would be very unpleasant. If anyone can help I would really appreciate it. Someone mentioned a "thinking macro" but that's out of my league. Thanks.

Sherry

 

[Smack]

If each child has the same family id as the family table in their table, and a unique child id, it should not be to hard. In relationships, create a one to many relationship, family>child. Create a form based on the families table and insert a subform using the child table showing a record by family id. In the subform you will have multiple child entries for a single family record.
Hope this helps.I do not know how much experience you have with access, so I'm not sure what level your at. Do you have any reference material to work with? Access help provides alot of info, but I've found it easy to get sidetracked with it, and at times, extremely confused. You may also want to check out FAQ's, search and a few of the other forums at tek-tips. I did a search on day and found over 15,000 references to my question!
-Smack s-)

 

[gwinn7]

What I would do is import the entire spreadsheet and use append queries to populate your normalized tables.

If you are still concerned about duplicate records being appended, then create the Append query based on a SELECT query that uses the SELECT DISTINCTROW clause which will filter out duplicate records. In other words, first make your select query, then create your append query based on that select query. The Select query is based on your imported Excel data.

Does this make sense?

Gary
gwinn7

 

[MichaelRed]

Sherry1,

First item of advice is to JOIN tek-Tips as a member. It is free, and several features which you may want to use are not available to visitors.

Second item is a question. Are the coloumns' content identifiable? e.g. can you know programmatically which col in Excel has perp last name, which col(s) are specific victims names? If so, the import can probably be accomplished w/o any manual labor. Otherwise ...


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over