Adding Columns to an existing table

[lwilt]

I'm trying to add columns of data onto an existing customer table I have. I tried using an update query setting the Update parameter to the table I want the new columns to be added to when I run the new query but I keep a pop up for entering parameters values so I'm not sure what to do.

please help

 

[lwilt]

or if you have a better way of doing this I'm all ears

 

[Micron]

You can start by posting your sql statement - maybe we can spot something in it. Or you can carefully examine your query in design view, looking for mis-spelled table or field names. Or you have an actual parameter in one or more of your criteria rows which needs to be provided when run.

 

[lwilt]

UPDATE [Product aff_2] SET [Product aff_2].CUST_EMAIL = [Product aff_3], [Product aff_2].u2_id = [Product aff_3], [Product aff_2].CUST_PARENT = [Product aff_3], [Product aff_2].[PA_DISPLAY HARDWARE] = [Product aff_3], [Product aff_2].[PA_LAPEL MAGNET] = [Product aff_3]
WHERE ((([Product aff_2].[PA_DISPLAY HARDWARE])="Y")) OR ((([Product aff_2].[PA_LAPEL MAGNET])="Y"));

There is criteria in the rows where I'm specifially looking for certain info and having those columns added to the existing table but it's in there

 

[Micron]

Sorry, I don't understand this statement.

There is criteria in the rows where I'm specifially looking for certain info and having those columns added to the existing table but it's in there

Also, your terminology is a bit strange to me. Criteria is not in tables - records are. Criteria is what you supply to a query to get the records you want. So are you saying the criteria is in the row of your query? Also, an update query cannot add "columns" (which are called fields in Access, not columns) - it can only update fields that are already there. Even if a field in a record has 'nothing' in it, it is still considered an update when you put something in it.

Perhaps I should have asked you to provide the prompt message you are getting. I suspect it is "[Product aff_3]". It looks like a table name. If so, not only have you not included it in a table join, you cannot only reference the table even if you did.

 

[lwilt]

Sorry about that....I was rushing to a meeting and wanted to reply before I went to that.

You said in your previous post about having parameters in the criteria row which I do have. I was only trying to have the "columns"(Fields) that would match that parameter in the criteria row to get filled in on the existing table I already have. Basically I have a big customer table with blank fields already created and want to fill those fields in if they meet the correct criteria in the criteria row.

Product aff_3 is the table I'm trying to update. I've tried this with that table in the query joined to the table I'm trying to use to update PA3 as well as it not in the query at all and each time it prompts me to enter a parameter value for Product aff_3.

 

[Micron]

You need to join [Product aff_2] to [Product aff_3] on a related field or fields and for each field in [Product aff_2] you want to update, put [Product aff_3].NameOfField ( the field name you want to get values from [Product aff_3]) in the Update To: row of the query, not in the criteria rows. As mentioned, criteria field is for filtering records - not for supplying values to any update or append query. Do this on a copy of your query and table (to test) before making any permanent changes to these objects. Also, you can preview the results of an append query by switching to datasheet view first. Be advised that when you do this for a table with no values in the fields, the query will display a bunch of blank records to be updated. At least it will give you an idea of how many records will be affected, or if it even runs.