How to add new fields on the fly

[lwilt]

How can I run a query to update an existing table by adding a new field with new information without having to make a new table everytime?

 

[Joe4]

Update queries can only update values in existing fields, not add new fields.
Make table queries can add as many records as you like, but it creates a new table (not update an existing one).

What exactly are you trying to do and why?
I suspect you may have a design issue/flaw if you need to do this. It sounds like you may not be working with a Normalized table structure.
If we understand exactly what you are trying to do any why, we may be able to offer other solutions.

 

[lwilt]

I have a table with a bunch of customer data in it and I want to be able to take that list and run it against a series of tables with marketing information(different things they were marketed where on part of the customer table would be in each) and add new fields of data to the original table without having to make a new table everytime

 

[Joe4]

What kind of information/data are you trying to add to these tables?
If your tables are designed correctly, you will probably should be adding new records, not new fields.

An example of this that I see a lot is that someone has a table of monthly revenue, so each month they want to add a new field (column) for a particular month's revenue. That is the wrong way to go. Instead of having a field for each month, they should have one field for month name, and one field for month amount. So for a single client, instead of have one record with 12 different month fields, they should have 12 different records.

It may sound counter-intuitive, but designing your database in this manner is actually preferred ("normalized") and makes it more efficient and easier to work with.

For example, what if you had a recipe database, and you had one record for each recipe with 50 different fields for ingredients. Now say that someone comes along and wants to know all the recipes that have chocolate as as ingredient. If you have 50 different ingredient fields, you have to search 50 fields (have fun writing that query!). However, if you only have one ingredient field per record, you only have to search one field.

Do you have a scenario like this going on where you might need to rethink how you are structuring your tables?

 

[lwilt]

I know it will be inefficient trying to do it this way but that is what the boss wants...sounds like I will just be creating a lot of extra work having to do it this way

 

[lwilt]

so for your recipe example it would basically look like:

salad cheese
salad dressing
salad lettuce
pasta sauce
pasta noodles
pasta chicken
pasta Basel

 

[Joe4]

Or more like:

recipe	ingredient_number	ingredient_name
salad	1	cheese
salad	2	dressing
salad	3	lettuce
pasta	1	sauce
pasta	2	noodles
pasta	3	chicken
pasta	4	basil

<caption> Query2 </caption><thead>
</thead><tbody>
</tbody><tfoot></tfoot>

Note that it would require VBA to do it the way you were asking. Not only that, if you plan on doing any Reporting on it, you would probably need to update your Report every time you added a new field.