If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Queries to import data from Access | R Ormerod | General Discussion | 3 | October 12th, 2004 12:39 PM |
Accessing a databse thru a webpage (newbie question) | Chang | General Discussion | 1 | August 23rd, 2004 09:10 AM |
Data from Excel to Access | Database Design | 2 | August 20th, 2004 12:53 PM | |
import data from access database to outlook contact | Ulrich | Contacts | 2 | May 6th, 2004 08:48 PM |
Excel - Import External Data from Access | VLeonard | Setting up and Configuration | 1 | November 7th, 2003 05:59 PM |