Transferring Field from Existing Table/limitations and change of d

[Inuchan]

Thank you in advance for your help! I have two Excel spreadsheets that I
successfully imported into Access 2003 and created tables for. I need to add
the field from one table to the other, but there is not a direct match in the
relationship. The large table uses the Employee ID as the primary key. The
smaller table contains one field that lists a subset of these Employee ID
numbers (a selection of certain employees). I need to transfer this field to
the larger table, but I do not know how to tell Access to match up the
corresponding numbers (i.e., the large table lists all employees, but the
added field will only match up with those employees who appear in it.) In
addition, I need to change the value of the data in the added field from its
original number form to a "yes." Therefore, the "yes" would appear in the
added field in the record that corresponds with the appropriate Employee.
Any help would be greatly appreciated. I'm fairly new at this program and
feel that there has to be an easier way to do this than just adding a field
and hand keying all of the "yes" entries with the corresponding numbers.
THANK YOU!

 

[Wayne-I-M]

Hi

A little bit more information is needed (for me - someone else may understand)

You hve 2 tables - large table and small table
What are the names if the tables

In the large table you have a primary field
Is this unique (ie. autonumber)

In the small table you have a number of records that have the same primary
field (???) or do you have records with a field that correceponds to the
primary field in the large table - sorry I'm lost with this bit.

Wha field do you want to add to the large table from the small table
Field names would give you a better answer

The field that you want to add to the large table from the smaller table is
a number (???) and you want to change this to the test string "yes".
Sorry lost with this as well. Is it a yes/no (0/-1) format or something
else. If it's a number do you want to change the numbers or are the all the
same
Sorry I just don't understand this bit - can you give more info.

 

[John W. Vinson]

Thank you in advance for your help! I have two Excel spreadsheets that I
successfully imported into Access 2003 and created tables for. I need to add
the field from one table to the other, but there is not a direct match in the
relationship. The large table uses the Employee ID as the primary key. The
smaller table contains one field that lists a subset of these Employee ID
numbers (a selection of certain employees). I need to transfer this field to
the larger table, but I do not know how to tell Access to match up the
corresponding numbers (i.e., the large table lists all employees, but the
added field will only match up with those employees who appear in it.) In
addition, I need to change the value of the data in the added field from its
original number form to a "yes." Therefore, the "yes" would appear in the
added field in the record that corresponds with the appropriate Employee.
Any help would be greatly appreciated. I'm fairly new at this program and
feel that there has to be an easier way to do this than just adding a field
and hand keying all of the "yes" entries with the corresponding numbers.
THANK YOU!

You may still be in a spreadsheet mindset. If all you need is a report, or a
display, listing a YES for all employees who match in the two tables, you
don't need any new fields in either table (and in fact should not create such
a redundant field at all!)

Instead, create a Query joining the two tables on this ID. Select whatever
fields you want to see from each query. This query will show only those
employees who appear in both tables.

If you want to see all the employees, with a Yes/No field showing Yes for
those who match in the smaller table, select the Join line in the query design
window and choose option 2 (or maybe 3 depending on how you created the
query): Show All Records in <employees> and matching records in <matching
table>. In a vacant Field cell type something like

InSmallTable: Not IsNull([smalltable].[Employee ID])

This field will be YES if there is a matching record, NO otherwise.

John W. Vinson [MVP]

 

[Inuchan]

Thank you both so much for your help! Alas, I have been directed to insert
the field from the small table so that it exists in the table but in the
different form of a yes response for the matching information. To clarify,
the "Employees" table is the large table and has a primary key of Employee
ID. The "Bad Rating" table consists of one field, "Bad Rating," that is a
list of employee ID numbers of those employees who received a poor rating.
This list of numbers is what needs to be added to the "Employees" table, but
instead of listing the ID numbers, it needs to correspond with the "Employee
ID" field and return a "Yes" for those numbers that were in the "Bad Rating"
field.

Does this field need to be renamed "Employee ID" so that it can link with
the "Employee ID" field in the "Employees" table?

I'm going to try the query suggested below so that I may view all employees,
but I had one question (not to sound like an idiot - I'm just an amateur!):
the formula I am to type in a vacant field cell should go in the field where
I want the "Yes" response to show up?

Once again, I cannot thank you enough for your help. Going to try
suggestions now!

Thank you in advance for your help! I have two Excel spreadsheets that I
successfully imported into Access 2003 and created tables for. I need to add
the field from one table to the other, but there is not a direct match in the
relationship. The large table uses the Employee ID as the primary key. The
smaller table contains one field that lists a subset of these Employee ID
numbers (a selection of certain employees). I need to transfer this field to
the larger table, but I do not know how to tell Access to match up the
corresponding numbers (i.e., the large table lists all employees, but the
added field will only match up with those employees who appear in it.) In
addition, I need to change the value of the data in the added field from its
original number form to a "yes." Therefore, the "yes" would appear in the
added field in the record that corresponds with the appropriate Employee.
Any help would be greatly appreciated. I'm fairly new at this program and
feel that there has to be an easier way to do this than just adding a field
and hand keying all of the "yes" entries with the corresponding numbers.
THANK YOU!

You may still be in a spreadsheet mindset. If all you need is a report, or a
display, listing a YES for all employees who match in the two tables, you
don't need any new fields in either table (and in fact should not create such
a redundant field at all!)

Instead, create a Query joining the two tables on this ID. Select whatever
fields you want to see from each query. This query will show only those
employees who appear in both tables.

If you want to see all the employees, with a Yes/No field showing Yes for
those who match in the smaller table, select the Join line in the query design
window and choose option 2 (or maybe 3 depending on how you created the
query): Show All Records in <employees> and matching records in <matching
table>. In a vacant Field cell type something like

InSmallTable: Not IsNull([smalltable].[Employee ID])

This field will be YES if there is a matching record, NO otherwise.

John W. Vinson [MVP]

 

[Wayne-I-M]

Try something like this

UPDATE tblBadRating, tblEmployee SET tblEmployee.BadRating = "Yes"
WHERE ((([tblEmployee]![EmployeeID])=[tblBadRating]![EmployeeID]));


--
Wayne
Manchester, England.

Thank you both so much for your help! Alas, I have been directed to insert
the field from the small table so that it exists in the table but in the
different form of a yes response for the matching information. To clarify,
the "Employees" table is the large table and has a primary key of Employee
ID. The "Bad Rating" table consists of one field, "Bad Rating," that is a
list of employee ID numbers of those employees who received a poor rating.
This list of numbers is what needs to be added to the "Employees" table, but
instead of listing the ID numbers, it needs to correspond with the "Employee
ID" field and return a "Yes" for those numbers that were in the "Bad Rating"
field.

Does this field need to be renamed "Employee ID" so that it can link with
the "Employee ID" field in the "Employees" table?

I'm going to try the query suggested below so that I may view all employees,
but I had one question (not to sound like an idiot - I'm just an amateur!):
the formula I am to type in a vacant field cell should go in the field where
I want the "Yes" response to show up?

Once again, I cannot thank you enough for your help. Going to try
suggestions now!

Thank you in advance for your help! I have two Excel spreadsheets that I
successfully imported into Access 2003 and created tables for. I need to add
the field from one table to the other, but there is not a direct match in the
relationship. The large table uses the Employee ID as the primary key. The
smaller table contains one field that lists a subset of these Employee ID
numbers (a selection of certain employees). I need to transfer this field to
the larger table, but I do not know how to tell Access to match up the
corresponding numbers (i.e., the large table lists all employees, but the
added field will only match up with those employees who appear in it.) In
addition, I need to change the value of the data in the added field from its
original number form to a "yes." Therefore, the "yes" would appear in the
added field in the record that corresponds with the appropriate Employee.
Any help would be greatly appreciated. I'm fairly new at this program and
feel that there has to be an easier way to do this than just adding a field
and hand keying all of the "yes" entries with the corresponding numbers.
THANK YOU!

You may still be in a spreadsheet mindset. If all you need is a report, or a
display, listing a YES for all employees who match in the two tables, you
don't need any new fields in either table (and in fact should not create such
a redundant field at all!)

Instead, create a Query joining the two tables on this ID. Select whatever
fields you want to see from each query. This query will show only those
employees who appear in both tables.

If you want to see all the employees, with a Yes/No field showing Yes for
those who match in the smaller table, select the Join line in the query design
window and choose option 2 (or maybe 3 depending on how you created the
query): Show All Records in <employees> and matching records in <matching
table>. In a vacant Field cell type something like

InSmallTable: Not IsNull([smalltable].[Employee ID])

This field will be YES if there is a matching record, NO otherwise.

John W. Vinson [MVP]

 

[Inuchan]

Thank you, Wayne.
If you wouldn't mind, can you give me a bit more instruction on where to
type the below formula? I tried doing it in SQL view of a query, but I keep
getting errors.
Thank you for your patience!

Try something like this

UPDATE tblBadRating, tblEmployee SET tblEmployee.BadRating = "Yes"
WHERE ((([tblEmployee]![EmployeeID])=[tblBadRating]![EmployeeID]));


--
Wayne
Manchester, England.

Thank you both so much for your help! Alas, I have been directed to insert
the field from the small table so that it exists in the table but in the
different form of a yes response for the matching information. To clarify,
the "Employees" table is the large table and has a primary key of Employee
ID. The "Bad Rating" table consists of one field, "Bad Rating," that is a
list of employee ID numbers of those employees who received a poor rating.
This list of numbers is what needs to be added to the "Employees" table, but
instead of listing the ID numbers, it needs to correspond with the "Employee
ID" field and return a "Yes" for those numbers that were in the "Bad Rating"
field.

Does this field need to be renamed "Employee ID" so that it can link with
the "Employee ID" field in the "Employees" table?

I'm going to try the query suggested below so that I may view all employees,
but I had one question (not to sound like an idiot - I'm just an amateur!):
the formula I am to type in a vacant field cell should go in the field where
I want the "Yes" response to show up?

Once again, I cannot thank you enough for your help. Going to try
suggestions now!

On Sat, 12 Jan 2008 08:27:00 -0800, Inuchan

Thank you in advance for your help! I have two Excel spreadsheets that I
successfully imported into Access 2003 and created tables for. I need to add
the field from one table to the other, but there is not a direct match in the
relationship. The large table uses the Employee ID as the primary key. The
smaller table contains one field that lists a subset of these Employee ID
numbers (a selection of certain employees). I need to transfer this field to
the larger table, but I do not know how to tell Access to match up the
corresponding numbers (i.e., the large table lists all employees, but the
added field will only match up with those employees who appear in it.) In
addition, I need to change the value of the data in the added field from its
original number form to a "yes." Therefore, the "yes" would appear in the
added field in the record that corresponds with the appropriate Employee.
Any help would be greatly appreciated. I'm fairly new at this program and
feel that there has to be an easier way to do this than just adding a field
and hand keying all of the "yes" entries with the corresponding numbers.
THANK YOU!

You may still be in a spreadsheet mindset. If all you need is a report, or a
display, listing a YES for all employees who match in the two tables, you
don't need any new fields in either table (and in fact should not create such
a redundant field at all!)

Instead, create a Query joining the two tables on this ID. Select whatever
fields you want to see from each query. This query will show only those
employees who appear in both tables.

If you want to see all the employees, with a Yes/No field showing Yes for
those who match in the smaller table, select the Join line in the query design
window and choose option 2 (or maybe 3 depending on how you created the
query): Show All Records in <employees> and matching records in <matching
table>. In a vacant Field cell type something like

InSmallTable: Not IsNull([smalltable].[Employee ID])

This field will be YES if there is a matching record, NO otherwise.

John W. Vinson [MVP]

 

[Wayne-I-M]

Sorry my fault. I used made-up names.

1st MAKE A BACKUP of your DB.

I have assumed this
You have a table called tblEmpoyee
This is the large table

You have a table called
tblBadRating
This is the small

In tblBadRating you have a field that holds numbers that are the same as
"some" of the record primary field in tblEmployee

You want to add the word (text) "Yes" to tblEmployee for each record in
tblBadRating

---------------------------------
Add a field to tblEmployee (or whatever your large table is called)
Call this new field BadRating


Open a new query and insert the sql "then change each of the names of the
tables" from;

tblEmployee to the real name of your large table
tblBadRating to the ral name of your smaller table

UPDATE tblBadRating, tblEmployee SET tblEmployee.BadRating = "Yes"
WHERE ((([tblEmployee]![EmployeeID])=[tblBadRating]![EmployeeID]));

Then run this (click the red apostraphy - or just save the query and then
open it again)

Hope this helps



--
Wayne
Manchester, England.

Thank you, Wayne.
If you wouldn't mind, can you give me a bit more instruction on where to
type the below formula? I tried doing it in SQL view of a query, but I keep
getting errors.
Thank you for your patience!

Try something like this

UPDATE tblBadRating, tblEmployee SET tblEmployee.BadRating = "Yes"
WHERE ((([tblEmployee]![EmployeeID])=[tblBadRating]![EmployeeID]));


--
Wayne
Manchester, England.

Thank you both so much for your help! Alas, I have been directed to insert
the field from the small table so that it exists in the table but in the
different form of a yes response for the matching information. To clarify,
the "Employees" table is the large table and has a primary key of Employee
ID. The "Bad Rating" table consists of one field, "Bad Rating," that is a
list of employee ID numbers of those employees who received a poor rating.
This list of numbers is what needs to be added to the "Employees" table, but
instead of listing the ID numbers, it needs to correspond with the "Employee
ID" field and return a "Yes" for those numbers that were in the "Bad Rating"
field.

Does this field need to be renamed "Employee ID" so that it can link with
the "Employee ID" field in the "Employees" table?

I'm going to try the query suggested below so that I may view all employees,
but I had one question (not to sound like an idiot - I'm just an amateur!):
the formula I am to type in a vacant field cell should go in the field where
I want the "Yes" response to show up?

Once again, I cannot thank you enough for your help. Going to try
suggestions now!


:

On Sat, 12 Jan 2008 08:27:00 -0800, Inuchan

Thank you in advance for your help! I have two Excel spreadsheets that I
successfully imported into Access 2003 and created tables for. I need to add
the field from one table to the other, but there is not a direct match in the
relationship. The large table uses the Employee ID as the primary key. The
smaller table contains one field that lists a subset of these Employee ID
numbers (a selection of certain employees). I need to transfer this field to
the larger table, but I do not know how to tell Access to match up the
corresponding numbers (i.e., the large table lists all employees, but the
added field will only match up with those employees who appear in it.) In
addition, I need to change the value of the data in the added field from its
original number form to a "yes." Therefore, the "yes" would appear in the
added field in the record that corresponds with the appropriate Employee.
Any help would be greatly appreciated. I'm fairly new at this program and
feel that there has to be an easier way to do this than just adding a field
and hand keying all of the "yes" entries with the corresponding numbers.
THANK YOU!

You may still be in a spreadsheet mindset. If all you need is a report, or a
display, listing a YES for all employees who match in the two tables, you
don't need any new fields in either table (and in fact should not create such
a redundant field at all!)

Instead, create a Query joining the two tables on this ID. Select whatever
fields you want to see from each query. This query will show only those
employees who appear in both tables.

If you want to see all the employees, with a Yes/No field showing Yes for
those who match in the smaller table, select the Join line in the query design
window and choose option 2 (or maybe 3 depending on how you created the
query): Show All Records in <employees> and matching records in <matching
table>. In a vacant Field cell type something like

InSmallTable: Not IsNull([smalltable].[Employee ID])

This field will be YES if there is a matching record, NO otherwise.

John W. Vinson [MVP]

 

[Inuchan]

No errors this time, but it is now asking for a parameter value for
Employee.BadRating, then for Employee!EmployeeID and BadRating!EmployeeID.
Any advice?

THANK YOU so very much for the great step-by-step in your last post.

Sorry my fault. I used made-up names.

1st MAKE A BACKUP of your DB.

I have assumed this
You have a table called tblEmpoyee
This is the large table

You have a table called
tblBadRating
This is the small

In tblBadRating you have a field that holds numbers that are the same as
"some" of the record primary field in tblEmployee

You want to add the word (text) "Yes" to tblEmployee for each record in
tblBadRating

---------------------------------
Add a field to tblEmployee (or whatever your large table is called)
Call this new field BadRating


Open a new query and insert the sql "then change each of the names of the
tables" from;

tblEmployee to the real name of your large table
tblBadRating to the ral name of your smaller table

UPDATE tblBadRating, tblEmployee SET tblEmployee.BadRating = "Yes"
WHERE ((([tblEmployee]![EmployeeID])=[tblBadRating]![EmployeeID]));

Then run this (click the red apostraphy - or just save the query and then
open it again)

Hope this helps



--
Wayne
Manchester, England.

Thank you, Wayne.
If you wouldn't mind, can you give me a bit more instruction on where to
type the below formula? I tried doing it in SQL view of a query, but I keep
getting errors.
Thank you for your patience!

Try something like this

UPDATE tblBadRating, tblEmployee SET tblEmployee.BadRating = "Yes"
WHERE ((([tblEmployee]![EmployeeID])=[tblBadRating]![EmployeeID]));


--
Wayne
Manchester, England.



:

Thank you both so much for your help! Alas, I have been directed to insert
the field from the small table so that it exists in the table but in the
different form of a yes response for the matching information. To clarify,
the "Employees" table is the large table and has a primary key of Employee
ID. The "Bad Rating" table consists of one field, "Bad Rating," that is a
list of employee ID numbers of those employees who received a poor rating.
This list of numbers is what needs to be added to the "Employees" table, but
instead of listing the ID numbers, it needs to correspond with the "Employee
ID" field and return a "Yes" for those numbers that were in the "Bad Rating"
field.

Does this field need to be renamed "Employee ID" so that it can link with
the "Employee ID" field in the "Employees" table?

I'm going to try the query suggested below so that I may view all employees,
but I had one question (not to sound like an idiot - I'm just an amateur!):
the formula I am to type in a vacant field cell should go in the field where
I want the "Yes" response to show up?

Once again, I cannot thank you enough for your help. Going to try
suggestions now!


:

On Sat, 12 Jan 2008 08:27:00 -0800, Inuchan

Thank you in advance for your help! I have two Excel spreadsheets that I
successfully imported into Access 2003 and created tables for. I need to add
the field from one table to the other, but there is not a direct match in the
relationship. The large table uses the Employee ID as the primary key. The
smaller table contains one field that lists a subset of these Employee ID
numbers (a selection of certain employees). I need to transfer this field to
the larger table, but I do not know how to tell Access to match up the
corresponding numbers (i.e., the large table lists all employees, but the
added field will only match up with those employees who appear in it.) In
addition, I need to change the value of the data in the added field from its
original number form to a "yes." Therefore, the "yes" would appear in the
added field in the record that corresponds with the appropriate Employee.
Any help would be greatly appreciated. I'm fairly new at this program and
feel that there has to be an easier way to do this than just adding a field
and hand keying all of the "yes" entries with the corresponding numbers.
THANK YOU!

You may still be in a spreadsheet mindset. If all you need is a report, or a
display, listing a YES for all employees who match in the two tables, you
don't need any new fields in either table (and in fact should not create such
a redundant field at all!)

Instead, create a Query joining the two tables on this ID. Select whatever
fields you want to see from each query. This query will show only those
employees who appear in both tables.

If you want to see all the employees, with a Yes/No field showing Yes for
those who match in the smaller table, select the Join line in the query design
window and choose option 2 (or maybe 3 depending on how you created the
query): Show All Records in <employees> and matching records in <matching
table>. In a vacant Field cell type something like

InSmallTable: Not IsNull([smalltable].[Employee ID])

This field will be YES if there is a matching record, NO otherwise.

John W. Vinson [MVP]

 

[Wayne-I-M]

Have you added the field BadRating to the Employee table (the large table).
Have you changed the sql to show the real name of the tables in your DB
Is your primary field in the small table called EmployeeID

I don't have all the correct tables names and field names so I can't write
the sql here - just give you an example - you need to alter the sql to get it
to work in your DB.

If you post the real field names and table name then someone will be able to
quicky give you an update query but you should try and just change the snipet
I gave to show the real names 1st. The more you do on this type of stuff the
simpler it gets.

Give it a go.



--
Wayne
Manchester, England.

No errors this time, but it is now asking for a parameter value for
Employee.BadRating, then for Employee!EmployeeID and BadRating!EmployeeID.
Any advice?

THANK YOU so very much for the great step-by-step in your last post.

Sorry my fault. I used made-up names.

1st MAKE A BACKUP of your DB.

I have assumed this
You have a table called tblEmpoyee
This is the large table

You have a table called
tblBadRating
This is the small

In tblBadRating you have a field that holds numbers that are the same as
"some" of the record primary field in tblEmployee

You want to add the word (text) "Yes" to tblEmployee for each record in
tblBadRating

---------------------------------
Add a field to tblEmployee (or whatever your large table is called)
Call this new field BadRating


Open a new query and insert the sql "then change each of the names of the
tables" from;

tblEmployee to the real name of your large table
tblBadRating to the ral name of your smaller table

UPDATE tblBadRating, tblEmployee SET tblEmployee.BadRating = "Yes"
WHERE ((([tblEmployee]![EmployeeID])=[tblBadRating]![EmployeeID]));

Then run this (click the red apostraphy - or just save the query and then
open it again)

Hope this helps



--
Wayne
Manchester, England.

Thank you, Wayne.
If you wouldn't mind, can you give me a bit more instruction on where to
type the below formula? I tried doing it in SQL view of a query, but I keep
getting errors.
Thank you for your patience!

:

Try something like this

UPDATE tblBadRating, tblEmployee SET tblEmployee.BadRating = "Yes"
WHERE ((([tblEmployee]![EmployeeID])=[tblBadRating]![EmployeeID]));


--
Wayne
Manchester, England.



:

Thank you both so much for your help! Alas, I have been directed to insert
the field from the small table so that it exists in the table but in the
different form of a yes response for the matching information. To clarify,
the "Employees" table is the large table and has a primary key of Employee
ID. The "Bad Rating" table consists of one field, "Bad Rating," that is a
list of employee ID numbers of those employees who received a poor rating.
This list of numbers is what needs to be added to the "Employees" table, but
instead of listing the ID numbers, it needs to correspond with the "Employee
ID" field and return a "Yes" for those numbers that were in the "Bad Rating"
field.

Does this field need to be renamed "Employee ID" so that it can link with
the "Employee ID" field in the "Employees" table?

I'm going to try the query suggested below so that I may view all employees,
but I had one question (not to sound like an idiot - I'm just an amateur!):
the formula I am to type in a vacant field cell should go in the field where
I want the "Yes" response to show up?

Once again, I cannot thank you enough for your help. Going to try
suggestions now!


:

On Sat, 12 Jan 2008 08:27:00 -0800, Inuchan

Thank you in advance for your help! I have two Excel spreadsheets that I
successfully imported into Access 2003 and created tables for. I need to add
the field from one table to the other, but there is not a direct match in the
relationship. The large table uses the Employee ID as the primary key. The
smaller table contains one field that lists a subset of these Employee ID
numbers (a selection of certain employees). I need to transfer this field to
the larger table, but I do not know how to tell Access to match up the
corresponding numbers (i.e., the large table lists all employees, but the
added field will only match up with those employees who appear in it.) In
addition, I need to change the value of the data in the added field from its
original number form to a "yes." Therefore, the "yes" would appear in the
added field in the record that corresponds with the appropriate Employee.
Any help would be greatly appreciated. I'm fairly new at this program and
feel that there has to be an easier way to do this than just adding a field
and hand keying all of the "yes" entries with the corresponding numbers.
THANK YOU!

You may still be in a spreadsheet mindset. If all you need is a report, or a
display, listing a YES for all employees who match in the two tables, you
don't need any new fields in either table (and in fact should not create such
a redundant field at all!)

Instead, create a Query joining the two tables on this ID. Select whatever
fields you want to see from each query. This query will show only those
employees who appear in both tables.

If you want to see all the employees, with a Yes/No field showing Yes for
those who match in the smaller table, select the Join line in the query design
window and choose option 2 (or maybe 3 depending on how you created the
query): Show All Records in <employees> and matching records in <matching
table>. In a vacant Field cell type something like

InSmallTable: Not IsNull([smalltable].[Employee ID])

This field will be YES if there is a matching record, NO otherwise.

John W. Vinson [MVP]

 

[Inuchan]

OK...we're almost there! I was able to correct my error (didn't include a
space in the Field name when there was one) and got the query to work.
However, instead of adding the data to the Employee table, it just displays a
list of "Yes" entries in a Bad Rating field that stands alone - it was not
incorporated into the Employee table and matching up with the ID numbers that
needed the "Yes."

THANK YOU!

No errors this time, but it is now asking for a parameter value for
Employee.BadRating, then for Employee!EmployeeID and BadRating!EmployeeID.
Any advice?

THANK YOU so very much for the great step-by-step in your last post.

Sorry my fault. I used made-up names.

1st MAKE A BACKUP of your DB.

I have assumed this
You have a table called tblEmpoyee
This is the large table

You have a table called
tblBadRating
This is the small

In tblBadRating you have a field that holds numbers that are the same as
"some" of the record primary field in tblEmployee

You want to add the word (text) "Yes" to tblEmployee for each record in
tblBadRating

---------------------------------
Add a field to tblEmployee (or whatever your large table is called)
Call this new field BadRating


Open a new query and insert the sql "then change each of the names of the
tables" from;

tblEmployee to the real name of your large table
tblBadRating to the ral name of your smaller table

UPDATE tblBadRating, tblEmployee SET tblEmployee.BadRating = "Yes"
WHERE ((([tblEmployee]![EmployeeID])=[tblBadRating]![EmployeeID]));

Then run this (click the red apostraphy - or just save the query and then
open it again)

Hope this helps



--
Wayne
Manchester, England.

Thank you, Wayne.
If you wouldn't mind, can you give me a bit more instruction on where to
type the below formula? I tried doing it in SQL view of a query, but I keep
getting errors.
Thank you for your patience!

:

Try something like this

UPDATE tblBadRating, tblEmployee SET tblEmployee.BadRating = "Yes"
WHERE ((([tblEmployee]![EmployeeID])=[tblBadRating]![EmployeeID]));


--
Wayne
Manchester, England.



:

Thank you both so much for your help! Alas, I have been directed to insert
the field from the small table so that it exists in the table but in the
different form of a yes response for the matching information. To clarify,
the "Employees" table is the large table and has a primary key of Employee
ID. The "Bad Rating" table consists of one field, "Bad Rating," that is a
list of employee ID numbers of those employees who received a poor rating.
This list of numbers is what needs to be added to the "Employees" table, but
instead of listing the ID numbers, it needs to correspond with the "Employee
ID" field and return a "Yes" for those numbers that were in the "Bad Rating"
field.

Does this field need to be renamed "Employee ID" so that it can link with
the "Employee ID" field in the "Employees" table?

I'm going to try the query suggested below so that I may view all employees,
but I had one question (not to sound like an idiot - I'm just an amateur!):
the formula I am to type in a vacant field cell should go in the field where
I want the "Yes" response to show up?

Once again, I cannot thank you enough for your help. Going to try
suggestions now!


:

On Sat, 12 Jan 2008 08:27:00 -0800, Inuchan

Thank you in advance for your help! I have two Excel spreadsheets that I
successfully imported into Access 2003 and created tables for. I need to add
the field from one table to the other, but there is not a direct match in the
relationship. The large table uses the Employee ID as the primary key. The
smaller table contains one field that lists a subset of these Employee ID
numbers (a selection of certain employees). I need to transfer this field to
the larger table, but I do not know how to tell Access to match up the
corresponding numbers (i.e., the large table lists all employees, but the
added field will only match up with those employees who appear in it.) In
addition, I need to change the value of the data in the added field from its
original number form to a "yes." Therefore, the "yes" would appear in the
added field in the record that corresponds with the appropriate Employee.
Any help would be greatly appreciated. I'm fairly new at this program and
feel that there has to be an easier way to do this than just adding a field
and hand keying all of the "yes" entries with the corresponding numbers.
THANK YOU!

You may still be in a spreadsheet mindset. If all you need is a report, or a
display, listing a YES for all employees who match in the two tables, you
don't need any new fields in either table (and in fact should not create such
a redundant field at all!)

Instead, create a Query joining the two tables on this ID. Select whatever
fields you want to see from each query. This query will show only those
employees who appear in both tables.

If you want to see all the employees, with a Yes/No field showing Yes for
those who match in the smaller table, select the Join line in the query design
window and choose option 2 (or maybe 3 depending on how you created the
query): Show All Records in <employees> and matching records in <matching
table>. In a vacant Field cell type something like

InSmallTable: Not IsNull([smalltable].[Employee ID])

This field will be YES if there is a matching record, NO otherwise.

John W. Vinson [MVP]

 

[Inuchan]

HOORAY! It worked! Thank you so very much, Wayne, for your time and patience.

OK...we're almost there! I was able to correct my error (didn't include a
space in the Field name when there was one) and got the query to work.
However, instead of adding the data to the Employee table, it just displays a
list of "Yes" entries in a Bad Rating field that stands alone - it was not
incorporated into the Employee table and matching up with the ID numbers that
needed the "Yes."

THANK YOU!

No errors this time, but it is now asking for a parameter value for
Employee.BadRating, then for Employee!EmployeeID and BadRating!EmployeeID.
Any advice?

THANK YOU so very much for the great step-by-step in your last post.

Sorry my fault. I used made-up names.

1st MAKE A BACKUP of your DB.

I have assumed this
You have a table called tblEmpoyee
This is the large table

You have a table called
tblBadRating
This is the small

In tblBadRating you have a field that holds numbers that are the same as
"some" of the record primary field in tblEmployee

You want to add the word (text) "Yes" to tblEmployee for each record in
tblBadRating

---------------------------------
Add a field to tblEmployee (or whatever your large table is called)
Call this new field BadRating


Open a new query and insert the sql "then change each of the names of the
tables" from;

tblEmployee to the real name of your large table
tblBadRating to the ral name of your smaller table

UPDATE tblBadRating, tblEmployee SET tblEmployee.BadRating = "Yes"
WHERE ((([tblEmployee]![EmployeeID])=[tblBadRating]![EmployeeID]));

Then run this (click the red apostraphy - or just save the query and then
open it again)

Hope this helps



--
Wayne
Manchester, England.



:

Thank you, Wayne.
If you wouldn't mind, can you give me a bit more instruction on where to
type the below formula? I tried doing it in SQL view of a query, but I keep
getting errors.
Thank you for your patience!

:

Try something like this

UPDATE tblBadRating, tblEmployee SET tblEmployee.BadRating = "Yes"
WHERE ((([tblEmployee]![EmployeeID])=[tblBadRating]![EmployeeID]));


--
Wayne
Manchester, England.



:

Thank you both so much for your help! Alas, I have been directed to insert
the field from the small table so that it exists in the table but in the
different form of a yes response for the matching information. To clarify,
the "Employees" table is the large table and has a primary key of Employee
ID. The "Bad Rating" table consists of one field, "Bad Rating," that is a
list of employee ID numbers of those employees who received a poor rating.
This list of numbers is what needs to be added to the "Employees" table, but
instead of listing the ID numbers, it needs to correspond with the "Employee
ID" field and return a "Yes" for those numbers that were in the "Bad Rating"
field.

Does this field need to be renamed "Employee ID" so that it can link with
the "Employee ID" field in the "Employees" table?

I'm going to try the query suggested below so that I may view all employees,
but I had one question (not to sound like an idiot - I'm just an amateur!):
the formula I am to type in a vacant field cell should go in the field where
I want the "Yes" response to show up?

Once again, I cannot thank you enough for your help. Going to try
suggestions now!


:

On Sat, 12 Jan 2008 08:27:00 -0800, Inuchan

Thank you in advance for your help! I have two Excel spreadsheets that I
successfully imported into Access 2003 and created tables for. I need to add
the field from one table to the other, but there is not a direct match in the
relationship. The large table uses the Employee ID as the primary key. The
smaller table contains one field that lists a subset of these Employee ID
numbers (a selection of certain employees). I need to transfer this field to
the larger table, but I do not know how to tell Access to match up the
corresponding numbers (i.e., the large table lists all employees, but the
added field will only match up with those employees who appear in it.) In
addition, I need to change the value of the data in the added field from its
original number form to a "yes." Therefore, the "yes" would appear in the
added field in the record that corresponds with the appropriate Employee.
Any help would be greatly appreciated. I'm fairly new at this program and
feel that there has to be an easier way to do this than just adding a field
and hand keying all of the "yes" entries with the corresponding numbers.
THANK YOU!

You may still be in a spreadsheet mindset. If all you need is a report, or a
display, listing a YES for all employees who match in the two tables, you
don't need any new fields in either table (and in fact should not create such
a redundant field at all!)

Instead, create a Query joining the two tables on this ID. Select whatever
fields you want to see from each query. This query will show only those
employees who appear in both tables.

If you want to see all the employees, with a Yes/No field showing Yes for
those who match in the smaller table, select the Join line in the query design
window and choose option 2 (or maybe 3 depending on how you created the
query): Show All Records in <employees> and matching records in <matching
table>. In a vacant Field cell type something like

InSmallTable: Not IsNull([smalltable].[Employee ID])

This field will be YES if there is a matching record, NO otherwise.

John W. Vinson [MVP]

 

[Wayne-I-M]

I'm pleased it worked for you.


--
Wayne
Manchester, England.

HOORAY! It worked! Thank you so very much, Wayne, for your time and patience.

OK...we're almost there! I was able to correct my error (didn't include a
space in the Field name when there was one) and got the query to work.
However, instead of adding the data to the Employee table, it just displays a
list of "Yes" entries in a Bad Rating field that stands alone - it was not
incorporated into the Employee table and matching up with the ID numbers that
needed the "Yes."

THANK YOU!

No errors this time, but it is now asking for a parameter value for
Employee.BadRating, then for Employee!EmployeeID and BadRating!EmployeeID.
Any advice?

THANK YOU so very much for the great step-by-step in your last post.

:

Sorry my fault. I used made-up names.

1st MAKE A BACKUP of your DB.

I have assumed this
You have a table called tblEmpoyee
This is the large table

You have a table called
tblBadRating
This is the small

In tblBadRating you have a field that holds numbers that are the same as
"some" of the record primary field in tblEmployee

You want to add the word (text) "Yes" to tblEmployee for each record in
tblBadRating

---------------------------------
Add a field to tblEmployee (or whatever your large table is called)
Call this new field BadRating


Open a new query and insert the sql "then change each of the names of the
tables" from;

tblEmployee to the real name of your large table
tblBadRating to the ral name of your smaller table

UPDATE tblBadRating, tblEmployee SET tblEmployee.BadRating = "Yes"
WHERE ((([tblEmployee]![EmployeeID])=[tblBadRating]![EmployeeID]));

Then run this (click the red apostraphy - or just save the query and then
open it again)

Hope this helps



--
Wayne
Manchester, England.



:

Thank you, Wayne.
If you wouldn't mind, can you give me a bit more instruction on where to
type the below formula? I tried doing it in SQL view of a query, but I keep
getting errors.
Thank you for your patience!

:

Try something like this

UPDATE tblBadRating, tblEmployee SET tblEmployee.BadRating = "Yes"
WHERE ((([tblEmployee]![EmployeeID])=[tblBadRating]![EmployeeID]));


--
Wayne
Manchester, England.



:

Thank you both so much for your help! Alas, I have been directed to insert
the field from the small table so that it exists in the table but in the
different form of a yes response for the matching information. To clarify,
the "Employees" table is the large table and has a primary key of Employee
ID. The "Bad Rating" table consists of one field, "Bad Rating," that is a
list of employee ID numbers of those employees who received a poor rating.
This list of numbers is what needs to be added to the "Employees" table, but
instead of listing the ID numbers, it needs to correspond with the "Employee
ID" field and return a "Yes" for those numbers that were in the "Bad Rating"
field.

Does this field need to be renamed "Employee ID" so that it can link with
the "Employee ID" field in the "Employees" table?

I'm going to try the query suggested below so that I may view all employees,
but I had one question (not to sound like an idiot - I'm just an amateur!):
the formula I am to type in a vacant field cell should go in the field where
I want the "Yes" response to show up?

Once again, I cannot thank you enough for your help. Going to try
suggestions now!


:

On Sat, 12 Jan 2008 08:27:00 -0800, Inuchan

Thank you in advance for your help! I have two Excel spreadsheets that I
successfully imported into Access 2003 and created tables for. I need to add
the field from one table to the other, but there is not a direct match in the
relationship. The large table uses the Employee ID as the primary key. The
smaller table contains one field that lists a subset of these Employee ID
numbers (a selection of certain employees). I need to transfer this field to
the larger table, but I do not know how to tell Access to match up the
corresponding numbers (i.e., the large table lists all employees, but the
added field will only match up with those employees who appear in it.) In
addition, I need to change the value of the data in the added field from its
original number form to a "yes." Therefore, the "yes" would appear in the
added field in the record that corresponds with the appropriate Employee.
Any help would be greatly appreciated. I'm fairly new at this program and
feel that there has to be an easier way to do this than just adding a field
and hand keying all of the "yes" entries with the corresponding numbers.
THANK YOU!

You may still be in a spreadsheet mindset. If all you need is a report, or a
display, listing a YES for all employees who match in the two tables, you
don't need any new fields in either table (and in fact should not create such
a redundant field at all!)

Instead, create a Query joining the two tables on this ID. Select whatever
fields you want to see from each query. This query will show only those
employees who appear in both tables.

If you want to see all the employees, with a Yes/No field showing Yes for
those who match in the smaller table, select the Join line in the query design
window and choose option 2 (or maybe 3 depending on how you created the
query): Show All Records in <employees> and matching records in <matching
table>. In a vacant Field cell type something like

InSmallTable: Not IsNull([smalltable].[Employee ID])

This field will be YES if there is a matching record, NO otherwise.

John W. Vinson [MVP]