Add new field to existing table and populate the new field with existing data

[twildt]

I need the three sets to still be 3 sets returned to the user. The big difference is I don't want to use the seperate 3 tables, just the master instead, and also it needs to be multiple returns(if exist) to the three sets of account controls.

[twildt]

I will review post #4 and give it a whirl. What is the best method to return the query results to 3 seperate controls? Combo boxes or a datasheet return? Or something else?

[twildt]

I am attempting this query with error: "Syntax error in query. incomplete query clause."

SELECT DOL, [last name], [first name], 1 AS Source, account1 AS AccountNum FROM master
UNION SELECT DOL, [last name], [first name], 2, account2 FROM master
UNION SELECT DOL, [last name], [first name], 3, account3 FROM master;

What are the 1, 2, and 3 for? I am missing something...

[twildt]

I have my query working. Now I am trying to figure out how to do a lookup to return the results to the users form. What we do is in the form have them enter the account number in the box. When they press tab and that account number field loses focus it automatically does a DLookup of the account number for all three account fields. I was thinking of using an ELookup instead but can't quite figure that one out. Any suggestions?

PS: I am truly a beginner at this so i'm sorry for my ignorance on some of these matters...

[June7]

The 1, 2, 3, identify the source field (account1, account2, account3). Can use this as a filter parameter in the lookup if you want.

There is no ELookup intrinsic domain aggregate function. There is DLookup.

[twildt]

Ok, thank you for all your help. I have my form working now!

So now I need to work on the table. I added the new column [date] to the table. I need to match the accountnumber AND lastname fields of my new UNION query you helped me build and populate the [date] field of those matches to the new [date] field i created in the table.

So:

Table with new date field:

Table1
...ID
...box
...lastname
...firstname
...accountnumber
...user
...datetime
...date(this is the new field to be populated)

UNIONquery
...accountnumber
...date
...lastname
...firstname

I need to match the accountnumber AND lastname from the table and the UNION query and populate the [date] field in the table from the matches. How can I do this?

Thanks,
Tom

[June7]

Create query to join the two tables on the accountnumber and lastname and firstname fields. Click UPDATE on the ribbon, set update criteria under the new field to the old date field. Click run on ribbon.

Access Help has guidelines on using the query builder.

[twildt]

I did an update and it's updating the wrong way. It's taking the empty field from the new column I created and copying those empty [date] fields over to the source database.

So I reversed the UPDATE [table1] statement and its STILL updating the wrong way??

What am I doing wrong?

At first I had it like this:
UPDATE DestinationTable INNER JOIN [SourceTable]
ON (SourceTable.[Indv Lst Nm] = [DestinationTable].custnamel) AND (SourceTable.AcctNum = [DestinationTable].account)
SET SourceTable.date = [DestinationTable].[date]
WHERE ((([DestinationTable].[date])<>[SourceTable].[date]));

So I changed it to:
UPDATE SourceTable INNER JOIN [DestinationTable]
ON (SourceTable.[Indv Lst Nm] = [DestinationTable].custnamel) AND (SourceTable.AcctNum = [DestinationTable].account)
SET SourceTable.date = [DestinationTable].[date]
WHERE ((([DestinationTable].[date])<>[SourceTable].[date]));

What is my malfunction?

[June7]

The SET clause is backwards.

[twildt]

I have this:

UPDATE SourceTable INNER JOIN [DestinationTable]
ON (SourceTable.[Indv Lst Nm] = [DestinationTable].custnamel) AND (SourceTable.AcctNum = [DestinationTable].account)
SET [DestinationTable].[date] = SourceTable.date
WHERE ((([DestinationTable].[date])<>[SourceTable].[date]));

and when I hit the "View" button to see the datasheet view before I actualy run the query, all the [date] fields are empty. Do I still have something wrong? I tried removing the WHERE statement all together and it still didn't work.

[June7]

If the field to be updated is currently empty, I would expect datasheet view of UPDATE to be blank for that field because it hasn't been updated yet. It's an action sql.

[twildt]

When I hit Run, it says it's going to update 0 rows...

Any ideas?

[June7]

No. AFAIK, that should work. At this point would have to work with your database to analysis issue. I won't have time for that for a week.

[twildt]

I figured it out. I took out the WHERE statement again and it worked. I guess because the initial fields were blank it couldn't determine they were <> not alike.

You have single handedly helped me solve my biggest issue. I have to say, it was tough for me not knowing some of these features(I know, I need to take a friggin class on Access), but you really taught me a lot. I have been doing some serious reading and research on these topics your helping me with and I am impressed I was able to figure this out so quickly.

Slow for you I'm sure. Nonetheless I couldn't have done it without you.

Thanks again,
Tom

[June7]

Ooops! I should of recognized that issue with Null.

Perhaps this:

WHERE IsNull([DestinationTable].[date])

However, if all the records needed to be updated then no reason to filter.