populating a table from another 1

[matpj]

I have a bit of a dilemma with a task I have been set, and i'm not sure its even possible using SQL.

I have an MS Project table (MSP_TEXT_FIELDS) containing project attributes for many different projects. I can only describe it as 'vertical' in nature (i.e. each row contains one project attribute for a project, so there could be many rows for each project)

I have created an empty table that is a 'horizontal' version of the same table (more or less - i.e. one row for each project, with the attributes held in each column)

For the simple solution, I need to somehow run a script that reads all the information from the first table and puts it in the second.
This script would need running daily to make sure both tables are consistent.

This would be a big step towards solving the problem.
The second being that the attributes in the first table are only identified y a correspondin ID value in the same row.

each ID represents an attribute such as: Project Manager, Product etc.

my new table I have created has the actual attributes as column headings, so I have created a lookup table that has two columns - one containing the ID values and the other containing the corresponding attribute title.

so I need to tell the PC to go through each row of the first table, get the project number, ID and attribute value and then put the attribute value in the correct place in the second table (by using the lookup table that I created)

If anyone can help, or start suggesting ways to do this I would be very gratfeul.
I would be happy to email excel spreadsheets containing an exmple of the tables before and after!

thanks in advance,
Matt

 

[OhioSteve]

Unfortunately, I can't work on this project today. However, to do the first part of the project, you may just need a macro that deletes the records in the second table, and then appends new records. Tell me the selection parameters for putting stuff into the 2nd table.

 

[matpj]

I don't need to delete records from the second table, as it is empty at the moment - apart from the PROJ_ID field which I have populated with each of the PROJ_ID's from the MSP_TEXT_FIELDS table


for putting the stuff into the second table I need to use the following logic:

go through each row in the second table (each one being a new PROJ_ID)
INSERT INTO table2.version_number (MSP__TEXT_FIELDS.TEXT_VALUE WHERE table2.PROJ_ID=MSP_TEXT_FIELD.PROJ_ID AND TEXT_FIELD_ID = 1887007001)

and then the same for each field in table2.
each having a different TEXT_VALUE.


basically I will have a script that puts all of the project numbers into table2 each time the process is needed (deletes the old) and NULL values into the other fields.
Then the main script will run, which will enter the values in table2 where the PROJ_ID = the same PROJ_ID in MSP_TEXT_FIELDS.

No other criteria is needed really.
Just where the two PROJ_IDs match, put the text_value in table2.Version_number where the text_field_id = 1887407001
put the text_value in table2.product where the text_field_id = 1887454210
etc
It should do this for each row in table2.

 

[OhioSteve]

You want to...

1) Delete the current records in table2
2) Append records from table1 to table2

You seem to think that you need to divide step two into two queries. The first one will just append the proj_id field. The second will append the other values for each proj_id. Based upon what you have told me, you don't need to divide step two. It should all be done with one query.

Perhaps we should get started on the macro.
1) create a query that will delete the contents of the second table. Here is some sample code~
DELETE *
FROM Table1;
2) Create an append query that will transfer data from the first table to the second table. You have hinted that proj_id is a key field, and you say that we don't need to perform any domain aggregate functions. So a simple "insert" statement should do it. Perhaps you should start with just transfering proj_id and one other field. Here is some sample code~

INSERT INTO tablex ( a, b )
SELECT Table1.a, Table1.b
FROM Table1;

3) make a copy of the destination table, in case it has something you want to retrieve later.

4) Run the delete query. Check to make sure that it zapped the target.

5) Run the append query. Check to make sure that it populated that target.

6) Create a macro to perform this task. The actions should be~
set warnings=no
open query (the delete query)
open query (the append query)
set warnings=yes

On the open query action, don't forget the query name argument. The other aguments are unimportant.

7) Test the macro.

 

[matpj]

This all looks fine, apart from step 2.
Basically that is what I should do, except in order to know what columns to apend the data to, I need to say:
put THIS data into THAT column IF THIS text_field_id is equal to [corresponding text field id].

where THIS table = MSP_TEXT_FIELDS
and THAT = table2

there is only one column in MSP_TEXT_FIELDS that the required data is in, but just spread over different rows.
Each ROW will be put into a seperate COLUMN in the new table.

Its a shame you wont accept emails, as I have a spreadsheet that illustrates it perfectly
its quite difficult to explain by writing!!

 

[OhioSteve]

You are right; this is terribly frustrating because we can't communicate effectively. Send the spreadsheets to KAVU2002@YAHOO.COM. I will open them at a public terminal and print them. Just send a representative sample of the data. Label the tables with names, so I can tell which is which. I will get back to you on Monday.

 

[matpj]

you are a star!
i will send them in a minute.
its from my compay - so all mail is virus checked etc!

 

[OhioSteve]

Okay, that is good. You will probably hear from me again Monday.

 

[matpj]

ok, thanks Steve!

have a good weekend!

 

[OhioSteve]

I have solved the first part of your problem. I am going to give you an example that is very similar to your data.

MSP_TEXT_FIELDS is analogous to this table, nation1

recordID fieldID fieldValue
1 A China
1 B Beijing
1 C dictatorship
2 A United Kingdom
2 B London
2 C constitutional monarchy
3 A United States
3 B Washington
3 C democracy

MSP_SYNCHTABLE is analogous to this table, nation2

Name Capital formOfGovt recID
China Beijing dictatorship 1
UK London monarchy 2
USA Washington democracy 3

You also have a lookup table. You will NOT need that in the solution, so I won't invent an analogue for it.

Create a new macro called "nationMacro".
action/argument
setWarning,"no".
openQuery, "firstStep"
openQuery, "secondStep"
openQuery, "thirdStep"
openQuery, "fourthStep"
openQuery, "fifthStep"
setWarnings,"yes"

firstStep sql:
DELETE *
FROM nation2;

secondStep sql:
INSERT INTO nation2 ( recID )
SELECT nation1.recordID
FROM nation1
GROUP BY nation1.recordID
ORDER BY nation1.recordID;

thirdStep sql:
UPDATE nation2
INNER JOIN nation1 ON nation2.recID = nation1.recordID
SET nation2.Name = nation1!fieldValue
WHERE ((([nation1]![fieldID])='A'));

fourthStep sql:
UPDATE nation2
INNER JOIN nation1 ON nation2.recID = nation1.recordID
SET nation2.Capital = nation1!fieldValue
WHERE ((([nation1]![fieldID])='B'));

fifthStep sql:
UPDATE nation2
INNER JOIN nation1 ON nation2.recID = nation1.recordID SET nation2.formOfGovt = nation1!fieldValue
WHERE ((([nation1]![fieldID])='C'));

PS: There are MANY ways to solve this problem. I like to break big tasks down to small pieces. Perhaps that is why I choose this solution.

 

[matpj]

Steve,

you are a STAR!

that seems to work.
I will go ahead and create mini sql scripts for each of the columns in my new table.

That will allow my LEI tool to run, and I guess I should be able to make the reverse script by 'reversing' your SQL.

thank you so much for this.

 

[matpj]

Steve,

a slight problem.
I created all of the SQL scripts required, running each one as I created it.
they all worked.
When I then ran the macro to test them all together, at some point I got the message:

"Single-row update/delete affected more than one row of a linked table. Unique index contains duplicate values"

now if I try to run any of the queries, including the DELETE query, I get the same message.
any ideas why?

 

[matpj]

false alarm, my macro didn't contain the delete query!!

 

[OhioSteve]

Yeah, well I'm happy to help Remember that my process assumes that each column in the destination table always has a matching row in the source table. I also assume that you will not have more than one matching row. For example my process would fail if this was the source~

recordID fieldID fieldValue
1 A China
1 C dictatorship
2 A United Kingdom
2 B London
2 C monarchy
2 C democracy
3 A United States
3 B Washington
3 C democracy

It is accurate to describe the United Kingdom as both a democracy and a monarchy, and both records exist. Also, the source table does not contain the capital of China.

In time you will learn how to address this problem.

 

[matpj]

I do only have one of any field ID for each record ID (luckily!)

 

[matpj]

Steve,

what does the '!' in [MSP_TEXT_FIELDS]![TEXT_VALUE] actually do?

I have come up with some SQL to put them back into the first table (after changes have been made). can you have a look and let me know if this seems ok to you? (again, there would be a seperate script for each value):

UPDATE MSP2003_MSP_TEXT_FIELDS

INNER JOIN MSP2003_MSP_SYNCHTABLE ON [MSP2003_MSP_SYNCHTABLE].[Proj_ID]=[MSP2003_MSP_TEXT_FIELDS].[Proj_ID]

SET MSP2003_MSP_TEXT_FIELDS.TEXT_VALUE = [MSP2003_MSP_SYNCHTABLE]![VERSIONNO]

WHERE ([MSP2003_MSP_TEXT_FIELDS].[TEXT_REF_UID]=0 AND [MSP2003_MSP_TEXT_FIELDS].[TEXT_FIELD_ID] = ‘188744011’);

 

[OhioSteve]

Well, I didn't test your sql but I will offer some general comments.

You asked about periods vs exclamation points in Access sql. I don't know what the rule is that determines whether to use periods or exclamation points. It seems kinda arbitrary. Maybe a lurker can offer an explanation.

The second part of the project should also use a macro. Its actions will be something like this ~

set warnings= no
open delete query
open append query1
open update query1
open append query2
open update query2
open append queryx...
open update queryx...
set warnings= yes

The delete query will delete the appropriate records from the other table.

The append queries will each append the all of the index values and one of the other fields. For example, you might use one to transfer recNo and FormOfGovernment.

Each update query will use your lookup table. It will take the existing phrase and lookup the code for that phrase, then put that code into the destination table.

You should begin to rely on the query building interface a bit more. If you use it and you have a good Access book, you should be able to implement my suggestions.

 

[matpj]

thank you once again!
you've been a great help.