Format on data to import to Access tables? (I need your advice)

Good morning!

I´m in that luxury situation that I can format the data which need to be
imported into my application the way I want. I´m developing a db for storing
member data about members in our assosiation. Like names, addresses,
membership types but allso family members names and DoB:s.

Right now I´m having a dialoge with the webmaster of our website because
it´s possible to apply for a membership through our website. The diskussion
right now is exactly how shall the data that I get from this website be
formatted so I can import it into my db?

In my db I have 2 table for this purpose tblMemberApplyRegistration and
tblFamilyMemberRegistration with a ONE - MANY relation. These tables is
only used to import the data to and also used in a wizard that I allready
have created with several forms.

The data, from the website, is today sent over with e-mail but my intention
is to ask the webmaster if he can´t send it as a attached textfile so the
user of my application can save the attached textfile in a folder only for
this and then, through my application, selecte the file to be imported (by
using Save/Open Dialoge). The data will then be imported into the two tables
and I start the wizard so the user can select further options.

Now to my problem:

Since I havn´t done this a lot before I´m a little bit unsure which way is
the best to go. I intend to use DoCmd.TransfereText to do this, I have
used this before.

The data that I need to import can look like this:

FirstName,LastName,DoB....etc.
Niklas,Östergren,041224

This is if the person applying for membership only for ONE person. But how
shall the data be formatted in the textfield if he applyés for membership
for his whole family? The data needs to go in 2 different tables (to start
with anyway). The "applyer´s" data (Name, DoB, Addreses etc.) in
tblMemberApplyRegistration and the family members names, DoB in table
tblFamilyMemberRegistration.

If I use acImportDelim or any of the other transfere types for importing
data I don´t know which data in the attached textfile to import into which
table, or?

I know that some of you now are going to mention that the names and DoB´s
needs to go into the same tables no matter if it´s family members on the
"applyer´s" names. In the end it will. What is actually happening in my
application is that I store ALL names, DoB´s in table
tblFamilyMemberRegistration, including the "applyer´s". But when the user
step´s on to the next form inmy wizard I copy the data into the real tables
tblPerson (Names,DoB) which have a ONE - MANY relation to
tblPersonAddress in which I store the address for all people. And since a
family lives on ONE address (the have to to be able to join as a family
member in our assosiation it has to do with dispatches) I only need to
change the address in one place.

tblPerson is also related to tblFamilyMembers with a ONE - MANY
relation. In this table I store primary key for each person whco is a family
member and which data is stored in tblPerson. But also primary key for the
membership that each person have (stored in tblMember). The family is then
linked together in this table with the main member´s MemberNo, which is the
person that applyed for the membership.

I recon that my db is petty much normalized so no I hope that I have
explaned the information needed to be able to help me out with this problem
of HOW the data shall be formatted for me to be able to import it in the
correct tables?

TIA!
// Niklas

Maby I wrote to much for you to read!? If so I´m sorry so I´ll try to be a
little bit more specific here with my Q.

I´d like to import data from a textfile into two different tables in my db.
How shall the data in the textfile be formatted for me knowing which data to
go in which table in my db?

I intend to use DoCmd.TransfereText to import this data and i know of fixed
import and delimeter import but that doesn´t tell me which data to import
where, or?

Is there a better way of doing this than to use TransfereText? Or is there a
better form to import from than a textfile?

The data I´d like to import is "applyer data":
Niklas,Östergren,041224

This data is allways ONLY one record. ONE FirstName, ONE LastName etc.


The second data, which is recived in the same textfile (or what ever
filetype I want, allmost anyway), is the names of family members:

Niklas,Östergren,041224
Erika,Svensson,041223
Magnus,Svensson,041231
etc.

This data, as you understand, can contain names and other data for 0 (zero)
to as many records as a person can produce children during a lifetime. And I
don´t know how many it is from time to time.

Some of the data is the same as for the "applyer" and my Q is how shall it
be seperated in the textfile so I know which data to import to which table
in my application?

The second Q is:
Is there a better way to do this then using DoCmd.TransfereText?

The last Q is:
Shall I ask to get the data in any other media then a textfile? If so, which
format?

TIA!
// Niklas
"Niklas Östergren" wrote in message
...
Good morning!

I´m in that luxury situation that I can format the data which need to be
imported into my application the way I want. I´m developing a db for
storing
member data about members in our assosiation. Like names, addresses,
membership types but allso family members names and DoB:s.

Right now I´m having a dialoge with the webmaster of our website because
it´s possible to apply for a membership through our website. The
diskussion
right now is exactly how shall the data that I get from this website be
formatted so I can import it into my db?

In my db I have 2 table for this purpose tblMemberApplyRegistration and
tblFamilyMemberRegistration with a ONE - MANY relation. These tables is
only used to import the data to and also used in a wizard that I allready
have created with several forms.

The data, from the website, is today sent over with e-mail but my
intention
is to ask the webmaster if he can´t send it as a attached textfile so the
user of my application can save the attached textfile in a folder only for
this and then, through my application, selecte the file to be imported (by
using Save/Open Dialoge). The data will then be imported into the two
tables
and I start the wizard so the user can select further options.

Now to my problem:

Since I havn´t done this a lot before I´m a little bit unsure which way is
the best to go. I intend to use DoCmd.TransfereText to do this, I have
used this before.

The data that I need to import can look like this:

FirstName,LastName,DoB....etc.
Niklas,Östergren,041224

This is if the person applying for membership only for ONE person. But how
shall the data be formatted in the textfield if he applyés for membership
for his whole family? The data needs to go in 2 different tables (to start
with anyway). The "applyer´s" data (Name, DoB, Addreses etc.) in
tblMemberApplyRegistration and the family members names, DoB in table
tblFamilyMemberRegistration.

If I use acImportDelim or any of the other transfere types for importing
data I don´t know which data in the attached textfile to import into which
table, or?

I know that some of you now are going to mention that the names and DoB´s
needs to go into the same tables no matter if it´s family members on the
"applyer´s" names. In the end it will. What is actually happening in my
application is that I store ALL names, DoB´s in table
tblFamilyMemberRegistration, including the "applyer´s". But when the
user
step´s on to the next form inmy wizard I copy the data into the real
tables
tblPerson (Names,DoB) which have a ONE - MANY relation to
tblPersonAddress in which I store the address for all people. And since
a
family lives on ONE address (the have to to be able to join as a family
member in our assosiation it has to do with dispatches) I only need to
change the address in one place.

tblPerson is also related to tblFamilyMembers with a ONE - MANY
relation. In this table I store primary key for each person whco is a
family
member and which data is stored in tblPerson. But also primary key for
the
membership that each person have (stored in tblMember). The family is
then
linked together in this table with the main member´s MemberNo, which is
the
person that applyed for the membership.

I recon that my db is petty much normalized so no I hope that I have
explaned the information needed to be able to help me out with this
problem
of HOW the data shall be formatted for me to be able to import it in the
correct tables?

TIA!
// Niklas

My initial impression is that you should get the data in a text file. I
would import the data as a single field for each record in the text file.
Then use append queries to parse the data and copy it into the permanent
table.

If your web designer can do it, I'd recommend that he use a | delimiter to
separate the data into fields; that will make it easy to parse out the data
in your append query - or via an import spec if you decide to use that
method to parse the data immediately.
--

Ken Snell
MS ACCESS MVP




"Niklas Östrergren" wrote in message
...
Maby I wrote to much for you to read!? If so I´m sorry so I´ll try to be a
little bit more specific here with my Q.

I´d like to import data from a textfile into two different tables in my
db.
How shall the data in the textfile be formatted for me knowing which data
to
go in which table in my db?

I intend to use DoCmd.TransfereText to import this data and i know of
fixed
import and delimeter import but that doesn´t tell me which data to import
where, or?

Is there a better way of doing this than to use TransfereText? Or is there
a
better form to import from than a textfile?

The data I´d like to import is "applyer data":
Niklas,Östergren,041224

This data is allways ONLY one record. ONE FirstName, ONE LastName etc.


The second data, which is recived in the same textfile (or what ever
filetype I want, allmost anyway), is the names of family members:

Niklas,Östergren,041224
Erika,Svensson,041223
Magnus,Svensson,041231
etc.

This data, as you understand, can contain names and other data for 0
(zero)
to as many records as a person can produce children during a lifetime. And
I
don´t know how many it is from time to time.

Some of the data is the same as for the "applyer" and my Q is how shall it
be seperated in the textfile so I know which data to import to which table
in my application?

The second Q is:
Is there a better way to do this then using DoCmd.TransfereText?

The last Q is:
Shall I ask to get the data in any other media then a textfile? If so,
which
format?

TIA!
// Niklas
"Niklas Östergren" wrote in message
...
Good morning!

I´m in that luxury situation that I can format the data which need to be
imported into my application the way I want. I´m developing a db for
storing
member data about members in our assosiation. Like names, addresses,
membership types but allso family members names and DoB:s.

Right now I´m having a dialoge with the webmaster of our website because
it´s possible to apply for a membership through our website. The
diskussion
right now is exactly how shall the data that I get from this website be
formatted so I can import it into my db?

In my db I have 2 table for this purpose tblMemberApplyRegistration
and
tblFamilyMemberRegistration with a ONE - MANY relation. These tables
is
only used to import the data to and also used in a wizard that I
allready
have created with several forms.

The data, from the website, is today sent over with e-mail but my
intention
is to ask the webmaster if he can´t send it as a attached textfile so
the
user of my application can save the attached textfile in a folder only
for
this and then, through my application, selecte the file to be imported
(by
using Save/Open Dialoge). The data will then be imported into the two
tables
and I start the wizard so the user can select further options.

Now to my problem:

Since I havn´t done this a lot before I´m a little bit unsure which way
is
the best to go. I intend to use DoCmd.TransfereText to do this, I have
used this before.

The data that I need to import can look like this:

FirstName,LastName,DoB....etc.
Niklas,Östergren,041224

This is if the person applying for membership only for ONE person. But
how
shall the data be formatted in the textfield if he applyés for
membership
for his whole family? The data needs to go in 2 different tables (to
start
with anyway). The "applyer´s" data (Name, DoB, Addreses etc.) in
tblMemberApplyRegistration and the family members names, DoB in table
tblFamilyMemberRegistration.

If I use acImportDelim or any of the other transfere types for importing
data I don´t know which data in the attached textfile to import into
which
table, or?

I know that some of you now are going to mention that the names and
DoB´s
needs to go into the same tables no matter if it´s family members on the
"applyer´s" names. In the end it will. What is actually happening in my
application is that I store ALL names, DoB´s in table
tblFamilyMemberRegistration, including the "applyer´s". But when the
user
step´s on to the next form inmy wizard I copy the data into the real
tables
tblPerson (Names,DoB) which have a ONE - MANY relation to
tblPersonAddress in which I store the address for all people. And
since
a
family lives on ONE address (the have to to be able to join as a family
member in our assosiation it has to do with dispatches) I only need to
change the address in one place.

tblPerson is also related to tblFamilyMembers with a ONE - MANY
relation. In this table I store primary key for each person whco is a
family
member and which data is stored in tblPerson. But also primary key for
the
membership that each person have (stored in tblMember). The family is
then
linked together in this table with the main member´s MemberNo, which is
the
person that applyed for the membership.

I recon that my db is petty much normalized so no I hope that I have
explaned the information needed to be able to help me out with this
problem
of HOW the data shall be formatted for me to be able to import it in the
correct tables?

TIA!
// Niklas

OK!

But how do I seperate which data to go in which table if the only thing
seperating the data in the textfile is semicólon´s?

This is the data I need to repetadly imprt to my db:

FirstName;LastName;Gender;DoB;Street;ZipCode;Count ry;PhoneHome;PhoneWork;Pho
neMobile;EmailAddress etc.
Niklas;Östergren;Male;041224;Uperstreet 34;541

Johan;Östergren;Male;041228
Erika;Östergren;Female;041231

The first row of data should go in ONE table "tblNewMemberShipEntry" and the
other row´s shall go in table "tblFamilyMemberRegistration". Then when the
wizard starts I copy [FirstName], [LastName], [Gender] and [DoB] to
"tblFamilyMemberRegistration" from table "tblNewMemberShipEntry" and set
field [tblFamilyMemberRegistration.mainMember] = True for that record.

When the wizard is finished I copy nessesary data to the final tables and
delete all records in these two tables to make it possible for the user to
import another memberapply.

So I still doesn´t know how to sort out which data from a delimeter textfile
to import in which table?

I hope I make myself cleare about my problem here!? If not I applogize for
it!

Thank´s for helping out!
// Niklas



"Ken Snell [MVP]" skrev i meddelandet
...
My initial impression is that you should get the data in a text file. I
would import the data as a single field for each record in the text file.
Then use append queries to parse the data and copy it into the permanent
table.

If your web designer can do it, I'd recommend that he use a | delimiter to
separate the data into fields; that will make it easy to parse out the
data
in your append query - or via an import spec if you decide to use that
method to parse the data immediately.
--

Ken Snell
MS ACCESS MVP




"Niklas Östrergren" wrote in message
...
Maby I wrote to much for you to read!? If so I´m sorry so I´ll try to be
a
little bit more specific here with my Q.

I´d like to import data from a textfile into two different tables in my
db.
How shall the data in the textfile be formatted for me knowing which
data
to
go in which table in my db?

I intend to use DoCmd.TransfereText to import this data and i know of
fixed
import and delimeter import but that doesn´t tell me which data to
import
where, or?

Is there a better way of doing this than to use TransfereText? Or is
there
a
better form to import from than a textfile?

The data I´d like to import is "applyer data":
Niklas,Östergren,041224

This data is allways ONLY one record. ONE FirstName, ONE LastName etc.


The second data, which is recived in the same textfile (or what ever
filetype I want, allmost anyway), is the names of family members:

Niklas,Östergren,041224
Erika,Svensson,041223
Magnus,Svensson,041231
etc.

This data, as you understand, can contain names and other data for 0
(zero)
to as many records as a person can produce children during a lifetime.
And
I
don´t know how many it is from time to time.

Some of the data is the same as for the "applyer" and my Q is how shall
it
be seperated in the textfile so I know which data to import to which
table
in my application?

The second Q is:
Is there a better way to do this then using DoCmd.TransfereText?

The last Q is:
Shall I ask to get the data in any other media then a textfile? If so,
which
format?

TIA!
// Niklas
"Niklas Östergren" wrote in message
...
Good morning!

I´m in that luxury situation that I can format the data which need to
be
imported into my application the way I want. I´m developing a db for
storing
member data about members in our assosiation. Like names, addresses,
membership types but allso family members names and DoB:s.

Right now I´m having a dialoge with the webmaster of our website
because
it´s possible to apply for a membership through our website. The
diskussion
right now is exactly how shall the data that I get from this website
be
formatted so I can import it into my db?

In my db I have 2 table for this purpose tblMemberApplyRegistration
and
tblFamilyMemberRegistration with a ONE - MANY relation. These
tables
is
only used to import the data to and also used in a wizard that I
allready
have created with several forms.

The data, from the website, is today sent over with e-mail but my
intention
is to ask the webmaster if he can´t send it as a attached textfile so
the
user of my application can save the attached textfile in a folder only
for
this and then, through my application, selecte the file to be imported
(by
using Save/Open Dialoge). The data will then be imported into the two
tables
and I start the wizard so the user can select further options.

Now to my problem:

Since I havn´t done this a lot before I´m a little bit unsure which
way
is
the best to go. I intend to use DoCmd.TransfereText to do this, I
have
used this before.

The data that I need to import can look like this:

FirstName,LastName,DoB....etc.
Niklas,Östergren,041224

This is if the person applying for membership only for ONE person. But
how
shall the data be formatted in the textfield if he applyés for
membership
for his whole family? The data needs to go in 2 different tables (to
start
with anyway). The "applyer´s" data (Name, DoB, Addreses etc.) in
tblMemberApplyRegistration and the family members names, DoB in
table
tblFamilyMemberRegistration.

If I use acImportDelim or any of the other transfere types for
importing
data I don´t know which data in the attached textfile to import into
which
table, or?

I know that some of you now are going to mention that the names and
DoB´s
needs to go into the same tables no matter if it´s family members on
the
"applyer´s" names. In the end it will. What is actually happening in
my
application is that I store ALL names, DoB´s in table
tblFamilyMemberRegistration, including the "applyer´s". But when the
user
step´s on to the next form inmy wizard I copy the data into the real
tables
tblPerson (Names,DoB) which have a ONE - MANY relation to
tblPersonAddress in which I store the address for all people. And
since
a
family lives on ONE address (the have to to be able to join as a
family
member in our assosiation it has to do with dispatches) I only need to
change the address in one place.

tblPerson is also related to tblFamilyMembers with a ONE - MANY
relation. In this table I store primary key for each person whco is a
family
member and which data is stored in tblPerson. But also primary key
for
the
membership that each person have (stored in tblMember). The family
is
then
linked together in this table with the main member´s MemberNo, which
is
the
person that applyed for the membership.

I recon that my db is petty much normalized so no I hope that I have
explaned the information needed to be able to help me out with this
problem
of HOW the data shall be formatted for me to be able to import it in
the
correct tables?

TIA!
// Niklas

If you import the entire line from the text file into one field in a
temporary table, you then can use a query to split out the data.

This can be done using "parsing" functions (Left, InStr, Mid, Right)
directly in expressions, which is a bit tricky, or you can use a VBA
function to parse the data into separate fields using the Split function and
then return the desired data to the query. For example, for the latter
example, use a public function similar to this:

Public Function SplitMyImportedData(strOriginalString As String, _
strDelimiter As String, intFieldNumber As Integer)
Dim varArray As Variant
varArray = Split(strOriginalString, strDelimiter)
SplitMyImportedData = varArray(intFieldNumber - 1)
End Function

You then would call this function for each field in the append query. For
example, to get the value of the first field, the "Field:" cell would
contain
Field1: SplitMyImportedData([MemoFieldName], ";", 1)


--

Ken Snell
MS ACCESS MVP


"Niklas Östergren" wrote in message
...
OK!

But how do I seperate which data to go in which table if the only thing
seperating the data in the textfile is semicólon´s?

This is the data I need to repetadly imprt to my db:


FirstName;LastName;Gender;DoB;Street;ZipCode;Count ry;PhoneHome;PhoneWork;Pho
neMobile;EmailAddress etc.
Niklas;Östergren;Male;041224;Uperstreet 34;541

Johan;Östergren;Male;041228
Erika;Östergren;Female;041231

The first row of data should go in ONE table "tblNewMemberShipEntry" and
the
other row´s shall go in table "tblFamilyMemberRegistration". Then when the
wizard starts I copy [FirstName], [LastName], [Gender] and [DoB] to
"tblFamilyMemberRegistration" from table "tblNewMemberShipEntry" and set
field [tblFamilyMemberRegistration.mainMember] = True for that record.

When the wizard is finished I copy nessesary data to the final tables and
delete all records in these two tables to make it possible for the user to
import another memberapply.

So I still doesn´t know how to sort out which data from a delimeter
textfile
to import in which table?

I hope I make myself cleare about my problem here!? If not I applogize for
it!

Thank´s for helping out!
// Niklas



"Ken Snell [MVP]" skrev i meddelandet
...
My initial impression is that you should get the data in a text file. I
would import the data as a single field for each record in the text
file.
Then use append queries to parse the data and copy it into the permanent
table.

If your web designer can do it, I'd recommend that he use a | delimiter
to
separate the data into fields; that will make it easy to parse out the
data
in your append query - or via an import spec if you decide to use that
method to parse the data immediately.
--

Ken Snell
MS ACCESS MVP




"Niklas Östrergren" wrote in message
...
Maby I wrote to much for you to read!? If so I´m sorry so I´ll try to
be
a
little bit more specific here with my Q.

I´d like to import data from a textfile into two different tables in
my
db.
How shall the data in the textfile be formatted for me knowing which
data
to
go in which table in my db?

I intend to use DoCmd.TransfereText to import this data and i know of
fixed
import and delimeter import but that doesn´t tell me which data to
import
where, or?

Is there a better way of doing this than to use TransfereText? Or is
there
a
better form to import from than a textfile?

The data I´d like to import is "applyer data":
Niklas,Östergren,041224

This data is allways ONLY one record. ONE FirstName, ONE LastName etc.


The second data, which is recived in the same textfile (or what ever
filetype I want, allmost anyway), is the names of family members:

Niklas,Östergren,041224
Erika,Svensson,041223
Magnus,Svensson,041231
etc.

This data, as you understand, can contain names and other data for 0
(zero)
to as many records as a person can produce children during a lifetime.
And
I
don´t know how many it is from time to time.

Some of the data is the same as for the "applyer" and my Q is how
shall
it
be seperated in the textfile so I know which data to import to which
table
in my application?

The second Q is:
Is there a better way to do this then using DoCmd.TransfereText?

The last Q is:
Shall I ask to get the data in any other media then a textfile? If so,
which
format?

TIA!
// Niklas
"Niklas Östergren" wrote in message
...
Good morning!

I´m in that luxury situation that I can format the data which need
to
be
imported into my application the way I want. I´m developing a db for
storing
member data about members in our assosiation. Like names, addresses,
membership types but allso family members names and DoB:s.

Right now I´m having a dialoge with the webmaster of our website
because
it´s possible to apply for a membership through our website. The
diskussion
right now is exactly how shall the data that I get from this website
be
formatted so I can import it into my db?

In my db I have 2 table for this purpose
tblMemberApplyRegistration
and
tblFamilyMemberRegistration with a ONE - MANY relation. These
tables
is
only used to import the data to and also used in a wizard that I
allready
have created with several forms.

The data, from the website, is today sent over with e-mail but my
intention
is to ask the webmaster if he can´t send it as a attached textfile
so
the
user of my application can save the attached textfile in a folder
only
for
this and then, through my application, selecte the file to be
imported
(by
using Save/Open Dialoge). The data will then be imported into the
two
tables
and I start the wizard so the user can select further options.

Now to my problem:

Since I havn´t done this a lot before I´m a little bit unsure which
way
is
the best to go. I intend to use DoCmd.TransfereText to do this, I
have
used this before.

The data that I need to import can look like this:

FirstName,LastName,DoB....etc.
Niklas,Östergren,041224

This is if the person applying for membership only for ONE person.
But
how
shall the data be formatted in the textfield if he applyés for
membership
for his whole family? The data needs to go in 2 different tables (to
start
with anyway). The "applyer´s" data (Name, DoB, Addreses etc.) in
tblMemberApplyRegistration and the family members names, DoB in
table
tblFamilyMemberRegistration.

If I use acImportDelim or any of the other transfere types for
importing
data I don´t know which data in the attached textfile to import into
which
table, or?

I know that some of you now are going to mention that the names and
DoB´s
needs to go into the same tables no matter if it´s family members on
the
"applyer´s" names. In the end it will. What is actually happening in
my
application is that I store ALL names, DoB´s in table
tblFamilyMemberRegistration, including the "applyer´s". But when
the
user
step´s on to the next form inmy wizard I copy the data into the real
tables
tblPerson (Names,DoB) which have a ONE - MANY relation to
tblPersonAddress in which I store the address for all people. And
since
a
family lives on ONE address (the have to to be able to join as a
family
member in our assosiation it has to do with dispatches) I only need
to
change the address in one place.

tblPerson is also related to tblFamilyMembers with a ONE - MANY
relation. In this table I store primary key for each person whco is
a
family
member and which data is stored in tblPerson. But also primary key
for
the
membership that each person have (stored in tblMember). The family
is
then
linked together in this table with the main member´s MemberNo, which
is
the
person that applyed for the membership.

I recon that my db is petty much normalized so no I hope that I have
explaned the information needed to be able to help me out with this
problem
of HOW the data shall be formatted for me to be able to import it in
the
correct tables?

TIA!
// Niklas

OK!

Thank´s a lot for helping out Ken!

I´ll look closer into this and try to apply it in my application. Now I have
something to do for a while! :-)

Thank´s a lot!
// Niklas


"Ken Snell [MVP]" skrev i meddelandet
...
If you import the entire line from the text file into one field in a
temporary table, you then can use a query to split out the data.

This can be done using "parsing" functions (Left, InStr, Mid, Right)
directly in expressions, which is a bit tricky, or you can use a VBA
function to parse the data into separate fields using the Split function
and
then return the desired data to the query. For example, for the latter
example, use a public function similar to this:

Public Function SplitMyImportedData(strOriginalString As String, _
strDelimiter As String, intFieldNumber As Integer)
Dim varArray As Variant
varArray = Split(strOriginalString, strDelimiter)
SplitMyImportedData = varArray(intFieldNumber - 1)
End Function

You then would call this function for each field in the append query. For
example, to get the value of the first field, the "Field:" cell would
contain
Field1: SplitMyImportedData([MemoFieldName], ";", 1)


--

Ken Snell
MS ACCESS MVP


"Niklas Östergren" wrote in message
...
OK!

But how do I seperate which data to go in which table if the only thing
seperating the data in the textfile is semicólon´s?

This is the data I need to repetadly imprt to my db:



FirstName;LastName;Gender;DoB;Street;ZipCode;Count ry;PhoneHome;PhoneWork;Pho
neMobile;EmailAddress etc.
Niklas;Östergren;Male;041224;Uperstreet 34;541

Johan;Östergren;Male;041228
Erika;Östergren;Female;041231

The first row of data should go in ONE table "tblNewMemberShipEntry" and
the
other row´s shall go in table "tblFamilyMemberRegistration". Then when
the
wizard starts I copy [FirstName], [LastName], [Gender] and [DoB] to
"tblFamilyMemberRegistration" from table "tblNewMemberShipEntry" and set
field [tblFamilyMemberRegistration.mainMember] = True for that record.

When the wizard is finished I copy nessesary data to the final tables
and
delete all records in these two tables to make it possible for the user
to
import another memberapply.

So I still doesn´t know how to sort out which data from a delimeter
textfile
to import in which table?

I hope I make myself cleare about my problem here!? If not I applogize
for
it!

Thank´s for helping out!
// Niklas



"Ken Snell [MVP]" skrev i meddelandet
...
My initial impression is that you should get the data in a text file.
I
would import the data as a single field for each record in the text
file.
Then use append queries to parse the data and copy it into the
permanent
table.

If your web designer can do it, I'd recommend that he use a |
delimiter
to
separate the data into fields; that will make it easy to parse out the
data
in your append query - or via an import spec if you decide to use that
method to parse the data immediately.
--

Ken Snell
MS ACCESS MVP




"Niklas Östrergren" wrote in message
...
Maby I wrote to much for you to read!? If so I´m sorry so I´ll try
to
be
a
little bit more specific here with my Q.

I´d like to import data from a textfile into two different tables in
my
db.
How shall the data in the textfile be formatted for me knowing which
data
to
go in which table in my db?

I intend to use DoCmd.TransfereText to import this data and i know
of
fixed
import and delimeter import but that doesn´t tell me which data to
import
where, or?

Is there a better way of doing this than to use TransfereText? Or is
there
a
better form to import from than a textfile?

The data I´d like to import is "applyer data":
Niklas,Östergren,041224

This data is allways ONLY one record. ONE FirstName, ONE LastName
etc.


The second data, which is recived in the same textfile (or what ever
filetype I want, allmost anyway), is the names of family members:

Niklas,Östergren,041224
Erika,Svensson,041223
Magnus,Svensson,041231
etc.

This data, as you understand, can contain names and other data for 0
(zero)
to as many records as a person can produce children during a
lifetime.
And
I
don´t know how many it is from time to time.

Some of the data is the same as for the "applyer" and my Q is how
shall
it
be seperated in the textfile so I know which data to import to which
table
in my application?

The second Q is:
Is there a better way to do this then using DoCmd.TransfereText?

The last Q is:
Shall I ask to get the data in any other media then a textfile? If
so,
which
format?

TIA!
// Niklas
"Niklas Östergren" wrote in message
...
Good morning!

I´m in that luxury situation that I can format the data which need
to
be
imported into my application the way I want. I´m developing a db
for
storing
member data about members in our assosiation. Like names,
addresses,
membership types but allso family members names and DoB:s.

Right now I´m having a dialoge with the webmaster of our website
because
it´s possible to apply for a membership through our website. The
diskussion
right now is exactly how shall the data that I get from this
website
be
formatted so I can import it into my db?

In my db I have 2 table for this purpose
tblMemberApplyRegistration
and
tblFamilyMemberRegistration with a ONE - MANY relation. These
tables
is
only used to import the data to and also used in a wizard that I
allready
have created with several forms.

The data, from the website, is today sent over with e-mail but my
intention
is to ask the webmaster if he can´t send it as a attached textfile
so
the
user of my application can save the attached textfile in a folder
only
for
this and then, through my application, selecte the file to be
imported
(by
using Save/Open Dialoge). The data will then be imported into the
two
tables
and I start the wizard so the user can select further options.

Now to my problem:

Since I havn´t done this a lot before I´m a little bit unsure
which
way
is
the best to go. I intend to use DoCmd.TransfereText to do this,
I
have
used this before.

The data that I need to import can look like this:

FirstName,LastName,DoB....etc.
Niklas,Östergren,041224

This is if the person applying for membership only for ONE person.
But
how
shall the data be formatted in the textfield if he applyés for
membership
for his whole family? The data needs to go in 2 different tables
(to
start
with anyway). The "applyer´s" data (Name, DoB, Addreses etc.) in
tblMemberApplyRegistration and the family members names, DoB in
table
tblFamilyMemberRegistration.

If I use acImportDelim or any of the other transfere types for
importing
data I don´t know which data in the attached textfile to import
into
which
table, or?

I know that some of you now are going to mention that the names
and
DoB´s
needs to go into the same tables no matter if it´s family members
on
the
"applyer´s" names. In the end it will. What is actually happening
in
my
application is that I store ALL names, DoB´s in table
tblFamilyMemberRegistration, including the "applyer´s". But when
the
user
step´s on to the next form inmy wizard I copy the data into the
real
tables
tblPerson (Names,DoB) which have a ONE - MANY relation to
tblPersonAddress in which I store the address for all people.
And
since
a
family lives on ONE address (the have to to be able to join as a
family
member in our assosiation it has to do with dispatches) I only
need
to
change the address in one place.

tblPerson is also related to tblFamilyMembers with a ONE -
MANY
relation. In this table I store primary key for each person whco
is
a
family
member and which data is stored in tblPerson. But also primary
key
for
the
membership that each person have (stored in tblMember). The
family
is
then
linked together in this table with the main member´s MemberNo,
which
is
the
person that applyed for the membership.

I recon that my db is petty much normalized so no I hope that I
have
explaned the information needed to be able to help me out with
this
problem
of HOW the data shall be formatted for me to be able to import it
in
the
correct tables?

TIA!
// Niklas