Clean up on Database

[jtfrier]

I have a table that has two main fields one called part# and the other des for decription my table has mutipal part# with dec that that are defrent I need to have one part # and the decription field des need to be combinded. The tables are stored in access I also need to create a concetion to the access database coding has to be java.I am just learning java pleas give detail. Thank you

Ex what I want it to look like
    part#                   des
    1234            pincle, .09, red, black

Ex what it looks like
    part#                    des
    1234                    pincle
    1234                    .09
    1234                    red
    1234                    black

 

[Diancecht]

What's the question?

Anyway, if you're learning Java, I think you should take a simpler example.

Cheers,
Dian

 

[jtfrier]

any ideas on how to get started or what type of code i can search for that might get me what i want.

 

[stevexff]

The problem you are going to have is that the 'how it is now' table doesn't have any sequencing. So if we write

SELECT part#, des FROM tblNow ORDER BY #part;

they could come out in any order within part#. We could ORDER BY part#, des but that might not be what you want.

Also, what's the purpose? Someone obviously thought at some time that your database should be normalised in this way, but you want to catenate the values for 'des' into a single column. Are you sure this is a good idea?

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[jtfrier]

this data was exported out and im cleaning it up i have it in a excell spreed sheet two if you think it might be easer to right code to change that, I dont realy care what order Dec gets put into in the single column just needs to be done.

 

[jtfrier]

sorry about that I was wrong I do not have a Excell file with information.

 

[jtfrier]

Ok this is what i have got so fare This connects me to the database and lets me call a SQL string

//Concatenate.java
//adds decription fields together
import java.sql.*;

public class Concatenate
{
	private static Connection connection;
	private static Statement statement;
	
	public static void main(String args[])
	{
		try
		{
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			connection = DriverManager.getConnection("jdbc:odbc:manman","","");
			statement = connection.createStatement();		
			String Query ="SELECT test1.partno, test1.lineno, test1.Desc FROM test1";
			ResultSet result = statement.executeQuery(Query);
			while(result.next()) 
			{ 
				// process results one row at a time                                                                    
	            String partno = result.getString("partno");
	            String lineno = result.getString("lineno");
	            String desc = result.getString("Desc");
	            System.out.println(partno + "\t\t" + lineno + "\t\t" + desc);
	
			}
		}
		catch(Exception E)
		{
		
		}
	}
}

This is the loop that I need to run but it is In VBA. do you have any sugestions or know were I can find something like this in java the sql string I use to call this looks like this.

SELECT Partno, Concatenate("SELECT Desc FROM Edmanmanruby1 WHERE Partno='" & [Partno] & "'") AS Descriptions
FROM Edmanmanruby1;

Option Compare Database

Function Concatenate(pstrSQL As String, _
        Optional pstrDelim As String = ", ") As String

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset(pstrSQL)
       
    Dim strConcat As String
    With rs
        If Not .EOF Then
            .MoveFirst
            Do While Not .EOF
                strConcat = strConcat & _
                    .Fields(0) & pstrDelim
                .MoveNext
            Loop
        End If
        .Close
    End With
    Set rs = Nothing
    Set db = Nothing
    If Len(strConcat) > 0 Then
        strConcat = Left(strConcat, _
            Len(strConcat) - Len(pstrDelim))
    End If
    Concatenate = strConcat
End Function

 

[jtfrier]

ok still need some help here is what i have so fare
I need to concatenated decs with , and it seems im only geting the first desc in my array list any ideas on how to get all decs in the array list.

//Concatenate.java
//adds decription fields together
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;

public class Concatenate
{
	private static Connection connection;
	private static Statement statement;
	private static HashMap map = new HashMap();
	static ArrayList Data;
	public static void main(String args[])
	{
		try
		{
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			connection = DriverManager.getConnection("jdbc:odbc:manman","","");
			statement = connection.createStatement();		
			String Query ="SELECT test1.partno, test1.lineno, test1.Desc FROM test1";
			ResultSet result = statement.executeQuery(Query);
			while(result.next()) 
			{ 
				// process results one row at a time                                                                    
	            String partno = result.getString("partno");
	            String lineno = result.getString("lineno");
	            String desc = result.getString("Desc");
	            System.out.println(map);
	            
	            if (map.containsKey(partno))
	            {
				//here is where i need to concatenate decs  
	            }
	            else
			    {
			    	Data = new ArrayList();
			    	Data.add(desc);
			    	map.put(partno,Data);
			    	
			    }
			}
		}
		catch(Exception E)
		{
		}
	}
}