Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Lounger
    Join Date
    Jul 2003
    Location
    Dublin, Ireland, Republic of
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    copying info from one table to another (97)

    I have a table which has copied the values for the rows of one of the fields in the table from an outside database. This table is to contain critical information that is contained in another, larger table in the same database. Would anyone know of a way that I can take the values from the field that is filled in in the smaller table and search through the rows of the larger table for these values and copy the information for the other fields from the rows in the larger table into the rows in the smaller one. Both tables have corresponding fields.
    Thanks!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: copying info from one table to another (97)

    1. In general, it should not be necessary to copy the information. If there is a way to match the tables, you can create a selection query that returns fields from both tables.

    2. If you do want to copy the information, you can create an update query:

    Start by creating a new query, and add both tables.
    Join the tables on the matching field.
    Select Query | Update Query
    Let's say that the "small" table is TableA and that the "large" table is TableB.
    Add the fields from TableA that you want to populate to the query grid.
    In the Update To: row, enter <tableB>.[appropriate field name] under each field, where the "appropriate field name" must be substituted for each column.
    Click the Run button (the red exclamation mark) or select Query | Run to execute the query.
    This will fail if the match is not unique.

  3. #3
    Lounger
    Join Date
    Jul 2003
    Location
    Dublin, Ireland, Republic of
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copying info from one table to another (97)

    Thanks for the reply Hans, the only problem with that way of doing it is that the tables to be used are going to be different for each sale. I want to make it so that in the menu for a particular Sales Order Number a command button will be clicked and then info from TableB (the large one) will automatically be transfered to TableA. It will search through the particular field thats filled in in TableA and fill in the other fields with the info that it get from TableB. Any idea how I can do this?
    Thanks again for your help

    the large table with be called 12345 (where 12345 is the sales order no. for the particular sale) and the smaller one will be 12345crit

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: copying info from one table to another (97)

    You haven't reacted to my first remark. Is it really necessary to create all this redundant information? The result of what you ask is that exactly the same information is stored in different places. that is not considered good practice in a relational database.

  5. #5
    Lounger
    Join Date
    Jul 2003
    Location
    Dublin, Ireland, Republic of
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copying info from one table to another (97)

    The reason I want to copy the information is I need to have two tables, one which contains all the information for a particular Sales Order Number, and one which contains the information for certain critical tasks within a sale (a sale consists of a number of task cards, which are listed in the rows of the larger table). So far I have made the smaller table take critical information from one database, it fills all the fields for these tasks. It also takes info from an outside database, but only fills one field for these tasks as the rest of the info is not stored in the outside database, so I need to search through the rows of the larger table and collect the info for the rows whose fields are not all filled, the result will be 2 tables, one which contains all the task cards, and one which contains only those which are considered critical. I need both as the larger one cannot be deleted as it contains alot of info that the smaller one doesn't, and I need to keep the smaller one as I need to have a table of that format for export to another database. I hope I'm being clear enough, thanks Hans.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: copying info from one table to another (97)

    Sorry to keep on egging about this, but what you describe doesn't constitute enough reason to duplicate the data. As I mentioned, you can create a query that combines fields from both tables. And you can create a make-table query that creates a table in another database from the data returned by the query; this would take care of the export. If necessary, you can create this make-table query in code.

  7. #7
    Lounger
    Join Date
    Jul 2003
    Location
    Dublin, Ireland, Republic of
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copying info from one table to another (97)

    If you could show me how to make a create table query in code that creates a table in another database and also maps data from rows in 12345 to rows in 12345crit. It needs to look at the entries in the field 'X' in 12345crit and find them in the field 'X' in 12345 and then fill in the other fields for all these rows. I don't know if this is possible or if I'm just saying the same thing as I said last time. Sorry, I'm bad at explaining what I mean.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: copying info from one table to another (97)

    I think it is possible, but it is not a trivial task; I don't have time for it right now, hopefully somebody else will pick up on this. If nobody else reacts, I'll look into it later, but I don't know when that will be.

  9. #9
    Lounger
    Join Date
    Jul 2003
    Location
    Dublin, Ireland, Republic of
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copying info from one table to another (97)

    That would be great, thanks!

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: copying info from one table to another (97)

    You keep asking for specifics on how to do something but you haven't explained what you're trying to accomplish. Sometimes it's hard to see around the details and figure out what help is really needed. Can you give us an overview? You talk about a MakeTable query but also about "finding" and "mapping" data between fields in two tables, which is not the same thing. What are you actually trying to achieve?
    Charlotte

  11. #11
    Lounger
    Join Date
    Jul 2003
    Location
    Dublin, Ireland, Republic of
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copying info from one table to another (97)

    Basically I have 2 tables. I don't want to make a new table, I want to copy data from one table into the other. Both tables contain the same fields. Each has a list of tasks associated with a particular sales order number. One of the tables contains all the task cards for a particular sales order number, the other contains only the critical task cards and has a much smaller number of rows. The smaller table is called 12345crit, where 12345 is the sales order no.. The larger table is caller 12345. The 12345crit table only has one field filled in on all the rows. I want to use the values contained in this field to get the info that should be in the other fields for the critical tasks into the 12345crit table- this info will need to be taken out of the 12345 table. Does this make sense? thanks for reply, sorry I'm so bad at explaining this stuff!

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: copying info from one table to another (97)

    Frankly, no. The normal way to do this would be to have a field in the original single that allowed you to designate the card as critical. Then you would just query out the "critical" items. Putting them in another table is a very bad idea and would be a nightmare to sustain over time. What you should have is a table that holds the unique sales orders with a unique identifier (probably not the sales order number, although that will be unique as well), then another table that holds the task cards and includes the unique identifier for the sales order as a foreign key. In the latter table, a "critical" field would allow you to indicate the critical tasks.
    Charlotte

  13. #13
    Lounger
    Join Date
    Jul 2003
    Location
    Dublin, Ireland, Republic of
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copying info from one table to another (97)

    Some of the critical tasks are put into the critical table in this way, using a checkbox bound to a field in the larger table and then a make table query. However the database must also download more critical tasks from an outside database (which I cannot change),. I have code that imports these into the table created from the make table query above, but only one of the fields will be filled in so I need to copy the data out of the larger table.

    Maybe you'll have a better idea of what i'm trying to do if I show you what I have done, but it doesn't compile and also I'm not confident at all that its the right way to go about what I want to accomplish:

    Dim strSQL As String

    strSQL = "SELECT DISTINCTROW [" & Me.txtSalesOrder & "].Field1, [" & Me.txtSalesOrder & "].Field2, [" & Me.txtSalesOrder & "].Field3, [" & Me.txtSalesOrder & "].Field4, [" & Me.txtSalesOrder & "].Field5, [" & Me.txtSalesOrder & "].Field6, [" & Me.txtSalesOrder & "].Field7, [" & Me.txtSalesOrder & "].Field8, [" & Me.txtSalesOrder & "].Field9, [" & Me.txtSalesOrder & "].Field10, [" & Me.txtSalesOrder & "].Field11, [" & Me.txtSalesOrder & "]Field12, [" & Me.txtSalesOrder & "].Field13, [" & Me.txtSalesOrder & "].Field14, [" & Me.txtSalesOrder & "].Field15, [" & Me.txtSalesOrder & "].Field16, [" & Me.txtSalesOrder & "].Field17, [" & Me.txtSalesOrder & "].Field18, [" & Me.txtSalesOrder & "].Field19, [" & Me.txtSalesOrder & "].Field20
    FROM " & Me.txtSalesOrder & " LEFT JOIN crit" & Me.txtSalesOrder & " ON [" & Me.txtSalesOrder & "].Field5= crit" & Me.txtSalesOrder & ".Field5
    WHERE (((crit(" & Me.txtSalesOrder & ").Field5) = [" & Me.txtSalesOrder & "].Field5))";"

    DoCmd.RunSQL strSQL

    This is probably completely the wrong way of doing it though

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: copying info from one table to another (97)

    Here is example code that creates a new table in another database with the merged values from the two tables. It assumes that you have text boxes on the form that specify the variable parts of the SQL string to be assembled. Of course, you must substitute the names you have.

    <table border=1><td>Text box</td><td>Meaning</td><td>Example</td><td>txtDatabase</td><td>Path and filename of target database</td><td>C:AccessOther.mdb</td><td>txtSalesOrder</td><td>Sales order number</td><td align=right>12345</td><td>txtLinkField</td><td>Name of identifier field</td><td>Field5</td></table>
    Note that the _ characters are line continuation characters; there should be a space before them.

    Sub CreateTableInOtherDatabase()
    Dim strSQL As String
    Dim strDatabase As String
    Dim strSmallTable As String
    Dim strLargeTable As String
    Dim strTargetTable As String
    Dim strLinkField As String

    strDatabase = Me.[txtDatabase]
    strLargeTable = Me.[txtSalesOrder]
    strSmallTable = "crit" & strLargeTable
    strTargetTable = strSmallTable
    strLinkField = Me.[txtLinkField]

    strSQL = "SELECT [" & strLargeTable & "].* " & _
    "INTO [" & strTargetTable & "] " & _
    "IN '" & strDatabase & "' " & _
    "FROM [" & strSmallTable & "] INNER JOIN [" & strLargeTable & "] " & _
    "ON [" & strSmallTable & "].[" & strLinkField & "] = " & _
    "[" & strLargeTable & "].[" & strLinkField & "];"

    CurrentDb.Execute strSQL, dbFailOnError
    End Sub

  15. #15
    Lounger
    Join Date
    Jul 2003
    Location
    Dublin, Ireland, Republic of
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copying info from one table to another (97)

    Thanks for that piece of code Hans, I have been out of work the last week so I haven't had a chance to try it yet, but I'll look at it now, thanks again!

Page 1 of 2 12 LastLast

Posting Permissions

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