Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    May 2013
    Posts
    28

    Question Unanswered: Changing Tables Columns and Rows

    Hello Guys,

    I have a table set up by 5 fields or columns. They are Product, Language, English, ID, Current Translation. I want to make a query to make a new table. Which isn't that hard but here is my problem. So I want to user to basically pick the Product they want the table to be. So now it would create a table of Language, English, ID, Current Translation.

    Under English there are multiple different languages to show what the current translation is. So I want to be able to take the languages and make them fields or columns in the table. So now the table would look like English, ID, Language 1 Translation, Language 2 Translation, Language 3 Translation and so on. Is this possible?

    The current translation of each language would be put under the new field/column that it belongs too. So to test this I would have to use the English and ID fields to make sure they belong to that record.

    Thanks and if it is unclear ask questions

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I want to make a query to make a new table
    why?
    isn't the product already in the table, so if you create a unique index that includes the product then you will be sorted
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2013
    Posts
    28
    Confused by what you mean? I will have a combo list for the user to pick the product, there are 5 products. Then I want a table to be generated from that so it can be exported or printed.

  4. #4
    Join Date
    Jan 2005
    Posts
    146
    Take a look at this and see if you can use it. It first creates a new table with your 3 basic fields with that data. It then searches for each language and adds those columns and the translations. For my test I just created a product called 'Book' but you would need to reference from your form.

    [code]
    Dim Rst As DAO.Recordset, MySql As String, FldName As String, NewTblName As String
    NewTblName = InputBox("Enter new table name")
    DoCmd****nSQL "CREATE TABLE " & NewTblName & " (ID Number, Language Text, English Text);"
    If DCount("*", "tblProducts", "[Product]='Book'") > 0 Then
    MySql = "SELECT * FROM tblProducts WHERE ((([Product])='Book'));"
    Set Rst = CurrentDb.OpenRecordset(MySql, dbOpenSnapshot)
    Rst.MoveLast
    Rst.MoveFirst
    DoCmd****nSQL "INSERT INTO " & NewTblName & " (ID, Language, English) VALUES (" & Rst!id & ",'" & Rst!language & "','" & Rst!english & "');"
    Do While Not Rst.EOF
    FldName = Rst!language & "_Translation"
    DoCmd****nSQL "ALTER TABLE " & NewTblName & " ADD " & FldName & " Text;"
    DoCmd****nSQL "UPDATE " & NewTblName & " SET " & FldName & " ='" & Rst![CurrentTranslation] & "';"
    Rst.MoveNext
    Loop
    Rst.Close
    Set Rst = Nothing
    End If
    [/doe]

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why not just do a select into AKA a make table query
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    May 2013
    Posts
    28
    @billmeye and healdem

    I am a bit of a novice, with Access and databasing. I partially understand the code given but I am not quite sure how to implement it. Can I bind it to a button in VBA?

  7. #7
    Join Date
    Jan 2005
    Posts
    146
    I'm assuming your running from a form so you can put the code in the On Click event for a button.

  8. #8
    Join Date
    May 2013
    Posts
    28
    Thats what I am doing, I put the code in there but the code builder is showing syntax errors on all the DoCmd****nSQL lines

  9. #9
    Join Date
    Jan 2005
    Posts
    146
    dBfourums automatically removes that part of the code and replaces it with ****. It should be DoCmd . RunSQL just get rid of the spaces.

  10. #10
    Join Date
    May 2013
    Posts
    28
    I believe ive been putting in the right data where needed be, but it is saying item not found in this collection for the, VALUES (" & Rst!Id & ",'" & Rst!Language & "','" & Rst!English & "');" its saying it about Rst!ID

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you want to export data to a table for use elsewhere then you don't need to mess around with recordsets. use a make query
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Jan 2005
    Posts
    146
    The reason for the recordsets is not to make a table, it is to extract the unknown number of languages by cycling through the existing table and adding new columns to the new table.

    Kpelkey151, would you be able to attach your DB so I can quickly through it? Thanks.

  13. #13
    Join Date
    May 2013
    Posts
    28
    Ok I attached a zip, sorry for delayed reply, my internet ended up going out. But basically I want to make a form, which I haven't yet that will give you the option to pick a product. Once that product is picked I want to take Translations Full table and filter? the product that was chosen and create my table.

    The zip contains my database and two excel files. The HRC Glosary is kinda like an example that I want the final table too look like. Shows English, ID, and All language with translations. I want that for a specific product. The FIN excel file is the next step I want to take. Should be much easier, but I want to also be able to create tables based on product and language. If it is not possible to do what we've been working on then I am fine with just the second way I want it done.
    Attached Files Attached Files

  14. #14
    Join Date
    Jan 2005
    Posts
    146
    I've updated your DB to include a module name CreateNewTables which includes the functions you can use to perform your conversion. To use the function, you will need to place the code behind a button on a form or where ever you prefer as:

    Code:
    Call MakeTables("ProductName","NewTableName")
    You can either use fields on your form to determine the Product and table name or variables via an InputBox prompt in VBA.

    Let me know if it was not quite what you had in mind. Also, I assumed you were pulling data from the Translations Full table. Also, just a note, it is always best to include a primary key autonumber, it makes keeping track of records easier and I added it to the new tables.
    Attached Files Attached Files

  15. #15
    Join Date
    May 2013
    Posts
    28
    Wow, That is pretty amazing I must say. I still have a ton to learn about databasing and coding. Now I am stuck at the point where I have to call, the combo box in the function (cboProductSelect) and the text box (NewTableName). I am sure I can easily figure this out, but wanted to update you that it works for hard coded data.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •