Adding Fields With Make Table

  • Thread starter Thread starter Joe Gieder
  • Start date Start date
J

Joe Gieder

I'm trying to crreate a new table by using a make table
query and was wondering; is there a way to add the new
fields I want through the query or can I only do it
through the design of the table itself? I tried and came
up the names of the fields as expr1, expr2, expr3 and so
on.

Any help is appreciated.

TIA
Joe
 
Joe

For testing purposes, change your make-table query back to a select query.
Take a look at the "name" of each of the fields -- you will see the
"Expr1:", "Expr2:", etc. in front of some of the fields.

Modify these "field names" to be what you want (?e.g., "MyFirstField",
"ShipDate", etc.). Be sure to keep the colon and space. You can do this
naming process with ANY field in the query definition.

Run the (select) query -- you should have the new field names. When it's
working the way you want, convert it back to a make-table query.

Now the down-side ... Access decides what field types and sizes you need
when you do a make-table query. If you want more control, you can use a
make-table query to start with, then modify the table definition to be what
you REALLY need, then convert your make-table query to an append query.

You may need to modify your (append) query to convert fields to the
appropriate type (see CStr(), CDbl(), and other conversion-related
functions).
 
You can make any new fields you want in a make table query.
Suppose you wanted to make a calculated field based on 2
fields in your existing table:

Ex. Calcfld:fld1 * fld2

You would end up with a column called Calcfld in your new
table.

Expr1, Expr2, etc. are default names assigned by Access
when you don't supply a name.
 
Thank you for the help.

I have another question now. After I created the table
with the fields required I want to join two tables
together and update the new table with informaition from
the old table. What I have is two fields one called
Material and the other Plant. Each record under Material
may contain a duplicated material number but a different
plant location, what I'm trying to do is have only one
occurance of the material number (I did this with a
groupby) and update each of the new fields with an "X" if
the material exists at that plant (there are 17 different
plant locations so i created 17 fields, some material is
in only one plant but others may be in all 17). I tried
to do an update query by adding all 17 plants to the
fields and then in the criteria for each different plant
saying [marcmay404]![plant]="ac01", [marcmay404]![plant]
="ac02" respectively and so on for all 17 but it came
back saying 0 records to update (I have 265,000 records).
Can this be done all at one time or do I need to do it
one field at a time?

Thanks again
Joe
 
Create a new query. Pull in your tables and join them.
Then, change the query to an update query. Include the
fields that you want to update from your new table.
In the "Update To" fields, you want to do something like
you proposed

iif([marcmay404]![plant]="ac01","X","")
-----Original Message-----
Thank you for the help.

I have another question now. After I created the table
with the fields required I want to join two tables
together and update the new table with informaition from
the old table. What I have is two fields one called
Material and the other Plant. Each record under Material
may contain a duplicated material number but a different
plant location, what I'm trying to do is have only one
occurance of the material number (I did this with a
groupby) and update each of the new fields with an "X" if
the material exists at that plant (there are 17 different
plant locations so i created 17 fields, some material is
in only one plant but others may be in all 17). I tried
to do an update query by adding all 17 plants to the
fields and then in the criteria for each different plant
saying [marcmay404]![plant]="ac01", [marcmay404]![plant]
="ac02" respectively and so on for all 17 but it came
back saying 0 records to update (I have 265,000 records).
Can this be done all at one time or do I need to do it
one field at a time?

Thanks again
Joe







-----Original Message-----
I'm trying to crreate a new table by using a make table
query and was wondering; is there a way to add the new
fields I want through the query or can I only do it
through the design of the table itself? I tried and came
up the names of the fields as expr1, expr2, expr3 and so
on.

Any help is appreciated.

TIA
Joe
.
Click to expand...
.
Click to expand...
 
I tried what you suggested and I get an error saying
220,212 records not updated due to validation rule
violations. I looked at the structure of the fields in
both tables and the field types are all text. Do you have
any ideas what I might try?

Thanks
Joe
-----Original Message-----
Create a new query. Pull in your tables and join them.
Then, change the query to an update query. Include the
fields that you want to update from your new table.
In the "Update To" fields, you want to do something like
you proposed

iif([marcmay404]![plant]="ac01","X","")
-----Original Message-----
Thank you for the help.

I have another question now. After I created the table
with the fields required I want to join two tables
together and update the new table with informaition from
the old table. What I have is two fields one called
Material and the other Plant. Each record under Material
may contain a duplicated material number but a different
plant location, what I'm trying to do is have only one
occurance of the material number (I did this with a
groupby) and update each of the new fields with an "X" if
the material exists at that plant (there are 17 different
plant locations so i created 17 fields, some material is
in only one plant but others may be in all 17). I tried
to do an update query by adding all 17 plants to the
fields and then in the criteria for each different plant
saying [marcmay404]![plant]="ac01", [marcmay404]![plant]
="ac02" respectively and so on for all 17 but it came
back saying 0 records to update (I have 265,000 records).
Can this be done all at one time or do I need to do it
one field at a time?

Thanks again
Joe







-----Original Message-----
I'm trying to crreate a new table by using a make table
query and was wondering; is there a way to add the new
fields I want through the query or can I only do it
through the design of the table itself? I tried and came
up the names of the fields as expr1, expr2, expr3 and so
on.

Any help is appreciated.

TIA
Joe
.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Joe,
I finally had a chance to thoroughly read what you were
trying to do.
I think you can accomplish it all in 1 query.

Create a new query (or modify your existing one). Pull
your material field into the design grid. Then, have your
17 new columns like before.

I made a test query with fields like this:

acc1: IIF([plant]="ac01","X","O") thru
acc17:IIF([plant]="ac17","X","O")

I just wanted to have a value in each column. X or O
Change this query to a totals query. Group by material,
and change your 17 fields from group by to max.

Once that's done, change the query to a make table query.
I think this should give you what you're looking for.
-----Original Message-----
I tried what you suggested and I get an error saying
220,212 records not updated due to validation rule
violations. I looked at the structure of the fields in
both tables and the field types are all text. Do you have
any ideas what I might try?

Thanks
Joe
-----Original Message-----
Create a new query. Pull in your tables and join them.
Then, change the query to an update query. Include the
fields that you want to update from your new table.
In the "Update To" fields, you want to do something like
you proposed

iif([marcmay404]![plant]="ac01","X","")
-----Original Message-----
Thank you for the help.

I have another question now. After I created the table
with the fields required I want to join two tables
together and update the new table with informaition from
the old table. What I have is two fields one called
Material and the other Plant. Each record under Material
may contain a duplicated material number but a different
plant location, what I'm trying to do is have only one
occurance of the material number (I did this with a
groupby) and update each of the new fields with an "X" if
the material exists at that plant (there are 17 different
plant locations so i created 17 fields, some material is
in only one plant but others may be in all 17). I tried
to do an update query by adding all 17 plants to the
fields and then in the criteria for each different plant
saying [marcmay404]![plant]="ac01", [marcmay404]![plant]
="ac02" respectively and so on for all 17 but it came
back saying 0 records to update (I have 265,000 records).
Can this be done all at one time or do I need to do it
one field at a time?

Thanks again
Joe








-----Original Message-----
I'm trying to crreate a new table by using a make table
query and was wondering; is there a way to add the new
fields I want through the query or can I only do it
through the design of the table itself? I tried and came
up the names of the fields as expr1, expr2, expr3 and so
on.

Any help is appreciated.

TIA
Joe
.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Thanks Les. It works great. I need to understand Access
more, I need to create a pricing program on day and hope
to be able to make it do what I need it to.

Joe
-----Original Message-----
Joe,
I finally had a chance to thoroughly read what you were
trying to do.
I think you can accomplish it all in 1 query.

Create a new query (or modify your existing one). Pull
your material field into the design grid. Then, have your
17 new columns like before.

I made a test query with fields like this:

acc1: IIF([plant]="ac01","X","O") thru
acc17:IIF([plant]="ac17","X","O")

I just wanted to have a value in each column. X or O
Change this query to a totals query. Group by material,
and change your 17 fields from group by to max.

Once that's done, change the query to a make table query.
I think this should give you what you're looking for.
-----Original Message-----
I tried what you suggested and I get an error saying
220,212 records not updated due to validation rule
violations. I looked at the structure of the fields in
both tables and the field types are all text. Do you have
any ideas what I might try?

Thanks
Joe
-----Original Message-----
Create a new query. Pull in your tables and join them.
Then, change the query to an update query. Include the
fields that you want to update from your new table.
In the "Update To" fields, you want to do something like
you proposed

iif([marcmay404]![plant]="ac01","X","")
-----Original Message-----
Thank you for the help.

I have another question now. After I created the table
with the fields required I want to join two tables
together and update the new table with informaition from
the old table. What I have is two fields one called
Material and the other Plant. Each record under Material
may contain a duplicated material number but a different
plant location, what I'm trying to do is have only one
occurance of the material number (I did this with a
groupby) and update each of the new fields with
Click to expand...
an "X"
if
the material exists at that plant (there are 17 different
plant locations so i created 17 fields, some material is
in only one plant but others may be in all 17). I tried
to do an update query by adding all 17 plants to the
fields and then in the criteria for each different plant
saying [marcmay404]![plant]="ac01", [marcmay404]! [plant]
="ac02" respectively and so on for all 17 but it came
back saying 0 records to update (I have 265,000 records).
Can this be done all at one time or do I need to do it
one field at a time?

Thanks again
Joe








-----Original Message-----
I'm trying to crreate a new table by using a make table
query and was wondering; is there a way to add the new
fields I want through the query or can I only do it
through the design of the table itself? I tried and came
up the names of the fields as expr1, expr2, expr3
Click to expand...
and
so
on.

Any help is appreciated.

TIA
Joe
.

.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Glad that helped you out. Good luck with your pricing
program. People here are always very helpful if you get
stuck.
-----Original Message-----
Thanks Les. It works great. I need to understand Access
more, I need to create a pricing program on day and hope
to be able to make it do what I need it to.

Joe
-----Original Message-----
Joe,
I finally had a chance to thoroughly read what you were
trying to do.
I think you can accomplish it all in 1 query.

Create a new query (or modify your existing one). Pull
your material field into the design grid. Then, have your
17 new columns like before.

I made a test query with fields like this:

acc1: IIF([plant]="ac01","X","O") thru
acc17:IIF([plant]="ac17","X","O")

I just wanted to have a value in each column. X or O
Change this query to a totals query. Group by material,
and change your 17 fields from group by to max.

Once that's done, change the query to a make table query.
I think this should give you what you're looking for.
-----Original Message-----
I tried what you suggested and I get an error saying
220,212 records not updated due to validation rule
violations. I looked at the structure of the fields in
both tables and the field types are all text. Do you have
any ideas what I might try?

Thanks
Joe
-----Original Message-----
Create a new query. Pull in your tables and join them.
Then, change the query to an update query. Include the
fields that you want to update from your new table.
In the "Update To" fields, you want to do something like
you proposed

iif([marcmay404]![plant]="ac01","X","")
-----Original Message-----
Thank you for the help.

I have another question now. After I created the table
with the fields required I want to join two tables
together and update the new table with informaition
from
the old table. What I have is two fields one called
Material and the other Plant. Each record under
Material
may contain a duplicated material number but a
different
plant location, what I'm trying to do is have only one
occurance of the material number (I did this with a
groupby) and update each of the new fields with an "X"
if
the material exists at that plant (there are 17
different
plant locations so i created 17 fields, some material
is
in only one plant but others may be in all 17). I tried
to do an update query by adding all 17 plants to the
fields and then in the criteria for each different
plant
saying [marcmay404]![plant]="ac01", [marcmay404]! [plant]
="ac02" respectively and so on for all 17 but it came
back saying 0 records to update (I have 265,000
records).
Can this be done all at one time or do I need to do it
one field at a time?

Thanks again
Joe








-----Original Message-----
I'm trying to crreate a new table by using a make
table
query and was wondering; is there a way to add the new
fields I want through the query or can I only do it
through the design of the table itself? I tried and
came
up the names of the fields as expr1, expr2, expr3 and
so
on.

Any help is appreciated.

TIA
Joe
.

.

.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Back
Top