I need to import data into two tables...?

[Kelvin Beaton]

I have a database that is basically a contact database.
A Client's table and a Contact's table.

We will be recieving data in a spreadsheet that consistes of contact data,
and the children's name of the person bing contacted.

I'd like to upload the children's names into the memo field of the Contact
table.

My VBA skills are very basic. Most of the time I look for basic examples I
modify just a little to make things work.
I'm guessing this will take more skill then coping and pasting, but I was
hoping someone could a least point me in the right direction or maybe
someone knows of an example I can learn from?

Any help would be appreciated.

Kelvin

 

[kingston via AccessMonster.com]

If the format of the spreadsheet is consistent, you can create a link to it
(File->Get External Data->Link Tables...). Then all you need to do when you
receive a new spreadsheet is replace the existing one (make sure to use the
same file name). In Access, create an update query that uses this table to
modify the Memo field of your Contact table. Hopefully, the spreadsheet uses
the same Contact ID as the table so you can join on this field. I don't
think VBA is necessary here unless I've missed something.

 

[Arvin Meyer [MVP]]

No you really don't want to put the same data in 2 places, nor do you want
to put multiple values for the children in a memo field. That would be a
completely incorrect database design. Since there is a Contact Management
database wizard template that comes with most later versions of Access, and
can be downloaded from the Microsoft Access website, I suggest you work with
that and customize it to fit your needs. It's far from perfect, but it will
be a much better start than your proposed design.

 

[Kelvin Beaton]

Hi Arvin

Thanks for the reply.

The database is a Custom Database for a program we run here.
I have the database together fine, now we learned that some of the data will
come from another source and instead of copying and pasting the date in, I
though it might work better to import that set of data.

In our database we use the parent's names for the client record and we don't
store the kids names, but the data we are being given has the kids names ( a
record for each)
ie
Bob Smith Address City State Zip Child1
Bob Smith Address City State Zip Child2

We don't need the childs names in the Client record, but would use it in the
contact record. The parent is contacting us about a child.
I need the kids name to go in the contact record with the same ClientID as
the contact record (the parents record)

Don't know if this is any clearer of not.

Any input would be appreciate!

Kelvin

Here is an abreviated list of the table and related fields
tbl_ClientRecord
ClientID (primarykey)
FirstName
LastName
Address
City
State
Zip

tbl_ContactLog
ContactLogID (primarykey)
ClientID
ContactNotes

 

[Arvin Meyer [MVP]]

I think what you are asking is how to normalize data that you import. You
are probably importing either data from a query, or un-normalized data. I
think that the easiest way to handle it might be manually with a local temp
table. Imort the record as it stands into the temp table, having that table
assign the ClientID. The use an Append query to split the data into 2
tables:

INSERT INTO tblClient(ID, Field1, Field2 )
SELECT ID, Field1, Field2
FROM tblTemp;

and:

INSERT INTO tblClient_Kids(ID, Field1, Field2 )
SELECT ID, Field3, Field4
FROM tblTemp;

Notice the ID field is put into both tables. If you use an autonumber as a
key, it must be generated in the temp table.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[i_takeuti]

I have a database that is basically a contact database.
A Client's table and a Contact's table.

We will be recieving data in a spreadsheet that consistes of contact data,
and the children's name of the person bing contacted.

I'd like to upload the children's names into the memo field of the Contact
table.

My VBA skills are very basic. Most of the time I look for basic examples I
modify just a little to make things work.
I'm guessing this will take more skill then coping and pasting, but I was
hoping someone could a least point me in the right direction or maybe
someone knows of an example I can learn from?

Any help would be appreciated.

Kelvin

 

[Kelvin Beaton]

Hi Arvin

This is sounding like it might work...

The only catch I can think of at the moment is if I generate the Client ID
in the temp table, I'm going to end up with duplicate numbers. The
PrimaryKeys from data already entered and from the new data.

This seems crood but I could assign Primary Keys to the temp table records
(numbers above what has already been generated in the live database)

Can you think of a better way to do this?

Thanks

Kelvin