Append Data

[Graham T]

I would like to append a UserID to a table that contains 3 fields

tblUsers:
UserID
UserFirstName
UserLastName

From a table that contains only 2 fields

tblTempUsers:
UserID
UserName

So, this should append the UserID WHERE [tblUsers].[UserFirstName]&" "&[tblUsers].[UserFirstName] = [tblTempUsers].[UserName]

I have run out of ideas for the correct syntax (after an hour of trying!) and would appreiciate any input.

TIA

Graham

 

[ColinEssex]

Hi

The way I did this was as follows

1) Create a masterfile UserTable of UserName, Forename, Surname, Dept, LoginTime, LogOutTime (or whatever you want)

2) Pick up the UserName from the network when they log into the database using a function.

3) Create a LogInTable of the fields you want.

4) Use an append query to append the required fields from the UserTable to the LogIn table using the UserName function to pick up the right person.

5) Use an Update query to register LogOut time when they log out.

Hope this helps

Col

 

[Graham T]

Thanks Colin, however unfortunately not exactly what's required.

The tblTempUsers table already contains the UserID number that I need to append to the tblUsers table.

It's just the correct syntax to do this that I am struggling with.

Graham

 

[ColinEssex]

Yes my fault - I didn't read your problem carefully enough

Anyway, you put

WHERE [tblUsers].[UserFirstName]&" "&[tblUsers].[UserFirstName] = [tblTempUsers].[UserName]

shouldn't it be

WHERE [tblUsers].[UserFirstName]&" "&[tblUsers].[UserLastName] = [tblTempUsers].[UserName]

but it will only work if the combination matches the [UserName]

Col

 

[mrxdotcom]

may be this will help

may be this may solve your problem..

UPDATE tblTempUsers INNER JOIN concate ON tblTempUsers.UserName = concate.[Concatenate 1st & 2nd Name] SET concate.UserID = [tblTempUsers].[UserID];


SELECT tblUsers.ID, tblUsers.UserFirstName, tblUsers.UserLastName, tblUsers.UserID, [tblUsers]![UserFirstName] & " " & [tblUsers]![UserLastName] AS [Concatenate 1st & 2nd Name]
FROM tblUsers;


or you can assign the value to an expr1:

then use the Expr1 in the where clause...

good luck...

by the way, i have the table and it works.. if you want me to forward it to you, reply b4 3pm as i have to go home

 

[Graham T]

mrxdotcom

If you could send a copy of the database mentioned, it would be greatly apprieciated.

You will find my e-mail address in my profile.

TIA

Graham