Importing 4 separate records from one line of an import file

[kaisersose1995]

Hi,
I've got an import procedure working, using a standard import
specification to import a .csv file into a temporary table.

The problem i'm having is that i have 4 different sets of borrower
details on the same line e.g. B1-Title, B1-Initials, B1-Surname,
B2-Title, B2-Initials, B2-Surname, etc.
all linked to my main borrower table via an unique account number.
My 1st append query matches which account numbers are new to the main
table and appends the 1st line no problem, however, as soon as the 1st
borrower details are added, the query can no longer find the difference
in account numbers, with them already now existing, so borrower's 2,3
and 4 details are lost.

Is there anyway i can append all 4 borrowers details so that each set
of borrowers details would append separately to the main borrower table
giving 4 separate records all linked with the same account number?

thanks in advance

Richard

[pietlinden]

Looks like you could import to your temporary table and then append the
data with the account number to the child tables. It's hard to tell,
since your sample data has no account number, though. If you don't
have the account number in the imported data, you could probably import
the data, use an update query to write in the account number, and then
append those where the accountnumber is not null.

[kaisersose1995]

pi********@hotmail.com wrote:

Looks like you could import to your temporary table and then append the
data with the account number to the child tables. It's hard to tell,
since your sample data has no account number, though. If you don't
have the account number in the imported data, you could probably import
the data, use an update query to write in the account number, and then
append those where the accountnumber is not null.

Thats what i'm doing. I'm using the account number on the main table
and the temp import table to determine which accounts aren't there
already, and then appending the 1st set of borrower details from my
temp table to the main table. the problem is, once i've imported the
1st set of borrowers details, the link between the temp table and the
main table is lost (due to the account number now being populated off
the 1st borrower append query) so i now have no way of importing the
2nd, 3rd and 4th set of borrowers details to the main borrower table.
Is that any clearer?

Thanks
Rich

[pietlinden]

Rich,

Oh, I think I get it now. You're basically using a find unmatched
query to get the data. One way around this might be to open a
recordset based on this query and then run a bunch of updates/appends
in code, so that the data doesn't disappear. Either that or flat out
read the primary key in code, save it to a variable, and then use it in
the addnew part of the code.

if that's not it, could you post the barebones structure of the two
tables you're importing into? Could be I'm missing something.

[Larry Linson]

"kaisersose1995" wrote

>Looks like you could import to your
temporary table and then append the
data with the account number to the
child tables. It's hard to tell, since your
sample data has no account number, though.
If you don't have the account number in
the imported data, you could probably import
the data, use an update query to write in the
account number, and then append those where
the accountnumber is not null.

Thats what i'm doing. I'm using the account
number on the main table and the temp import
table to determine which accounts aren't there
already, and then appending the 1st set of
borrower details from my temp table to the
main table. the problem is, once i've imported the
1st set of borrowers details, the link between
the temp table and the main table is lost (due
to the account number now being populated off
the 1st borrower append query) so i now have
no way of importing the 2nd, 3rd and 4th set
of borrowers details to the main borrower table.
Is that any clearer?

Perhaps we might be able to help if you would clarify the specific details
of your Tables, how the Borrowers relate to Accounts, and what part the
Title may play in this. It's entirely possible that it is a problem that
will have to be handled by some redesign... and without the details, your
description of the problem you face is not, in fact, clear (nor even
clearer). Not to me, at least.

Larry Linson
Microsoft Access MVP

[kaisersose1995]

pi********@hotmail.com wrote:

Rich,

Oh, I think I get it now. You're basically using a find unmatched
query to get the data. One way around this might be to open a
recordset based on this query and then run a bunch of updates/appends
in code, so that the data doesn't disappear. Either that or flat out
read the primary key in code, save it to a variable, and then use it in
the addnew part of the code.

if that's not it, could you post the barebones structure of the two
tables you're importing into? Could be I'm missing something.

I'll give the recordset idea a blast, think that could be the answer,
thanks

[kaisersose1995]

Larry Linson wrote:

"kaisersose1995" wrote

>Looks like you could import to your
>temporary table and then append the
>data with the account number to the
>child tables. It's hard to tell, since your
>sample data has no account number, though.
>If you don't have the account number in
>the imported data, you could probably import
>the data, use an update query to write in the
>account number, and then append those where
>the accountnumber is not null.

>
Thats what i'm doing. I'm using the account
number on the main table and the temp import
table to determine which accounts aren't there
already, and then appending the 1st set of
borrower details from my temp table to the
main table. the problem is, once i've imported the
1st set of borrowers details, the link between
the temp table and the main table is lost (due
to the account number now being populated off
the 1st borrower append query) so i now have
no way of importing the 2nd, 3rd and 4th set
of borrowers details to the main borrower table.
Is that any clearer?

Perhaps we might be able to help if you would clarify the specific details
of your Tables, how the Borrowers relate to Accounts, and what part the
Title may play in this. It's entirely possible that it is a problem that
will have to be handled by some redesign... and without the details, your
description of the problem you face is not, in fact, clear (nor even
clearer). Not to me, at least.

Larry Linson
Microsoft Access MVP

Larry,
I have a main table which lists account details including an account
number and a concatenated borrower name. I also have a borrower table
which lists the same account number and borrower details such as title,
initials, surname etc. The relationship between main table and
borrowers is 1 to many, i.e. upto 4 borrowers can have the same account
number (e.g husband and wife), but can only have one account on the
main table. I am trying to append data from a temporary table
(populated from importing a .csv file) to the borrower table. The
import file has all 4 borrowers details as well all all the other main
account information on one line. I am using a query linking the temp
table to the borrower table via account number to determine which
account numbers appear in the temp table and which do not. If the
account number is not already on the borrowers table, it appenda the
data to the table. The problem with this is that only 1st borrowers
details are being appended to the table, because after that, the
account number for the remaining borrowers now exists in both tables,
so the query cannot find any new accounts to append, and running an
update query would only update the one borrowers details thats already
stored, and not add the remaining 3 borrowers records to the borrowers
table.

if you still require basic structure how should i represent this? in
words, or is there the facility for diagrams?

thanks

Rich

[pietlinden]

Perhaps we might be able to help if you would clarify the specific details
of your Tables, how the Borrowers relate to Accounts, and what part the
Title may play in this. It's entirely possible that it is a problem that
will have to be handled by some redesign... and without the details, your
description of the problem you face is not, in fact, clear (nor even
clearer). Not to me, at least.

Larry Linson
Microsoft Access MVP

Larry,
I have a main table which lists account details including an account
number and a concatenated borrower name. I also have a borrower table
which lists the same account number and borrower details such as title,
initials, surname etc. The relationship between main table and
borrowers is 1 to many, i.e. upto 4 borrowers can have the same account
number (e.g husband and wife), but can only have one account on the
main table. I am trying to append data from a temporary table
(populated from importing a .csv file) to the borrower table. The
import file has all 4 borrowers details as well all all the other main
account information on one line. I am using a query linking the temp
table to the borrower table via account number to determine which
account numbers appear in the temp table and which do not. If the
account number is not already on the borrowers table, it appenda the
data to the table. The problem with this is that only 1st borrowers
details are being appended to the table, because after that, the
account number for the remaining borrowers now exists in both tables,
so the query cannot find any new accounts to append, and running an
update query would only update the one borrowers details thats already
stored, and not add the remaining 3 borrowers records to the borrowers
table.

Accounts(AccountNumber(PK), other info)
Borrowers(AccountNumber(FK), FirstName, LastName...)

or if you're into SQL

CREATE TABLE Accounts(
AccountNumber text(20) PRIMARY KEY,
CreateDate DATE,
....)

CREATE TABLE Borrowers(
BAccount text(20),
FirstName text(20),
LastName text(20),
PRIMARY KEY (BAccount, FirstName, LastName),
FOREIGN KEY (BAccount) REFERENCES Accounts(AccountNumber));

My guess is still to open a recordset based on Accounts, or whatever
that table is, and then loop through the 4 fields. Check for null. If
not null, append to Borrowers.

But Larry's definitely right. If you want a good answer, you really
need to give enough information for someone to accurately recreate the
key parts of the problem: table structures (with data types), maybe one
or two <maybe truncatedsample records if your example is not clear.
The key thing to keep in mind is that we have nothing but what you tell
us to go on. So it's up to you to provide the necessary details (and
_ONLY_ the necessary details). 2000 lines of code or whatever is WAY
too much.

Hope it helps a little

[Larry Linson]

Now, back to my question... (1) are the records you described as your input,
the ones with Title, a record of a particular "borrowing," or are they
intended to provide _new records_ for the Accounts and Borrowers Table. (2)
That is, can you rely that the record will include all the Borrowers
associated with the Account (my recollection is that you did say those
records, contrary to your original post, do include the Account number).
(3) Will they contain either new records (which you would Append) or may
they also contain maintenance updates (in which case, you might use an
Update Query to modify existing information).

Now, back to my previous question. (4) What is the relationship of Title in
those records? I'd have guessed, from that information being present, they
were a record of a particular "borrowing" and not new/updated Account and
Borrower information.

And other: (5) Could a given set of input contain more than one record for a
given Account? (6) If so, what determines which is the "authoritative
source" for the information... sequence in the input, a date field, or
??? -- that is, if there are two records for an Account, and the Borrower
information is different, which should be used?

As is obvious, the input data is un-normalized, which makes it more
difficult to handle, especially if it includes updates... not impossible,
but not as easy. Clearly, you have found that you can't use the absence of
an Account to determine whether you add a Borrower, if you establish the
account when you add the first Borrower.

You can use the absence of an Account to determine whether to establish a
new Account, but you'll have to handle Borrowers for that Account
separately.

We will not deal, now, with borrowers with the same First and Last name, for
example, a Father named John A. Smith and a Son named John Z. Smith... names
are notoriously unreliable keys in general, but may work in specific
situations.

Larry Linson
Microsoft Access MVP
"kaisersose1995" <ka************@hotmail.comwrote in message
news:11**********************@p79g2000cwp.googlegr oups.com...

>
Larry Linson wrote:

>"kaisersose1995" wrote

> >Looks like you could import to your
temporary table and then append the
data with the account number to the
child tables. It's hard to tell, since your
sample data has no account number, though.
If you don't have the account number in
the imported data, you could probably import
the data, use an update query to write in the
account number, and then append those where
the accountnumber is not null.

Thats what i'm doing. I'm using the account
number on the main table and the temp import
table to determine which accounts aren't there
already, and then appending the 1st set of
borrower details from my temp table to the
main table. the problem is, once i've imported the
1st set of borrowers details, the link between
the temp table and the main table is lost (due
to the account number now being populated off
the 1st borrower append query) so i now have
no way of importing the 2nd, 3rd and 4th set
of borrowers details to the main borrower table.
Is that any clearer?

Perhaps we might be able to help if you would clarify the specific
details
of your Tables, how the Borrowers relate to Accounts, and what part the
Title may play in this. It's entirely possible that it is a problem that
will have to be handled by some redesign... and without the details, your
description of the problem you face is not, in fact, clear (nor even
clearer). Not to me, at least.

Larry Linson
Microsoft Access MVP

Larry,
I have a main table which lists account details including an account
number and a concatenated borrower name. I also have a borrower table
which lists the same account number and borrower details such as title,
initials, surname etc. The relationship between main table and
borrowers is 1 to many, i.e. upto 4 borrowers can have the same account
number (e.g husband and wife), but can only have one account on the
main table. I am trying to append data from a temporary table
(populated from importing a .csv file) to the borrower table. The
import file has all 4 borrowers details as well all all the other main
account information on one line. I am using a query linking the temp
table to the borrower table via account number to determine which
account numbers appear in the temp table and which do not. If the
account number is not already on the borrowers table, it appenda the
data to the table. The problem with this is that only 1st borrowers
details are being appended to the table, because after that, the
account number for the remaining borrowers now exists in both tables,
so the query cannot find any new accounts to append, and running an
update query would only update the one borrowers details thats already
stored, and not add the remaining 3 borrowers records to the borrowers
table.

if you still require basic structure how should i represent this? in
words, or is there the facility for diagrams?

thanks

Rich

[kaisersose1995]

Sorry gents, this is the 1st time i've asked for more technical help,
i'll bear in mind the level of detail you require for future posts.

my 3 tables are
TempImport(ACCNO, TITLE-CUS1, INITIALS-CUS1, SURNAME-CUS1, TITLE-CUS2,
INITIALS-CUS2, SURNAME-CUS2,TITLE-CUS3, INITIALS-CUS3,
SURNAME-CUS3,TITLE-CUS4, INITIALS-CUS4, SURNAME-CUS4, other info)
Borrowers(Account Number(PK), Borrower ID(PK), Title, Initials,
Surname)
sample data:

temp import table:
00150T-00102, Mr, RA, Martin, Mrs, K, Martin, , , , , , ,
00650U-03003, Mr, C, Siddons, , , , , , , , , , , ,
00650F-02988, Mr, J, Daglish, Mrs, M, Hepplewhite, , , , , , ,

borrowers:
00150T-00102, 29/08/06 14:52:15, Mr, RA, Martin
00150T-00102, 29/08/06 14:52:16, Mrs, K, Martin
00650U-03003, 29/08/06 14:52:20, Mr, C, Siddons
00650F-02988, 29/08/06 14:52:22, Mr, J, Daglish
00650F-02988, 29/08/06 14:52:22, Mrs, M, Hepplewhite

Just to clarify again,
I'm trying to split out the borrowers details in the temp table, and
append each set of new borrowers details to a new record in borrowers
table.
currently i'm using a select query to check is the account number on
the temp table already exists in the borrowers table using the
following sql:

SELECT tbl_TempImportTable.ACCNO, tbl_TempImportTable.[TITLE-CUS1],
tbl_TempImportTable.[INITIALS-CUS1],
tbl_TempImportTable.[FORNAME-CUS1], tbl_TempImportTable.[SURNAME-CUS1],
tbl_TempImportTable.[DOB-CUS1], tbl_TempImportTable.[HOME-TEL-CUS1],
tbl_TempImportTable.[WORK-TEL-CUS1], [FORWARD-ADD-LINE1] & ", " &
[FORWARD-ADD-LINE2] & ", " & [FORWARD-ADD-LINE3] & ", " &
[FORWARD-ADD-LINE4] & ", " & [FORWARD-ADD-LINE5] & ", " &
[FORWARD-POSTCODE] AS [FORWARD ADDRESS]
FROM tbl_TempImportTable LEFT JOIN tblBorrowers ON
tbl_TempImportTable.ACCNO = tblBorrowers.[Account Number]
WHERE (((tblBorrowers.[Account Number]) Is Null));

then imbedding this whithin an append query shown below:

INSERT INTO tblBorrowers ( [Account Number], Title, Initials, Forename,
Surname, [Date of Birth], [Telephone Number 1], [Telephone Number 2],
[Forwarding Address] )
SELECT [qry_IMPORT_UpdatetblBorrowers1-B1].ACCNO,
[qry_IMPORT_UpdatetblBorrowers1-B1].[TITLE-CUS1],
[qry_IMPORT_UpdatetblBorrowers1-B1].[INITIALS-CUS1],
[qry_IMPORT_UpdatetblBorrowers1-B1].[FORNAME-CUS1],
[qry_IMPORT_UpdatetblBorrowers1-B1].[SURNAME-CUS1],
[qry_IMPORT_UpdatetblBorrowers1-B1].[DOB-CUS1],
[qry_IMPORT_UpdatetblBorrowers1-B1].[HOME-TEL-CUS1],
[qry_IMPORT_UpdatetblBorrowers1-B1].[WORK-TEL-CUS1],
[qry_IMPORT_UpdatetblBorrowers1-B1].[FORWARD ADDRESS]
FROM [qry_IMPORT_UpdatetblBorrowers1-B1];

I have 2 queries for each borrower, 4 select queries and 4 append
queries. All queries are identical, except the B# changes from 1
through 4 for each specific query.
problem is once i run the 1st query, the account number exists in both
tables, and therefore i can't append the 3 other borrower records.

is that any clearer now?

many thanks for your patience and help

Richard

[pietlinden]

No skin off my nose if you don't take my advice.

open a readonly recordset based on the non-normalized table.
open a dynamic recordset based on the destination table.

grab the pairs of data (PK and person)
write them into the destination table (dynamic recordset).

Look up AddNew in a VB module. Experiment. have fun. Try new things.
Learn something.

[hbinc]

Hi Richard,

If you have some experience with VBA, it is not that difficult.
Open your csv-file for line input, and you can read line for line
through the file.
The values per line are separated by a comma and you can assign them to
local variables loc_accno, loc_title_cus1, loc_initials_cus1, etc.
It is now easy to process the data of cus1 separately from cus2 or cus3
or ...

To add the data to a table can be done with opening a recordset based
on that table, and with Addnew the data can be inserted in the table.

Success, HBInc.
kaisersose1995 wrote:

Sorry gents, this is the 1st time i've asked for more technical help,
i'll bear in mind the level of detail you require for future posts.

my 3 tables are
TempImport(ACCNO, TITLE-CUS1, INITIALS-CUS1, SURNAME-CUS1, TITLE-CUS2,
INITIALS-CUS2, SURNAME-CUS2,TITLE-CUS3, INITIALS-CUS3,
SURNAME-CUS3,TITLE-CUS4, INITIALS-CUS4, SURNAME-CUS4, other info)
Borrowers(Account Number(PK), Borrower ID(PK), Title, Initials,
Surname)
sample data:

temp import table:
00150T-00102, Mr, RA, Martin, Mrs, K, Martin, , , , , , ,
00650U-03003, Mr, C, Siddons, , , , , , , , , , , ,
00650F-02988, Mr, J, Daglish, Mrs, M, Hepplewhite, , , , , , ,

borrowers:
00150T-00102, 29/08/06 14:52:15, Mr, RA, Martin
00150T-00102, 29/08/06 14:52:16, Mrs, K, Martin
00650U-03003, 29/08/06 14:52:20, Mr, C, Siddons
00650F-02988, 29/08/06 14:52:22, Mr, J, Daglish
00650F-02988, 29/08/06 14:52:22, Mrs, M, Hepplewhite

Just to clarify again,
I'm trying to split out the borrowers details in the temp table, and
append each set of new borrowers details to a new record in borrowers
table.
currently i'm using a select query to check is the account number on
the temp table already exists in the borrowers table using the
following sql:

SELECT tbl_TempImportTable.ACCNO, tbl_TempImportTable.[TITLE-CUS1],
tbl_TempImportTable.[INITIALS-CUS1],
tbl_TempImportTable.[FORNAME-CUS1], tbl_TempImportTable.[SURNAME-CUS1],
tbl_TempImportTable.[DOB-CUS1], tbl_TempImportTable.[HOME-TEL-CUS1],
tbl_TempImportTable.[WORK-TEL-CUS1], [FORWARD-ADD-LINE1] & ", " &
[FORWARD-ADD-LINE2] & ", " & [FORWARD-ADD-LINE3] & ", " &
[FORWARD-ADD-LINE4] & ", " & [FORWARD-ADD-LINE5] & ", " &
[FORWARD-POSTCODE] AS [FORWARD ADDRESS]
FROM tbl_TempImportTable LEFT JOIN tblBorrowers ON
tbl_TempImportTable.ACCNO = tblBorrowers.[Account Number]
WHERE (((tblBorrowers.[Account Number]) Is Null));

then imbedding this whithin an append query shown below:

INSERT INTO tblBorrowers ( [Account Number], Title, Initials, Forename,
Surname, [Date of Birth], [Telephone Number 1], [Telephone Number 2],
[Forwarding Address] )
SELECT [qry_IMPORT_UpdatetblBorrowers1-B1].ACCNO,
[qry_IMPORT_UpdatetblBorrowers1-B1].[TITLE-CUS1],
[qry_IMPORT_UpdatetblBorrowers1-B1].[INITIALS-CUS1],
[qry_IMPORT_UpdatetblBorrowers1-B1].[FORNAME-CUS1],
[qry_IMPORT_UpdatetblBorrowers1-B1].[SURNAME-CUS1],
[qry_IMPORT_UpdatetblBorrowers1-B1].[DOB-CUS1],
[qry_IMPORT_UpdatetblBorrowers1-B1].[HOME-TEL-CUS1],
[qry_IMPORT_UpdatetblBorrowers1-B1].[WORK-TEL-CUS1],
[qry_IMPORT_UpdatetblBorrowers1-B1].[FORWARD ADDRESS]
FROM [qry_IMPORT_UpdatetblBorrowers1-B1];

I have 2 queries for each borrower, 4 select queries and 4 append
queries. All queries are identical, except the B# changes from 1
through 4 for each specific query.
problem is once i run the 1st query, the account number exists in both
tables, and therefore i can't append the 3 other borrower records.

is that any clearer now?

many thanks for your patience and help

Richard