access table merging

[bigmac]

I have a small problem. I have a table (many rows and columns) with names. I also have a bunch of ID numbers. Some names have multiple IDs associated with them. I'd like to replace all the names with IDs. If a name has mutiple IDs, all IDs have to be inserted (new cells have to be created to fit the extra ones).

Is this possible in Access? If it was just one ID per name (and vice versa), this would be a piece of cake. If I could use a real programming language, it would be harder, but possible. But I have to use just Office to accomplish this. I only have limited Access knowledge. Is there maybe some sort of an obscure function that can do this?

Any suggestions?

Thanks in advance.

 

[bigmac]

anyone?

 

[PTNL]

depending on how you view the workload compared to your current technical abilities, then you can either:
1 - write a sql script (my personal suggestion)
2 - use a quck 'n dirty access form
3 - program a simple form/webpage to create and populate a hash table of values, collect the values into an array of some sort, and update each row

for more detailed suggestions, please provide a short sample of each table you will be comparing against and/or adjusting. that would help anyone posting suggestions to provide a closer sample to what you're looking for.

 

[samerikanyets]

I'm not sure I understand your situation, but if you have two tables, one with names and IDs and one with all your data by name, you can join the two tables by name and Access will create a row for each ID. You'd just have to make sure the names are the same in both tables.

 

[bigmac]

I'm not sure I understand your situation, but if you have two tables, one with names and IDs and one with all your data by name, you can join the two tables by name and Access will create a row for each ID. You'd just have to make sure the names are the same in both tables.

I apologize for not being very clear.

Basically, a join is exactly what I want to do. A join is easy if you compare one column in one table to one column in a second table. My problem is comparing one column to many columns simultaneously. Table one has a column of names and a column of corresponding IDs. Pretty standard. Table two has many rows, each with names. Table two is not supposed to be in a relational database, since each column has to be treated independent of others. In other words, I have to break up the table two into individual columns, join each with table one, and then recombine them all.

Ideally, I would just create a parser (in perl, for example) that would restructure table two to be in a format that is fit for a relational database (with only one column of names). Unfortunately, I don't think I can do this without installing additional software.

 

[Hartlove]

If your 2 tables look like this:

   Table 1
    ID	 Name
  1	  bob
  2	  joe
  3	  bill
  4	  joe

    Table 2
    Name	 ...
  bob
  bill
  joe

Join your two tables on the name field.

Assuming you're just using Access query builder, change the query type to "make table".

In the fields, select the ID column from Table 1, and all the other columns from Table 2.

This will create a new table with just the ID's, where the names used to be in Table 2.

The above example would look like this:

    New Table
    Name	 ...
 1
 3
 2
 4

 

[bigmac]

If your 2 tables look like this:

   Table 1
    ID	 Name
  1	  bob
  2	  joe
  3	  bill
  4	  joe

    Table 2
    Name	 ...
  bob
  bill
  joe

I wish that was the case. Table 2 looks like

    Table 2
    Group1    Group2      Group3   
  bob           ann            john
  bill          phil          steve
  joe

That's why I mention it's not fit for a relational database. That's the root of the problem. What I really need to do is split all columns, process one at a time, and then recombine them, but I don't know how to do that automatically.

 

[Y2K SE]

Does the fact that Access allows you to add the same table to the query designer multiple times help any?

 

[bigmac]

Does the fact that Access allows you to add the same table to the query designer multiple times help any?

But to do it manually hundreds of times would take forever. Especially if you have to do it every day.

 

[Hartlove]

It should still be do-able. It won't be pretty or efficient, but it should be able to work. How many columns are in the second table that need to be joined into the first table?

 

[Y2K SE]

But to do it manually hundreds of times would take forever. Especially if you have to do it every day.

There might be an opportunity to do some automation via VBA.