Re: splitting excel spreadsheet fields into multiple tables
- From: John Nurick <j.mapSoN.nurick@xxxxxxxxxxxxxx>
- Date: Thu, 10 Aug 2006 06:28:41 +0100
Hi Ryan,
You need to use a query rather than working through the existing form.
The idea is to create a query that joins the imported table of expenses
(let's call it Imp) to the existing Employees table on the employee
name, returning EmployeeID and the expense fields from Imp. Then use
this as the source of an Append query that appends the new records to
the existing Expenses table.
Things can get complicated if (as is often the case) the names in the
Excel sheet don't exactly match the names in the Employees table.
You mention "EmployeeName" in the former and "First_Name", "Last_Name"
in the latter. If EmployeeName is of the form "Smith, John" then you
could join them with an query like this:
SELECT Employees.EmployeeID, Imp.AAA, Imp.BBB, ...
FROM Employees INNER JOIN Imp
ON Employees.LastName & ", "
& Employees.FirstName = Imp.EmployeeName
;
where AAA, BBB, ... are the fieldnames in the Imp table.
But watch out for problems with mis-typed names, names shared by two or
more people, and so on.
On Wed, 9 Aug 2006 13:34:09 -0700, InternRyan
<InternRyan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
I have a table in my db for employees that has First_Name, Last_Name and
EmployeeID fields. I have an expenses table that has the details for the
expenses but only the EmployeeID.
I imported an excel spreadsheet which is now a table with EmployeeName, and
the details I need for the expenses table.. I have a form which matches the
imported spreadsheet. It has an not-in-list event add feature so I thought I
would be able to cut and past from one to the other. This actually works
perfectly line for line but not for multiple lines.. is there a way to
automate it to do it line by line or does anyone know why this isn't
working.....
Thank you,
Ryan
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
- Prev by Date: Re: Urgent! NA values in table with Number Data Type
- Next by Date: Re: Opening Excel via code
- Previous by thread: Re: Urgent! NA values in table with Number Data Type
- Next by thread: Re: docmd.OutputTo errors
- Index(es):
Relevant Pages
|
