Question Multiple Excel Columns to one Field

[Drager]

Hi,

I'm fairly new to access, new enough to not really know what terms I should be using and thus not be able to search for the answer to this question.

I have an excel sheet, sent to me by someone else. It contains a column of unique information (site IDs), followed by three columns which each contain either a unique value or a blank, something like this:

sID|Code1|Code2|Code3
011|1234A|1234B| 
021|     |2345B|3456C
031|3456A|3456D|
041|     |     |7890E

The ID represents a particular business with which we interact, whilst the codes represent particular types of contract that we have with them.

This is then followed by a lot of columns containing information about each entry, name, address, post code, contract data etc.

I need to relate this to the employees at those businesses, whose data I have recorded in a separate spreadsheet, so that addresses and such are also related to people, not just the business unit.

This spreadsheet records each person by employee ID (a unique value for each entry) and Code, without taking account of whether it is a code of type 1, 2 or 3. Like this:

eID|CodeX|FirsN|SecoN
011|1234A|Teddy|Bears 
021|1234B|Harry|Horse     
031|3456A|Edwin|Eagle
041|3456A|David|Tyrex

I figure the best way to create a relationship between these tables once imported into access is via a third table containing a column with all the Codes in a single field and a second field with the sID related to each code.

sID|CodeX|
011|1234A|
011|1234B|  
031|3456A|
041|7890E|

The only problem is I have no idea how to do that.

My question is, how do I get all the Codes from the three different fields into a single field and relate it to the relevant sID for each?

 

[plog]

You are on the right track. What you described is exactly what you need to do. You didn't provide table names for the sample data you posted, so for reference sake, let's assume you've imported that first Excel file (the one with Code1, Code2, Code3) into Access and named the resulting table 'TableA'.

Now here's what you need to do to create and populate that table that normalizes that code data:

1. Create a query based on TableA and bring down sID and Code1.
2. Put an alias on the Code1 field, naming it 'Code'. It would look like this in the field area:

Code: Code1

3. Change the query type in the ribbon and make it a MAKE TABLE query, and naming the resulting table 'TableB'
4. Run the query and TableB will appear in the Table list.
5. Change the query type in the ribbon to an APPEND query, selecting TableB as the table to append into.
6. Make sure that beneath the sID and Code fields that the corresponding fields are populated that they need to go into for TableB.
7. Change the Code field to look at Code2, it will now look like this:

Code: Code2

8. Run that query and it adds all Code2 data to TableB.
9. Change Code field to look at Code3, it will now look like this:

Code: Code3

10. Run that query and it adds all Code3 data to TableB.
11. Open table B, filter it to show Null values in the Code field and delete them all.
12. Unfilter and you have your tabl.