database question ..actually not sure what the title should be on this..row merging??

[M@dH@tter]

in the picture attached..and i'm hoping it isn't too big..as my resolution is set at 1440 x 900..
you''l see a bunch of rows i've highlighted in in blue(selected)

what i need to do is take any row that has a duplicate Document and place all models of each row in one row and only the matching document in the same row.

so instead of having the same document number listed on a crap load of rows, there should only be one listing per document and all models on that same line..delimited with a " , "
hmm not sure if that makes sense but we'll see lol

I'm thinking that if i set the primary key on the document column so that there will only be one listing per document..i can have it make a new string list of all models that have that same document number when it detects a duplicate and then add the whole thing to the db .

My problem is i'm getting confused with trying to figure out how to do it lol..i mean i can picture what needs to happen in head,,but just not sure how to go about doing it in the code.

[jmcilhinney]

Making that column the PK won't magically turn multiple rows into one. All it will do is throw an error because of duplicate values in the desired PK column.

Is this actually a VB.NET question? Are you trying to do this in VB.NET or in Access? Do you want to change the contents of the Access database or just retrieve it from the database and display it in a VB.NET app in a particular way?

[M@dH@tter]

sorry i shoulda mentioned that the data is in an older .dbf file and i had to use access to open it..
the db file has the same document number listed on multiple rows but each row has a different model..so i need to combine all the models together in column 1 and just the single doc number in column 2 ,,all on the same row of course :-) and then store it in a new .accdb file.

oh and yes i'm writing code in vb..

[IanRyder]

Hi,

oh and yes i'm writing code in vb..

OK then, have a go with this example of using LINQ to group your Documents and create a new table with a single Document name and your Models joined into a single string:-

Public Class Form1
  Dim inputDT As New DataTable
  Dim outputDT As New DataTable
 
  Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
    'Create a Table similar to what you are using
    PopulateDataTable()
    'Show Table in DataGridView to demonstrate
    DataGridView1.DataSource = inputDT
 
    'Group the Table, using LINQ and Lambda Expressions, by Column 1 being the Document Name
    Dim groupedDocs = inputDT.Rows.Cast(Of DataRow).GroupBy(Function(x) x(1).ToString)
 
    'Iterate through the Groups, adding each one to a new table along with joining the
    'Model names into a single string
    For Each eachGroup As IGrouping(Of String, DataRow) In groupedDocs
      outputDT.Rows.Add(String.Join(", ", eachGroup.Select(Function(x) x(0)).ToArray), eachGroup.Key)
    Next
    'Show the New Table in a second DataGridView to demonstrate
    DataGridView2.DataSource = outputDT
  End Sub
 
  Private Sub PopulateDataTable()
    inputDT.Columns.AddRange({New DataColumn("Model", GetType(String)), New DataColumn("DocumentName", GetType(String))})
    outputDT = inputDT.Clone
 
    For modelCount As Integer = 1 To 10
      For docCount As Integer = 1 To 10
        inputDT.Rows.Add("Model" & modelCount, "DocName" & docCount)
      Next
    Next
  End Sub
End Class

As you can see, I make two tables here to demonstrate this but you would get your tables from your Database's.

Hope that helps.

Cheers,

Ian

[jmcilhinney]

Something like this should do the trick:

Dim sourceConnectionString = "source connection string here"
Dim destinationConnectionString = "destination connection string here"
Dim documentOnlyQuery = "SELECT DISTINCT Document, NULL AS Model FROM MyTable"
Dim documentAndModelQuery = "SELECT Document, Model FROM MyTable"
Dim insertStatement = "INSERT INTO MyTable (Document, Model) VALUES (@Document, @Model)"

Using sourceConnection As New OleDbConnection(sourceConnectionString),
      destinationConnection As New OleDbConnection(destinationConnectionString),
      documentOnlyAdapter As New OleDbDataAdapter(documentOnlyQuery, sourceConnection),
      documentAndModelAdapter As New OleDbDataAdapter(documentAndModelQuery, sourceConnection),
      insertCommand As New OleDbCommand(insertStatement, destinationConnection),
      destinationAdapter As New OleDbDataAdapter
    destinationAdapter.InsertCommand = insertCommand

    'Keep all RowStates as Added so they are ready to be inserted into the destination database.
    documentOnlyAdapter.AcceptChangesDuringFill = False

    sourceConnection.Open()

    Dim data As New DataSet

    'Get each unique Document number with an empty Model field.
    documentOnlyAdapter.Fill(data, "DistinctDocuments")

    'Get each Model number and its corresponding Document number.
    documentAndModelAdapter.Fill(data, "AllModels")

    Dim distinctDocumentsTable = data.Tables("DistinctDocuments")
    Dim allModelsTable = data.Tables("AllModels")
    Dim distinctDocumentColumn = distinctDocumentsTable.Columns("Document")

    'Make Document the PK for the parent table.
    distinctDocumentsTable.PrimaryKey = {distinctDocumentColumn}

    'Create a relation between the tables based on Document.
    Dim relation = data.Relations.Add("Document", distinctDocumentColumn, allModelsTable.Columns("Document"))

    For Each parentRow As DataRow In distinctDocumentsTable.Rows
        'Get all the child rows related to the current parent row.
        Dim childRows = parentRow.GetChildRows(relation)

        'Populate the parent Model field with a comma-separated list of the Model values from the child rows.
        parentRow("Model") = String.Join(",", childRows.Select(Function(dr) dr("Model")))
    Next

    With insertCommand.Parameters
        .Add("@Document", OleDbType.VarChar, 50, "Document")
        .Add("@Model", OleDbType.VarChar, 50, "Model")
    End With

    'Save the data to the destination database.
    destinationAdapter.Update(distinctDocumentsTable)
End Using

[M@dH@tter]

i'm getting this error "Unable to cast object of type 'WhereSelectArrayIterator`2[System.Data.DataRow,System.Object]' to type 'System.String[]'."

on this line

                'Populate the parent Model field with a comma-separated list of the Model values from the child rows.
                parentRow("Model") = String.Join(",", childRows.Select(Function(dr) dr("Model")))

[jmcilhinney]

Try:

parentRow("Model") = String.Join(",", childRows.Select(Function(dr) CStr(dr("Model"))).ToArray())

[M@dH@tter]

aaah yes that seems to have done the trick..have another question..

I'm getting this error now on some records
Conversion from type 'DBNull' to type 'String' is not valid.

there are some models that don't have info so i assume that's what is causing the error

And if it's no trouble brother can you elaborate a bit more on the new line you posted..i find it rather interesting :-)

[dunfiddlin]

there are some models that don't have info so i assume that's what is causing the error

Yup. Null means there is absolutely no value of any kind present. You can't convert nothing to anything. To avoid this problem you should set a default value rather than allowing Null.

[M@dH@tter]

hmm i did what ya suggested and opened the .dbf file and added 0 where there were no values and that let it go past the error and worked fine ..
except now i have 0,Model,Model,Model etc.....
so now to figure out how to make it add a 0 if it's null and then remove the 0, before it updates the db hehe

[jmcilhinney]

Yup. Null means there is absolutely no value of any kind present. You can't convert nothing to anything. To avoid this problem you should set a default value rather than allowing Null.

I feel compelled to ask you to stop recommending to people that they not allow NULLs in their databases. It is bad advice. While there are cases where a NULL value doesn't make sense, it is quite legitimate to allow NULLs in databases. I would say that, in my work, probably 30-40% of database columns allow NULLs and it is completely legitimate. There is nothing wrong with a NULL in database as long as it makes sense that that column have no value.

[jmcilhinney]

hmm i did what ya suggested and opened the .dbf file and added 0 where there were no values and that let it go past the error and worked fine ..
except now i have 0,Model,Model,Model etc.....
so now to figure out how to make it add a 0 if it's null and then remove the 0, before it updates the db hehe

Don't do that. You should absolutely not have zero in the database in this case. As far as I can tell, NULL is a completely appropriate possibility in this case. That column is supposed to contain the model number for the record. Do any of those records correspond to a model with the number 0? If not then that is completely inappropriate. The question you have to ask yourself is whether or not it is possible for a record to exist with no corresponding model. If the answer is yes then it absolutely appropriate, and in fact essential, that that column allow NULLs. All you have to do is write your code that uses the data in such a way that it allows for those NULLs and treats them correctly. Here is an adjustment to my code that does just that:

parentRow("Model") = String.Join(",", childRows.Where(Function(dr) Not dr.IsNull("Model")).Select(Function(dr) CStr(dr("Model"))).ToArray())

That Where call will filter out all the rows where the Model column contains NULL.

That does present another issue though. If there are any Document numbers for which all rows contain NULL in the Model column, you will end up with a row in the DistinctDocuments table that has an empty String in the model column. You need to decide what you want to do with those. If you want to discard them so that they don't get saved into the new database then you should loop through the DataTable and Remove them. If you do want to save the document numbers to the new database then you need to decide whether it's more appropriate to save an empty String or a NULL for the model numbers. As the data represents a list of model numbers rather than just one, I'd suggest that an empty String is the more appropriate option. NULL would imply that there's no list, while an empty String would imply that there is a list but it doesn't contain any items.

[M@dH@tter]

ah ok i see what you telling me and that new line worked fine..but as usual lol i now have another issue hehe..
i noticed that in the .dbf file they also have the same model/s listed on multiple rows as well as having multiple doc numbers on each of those rows,,
so i in effect end up with the following example in the model column..

CDXGT300,CDXGT300,CDXGT300

seems like i can never catch a break lol i think whoever created this db must of ate the lead paint off way too many cars as a child or something lol in one db file they have Models, Document... in another they have Document, Description..
I'm sure that they had these 2 db's linked in some way so that it would in effect show the data as Model,Document,Description when a search was performed on the database..but seems like alot of trouble when they coulda just put it all in one db file...
so now after i run the original import from the dbf file,,i'll have run another one to add in the descriptions for each row..
as if the first one doesn't take a long enough just by itself lol

[jmcilhinney]

Not a problem. Those Where and Select methods are part of LINQ and LINQ also includes a Distinct method:

parentRow("Model") = String.Join(",",
                                 childRows.Where(Function(dr) Not dr.IsNull("Model")).
                                           Select(Function(dr) dr.Field(Of String)("Model")).
                                           Distinct())

You can also use query syntax instead of function syntax:

parentRow("Model") = String.Join(",", From row In childRows
                                      Where Not row.IsNull("Model")
                                      Select row.Field(Of String)("Model")
                                      Distinct)

[M@dH@tter]

using either of the two revised additions of yours i get the following error.

Unable to cast object of type '<DistinctIterator>d__7a`1[System.String]' to type 'System.String[]'.

[IanRyder]

Hi,

Same problem and same fix as post No. 7.

Cheers,

Ian

[Evil_Giraffe]

Wait a minute:

what i need to do is take any row that has a duplicate Document and place all models of each row in one row and only the matching document in the same row.

so instead of having the same document number listed on a crap load of rows, there should only be one listing per document and all models on that same line..delimited with a " , "
hmm not sure if that makes sense but we'll see lol

Why do you want to do this in the database? That's a very unusual thing to need to do in a relational database. What makes you think that is what you need to do? There are almost certainly better ways to solve your ultimate problem than this approach.

[M@dH@tter]

well in a nutshell here's exactly what i'm doing..er trying to do..i have an older database program that i used back in 2000 or so...it uses the .dbf database's
so i want to update that program..or rather create my own revised version of that program..
thus far trying to figure out how the databases are put together has been friggin' headache.

i need to combine the info from 3 .dbf database files into just one database .accdb file

The first db has column headers "Model" and "Document", the 2nd has column header "Name" and "Description" (column header "Name" is actually Document numbers that match the first DB) ,the 3rd has column Header "FileName" which is actually a combination of the "Document Numbers" and "\manual.pdf".

So i'm at a loss as too what the best solution would be..
Plus My existing database1.accdb already has alot of the information from these databases in it..so simply using an Insert command ain't gonna work lol

and i think i figured out why the same document numbers appear in the dbf files more than once with a different model..i think it has to do with char length limit? so they couldn't put all the models delimited on one row with that manuals doc num in the second column.

I would upload these db files but there friggin' huge..well not real huge but range in the neighborhood of around 50M each..which is way too large for an attachment lol

the info from all 3 databases should appear like this in my Database1.accdb file

Models1,Model2,Model3 etc. | DocNum | Description | i used the bar to represent column dividers

[M@dH@tter]

IanRyder , i didn't mean to not respond to your reply, but i couldn't figure out to get the .dbf file into your code..ya gotta remember this database stuff is completely new to me..so when you guys start popping out functions i'm not familier with yet.. it gets kinda confusing to me lol

[M@dH@tter]

IanRyder , using your code i get the following error (Unable to cast object of type 'System.Object[]' to type 'System.String[]'.)
on this line ----> outputDT.Rows.Add(String.Join(", ", eachGroup.Select(Function(x) x(0)).ToArray), eachGroup.Key)

[M@dH@tter]

aaahhh

outputDT.Rows.Add(String.Join(", ", eachGroup.Select(Function(x) x(0).ToString).ToArray), eachGroup.Key)

i think that's the fix yes?

[IanRyder]

Hi,

IanRyder , i didn't mean to not respond to your reply, but i couldn't figure out to get the .dbf file into your code..ya gotta remember this database stuff is completely new to me..so when you guys start popping out functions i'm not familier with yet.. it gets kinda confusing to me lol

No problem, it's up to you to decide which route you want to take. The fact that you got 2 routes to choose from is just an example of how the same thing can be done in different ways.

IanRyder , using your code i get the following error (Unable to cast object of type 'System.Object[]' to type 'System.String[]'.)
on this line ----> outputDT.Rows.Add(String.Join(", ", eachGroup.Select(Function(x) x(0)).ToArray), eachGroup.Key)

Based on the example I provided you WILL NOT (using VS2010) get this issue so I cannot answer this without seeing exactly what you have tried in your own code to bring in your Database Table and use this example.

That said, I take it that you have abandoned the route you were pursuing due to the last error that you got? If so, then this is a BAD move! The key to learning is to keep on working on something until you have pushed through the hard bits and fully understand what went wrong so that you can not only avoid the same issue in the future that you have now, but you can implement the same principals to do other things in other projects later. Not only that, and by doing this, you disrespect those people that have freely supported you with their own ideas so far by basically ignoring them because you "did not get it".

I would suggest then that you continue on the route that you have started with and when you finally have this solved and understand how this works then make a new Thread at which time I, in addition to others, will be happy to help you understand how you could have done this a different way using the example I provided.

Hope that helps.

Cheers,

Ian

[IanRyder]

aaahhh

outputDT.Rows.Add(String.Join(", ", eachGroup.Select(Function(x) x(0).ToString).ToArray), eachGroup.Key)

i think that's the fix yes?

The question is, did it work for you? If so then great, but please take note of my last post.

Cheers,

Ian

[Evil_Giraffe]

So i'm at a loss as too what the best solution would be..

Sounds from your description that you want two tables: Firstly, one for documents that contains the document number (as primary key), description and filename. Secondly, one for models that contains the model number (as primary key) and the associated document.

Errr, kinda exactly how it was when you started?

What benefit do you see from having all the models for a given document in a single field?

and i think i figured out why the same document numbers appear in the dbf files more than once with a different model..i think it has to do with char length limit? so they couldn't put all the models delimited on one row with that manuals doc num in the second column.

No, I think it may have been because they understood how relational databases work.

[Pradeep1210]

Here is the non-programmatic easy way:

1. On the Create tab, click the Query Design toolbar button.
2. Close the Show Table dialog box that opens automatically.
3. Click the SQL View button, and paste the following query in that window (replace Table1 with your table name).

SELECT DISTINCT DOCUMENT FROM [Table1]

4. Click the Run button to verify that you get distinct DOCUMENT names.
5. Click the Save button (or press ctrl+S) to save this query by any name. Say name it "Group By Document".
6. Now on the Home tab, click the More button in the Records group. Point to SubDataSheet and click SubDataSheet.



7. In the Insert Sub DataSheet dialog box that opens, click Table1 (your table). Select DOCUMENT in both the dropdowns below.



8. Click Save button (or press ctrl+S) to save everything.

Now you will see a [+] sign with each Document. Clicking that [+] button will expand that record and show you the related Models.

[M@dH@tter]

Ian, No i wasn't given up on JMC's code.. i simply added a Form2 and put your code on it and left JMC's code on Form1... i then made your Form2 the default load page to experiment with..that way it wouldn't mess up what i was working in his Form1 :-).

Pradeep1210, Thanks for the info on how to link thru access, this however doesn't do what i need tho,,

I want all the models in one field because if i do a search for CDX-GT300 and there are 40 or more Models (and there can be up to 100 models that all use the same manual) ,,then viewing 1 row and not 40 or 100 makes more sense to me..likewise if i search for 988967453 and that doc number is listed in the database 40 or more times with 40 or more different models..then 1 row is better than 40 also the search will run 100 times faster as well.

Also i forgot to mention that the program that i want to create my own version of,,is still being updated on a monthly basis..so doing a one time transfer into my own DB is not a option ..i need to be able to import the .dbf files using Insert/Update commands..so that i can keep my database Up to date... there are about 10 or so columns in the .dbf files that i won't be using and won't need in my version.
So the actual db file size should go down by at least 50% or so

I'll post a screen shot of what the final views in my version will look like..this might give a better idea of what i'm looking for.

the Image below is prob huge but i couldn't get it to go any smaller . but this is a basic layout thus far,,i still have a lot of buttons to add for doing various things.but besides having the import from text files working....each row can be single clicked on while holding the ALT key to open the editor (Pictured) for that row.. double clicking the row without any keys held down will open that file in acrobat, winzip or what ever the file open with.
all this infio pictured is in one file. and only one table.

[Evil_Giraffe]

The search will not be 100 times slower if you keep the models and documents separate. Remember that this is a relational database, answering questions like "give me all the models that have this document number" is what they are really really good at.

What you would do is run one query to return the document data, that will return a single row. Then you run a second query that retrieves all the models that have that document (between 40 and 100 rows). Then in the client you can concatenate them together in a single line with commas between them.

The obvious extension to your program is to allow a search for document by model - trivial if you've modelled your database with separate model and document tables, a lot of messy string parsing in your proposed structure. I would suggest reading up database normalisation (alright, "normalization")

[Pradeep1210]

Pradeep1210, Thanks for the info on how to link thru access, this however doesn't do what i need tho,,

I want all the models in one field because if i do a search for CDX-GT300 and there are 40 or more Models (and there can be up to 100 models that all use the same manual) ,,then viewing 1 row and not 40 or 100 makes more sense to me..likewise if i search for 988967453 and that doc number is listed in the database 40 or more times with 40 or more different models..then 1 row is better than 40 also the search will run 100 times faster as well.

OK then try this:
1. Add a new module and the following code into it:
(replace Table1 with your table name)

Option Compare Database

Public Function GetModels(Document As String) As String
    Dim result As String
    Dim rs As DAO.Recordset
    Set rs = DBEngine(0)(0).OpenRecordset("select * from [Table1] where Document = '" & Document & "'")
    
    result = rs!Model
    rs.MoveNext
    While Not rs.EOF
        result = result & ", " & rs!Model
        rs.MoveNext
    Wend
    GetModels = result
End Function

2. Create a new Query with the following SQL statement.
(replace Table1 with your table name)

SELECT DISTINCT DOCUMENT, GetModels(Document) as MODEL FROM [Table1]

Save everything and run that query.
You can use call this query from your front end application also.

NB: You will need to enable macros for this to work.

[FunkyDexter]

SHTOOOOOOOOP!!!!

I just want to add my weight to Evil Giraffes. You really, REALLY should not be putting comma delimited lists in a single field of a database. I'm actually amazed that JM showed you how because it's the sort of thing he'd normally flat out refuse to do and would probably give you a good tongue lashing for even suggesting. I can only assume he was seething so mightily about the bad advice you were given on NULLS that he temporarily forgot his role as one of our better guardians of good practices. Any of us who work regularly with databases can tell you you are heading for a world of hurt when you start trying to query this later.

Relational databases are... relational. They're about storing related data in related tables which is what the designer of the original program has done and which is what you're undoing.

You DB should contain a table of documents and a table of models. The table of models should contain an extra field called something like DocumentID which says which document that model belongs to. This is called a foregn key. To find which document a model belongs to you then do this:-

Select DocumentID From Model Where ModelDescription = 'CDX-GT300'

To find all the models that belong to a document you do this:-

Select ModelDescription From Model where DocumentID = 'SomeDocumentID'

Those searches are very simple and easy for a DB to process. As the database scales you'll be able to take advantage of index to speed things up. It's what a database wants you to do. If you concatenate the models into a single field you're going to have to do this:-

Select DocumentID from Document where Models Like '%CDX-GT300%'

That Like with a wildcard on the beginning is going to be a killer. You won't be able to use indexes and when you introduce Model CDX-GT300-ABC the query will start returning you false positives.

I won't give a full description of relational theory in this thread because it's too long but, seriously, have a quick google for "relational theory" before you go any further. There are plenty of papers which will give the basics with an hour or so's reading. Failure to do this will cost you dearly.

[jmcilhinney]

I'm actually amazed that JM showed you how because it's the sort of thing he'd normally flat out refuse to do and would probably give you a good tongue lashing for even suggesting. I can only assume he was seething so mightily about the bad advice you were given on NULLS that he temporarily forgot his role as one of our better guardians of good practices

Sometimes, when you've tried to fight the good fight as many times as I have, many of them unappreciated and some of them downright resented, it's nice to just answer the question as asked and not bother reading any further into it.

[M@dH@tter]

Hi . ok first and foremost, i am extremely grateful to you all, your inputs on this topic is awsom,telling the directions i should go is great,and I do not leave any idea's behind, nor any suggestions,yeas i even went back and looked into relational databases,and here's my conclusions,,after testing how fast the original database runs a search for the very last item in the database and how fast my combined index searched and found the exact same items.

But before i post any of that....original database file sizes
Consumer Db file sizes
0311.dbf 58.8M (Models and Doc)
0928.dbf 21.0M (docNum and descriptions)
0318.dbf 2.0M (file Names)

Professional db file sizes
4001.dbf 43M (Models, docNum and descriptions)
4004.dbf 44M (DocNum and Models)
total database size with all 161 files 1.65G
------------------------------------------------

My combined db consumer and Professional
RefLib.accdb 24.6M


total time to import my old index.txt into the reflib.accdb 1.34sec

Search item ZS-YN7L

total time to find the last index item in my old vb6 version of the original program just under 1sec
Speed to find the last index item in the original program (NOT FOUND but item does exist in the dbf file)
Speed to find the last index item in my new VB2010 version average (1.4 sec) with 2 results (ZS-YN7L 987796101 987796101\MANUAL.PDF) and (ZS-YN7L 987796102 987796102\MANUAL.PDF)

so maybe a relational database would be the better choice..but it's obviously not working in the original version, but my combined version is working just fine.


I am in no way shape or form dissing anyone's input it's all valuable info to me.
I am by nature a type of person who must learn by my own mistakes,i cannot learn by other peoples mistakes, because it's not impressionable personally.

the type of database i'm trying to make work is the best choice for the moment for me ,since the original program has issues finding even files it just got thru updating from it's own online database.

I'll be spending alot of time i would imagine opening this page, and retrying everyone's input and suggestions over and over..so don't think that your input is going to waste it's not, i still reflect back on other previous post as well.

Actually at the moment i'm trying to figure out why JMC's last 2 code lines aren't working :-)
breaking it down into SQL helped alot tho.

[M@dH@tter]

oops my bad..i stuck that search model in the wrong box in the original program so it was looking for a model in the documents column,,,to find that model took just under 4sec..
which is another thing that's different about the 2 versions..there's has a box for searching for document numbers and one for searching for Models,,mine has one box that search's both column's.
If i'm right about how the bulletins are layed out i'll be able to define the search abit better based on the first letter or number, so it doesn't have to search both columns of each row, but will be able to narrow it down to a single column to search ,,but that's for later

[FunkyDexter]

I am by nature a type of person who must learn by my own mistakes

I can relate to that, I think many of us are the same. Sometimes you have to go through the pain yourself before you understand why it hurts so much. Still, if we can head you off from that pain we'll at least try.

The reason your search times were similar is because, as yet, you are not taking advantage of the performance mechanisms (e.g. indexes) that a relational model offers. If you were to take advantage of those mechanisms (which may be too big a leap yet but you'll get your head around them soon enough) you would probably see your query time drop to .1 of a second in the relational model. Still, that's probably not the biggest argument for a relational model in this case. The possibility for false positives I identified in my penultimate paragraph is probably a much more serious issue and one you should consider. How are you going to search for a model number that is completely contained by a different model number?

Other that that it sounds like you're pottering on and gradually working through your issues so just post back any specific questions that come up.