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

 

[lespaul]

So you have a table:

MSP_TEXT_FIELDS

that contains the projectID, the attribute name, and the attribute value:

ProjectID Attribute Value
1 Manager Joe Blow
1 Product Something
1 Start Date 5/15/2004

and you want to have:

ProjectID Manager Product Start Date
1 Joe Blow Something 5/15/2004

Is that right? If so, you should look into the cross tab query. I'm pretty sure that's what it's designed to do.

HTH









Leslie

 

[matpj]

hi Leslie,
almost right.

my MSP_TEXT_FIELDS table has a few more columns (I was just trying to simplify it so i'll just leave them out) and the ID is a number:

ProjectID TEXT_FIELD_ID TEXT_VALUE
1 1887443218 Joe Blow
1 1887454210 Something
1 1887437523 5/15/2004
1 1887007001 v12.1
and I want to have

ProjectID Manager Product Start Date Version etc
1 Joe Blow Something 5/15/2004 v12.1



I have created another table that could be used to look up the values:

TEXT_FIELD_ID TEXT_CAT
1887443218 Project Manager
1887454210 Product
1887007001 VersionNo
1887437523 Start Date
etc etc


does this make sense?
then the script would have to be run daily to capture any changes made to the MSP_TEXT_FIELDS table

 

[OhioSteve]

You wrote "...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..."

One of the primary rules of relational database design is not to store the same piece of information in two places. That can lead to confusion and failure. You should choose one method for storing the data, and stick with it.

If every record contains the exact same traits, then perhaps it would be wise to put everything into one table. For example, lets say that you want to capture information about cars. You know that EVERY car has a price, a model name, a certain number of doors, and a certain size of engine. You could build a table with the fields modelName, price, doorNumber, and engineSize.

If every record does NOT have the same traits, then you will need a one-to-many structure involving two tables. For example, let's say you are recording purchases at a grocery store. Everyone does NOT buy cat food or steaks- you can't predict the number/type of items purchased. To implement this structure, you would start with a table called "sales" with fields like "saleID", "customerID", and "saleDate". Then you would have a table called saleDetails. It would have fields like "saleID", "itemID", and "itemQuantity". You would connect the tables using saleID. Then for each sale, you can view the sale record and its children in saleDetails.

After you decide which structure suits you, I'm sure that we can help you to implement it.

 

[lespaul]

OK, then you are going to want to write a query:

SELECT ProjectID, Text_Cat, Text_Value FROM MSP_TEXT_FIELDS INNER JOIN OtherTableName on MSP_TEXT_FIELDS.TEXT_FIELD_ID = OtherTableName.TEXT_FIELD_ID

This will return

1 Project Manager Joe Blow
1 Product Something

now you take this query and change it to a cross tab query. I believe there's a wizard that helps, and you can get the information in the format you want.

Steve is correct about the two tables, you don't need a second table to store this information. Anytime you need it you just run this query. It can also be used as the source for a report.

HTH

leslie

 

[matpj]

Sorry folks, I should have given more background to this.
I HAVE to have the data in a horizontal form in order to use Lotus Enterprise Integrator to synchronise the values with a Notes Project Database.
Once the MSP data is in the format I require, LEI is run to synchronise between the two systems.
THEN I will run a reverse of the script I need help with to put the data back into its original MSP_TEXT_FIELDS form (as MS Project uses it!)
a bit long-winded, I know, but its the only way I can find to allow LEI to synchronise the two systems for the required attributes!!

any questions??!

 

[OhioSteve]

I am starting to understand this situation more clearly. You need to represent this data in one structure most of the time, but on occasion you need to express it in a different structure.

The technical term for what you call the "horozontal" form of the data is "flat" or "denormalized".

The technical term for what you call the "vertical" form of the table is a "child table". Its one half of a normalized relationship. The other half is the parent table. One record in the parent corresponds to many records in the child. My sales/sales details example is analogous.

The acronyms in your last post confused me. I know that you said something like "typically I use the data in form x, but sometimes I need to express it in form y". I was a bit confused about x and y.

We could surely develop a macro that denormalizes normalized data, or vice versa. It might require several steps. Just identify x and y.

 

[matpj]

x and y are the two tables I have mentioned.
x being MSP_TEXT_FIELDS (a MS Project table used to store prohect information)
y is the denormalized (thanks!!) table.

For my synchronisation using LEI to work I have to have table x denormalized, and then after LEI has done its thing on table y, I need to put any changes to table y BACK into x!

Table x needs to stay in exactly the same state, but just 'copy' the contents in a flat form to the table y - because MS Project will be using table x.

If you require more detailed table info I can email spreadsheets etc.
I just don't know how to go about doing this.
I don't know a lot of SQL and I somehow have to write a script that uses the lookup table I mentinoed above.

 

[OhioSteve]

Don't panic. We can probably devise a solution. And if I can't help you, someone else will step in.

Lets break this problem up into smaller problems. For now, lets concentrate on the first part of the problem: x to y.

Returning to my example, let's say that we had two normalized tables for grocery sales: sales and sales details.

Sales looks like this:
SaleID
SaleDate
SaleLocation

Sales details looks like this:
SaleID
ItemID
Quantity

The application would also have a lookup table called items:
item name
item ID
unit price

Lets say that you wanted to denormalize this. You could create this ugly (but flat) table:

sales ID
sales date
sales location
item ID
item ID name
unit price
quantity

We are assuming that the store only sells cat food, bread, and beer.

a typical record in the ugly table would be:
sales ID 1
sales date 1/1/2000
sales location Smithville
item ID 1
item ID name cat food
unit price $1
ID1 quantity 2
item ID 2
item ID name bread
unit price $2
ID2 quantity 0
item ID 3
item ID name beer
unit price $5
ID 3 quantity 0

Notice that in the ugly table, we must include a column for each item. Even though the guy didn't buy beer, we still include that column.

I know how to do the transformation from a normalized structure to the ugly table. However, now I have some questions for you.

You mentioned this table:
ProjectID TEXT_FIELD_ID TEXT_VALUE
1 1887443218 Joe Blow
1 1887454210 Something
1 1887437523 5/15/2004
1 1887007001 v12.1
Question: Is that analogous to my sales details table?

And there is this table:
ProjectID Manager Product Start Date Version etc
1 Joe Blow Something 5/15/2004 v12.1
Question: Is that like my ugly table?

And finally there is this:
TEXT_FIELD_ID TEXT_CAT
1887443218 Project Manager
1887454210 Product
1887007001 VersionNo
1887437523 Start Date
Question: Is that like my item table?

Question: Do you have anything like my Sales table? The first table seems to be a child with no parent.

I am busy this weekend. I may not check these message boards frequently. Next week, I will return.

 

[matpj]

thanks Steve,
answers:
You mentioned this table:
ProjectID TEXT_FIELD_ID TEXT_VALUE
1 1887443218 Joe Blow
1 1887454210 Something
1 1887437523 5/15/2004
1 1887007001 v12.1
Question: Is that analogous to my sales details table?

And there is this table:
ProjectID Manager Product Start Date Version etc
1 Joe Blow Something 5/15/2004 v12.1
Question: Is that like my ugly table?
No. Your ugly table uses columns from the other tables.
My table has columns for each of the TEXT_FIELD_ID's, but rather than a column called '1887443218', I nned to look up the corresponding 'Name' in my lookup table, so the column would be called 'Project Manager' etc.

And finally there is this:
TEXT_FIELD_ID TEXT_CAT
1887443218 Project Manager
1887454210 Product
1887007001 VersionNo
1887437523 Start Date
Question: Is that like my item table?

similar.

do you have an email I could send you an Excel spreadsheet illustrating an actual example?? I think it would be easier to understand if I show you how the tables look and should look etc.

 

[OhioSteve]

Okay, it sounds like my example is fairly analogous to the problem. The primary difference is that my "ugly" table draws some field names directly from the sales details table. You use field names that you fetch from a lookup table.

The next question to think about is..."how can I determine all the fields that will appear in the denormalized table?" I strongly suspect that the answer is in this table:

TEXT_FIELD_ID TEXT_CAT
1887443218 Project Manager
1887454210 Product
1887007001 VersionNo
1887437523 Start Date

That table is alot like my items table. Now, in my hypothetical grocery store, they just sell cat food, bread, and beer. So there would only be three entities to denormalize. I suspect that your table has alot more than three records. My first question would be "how many records does it have?" My second question is "does it change frequently?"

You asked about emailing files. I'm not eager to open attachments from unfamiliar sources. However, I will continue to exchange messages with you. I may even logon once or twice over the holidays.

 

[OhioSteve]

Here are three more questions to consider...

does this transformation process need an interface for users, or are you personally going to do it??? It might be easier to develop a workaround if we could actually work using the database window, rather than making a form for the users.

how many records are we transforming??? If its more than 200,000 we might run short of resources.

how frequently will we be doing the transformation?? If it is every 10 minutes, it must be very quick. If its once a month, we can tolerate a longer running time.

 

[matpj]

Steve, I must thank you for the time you have spared.
Its very much appreciated.

in answer to the first of your two posts, MS Project has been adapted by our company to have a set number of attributes (text fields). This is unlikely to change now.
I am starting this 'project' with probably 10 attributes.

the rest of the answers.
The transformation process will be a background task that I will schedule on a daily basis (preferably during the night) The users won't see anything different, as MS Project will still just be using MSP_TEXT_FIELDS (table x) as it always does.

When the transformation is cmoplete, LEI will be run to synchronise the Notes database and update the relavant fields) THEN the reverse script would have to be run to put it back into table x.
I'm hoping to have these all scheduled to trigger one another.




The number of records currently is 203,054 - although there is a field that a criteria will be run on (I didn't include this as I wanted to keep it as simple as possible - it will just be a case of adding "where MSP_TEXT_CATEGORY = 0") this will reduce the number of records, but I still think it will be pretty large!
Obvioulsy this would translate into only about 1000 rows int he new table, as there are about 1000 projects. (one project can take up 50 rows in table x!)

 

[OhioSteve]

You are saying that MSP_TEXT_FIELDS only has about 10 distinct TEXT_FIELD_IDs for any given ProjectID? That sounds too good to be true. I have created some new SQLs to test that claim.

First,creat this query, called query6. This SQL should tell you which project ID has the most records, assuming that project ID cannot be null:

SELECT Table3.Project_ID,
Count(Table3.Project_ID) AS [count]
FROM Table3
GROUP BY Table3.Project_ID
ORDER BY Count(Table3.Project_ID) DESC;

Then let's do a two-part task that will show which project ID has the most DISTINCT rectords.

first create this query, called query7

SELECT
Query6.Project_ID,
Query6.count
FROM Query6
GROUP BY Query6.Project_ID, Query6.count;

then, create this query, called query8

SELECT
Query7.Project_ID,
Count(Query7.Text_Field_ID) AS [count]
FROM Query7
GROUP BY Query7.Project_ID;

This sql should tell us which project ID has the most DISTINCT records.

If things are as rosy as you say, then the top value in query8.count will be a little number like ten or twenty. It will also be interesting to see query6.count.

 

[matpj]

slight problem with this.
you are counting the number of text_field_id's from query 7, but that field is not used in Query7, and not used in Query6.
i'm just trying to work on it to see fi I can get you what you are looking for.
watch this space!!

 

[matpj]

slight problem with this.
you are counting the number of text_field_id's from query 7, but that field is not used in Query7, and not used in Query6.

I have created the following SQL using MSQuery (it didn't like the syntax in MS Access):

SELECT MSP_TEXT_FIELDS.PROJ_ID, Count(Distinct MSP_TEXT_FIELDS.TEXT_FIELD_ID)
FROM MSP2003.MSP_TEXT_FIELDS MSP_TEXT_FIELDS
WHERE (MSP_TEXT_FIELDS.TEXT_REF_UID='0')
GROUP BY MSP_TEXT_FIELDS.PROJ_ID
ORDER BY Count(Distinct MSP_TEXT_FIELDS.TEXT_FIELD_ID) DESC

The top value is 18.
the TEXT_REF_UID = '0' criteria will be used in the actual solution, but as mentioned before I have kept this out of the thread to try and simplify.
Out of the 18 distinct TEXT_FIELD_ID values, only 4 or 5 need to be translated into the new table, via the look-up table.

 

[OhioSteve]

You are right about my queries...I did make some logical mistakes. However, you understood the essence of my post. I wanted to learn the number of distinct text_field_id per proj_id. Apparently, there are 18 in the most heterogeneous proj_id. Moreover, you say that you only need about five. Consequently, it should be pretty easy to do the first task. Are all of the fields (other than proj_id) number fields? If so, we want to create this table:

select each distinct proj_id
sum field1 for that proj_id
sum field2 ""
sum field3 ""
sum field4 ""
sum field5 ""

Perhaps you could tell me the five field names.

You mentioned that you are using MSquery. That may not be 100% compatible with the Access sql that I write.

 

[matpj]

hi Steve,
The fields I need to transfer are:
Project Manager
Version Number
Product
Project Code
Client Manager

none of them are numeric as the TEXT_VALUES field in the first table is VARCHAR.
The corresponding TEXT_FIELD_ID values for these are, respectively:
188744007
188744011
188744008
188744006
188744016

The columns in the 'target' table need to be called
PROJ_ID, Project Manager, Version Number, Product,
Project Code, Client Manager

I have a lookup table I created with two columns:
TEXT_FIELD_ID, TEXT_CAT
188744007 Project Manager
188744011 Version Number
188744008 Product
188744006 Project Code
188744016 Client Manager

I only used MSQuery, as I tried the distinct expression in MS Access and it was throwing up errors!

 

[OhioSteve]

Well, something about your last post is confusing to me. You posted this thread in the Access forum. However, later in the thread you mentioned using "MSQuery". Now, you say that one of the field's data types is "varchar". That data type exsts in SQL Server, but not in Access. Perhaps this is an SQL Server DB, not an Access DB. If it is an SQL Server DB, I can still help you. I also work with SQL Server. But it would be helpful to know which application you are using.

Now, about these five fields. You say that they contain text fields, so we can't sum them. I had thought that the new table would be a summary of the old table; I had thought that it would display totals for each proj_id. Its tough for me to understand how we will add up a field like "project manager". I mean, you could squish together two project managers, but that would get ugly.

Are we just transfering individual records to the new table, with no summarizing?

 

[matpj]

Sorry steve, I just said VARCHAR cos i'm used to that from working with other database in the past.
I just meant that the field is not numeric, it can have text and numbers! I should work on getting my terminolog.

The actual tables are in an Oracle Database, but to test this 'problem' I am linking the tables in an Access database - as I find it easier to do it this way.

Aplogies for not stating this earlier.

We are just transferring the data to the new form.
no summarizing is required, I just need to transfer the data I require into the new format so that a the Lotus Enterprise Integrator can update the values from a Notes database before putting them back into the original databse for MS Project to use!!!