Transferring Field from Existing Table/limitations and change of d

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!
0
Utf
1/12/2008 4:27:00 PM
access 16762 articles. 2 followers. Follow

11 Replies
883 Views

Similar Articles

[PageSpeed] 0

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.





-- 
Wayne
Manchester, England.



"Inuchan" wrote:

> 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!
0
Utf
1/12/2008 5:30:00 PM
On Sat, 12 Jan 2008 08:27:00 -0800, Inuchan
<Inuchan@discussions.microsoft.com> wrote:

>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]
0
John
1/12/2008 5:54:32 PM
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!


"John W. Vinson" wrote:

> On Sat, 12 Jan 2008 08:27:00 -0800, Inuchan
> <Inuchan@discussions.microsoft.com> wrote:
> 
> >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]
> 
0
Utf
1/12/2008 6:45:00 PM
Try something like this

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


-- 
Wayne
Manchester, England.



"Inuchan" wrote:

> 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!
> 
> 
> "John W. Vinson" wrote:
> 
> > On Sat, 12 Jan 2008 08:27:00 -0800, Inuchan
> > <Inuchan@discussions.microsoft.com> wrote:
> > 
> > >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]
> > 
0
Utf
1/12/2008 7:01:02 PM
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!

"Wayne-I-M" wrote:

> Try something like this
> 
> UPDATE tblBadRating, tblEmployee SET tblEmployee.BadRating = "Yes"
> WHERE ((([tblEmployee]![EmployeeID])=[tblBadRating]![EmployeeID]));
> 
> 
> -- 
> Wayne
> Manchester, England.
> 
> 
> 
> "Inuchan" wrote:
> 
> > 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!
> > 
> > 
> > "John W. Vinson" wrote:
> > 
> > > On Sat, 12 Jan 2008 08:27:00 -0800, Inuchan
> > > <Inuchan@discussions.microsoft.com> wrote:
> > > 
> > > >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]
> > > 
0
Utf
1/12/2008 7:17:01 PM
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.



"Inuchan" wrote:

> 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!
> 
> "Wayne-I-M" wrote:
> 
> > Try something like this
> > 
> > UPDATE tblBadRating, tblEmployee SET tblEmployee.BadRating = "Yes"
> > WHERE ((([tblEmployee]![EmployeeID])=[tblBadRating]![EmployeeID]));
> > 
> > 
> > -- 
> > Wayne
> > Manchester, England.
> > 
> > 
> > 
> > "Inuchan" wrote:
> > 
> > > 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!
> > > 
> > > 
> > > "John W. Vinson" wrote:
> > > 
> > > > On Sat, 12 Jan 2008 08:27:00 -0800, Inuchan
> > > > <Inuchan@discussions.microsoft.com> wrote:
> > > > 
> > > > >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]
> > > > 
0
Utf
1/12/2008 7:41:01 PM
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.

"Wayne-I-M" wrote:

> 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.
> 
> 
> 
> "Inuchan" wrote:
> 
> > 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!
> > 
> > "Wayne-I-M" wrote:
> > 
> > > Try something like this
> > > 
> > > UPDATE tblBadRating, tblEmployee SET tblEmployee.BadRating = "Yes"
> > > WHERE ((([tblEmployee]![EmployeeID])=[tblBadRating]![EmployeeID]));
> > > 
> > > 
> > > -- 
> > > Wayne
> > > Manchester, England.
> > > 
> > > 
> > > 
> > > "Inuchan" wrote:
> > > 
> > > > 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!
> > > > 
> > > > 
> > > > "John W. Vinson" wrote:
> > > > 
> > > > > On Sat, 12 Jan 2008 08:27:00 -0800, Inuchan
> > > > > <Inuchan@discussions.microsoft.com> wrote:
> > > > > 
> > > > > >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]
> > > > > 
0
Utf
1/12/2008 7:58:00 PM
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.



"Inuchan" wrote:

> 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.
> 
> "Wayne-I-M" wrote:
> 
> > 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.
> > 
> > 
> > 
> > "Inuchan" wrote:
> > 
> > > 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!
> > > 
> > > "Wayne-I-M" wrote:
> > > 
> > > > Try something like this
> > > > 
> > > > UPDATE tblBadRating, tblEmployee SET tblEmployee.BadRating = "Yes"
> > > > WHERE ((([tblEmployee]![EmployeeID])=[tblBadRating]![EmployeeID]));
> > > > 
> > > > 
> > > > -- 
> > > > Wayne
> > > > Manchester, England.
> > > > 
> > > > 
> > > > 
> > > > "Inuchan" wrote:
> > > > 
> > > > > 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!
> > > > > 
> > > > > 
> > > > > "John W. Vinson" wrote:
> > > > > 
> > > > > > On Sat, 12 Jan 2008 08:27:00 -0800, Inuchan
> > > > > > <Inuchan@discussions.microsoft.com> wrote:
> > > > > > 
> > > > > > >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]
> > > > > > 
0
Utf
1/12/2008 8:04:00 PM
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!

"Inuchan" wrote:

> 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.
> 
> "Wayne-I-M" wrote:
> 
> > 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.
> > 
> > 
> > 
> > "Inuchan" wrote:
> > 
> > > 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!
> > > 
> > > "Wayne-I-M" wrote:
> > > 
> > > > Try something like this
> > > > 
> > > > UPDATE tblBadRating, tblEmployee SET tblEmployee.BadRating = "Yes"
> > > > WHERE ((([tblEmployee]![EmployeeID])=[tblBadRating]![EmployeeID]));
> > > > 
> > > > 
> > > > -- 
> > > > Wayne
> > > > Manchester, England.
> > > > 
> > > > 
> > > > 
> > > > "Inuchan" wrote:
> > > > 
> > > > > 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!
> > > > > 
> > > > > 
> > > > > "John W. Vinson" wrote:
> > > > > 
> > > > > > On Sat, 12 Jan 2008 08:27:00 -0800, Inuchan
> > > > > > <Inuchan@discussions.microsoft.com> wrote:
> > > > > > 
> > > > > > >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]
> > > > > > 
0
Utf
1/12/2008 8:11:01 PM
HOORAY! It worked! Thank you so very much, Wayne, for your time and patience. 
:)

"Inuchan" wrote:

> 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!
> 
> "Inuchan" wrote:
> 
> > 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.
> > 
> > "Wayne-I-M" wrote:
> > 
> > > 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.
> > > 
> > > 
> > > 
> > > "Inuchan" wrote:
> > > 
> > > > 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!
> > > > 
> > > > "Wayne-I-M" wrote:
> > > > 
> > > > > Try something like this
> > > > > 
> > > > > UPDATE tblBadRating, tblEmployee SET tblEmployee.BadRating = "Yes"
> > > > > WHERE ((([tblEmployee]![EmployeeID])=[tblBadRating]![EmployeeID]));
> > > > > 
> > > > > 
> > > > > -- 
> > > > > Wayne
> > > > > Manchester, England.
> > > > > 
> > > > > 
> > > > > 
> > > > > "Inuchan" wrote:
> > > > > 
> > > > > > 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!
> > > > > > 
> > > > > > 
> > > > > > "John W. Vinson" wrote:
> > > > > > 
> > > > > > > On Sat, 12 Jan 2008 08:27:00 -0800, Inuchan
> > > > > > > <Inuchan@discussions.microsoft.com> wrote:
> > > > > > > 
> > > > > > > >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]
> > > > > > > 
0
Utf
1/12/2008 8:18:01 PM
I'm pleased it worked for you.


-- 
Wayne
Manchester, England.



"Inuchan" wrote:

> HOORAY! It worked! Thank you so very much, Wayne, for your time and patience. 
> :)
> 
> "Inuchan" wrote:
> 
> > 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!
> > 
> > "Inuchan" wrote:
> > 
> > > 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.
> > > 
> > > "Wayne-I-M" wrote:
> > > 
> > > > 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.
> > > > 
> > > > 
> > > > 
> > > > "Inuchan" wrote:
> > > > 
> > > > > 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!
> > > > > 
> > > > > "Wayne-I-M" wrote:
> > > > > 
> > > > > > Try something like this
> > > > > > 
> > > > > > UPDATE tblBadRating, tblEmployee SET tblEmployee.BadRating = "Yes"
> > > > > > WHERE ((([tblEmployee]![EmployeeID])=[tblBadRating]![EmployeeID]));
> > > > > > 
> > > > > > 
> > > > > > -- 
> > > > > > Wayne
> > > > > > Manchester, England.
> > > > > > 
> > > > > > 
> > > > > > 
> > > > > > "Inuchan" wrote:
> > > > > > 
> > > > > > > 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!
> > > > > > > 
> > > > > > > 
> > > > > > > "John W. Vinson" wrote:
> > > > > > > 
> > > > > > > > On Sat, 12 Jan 2008 08:27:00 -0800, Inuchan
> > > > > > > > <Inuchan@discussions.microsoft.com> wrote:
> > > > > > > > 
> > > > > > > > >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]
> > > > > > > > 
0
Utf
1/12/2008 9:04:01 PM
Reply:

Similar Artilces:

Preserving Fornatting in Pivot tables
I run quite a lot of pivot tables and update them regularly. I am struggling to preserve the formatting on them. When I refresh the table they lose some or all of the formatting. I have played about with table options - Autoformat & preserve formating but without any success... Any suggestions Chris Pick on one of the fields in the data area that has a value in it. Right click and choose Field settings / Number / Choose a format, hit OK, and it should be retained. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro /...

how would i change default user name all excel files
I am doing a learn direct course and had to use the help facility with question; Find out how you would change the default user name for all excel files. If somebody could help please email me on georginacambridg@aol.com Tools|Options|General Tab and modify the "user name" box learner wrote: > > I am doing a learn direct course and had to use the help facility with > question; > Find out how you would change the default user name for all excel files. > If somebody could help please email me on georginacambridg@aol.com -- Dave Peterson ...

Excluding blank fields in reports
I am trying to include 3 feilds in my report but exclude any blank entries in that report. How do I do this? Jenn Take a look at the Can Grow and Can Shrink properties of both those controls and the section within which they're located. NOTE: a blank (" "), a zero-length string (""), and a Null ALL look the same to casual human inspection. Are you quite certain you have "blanks"? -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacadem...

about transfer order
I created a transfer order "TO666", transfer items from store01 to store02, the system automaticly created a TO No."00001_TO666 ". And the strange thing happend: the items in "TO666" is so different with the items in "00001_TO666 ". where is the problem? with a lot of thanks. Try creating another order with the same items, if the problem still exists, most probably you have a problem in the IDs of those items. What HQ does is to map the items in the created order in Store1 to the corresponding items in HQ, then map them again to Store2 and th...

Transfering messages
I want to transfer all my messages from one PC running Vista to another running Win7 which I have Windows Mail running on. However, I have two problems: 1. On transferring some of the transferred folders contain the full message, some only contain what you might call place holders e.g my transferred Inbox has 'From' with a list of 30 or so messages but just shows an envelop and the date and time but no message 2. Is there away to transfer attachments too? -- Ron "Ron O'Brien" <castcall@ntlworld.com> wrote in message news:uBH3g6UjKHA.218...

HR Position Transfers
Is there a way to import a mass amount of position changes in GP HR? We are getting ready to effect changes for our upcoming fiscal year. There are numerous changes to our departments, positions moving from one department to another and some of the departments are altogether new. Is there a tool to import Position Change/Transfers? If not, does anyone know all the tables I would need to hit if I need to import the data? ...

Windows Easy Transfer
I am having trouble with Easy Transfer from an XPhome to XPhome installation. Actually it is the same computer after re-installing XP. I ran the Easy Transfer to back up the data onto an external drive. When I run it now on the new build I don't get the "Continue transfer in Progress" option. It does not ask if this is the new or old computer. I haven't used it before so maybe I am not using it as intended. I have user the XP File settings and transfer program for a situation just like this with no problems. Any suggestions? Hu Computer is a basic HP Pavi...

STM (streaming file) too big
Hi all We have just installed exchange 2003 standard and are already experiencing problems with hitting the 16Gb limit The server has only been runnning for 2 weeks! The .stm file is now over 14Gb in size even after we did an offline defrag! All users are currently running as POP3 clients and will be migrating over to MAPI integrated clients as soon as possible. Whilst i understand that the stm file contains message bodies to support non mapi clients what i dont understand is why this file remains so large after POP3 clients have downloaded all their mail. Is this by design? How ca...

Transferring an order to an invoice...
We are transferring over to GP 10. I have a question about invoicing that I hope someone here can help me with. In GP 8, there was a transfer button on the Sales Order Transaction screen to transfer the order to an invoice. In GP 10, the transfer option is in a pull down menu. Can we make the transfer option a 3rd button on the screen? If someone could let me know, I would appreciate your help greatly! yes you should be able to do that if you wish using a button and a bit of vba code. patrick developer support -- This posting is provided "AS IS" with no warranties, a...

System does not auto create Transfer In during Inter-store transfe
Hi, my customer running 2 stores and both store having the same interstore transfer option setting. my problem is 1 of the store does not auto generate Transfer In doc 1. store A - Transfer Out Doc store B - auto generate transfer In Doc store A uploaded 2 HQ message to HQ & store B received 2 HQ message. 2. store B - Transfer Out Doc store A - NOTHING GENERATED store B only uploaded 1 HQ message to HQ & store A received 1 HQ message. anyone got ideas how to solved this? Thanks Dennis Did you set the HQ Configuration options to auto gen...

table design question
I have table of 40 milion rows (sql 2005). There is one column, Id, which is integer (basically month), and there are 20 different values for this column (around 2 million rows per id value). At the same time, there is very long primary key on this table (12 columns, around 400 bytes), and nonclustered index on this primary key. Table is being accessed frequently by Id, so I created clustered index on this column (usually, developers create temporary tables per id, and then do further processing) to lower physical reads... Does this sound like a desirable design? I don't wan...

Reset existing customer vendor relationship
Hi, We have a customer vendor relationship set up incorrectly. GP doesn't allow me to edit the relationship or create a new one. How can I fix this? Any help is much appreciated. GG, You can delete the existing relationship, then create a new one. To delete: - Click on the line with the relationship you want to delete so it is highlighted. - Go to Edit > Delete Row. - Confirm the deletion. -- Victoria Yudin Dynamics GP MVP Want to use Crystal Reports with GP? http://www.flex-solutions.com/gpreports.html blog: http://www.victoriayudin.com "GG" &...

how a file can change its type from '.xls' to '.tmp'
I have a file that keeps changing its name by itself and if I change it back it locks the file It sounds like you are attempting to change a Template file into a Workbook file. Templates are intended to remain as such so that new workbooks can be created based on tem rather than alway using blank workbooks that have to be restructured each time. It's best to not OPEN the template. Instead use File>New or depending on version of Excel, New from Template in the Task Pane. When you save the new file it will prompt you for a name and be saved as a .xls as usual. HTH |:>) "...

bcc and attachment fields
Is there a way to make the bcc and attachments fields always show up at the top of a new email under the To: , CC:, and Subject fields??? Thanks are you meaning that attachments will not show in the body? I am looking for that answer too. -- Dave M Radeon 9800Pro 256mb P4 2.8 oc to 3.1 "sendhost3000" <sendhost3000@yahoo.com> wrote in message news:eYXGYYYxDHA.3224@tk2msftngp13.phx.gbl... > Is there a way to make the bcc and attachments fields always show up at the > top of a new email under the To: , CC:, and Subject fields??? > > Thanks > > -----= ...

TRANSFER OUT TO TRANSFER OUT
I HAVE BEEN WORKING WITH RMS FOR OVER A YEAR AND HAVE NEVER SEEN THIS PROBLEM. STORE A MAKES A TRANSFER OUT AND STORE B RECEIVES A COMPUTER TRANSFER IN THE TRANSFER OUT WINDOW INSTEAD OF A TRANSFER IN. IN ADDITION TO THAT THE TRANSFER HAS BEEN CLOSED AT STORE B'S END AUTOMITICALLY AND IT IS EMPTY. AN KNOWLEDGE AS TO WHY THIS HAPPENS THANKS Check HQ Client for errors, or from HQ Manager, view the event log for that store. You'll probably find some errors at Store B on the 401 that created the transfer. I would guess that Store A used a transfer number with more than 14 c...

limits to multiple subsitute funtion??
Is there a limit to how many substitute functions I can have in one formula? It does not seem to let me have more than 8. If there is a limit, is there a work around? =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(BM2,"Oscar Heyman","16431685"),"Buccelati","16431678"),"Cartier","16431674"),"Chanel","16431677"),"Chopard","16431686"),"David Yurman","16431676"),"K. Cord","16431682"),"Krypell","164316...

Table Failed to Open
Running an FRX report with calculated columns. For each printed and calculated column, I am getting an error box that says "Table failed to open." But the report looks fine. Any Ideas? "tjchamp" <toddcham79@yahoo.com> wrote in message news:eVoGPwLUFHA.928@TK2MSFTNGP15.phx.gbl... > Running an FRX report with calculated columns. For each printed and > calculated column, I am getting an error box that says "Table failed to > open." But the report looks fine. Any Ideas? More info. If I delete the formating from the column layout, I do ...

Name Change Problem
This is a multi-part message in MIME format. ------=_NextPart_000_0076_01C4B6BC.5E9F94C0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Ex-Admins: I setup a lab test where I created a user account to see what would = happen if I change the name, alias, email address,etc. Everything seemed OK, except when i send an email out to somebody, the = FROM field doesn't change, it still reads as if it's the old name. For example: The original username =3D john test, alias =3D jtest, email =3D = jtest@company.com, AD object name =3D john ...

Fixed Asset transfer for a New Company
Is selling the Fixed Asset to the new company the best option to make a transfer to a new company? Can you give us a little more detail on what you are needing to accomplish? If two companies are different legal entities there are all kinds of other rules on 'transferring' assets. Is this just a new database or are you doing an intercompany transaction? We can help, we just need a little more explanation. "FA user" wrote: > Is selling the Fixed Asset to the new company the best option to make a > transfer to a new company? ...

Which Transfer In?
A question for you all, which our support company can not answer... If branch 5 receives a transfer from another branch of 64 of item 68209, but the paperwork is missing, and there is no label on the box to say which branch it has come from, is there a quick way of finding the correct Transfer in document? We have tried the Item movement and item movement history reports, but the answer is NO!. Basically, look through all of the transfers in, until you find the correct one. I'm, after a report or a solution which does not involve Store Administrator, as it should be an easy to us...

CListBox: change to load rows first.
I am using a CListBox to show some small bitmaps. I would like to load all the columns of the first row before loading columns of the second row, etc. and use a vertical scroll bar. How can this be done? -- Regards, Frank Are you sure you aren't talking about CListCtrl? CListBox essentially doesn't have the concepts you are describing. A "multicolumn" CListBox doesn't have the concept of "columns" that are loadable. It sounds like you want a CListCtrl in icon mode. joe On Tue, 25 Sep 2007 17:44:29 -0500, Frank S <JazzedApps@community.nospam> ...

Activation Transfer
We currently have Publisher installed on our 3 computers. We have a computer we are replacing in our department. I need to know how I deactivate the computer we are replacing and transfer the activation onto the computer we are replacing it with. Thanks. There is no "deactivation". Uninstall it from old computer. Install it on new computer. Select telephone activation if necessary. -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 "Matt" <Matt@discussions.microsoft.com> wrote in message news:44CF7...

Calendar changing appointment details
Hi everyone, I have a Palm Tungsten T5 which I sync with Outlook. Anything I enter into the palm calendar appears as free and as a meeting in Outlook, as opposed to busy and as a appointment. Can anyone tell me if theres a setting somewhere that I'm missing? Thanks for your help, Paul Have you checked with Palm's Help and Support? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After furious head scratching, Pa...

MSP 2007
I've created a very simple test MSP file. It has 4 sub-tasks and 2 summary tasks. I've added resources and duration. I've also inserted the Text1 field and filled it for each sub-task. I then started the Visual reports. I've picked the Cash Flow report and edited it by adding Text1 field. When the report was generated Text1 field was not in the Excel report at all. What am I doing wrong? Thanks. This probably doesn't answer your question, but I've never had much luck getting fields into the Visual Reports. I prefer to save as Excel and manipulate the...

Transferring SMTP server to the new exchange
if I introduce new exchange server into the network how do i transfer SMTP server to the new exchange so that outgoing emails use it instead of the old exchange server? thank you On your current servers setup smart host to the new server. Do this from: 1. Exchange System Manager virtual server settings. 2. Delivery property sheet 3. Advanced 4. Smart Host - You can enter the IP insquare brackets or the machine name. cheers, Alexander Zammit Software Development Consultant Check out, ExchangeInbox.com the new MS Exchange Resource site at http://www.exchangeinbox.com/ "asdf" &...