By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,681 Members | 1,854 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,681 IT Pros & Developers. It's quick & easy.

Query to loop through data and append to table

P: n/a

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:
Expand|Select|Wrap|Line Numbers
  1.         Conference_A  Conference_B  Conference_C
  2. Jane Doe         y                           y
  3. John Doe                       y             y
  4. 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
Nov 16 '10 #1
Share this Question
Share on Google+
1 Reply

Expert Mod 15k+
P: 31,276
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).
Expand|Select|Wrap|Line Numbers
  1. =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!
Nov 17 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.