Query to loop through data and append to table

[Katie Howard]

Hi,

I’m just starting a database that will contain historical data of all the conferences that our employees have attended over the years. I have 3 tables (Employees, Conferences, and the 3rd is Attendance which has the EmployeeID and ConferenceID). Everything works great with my test data, but now I need to import from our existing spreadsheet of 700 Employees and 250 Conferences. I appended the employees to the Employee table and the conferences to the Conference table. Now I’m trying to figure out a query that I can run to append the EmployeeID and ConferenceID to the attendance table, based off of my existing spreadsheet.

The spreadsheet is set up like:

        Conference_A  Conference_B  Conference_C
Jane Doe         y                           y
John Doe                       y             y
Mary Doe         y

Can anyone recommend a way to set up my query so it will go through each record and attend the ID’s to my Attendance table? I found that if I replace the “y” in each column with the full name of the Conference, then I can do an update query for that one event. But going through and manually changing first the “y” to the Conference, and then changing the query event, will take many, many hours. Is there any easier way that I’m missing? Maybe some sort of loop query (though I don’t know anything about those)?

Thanks for your help!

- Katie

[NeoPa]

Working from the example data, in Excel, I would create a formula in a set of cells just to the right of row #2 (Assuming the current example data has TopLeft of A1 and BottomRight of D4, that would be at E2).

=IF(B2="Y",$A2 & CHAR(9) & B$1,"Empty")

This formula could then be duplicated by copying this cell and pasting across a range that covers all the rows (from #2 to the end) and a matching number of columns (in this case E through G).

This done, copy the whole new range to the clipboard (Ctrl-C) and use Edit / Paste Special / Values into a completely new workbook. This will produce data with all the "Y"s replaced with a reference which includes the names of both the employee as well as the conference. It also leaves the empty cells as "Empty" though, so from here select the new data range again if necessary (A1 through C3 of the new workbook) and do a Replace (Ctrl-H) of the word "Empty" with a blank value in the Replace with: box. This will remove these entries.

The next job is to move (Cut (Ctrl-X) and Paste (Ctrl-V)) the data from columns B through the last one down to the bottom of column A. Essentially consolidating all the data into a single column. A sort would be helpful next. This will ensure all blank cells are left at the end and need not be included in the saved workbook (Check when done that Ctrl-End finds the last cell with data in after saving the new workbook. If it doesn't, then select all rows below those that contain data and choose Delete from the Right-click menu).

You now have a workbook that can be imported into your table to produce the requisite data using an append query.

Let us know how you get on with this and welcome to Bytes!