Append Query

LocalUser

Registered User.
Local time
Yesterday, 21:04
Joined
Apr 24, 2011
Messages
23
Hi All,

I'm designing a database to capture data. I'm going to split my database to front end and back end then put copies of both on CDs to distribute them to my agents (I had to think of using CDs because not all location can access internet). When the CDs are returned I need to collate all gathered data in one Master Database. I tried to use append query to copy data from one database to the master but it didn't work because I have multiple values fields in one of my tables. How can I achieve this ? It's a lot of work if I do it manually copy and paste records.

Any ideas??
 
Avoid use of multiple values fields, they will follow you like a nightmare, nearly no matter what you want to do later.
Can't you use a "child" table instead, (this is more or less what multiple values field is)?
Below is a snip of some code which copy a multiple value from one table to another, for the field "From".
Code:
        Set rsMVold = rst.Fields("From").Value
        Set rsMVnew = rstInsert.Fields("From").Value
        Do While Not rsMVold.EOF
          rsMVnew.AddNew
          rsMVnew.Fields(0) = rsMVold.Fields(0)
          rsMVnew.Update
          rsMVold.MoveNext
        Loop
 
Thanks JHB for the response. I've created my database alreday and can't change the multiple values fields now.
Are you saying that there is no way to work around this?
Can I create a code to copy records from table in one database to the Master database?
 
Thanks JHB for the response. I've created my database alreday and can't change the multiple values fields now.
Are you saying that there is no way to work around this?
Can I create a code to copy records from table in one database to the Master database?
As you already discovered you can't use a update query, (and many other things - like running a normally counting query for selection in the multiply can be a hard work) .
I know you already have build your database and I also know how hard it is to scrap an idea and find another solution, but as long you haven't distribute it, you have the possibility. It is only my opinion. :)
Yes you can build code to copy records - the example I gave you is used to copy data from a multiply field in one table to a multiply field in another table, the field name is "From".
 
Hi JHB,
I'm thinking of the solution you suggested about using a child table. How can I achieve that? In my database users need to choose (5) different options for each category. I have 5 categories in my form. Thanks
 
Remove the multiple value field from the "main" table and make a table on its own to hold what the user choose. Link it to the "main" table.
 
Hi JHB,
Sorry to be a bit slow but how then users will make more than one choice for that firld? what should I do?
 
I want to run an append query i have not worked with append query before in ms-access . The response says that the records weren't added due to key .
 
Hi JHB,
Sorry to be a bit slow but how then users will make more than one choice for that firld? what should I do?
One way to do it is to make a subform with the recordsource set to the table you created and place it on the "main" form.
 
I want to run an append query i have not worked with append query before in ms-access . The response says that the records weren't added due to key .
I could be one of two, you are missing the value for the key-field(s) or you are inserting duplicate values.
Show you query, the data you want to append and the table structure you want to append the data to.
 

Users who are viewing this thread

Back
Top Bottom