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
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
3  2116 
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
You want to copy all the records from table 3 into table 2, overwriting any current records?
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Troll |
last post by:
Hi,
I need to write a script which reads some data and reports the findings.
Just to give you an idea the structure is similar to the following.
Data input example:
HEADING 1
**********...
|
by: robert |
last post by:
been ruminating on the question (mostly in a 390/v7 context) of
whether, and if so when, a row update becomes an insert/delete.
i assume that there is a threshold on the number of columns of
the...
|
by: Bill Smith |
last post by:
The initial row is inserted with the colPartNum column containing a valid
LIKE pattern, such as (without the single quotes) 'AB%DE'.
I want to update the column value with the results of a query...
|
by: Maxi |
last post by:
There is a lotto system which picks 21 numbers every day out of 80
numbers.
I have a table (name:Lotto) with 22 fields (name:Date,P1,P2....P21)
Here is the structure and sample data:
...
|
by: Timothy Perrigo |
last post by:
This bug? feature? caused a bit of havoc for us yesterday...A
reproducible example follows. Essentially, if you have a table with a
primary key called "id", and you create a temp table (via a...
|
by: Seth |
last post by:
I have one table of the form:
Delivery Product
101 A
101 B
101 C
102 A
102 E
103 C
104 A
|
by: Ian Davies |
last post by:
Hello
Needing help with a suitable solution.
I have extracted records into a table under three columns 'category',
'comment' and share (the category column also holds the index no of the
record...
|
by: ollyb303 |
last post by:
Hi,
I am trying to create a dynamic crosstab report which will display number of calls handled (I work for a call centre) per day grouped by supervisor.
I have one crosstab query (Query1) which...
|
by: Tempalli |
last post by:
I am entering records in Ms Access with a series as displayed where as i want to delete few records in this series.
Is it possible to assign the missing series to next entry.
for example i want...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |
