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

Update data which matches row data and column heading

P: 31
In Ms Access two tables as mentioned below. In table1 the city names are mentioned in Rows and in Table2 the same city names are mentioned in column headings and the unique Ref Code is same.

Table1
Ref Code City City_Code
1 Hyd 124
1 Sec 234
1 Viz 567

I want to Update the Table2 data which matches Ref Code and Column Heading as mentioned in table3.

Table2
Ref Code Hyd Sec Viz
1


Table3
Ref Code Hyd Sec Viz
1 124 234 567

Kindly define any code for the above.

Regards,
Tempalli
Jul 10 '09 #1

✓ answered by ajalwaysus

As I understand what you want, you want to update table2 with info from table1 so that it looks like table3. If my assumption is correct, these two queries should do it for you quickly.

Query1:
TRANSFORM Sum(Table1.City_Code) AS SumOfCity_Code
SELECT Table1.RefCode
FROM Table1
GROUP BY Table1.RefCode
PIVOT Table1.City;

Query2:
INSERT INTO Table2 ( RefCode, Hyd, Sec, Viz )
SELECT Query1.RefCode, Query1.Hyd, Query1.Sec, Query1.Viz
FROM Query1;

Create both these queries and then run Query2.

If this is what you were looking for, there are better ways of doing it, but I didn't want to spend too much time on something if it isn't what you need.

AJ

Share this Question
Share on Google+
3 Replies


Frinavale
Expert Mod 5K+
P: 9,731
Hi Tempalli,

Welcome to Bytes! I've moved your question to the Access Forum. You will be more likely to get help here than the Jobs forum :)

-Frinny
Jul 14 '09 #2

Expert 100+
P: 1,287
You want to copy all the records from table 3 into table 2, overwriting any current records?
Jul 14 '09 #3

Expert 100+
P: 266
As I understand what you want, you want to update table2 with info from table1 so that it looks like table3. If my assumption is correct, these two queries should do it for you quickly.

Query1:
TRANSFORM Sum(Table1.City_Code) AS SumOfCity_Code
SELECT Table1.RefCode
FROM Table1
GROUP BY Table1.RefCode
PIVOT Table1.City;

Query2:
INSERT INTO Table2 ( RefCode, Hyd, Sec, Viz )
SELECT Query1.RefCode, Query1.Hyd, Query1.Sec, Query1.Viz
FROM Query1;

Create both these queries and then run Query2.

If this is what you were looking for, there are better ways of doing it, but I didn't want to spend too much time on something if it isn't what you need.

AJ
Jul 17 '09 #4

Post your reply

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