append query (version 2000)

Topic

New Reply

I have created a combo box on a form. When the item is selected from the combo box – it updates several fields on my form. I want to next write only those things now showing (my selected item from the combo list along with the updated fields) to a new table by way of an append query. However, when I have the form do this step it wants to append all the records of the old table into my table instead of just the items I selected. I know that my joined properties are incorrect on my table. But I’m not sure how to get it to do what I am asking since the joined properties do not appear to give me this option? Please help?

Reply | Quote

Replies

We need additional information. Please explain:

1. Tell in plain words what your aim is (e.g. I want to add a new client to a table of clients)
2. Describe the tables involved – primary keys and other major fields
3. How are they joined?
4. You say “append…… just the items I selected”. Is it about records on a subform or a filtered main form?
5. Show your append query

Reply | Quote

1.2. – We create new items a couple of times a year. These new items are often made up of parts that are used on already existing items. I want to be able to enter a new item# and then be able to choose from my combo box a part# that will make up this item. when I bring up the part# I want it to retrieve all information regarding that part (who makes it, how may we have on hand …) After all this information is displayed on the screen. I want to have all this information saved onto a new table.

3. how are they joined? In the relationship fields – the two tables were joined so one would basically copy to the other. But I do not want that to happen but in the relationships between tables. I do not see an option to append just one record.

4. append — append records from one table to the other table.

5. I ‘m not sure how to give you a copy of my append query

Reply | Quote

It’s not a good idea to store the same information in more than one table in your database – unless you have a very good reason. Having the part number with the item number you should be able to retieve the other information from the parts table any time you want. It’s OK to display the other information about each part on the form but why do you want to store all of that information again in a new table?

Reply | Quote

Hi Karen,

It seems to me that you really need two tables, one for parts and one for items. If by chance they are really the same thing you could get along with one table using self joins through a linking table, but if each item always contains one or more parts then I would probably split things into two tables.

Then you would need a third table with only two fields, an item ID and a part ID. Each record would indicate that a specific part was used by a specific item. In this structure you don’t need to copy data from one table to another. To display the info you want, you simply create a subform on the main form that shows the parts that are used.

As to how to post a query, the simplest way is to display the query design and then click the mode button to select the SQL view. From there you can copy and paste the SQL statement into a post. Hope this helps.

Reply | Quote