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

[twildt]

-Microsoft Access 2010


-Existing Access Database contains tables with 1-2 million records

I would like to add a field[dol] to an existing table[rei]. I need this new field[rei].[dol] to be populated with existing data from another table[main] based on the associated field[main].[account1] or [main].[account2] or [main].[account3].

How can I do this?

Table1[main]
...account1
...account2
...account3
...dol

Table2[rei]
...ID (PK)
...lname
...fname
...account
...date
...dol <-------This is the new field that I need populated from Table1[main].[dol]

Just so you know. In the [main] table. There is always data in [dol]. But there is NOT always data in the [account] fields. Sometimes there is multiple account numbers per [dol] but not always.

Customers will ALWAYS have at least 1 [account] number and [dol]. Some will have multiple [account] numbers and [dol]. Sometimes these [account] numbers are the same in multiple fields[account1] [account2] [account3].

I just need to do a lookup or something to find the [account#] and pull in its [dol] from the [main] table and populate it in [rei].[dol].

Hope this makes sense!

[rpeare]

does your table1[main] have a primary key field? In your table2 [rei] where you have 'account' is that actually the FK to the account table? if the account is NOT a FK to the 'main' table but could be any one of the three account fields from the 'main' table you're going to have some difficulty and I don't know that it would be possible to do an update query when PK/FK relationships are not being followed (you're not using a normalized data structure which makes this harder).

[twildt]

No it doesn't have a primary key. And no the account is not the foreign key.

Here is the issue. WE ARE RE-USING ACCOUNT NUMBERS!!! I know, I know. Shoot me.

So the issue is the account is being re-used so it may be a duplicate account number with 2 different [dol] and 2 different [last name] and [first name].

Let me show you what I have and maybe you can help me re-structure this a bit and make this work. I could just create new tables and populate if need be.

[master table]
...account1
...account2
...account3
...dol
...lastname
...firstname

Now this master table was also broken up into 3 other tables for doing lookups on.

[account1 table]
...account
...dol
...lastname firstname

[account2 table]
...account
...dol
...lastname firstname

[account3 table]
...account
...dol
...lastname firstname

Then there is this table that some ID10T created and is doing a lookup for the user on account number. This lookup brings back data and they have the USER MANUALLY ENTER back into the form to update the following table:

[rei table]
...ID
...lastname
...firstname
...account
...dol

There are other fields in the [rei] table but nothing from the master or split tables so I didn't mention those fields as they are irrelevant. So. The form the users have. It's a simple form. Basically it has 4 fields at the top. The first field is account number. The second two fields are last name and first name. The fourth field is my new field they are requesting "dol".

When the user enters an account number into the first field and focus is lost it does a lookup of the other three lookup tables for that account number. When it finds the account number it populates a few fields below the information it finds. Which is:

account number
dol
lastname firstname

Then they enter a little more information that's irrelevant and this writes to the [rei] table.

Hope this helps. I need to rebuild this thing anyways since I found some issues with their searches not bringing back the duplicates that exist. But. How would you rebuild? How would you deal with the duplicate account numbers?

[June7]

What was purpose of 3 account fields anyway?

Maybe you should start with a query that pulls all records where any 2 of the 3 account fields are equal and edit to eliminate the duplicate value.

Now what do you want to do where the 3 fields have different values? Pick one to associate with dol and name and discard others?

Then maybe a UNION query to normalize the data structure of master.

SELECT dol, lastname, firstname, 1 AS Source, account1 AS AccountNum, {other fields as desired} FROM master
UNION SELECT dol, lastname, firstname, 2, account2, {other fields as desired} FROM master
UNION SELECT dol, lastname, firstname, 3, account3, {other fields as desired} FROM master;

Use this query as source for subsequent queries to analyze records to identify duplication of accounts across dols and names. Then make decision about which dol/name gets to keep the account and which needs a new account?

[twildt]

It was a company decision to allow the "re-use" of account numbers. I'm screwed in this decision. I can't remove the duplicates as they are valid. They are just simply dormant. Old accounts still used in lookups if you will.

I have to figure out a way to make this work. Any ideas on how to recreate the databases so that this is possible? And then of course re-populate the data in the new tables.

[June7]

At least edit the records where account number is same in 2 or 3 fields.

Then it sounds like you have compound unique identifier - dol & AccountNum. Create normalized table from the UNION query. Set dol & AccountNum as compound index. And maybe set autonumber field as PK/FK. Because I think compound PK/FK is a pain.

What does dol stand for?

No matter what, the search on account number could return multiple records. User will have to make a choice which one to use. Maybe an additional match on name? But what if name is misspelled? Present the choices in a combobox and user picks.

[twildt]

Here is my form lookup. How can I make this lookup return multiple entries if there are duplicates? So if it finds 2 records with that account number, I need it to populate(fields dedicated to the search in the form) with each record it finds?

=IIf([account]<>"",Nz(DLookUp("[account1]","account1 table","[account1]='" & [account] & "'"),"NF"),"No Account Number Entered")

[June7]

A DLookup only returns value from the first record that meets criteria. That does not present choices to user.

I am lost on what you are trying to do. If you want to allow user to input appropriate dol, account, name then need to give the user a list of choices because these screwed up business rules allow assignment of same account number to multiple names as well as multiple account numbers to the same name. Are this rules still in force - is this still the practice?

Use the UNION normalized dataset as source of the choices in combobox list. Maybe use cascading comboboxes. Select account number in first combobox. Second combobox presents all records associated with account number.

[rpeare]

In addition to what advice June7 is giving you, it is possible to assign PK's and still be able to retain account numbers and re-use them. It's a bad practice but you're stuck with it, it doesn't mean you have keep the structure the way it is, as long as nothing changes for the users it's really up to you how you construct the database. The Structure you're using is really limiting you, for instance each of your account tables (1, 2 and 3) should be in one table, with a PK, the account indicator (1, 2, 3 .... x) so the customer could have an unlimited number of accounts. Not to mention you're duplicating data across tables which is really a waste of time and resources. If you designed this database I would suggest you look into a re-design, if you didn't, and this is a legacy system you can still re-design it just make a copy of the live system and figure out how to convert your data to a structure that works better. It sounds like you're willing to do it but we'd probably need to see a dummy copy of this database with an example of the data included (it doesn't have to be your live data, if it's private and confidential change the data enough so it's unrecognizable then compact/repair, zip it up and upload it to this site).

[twildt]

Yes they are screwed up...

Um, we still allow the re-use of account numbers yes. But, the account number being re-used is never assigned to the same name.

Example:
[account]=123456
[dol]=03/01/1789
[name]=George Washington

[account]=123456
[dol]=05/05/2014
[name]=Barack Obama

My thought at current moment is to get them going again. Currently I have 2 problems. 1. is we re-use account numbers and they want to add a new field and update accordingly to pre-existing accounts. 2. is I have a search field searching these tables for accounts and not returning the data that has multiple entries.

[June7]

Thought I addressed both of those issues.

[twildt]

Sorry my browser didn't update fast enough and I missed your last response. I will give the UNION query a shot and see what happens.

rpeare - yes I need to redesign the database. I am just trying to figure out the best way to redesign with re-using account numbers. It's legacy and I have linked tables of data. Imported that data and am building an access database for a small department. I can't change business rules just have to design around them.

[June7]

So dol is a date type. Probably not the best data type for a unique identifier, compound or not. So stuck with name and account as compound identifier.

Didn't you say account1, account2, account3 could all be the same in a record?

[twildt]

Yep. Those 3 tables are basically just 3 tables split out of the master to be used for a "search" on specific accounts based on what location they were being managed by at the time.

[account1 table]
...account
...dol
...lastname firstname

[account2 table]
...account
...dol
...lastname firstname

[account3 table]
...account
...dol
...lastname firstname

I don't have to have these tables split out anymore if I don't need them to be. Basically the master table holds them all.

[master table]
...account1 field/column
...account2 field/column
...account3 field/column
...dol
...firstname
...lastname
...lastname firstname

And to make things clear I am first trying to simply fix the current search so I can get the employees entering data again. Then I want to work on rebuilding this database after I get them going again. They are at a stand still until I fix this search. And the search simply needs to return multiple values not just the first one it comes across.

I will try your thoughts on a query but I think I would rather just simply use the master instead of using the split tables from now on. I don't know why he split the table when all the information already exists in the master...

If you could help me build a better (multiple return) search from this master table I would be greatful. Don't need great detail just a quick start on how you would attempt or what kind of query you would use to return the data from the master instead of the seperate 3 tables.

Currently we are searching on the following similar strings:

=IIf([account]<>"",Nz(DLookUp("[account1]","account1 table","[account1]='" & [account] & "'"),"NF"),"No Account Number Entered")

We are doing this in 3 different fields.

Account1 table
...[account]
...[dol]
...[name]

Account2 table
etc.

Account3 table
etc.

I want to just pull this information from the master(see fields above), and pull back a popup box(datasheet style) maybe or some kind of other like combo box with all the account numbers it finds. They are to be seperated for the user based on Account Location(account1, account2, account3, from master table fields/columns)

Current form with single account info return:

[June7]

Are you saying you want to eliminate the 3 sets of account controls from the form and have only 1?

The UNION query will normalize the master table. Use the UNION query as the dataset to search. Review post 4 again.