updating from flat file

[romanzero]

Hi,

I have a db where an employee's record can have 1+ accounts. (tblEmployee linked to tblAccounts).

One of the auxillary data sources contains info on each employee, but is a flat file. That is, each employee record has only one record and 1 - 10 accounts (fields labeld: Acct1, Acct2, etc.)

How can I update tblAccounts from the 1 - 10 fields from the record from the auxillary flat file?

Most employees only have one account anyway, so fields 2 - 10 almost always null!

Thanks!

 

[dhookom]

How about giving us an example of a few source records and what you want to do with them. I picture using a union query to normalize your flat table.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[romanzero]

Example of the "flat file" data that I need to use ot update my db: (picture an Excel spreadsheet)

Employee A: [Account1], [Account2]
Employee B: [Account1]
Employee C: [Account1], [Account2],[Account3], [Account4]
Employee D: [Account1]


My db:

Employee A: - linked to tblAccounts (two records)
Employee B: - linked to tblAccounts (one record)
Employee C: - linked to tblAccounts (three records)
Employee D: - linked to tblAccounts (one record)

I need to convert (or update) 1 - 10 fields from the source data into 1 - 10 records in a table in my db, while retaining the unique criteria that links them to the employee.

Make any sense?

 

[dhookom]

You will not be able to update existing records but you could create a union query that would normalize:
SELECT Employee, Account1 as AcctVal, "1" as Account
FROM tblFlatFile
UNION ALL
SELECT Employee, Account2, "2"
FROM tblFlatFile
WHERE Account2 is not Null
UNION ALL
SELECT Employee, Account3, "3"
FROM tblFlatFile
WHERE Account3 is not Null
etc...
;

You can then append records from the union query to an existing table.


Duane
MS Access MVP
Find out how to get great answers faq219-2884.