Topic: Linked table in database

[Keith™]

Ok, I have a two seperate tables in an access database, what I don't want to do is duplicate the data. Now that we have that understood, I need to somehow have in one of the tables all of my records with the various fields.

In a seperate table, I need to have the ability for the user to enter data that corresponds to the records in the first table, but isn't joined so that I can update the second table easily.

*EDIT

Is there a way that I can read a specific column from table1 into a dataset in .NET then add another column for a user supplied rank, display that data in a datagridview, then update table2 with the result?

[Draftek]

piece of cake.

Since datasets are disconnected and reside in memory you can do just about anything with them in code using ado and sql statements.

I usually create a separate project that does nothing but handle the sql dataset stuff called data adapter controllers (DAC).

You could use the wizards to create typed datasets but I don't like using wizards and they don't work too well across web services if you ever decide to access remotely thru an http connection.

[Keith™]

man, I just don't know where to start ... so how about the beginning ...

I have 100+ records in table "surveys", which has 100+ fields ... I just need "Project_Name" ... but I need every record in column "Project_Name" to be in my second table, which will be created dynamically, so lets just call it "ranking" for now.

"ranking" will have a primary key ID and 2 columns "Project_Name" and "Rank"

I don't really need "ranking" to be associated with "surveys" if I update "ranking" from the "surveys" table, so that makes it easier .. I think ..

So, after I create the table

Code: [Select]

sql = "CREATE TABLE " & TableName & " (ID INT Identity(1,1), PRIMARY KEY (ID))"

I add the fields as needed, making sure to catch any errors if the field already exists ...

Code: [Select]

sql = "ALTER TABLE ranking ADD Rank NUMBER"

Then I want to copy the entire "Project_Name" column to the new table ...

Code: [Select]

sql = "INSERT INTO ranking (Project_Name) SELECT DISTINCT Project_Name FROM surveys"

I don't wan't duplicate entries, so I have to use the DISTINCT .. at least I think I have to ...

Now, if there were any deleted records in "surveys", I want those same records to disappear in "ranking" .. should I simply delete the "Project_Name" column from "ranking" and recreate it using the code to copy the column into the new table ... and if I do that, I suppose I still have to delete any records where the values don't match ...

I suppose the real kicker here is that if I delete the column first, then I don't have the ability to ensure the ranks are assigned to the correct project.

[Draftek]

woa!
step back for a minute.

Can you, in access, create a query of the table you would like to use?

[Keith™]

I suppose I can ... but I am accessing the database from a .NET application

[Draftek]

Create the query in access, then take a look at the sql for it.

That's probably the easiest way to make the table you want to use in .net

[Keith™]

Ok .. I'll have a look at it and see how it works out

[Ken Alexander]

man, I just don't know where to start ... so how about the beginning ...

I would start with a simple Data Access class that contains methods to work with databases.  For simple data access you can let .NET do most of the work while keeping your SQL statements very simple.  If you are dealing with many thousands of records, this may not be the most efficient, but it will get you working with the database quickly.

[Keith™]

Ken, that comment was more in the vein of .. how do I explain my needs, so far I feel as though I haven't done that adequately.

I have all of the infrastructure in place to manage the database i.e. I have functions to create tables, fields, perform queries etc., What is needed now is something that would be simple if this were a regular SQL server issue, but instead I am working with the limited aspects of an access database.

Here is the problem as I see it ...
I have 2 classes of users. These users must be able to assign a numeric rank to every record in the database, whether there is 1 or 10k records. I would simply add a field to the "surveys" table, but considering the number of users I expect, the 255 limit on the number of fields in Access would not be acceptable. To resolve this, I need to store the users ID vertically in one table and the user assigned rank in another table, again, vertically, but those ranks must correspond to the individual records in the surveys table.

The main problem I have experienced is that I can't seem to perform a join with access and then also perform an update on the changed data. Maybe I am doing it wrong .. which is a possibility. Maybe I need a roadmap to find my way.

As I see it the pseudo code would be:

read the "surveys" table getting the "project_name" column
read the "rank" table getting the "values" column where surveys.ID = rank.SurveysID (not sure how to accomplish this)
put "project_name" column and "values" column into a DataView and bind it to a datagridview
update "values" column in original DataSet and save

Of course somewhere I need to consider the users ID as well.

[fixo]

Hi Keith

I think you need use SELECT ..JOIN statement

Take a look at this page:

http://www.w3schools.com/SQL/sql_join_left.asp

Regards,

~''J~

[Keith™]

Ok, I have that, but it doesn't give me the desired results ... I have:

Code: [Select]

"SELECT Project_Name FROM(Surveys) LEFT JOIN Ranks ON Surveys.ID=Ranks.SurveyRecordID"

This returns a list of all the project names, where the corresponding ID exist in the Ranks table, but I also need to return ALL columns in the Ranks table.

[Ken Alexander]

You would need:

Select Project_Name, Rank from Survey Left Join Rank On Survey.ID = Rank.SurveyID

But if I understand this correctly the ranks table will contain multiple records from different users ranking a given survey project???

If this is the case you will get duplicate records in the Project_Name column.  Therefore you would want to add a Where clause.

Select Project_Name, Rank from Survey Left Join Rank On Survey.ID = Rank.SurveyID Where Rank.UserID = 1

With this you will only get the Project_Names of the Projects that given user has entered.  I believe the sql is going to get very tricky.

[fixo]

Can't check it witout VS right now
Perhaps...

Code: [Select]

SELECT * FROM(Surveys) LEFT JOIN Ranks ON
 Surveys.ID=Ranks.SurveyRecordID
~'J'~

[Keith™]

Ken, each user has a column in the Ranks table, since the data will be entered by one or more people, the columns will be displayed as needed via code. I need to retrieve the Project_Name column in Surveys and join it to the entire Rank table where Surveys.ID = Ranks.SurveyRecordID

fixo, I don't want all columns from surveys, but I do want all columns from Ranks ... however, I need ALL records from Surveys, even if there is no matching SurveyRecordID in Ranks.

Surveys contain a hundred or so columns, I just want Project_Name, Ranks has a column for each user, plus "ID" and "SurveyRecordsID"

I need to retrieve all of the records from Ranks, then only the column Project_Name from Surveys, then join them on a one to one ... each record in the Ranks table will be unique.

[fixo]

Did you tried to create relation between tables before?
Something like:

private partial class myForm : Form
{
private DataSet mydataset = new DataSet("RankSurveysDataSet");
...........
private void MakeRelation()
{
DataRelation rel = new DataRelation("ProjectsOrder",
mydataset.Tables["Rank"].Columns["ID"],
mydataset.Tables["Surveys"].Columns["SurveysID"]);
}
...........
connect to database
create adapter
fill mydataset with two tables
adapter.Fill(mydataset, "Rank");
adapter.Fill(mydataset, "Surveys");

populate two datagridview with data

datagridview1.DataSource = mydataset.Tables["Rank"];
datagridview1.DataSource =mydataset.Tables["Surveys"];

MakeRelation();
...........
 then you can create 3rd datagridview as a result table view

~'J'~

[Keith™]

I have a relation built into the database, but I cannot be sure it exists at runtime because I need the ability to create the tables programmatically.

Are you telling me that I can fill a dataset with data from two tablesthen display it as if it were a single table?

[fixo]

Sorry, Keith
Now is is too late in my village (01:40 local)
Check private message I need sleep

~'J'~

[fixo]

Here is a working code from lesson:
(tested on VS2005)
borrwed from "PRO C# 2005 and the .NET 2.0 Platform",
page 999 (russian edition) by Andrew Troelsen

Code: [Select]

         private void btnFill_Click(object sender, EventArgs e)
        {
            //int id = (int)this.DataGridViewInventory.SelectedRows[0].Cells[0].Value;//ok
 
            SqlCommand cmdPivot = new SqlCommand();
            cmdPivot.Connection = cn;
            //cmdPivot.CommandText=
            //    "SELECT e.FirstName, e.LastName FROM Customers e " +
            //    "JOIN Inventory h " +
            //    "ON e.custID = h.carID " +
            //    "WHERE h.carID = '" + id + "'";//ok also with single record
            cmdPivot.CommandText =
    "SELECT e.custID, e.FirstName, e.LastName FROM Customers e " +
    "JOIN Inventory h " +
    "ON e.custID = h.carID " +
    "WHERE h.carID = e.custID";
            pivotTableAdapter = new SqlDataAdapter(cmdPivot);
            sqlCBPivot = new SqlCommandBuilder(pivotTableAdapter);
            pivotTableAdapter.Fill(pivotDS,"PivotTable");
            DataGridViewPivot.DataSource = pivotDS.Tables["PivotTable"];
            SaveData();//
        }
HTH

~'J'~

[Keith™]

Ok, I have put this one to bed ... finally I think ...

I'll not post code because I am not at liberty to do so .. however, I have resolved the issue of linking tables, updating multiple tables with commandbuilder and generally all of the things that were making my life miserable over the last several months. The resolution was so simple, that I can't believe I didn't think of it along time ago.

I'll go through the steps in case anyone else would like a simple method to link multiple tables 1-to-1 in access.

1) Build each table with the requisite autonumber fields and foreign key fields, either programmatically or manually in the database

2) Using a single SQL statement, retrieve the fields from your tables with a left join i.e.

SELECT Table1.Field1, Table1.Field2, Table2.* FROM(Table1) LEFT JOIN Table2 ON Table1.Field1=Table2.Table1Field2

3) display and/or edit the records as desired

4) Now you have columns in the format of Table1.Field1 etc ... Rename Table1.Field1 to Field1 and update the records in Table1
5) Rename Field1 (from the previous action) to Table1Field1 and rename Table2.Field1 to Field1 and update again ... this time to table2

As long as the records are 1-to-1 and you have the requisite columns it will be very easy... it took me 6 months to figure it out ... hopefully it won't be so difficult for everyone else now.

[Draftek]

Congrats.
Nothing like a little blood letting to make you appreciate it.

[Keith™]

Congrats.
Nothing like a little blood letting to make you appreciate it.

and hair pulling