Access Query - Don't know what type to use - Inner Join? Update?

[newsneakers]

Hi,
I am customizing the Issues Database Template (from the MS Template page)
http://office.microsoft.com/en-us/templates/TC012186931033.aspx to use with
projects.
I have two tables and a one form that I am working with. There are (2) two
fields/columns named "Project Name" and "Comm Name", respectively in each of
the Issues Table and Projects Table that correspond to two fields on the
Issues form. For every Project Name there is a unique Comm Name. The fields
on the Issues Form are named "Project Name" (formerly the Category field)
and (a newly created field), "Comm Name".

I have tried several ways to do this, using the Controls on the form itself,
and/or by using queries, but none seem to be working. So, now I'm a bit
confused about whether to use an Update or Inner Join Query? And if either of
these are the solution, could someone help with the correct syntax! Grrrrhhh!
Because even after searching books and the online MS Office Assistance - for
correct syntax for queries - I've tried just about everthing - but nothing
seems to be working.

I would like to do the following: (basically populate the field from one
table based on control / or field from another table).

1) Populate the "Comm Name" field in the Issues Table with the corresponding
Comm Name, using data from the Projects Table - based on the Project Name
(field) that is entered in Issues Table. In other words, when I enter the
Project Name in the Issues Form - or the Issues Table, I would like to the
Comm Name to automatically appear. The Project Name field is a Combo Box,
while I would like the Comm Name to be a simple text box (which is locked -
no data entry allowed) in the Issues Table or the Issues Form.

2) When the Issues form is open during data entry, I would like the Comm
Name to appear (in a locked field) -when the Project Name is entered into
the Project Name field (which is a Combo Box). -Of course - as with all of
the other fields (I'm hoping that the control on that field will
automatically populate the Issues Table- based on what is entered into the
Issues Form.

If someone has a solution to this problem. I would be most appreciative. The
process of populating fields based on data in another field is pretty
frustrating.
Thanks so much in advance.

Sarah

 

[[MVP] S.Clark]

You may need to use an Update Query WITH an inner join.

e.g.

UPDATE t1
INNER JOIN t2
ON t1.CustomerID = t2.CustomerID
SET t1.field = [t2].[field]

 

[newsneakers]

Hi,
Thank you, I'll try this.

However, I'm also kind of confused as to where to place this code? Okay so,
I create a "new" query using the SQL view, add the query statement. However
do I need to link anything else on the form - other than linking the Comm
Name field (on the Issues form) to the Comm Name field in the Issues Table?

I'm assuming that the query will cause the Issues Table (Comm Name field) to
be automatically updated when I enter in data in the Issues Table (Project
Name field)? Hopefully when the (control of ) Comm Name field (on the Issues
Form) is linked to the Issues Table (Comm Name field) this will cause it (the
comm name) to appear there when the Project Name is entered into the Issues
Form.

Again, thank you for your help! I'll try it and be back later...

You may need to use an Update Query WITH an inner join.

e.g.

UPDATE t1
INNER JOIN t2
ON t1.CustomerID = t2.CustomerID
SET t1.field = [t2].[field]

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

Hi,
I am customizing the Issues Database Template (from the MS Template page)
http://office.microsoft.com/en-us/templates/TC012186931033.aspx to use
with
projects.
I have two tables and a one form that I am working with. There are (2) two
fields/columns named "Project Name" and "Comm Name", respectively in each
of
the Issues Table and Projects Table that correspond to two fields on the
Issues form. For every Project Name there is a unique Comm Name. The
fields
on the Issues Form are named "Project Name" (formerly the Category field)
and (a newly created field), "Comm Name".

I have tried several ways to do this, using the Controls on the form
itself,
and/or by using queries, but none seem to be working. So, now I'm a bit
confused about whether to use an Update or Inner Join Query? And if either
of
these are the solution, could someone help with the correct syntax!
Grrrrhhh!
Because even after searching books and the online MS Office Assistance -
for
correct syntax for queries - I've tried just about everthing - but
nothing
seems to be working.

I would like to do the following: (basically populate the field from one
table based on control / or field from another table).

1) Populate the "Comm Name" field in the Issues Table with the
corresponding
Comm Name, using data from the Projects Table - based on the Project Name
(field) that is entered in Issues Table. In other words, when I enter the
Project Name in the Issues Form - or the Issues Table, I would like to
the
Comm Name to automatically appear. The Project Name field is a Combo Box,
while I would like the Comm Name to be a simple text box (which is
locked -
no data entry allowed) in the Issues Table or the Issues Form.

2) When the Issues form is open during data entry, I would like the Comm
Name to appear (in a locked field) -when the Project Name is entered into
the Project Name field (which is a Combo Box). -Of course - as with all of
the other fields (I'm hoping that the control on that field will
automatically populate the Issues Table- based on what is entered into the
Issues Form.

If someone has a solution to this problem. I would be most appreciative.
The
process of populating fields based on data in another field is pretty
frustrating.
Thanks so much in advance.

Sarah

 

[newsneakers]

Hi again...
I just created a Query using the following syntax:

UPDATE Issues
INNER JOIN Projects
ON Issues.Project Name= Projects.Project Name
SET Issues.Comm Name = [Projects].[Comm Name]

however, I'm getting the following error message:

Syntax error (missing operator) in query expression 'Issues.Project Name =
Projects.Project Name'


Sarah

Hi,
Thank you, I'll try this.

However, I'm also kind of confused as to where to place this code? Okay so,
I create a "new" query using the SQL view, add the query statement. However
do I need to link anything else on the form - other than linking the Comm
Name field (on the Issues form) to the Comm Name field in the Issues Table?

I'm assuming that the query will cause the Issues Table (Comm Name field) to
be automatically updated when I enter in data in the Issues Table (Project
Name field)? Hopefully when the (control of ) Comm Name field (on the Issues
Form) is linked to the Issues Table (Comm Name field) this will cause it (the
comm name) to appear there when the Project Name is entered into the Issues
Form.

Again, thank you for your help! I'll try it and be back later...

You may need to use an Update Query WITH an inner join.

e.g.

UPDATE t1
INNER JOIN t2
ON t1.CustomerID = t2.CustomerID
SET t1.field = [t2].[field]

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

Hi,
I am customizing the Issues Database Template (from the MS Template page)
http://office.microsoft.com/en-us/templates/TC012186931033.aspx to use
with
projects.
I have two tables and a one form that I am working with. There are (2) two
fields/columns named "Project Name" and "Comm Name", respectively in each
of
the Issues Table and Projects Table that correspond to two fields on the
Issues form. For every Project Name there is a unique Comm Name. The
fields
on the Issues Form are named "Project Name" (formerly the Category field)
and (a newly created field), "Comm Name".

I have tried several ways to do this, using the Controls on the form
itself,
and/or by using queries, but none seem to be working. So, now I'm a bit
confused about whether to use an Update or Inner Join Query? And if either
of
these are the solution, could someone help with the correct syntax!
Grrrrhhh!
Because even after searching books and the online MS Office Assistance -
for
correct syntax for queries - I've tried just about everthing - but
nothing
seems to be working.

I would like to do the following: (basically populate the field from one
table based on control / or field from another table).

1) Populate the "Comm Name" field in the Issues Table with the
corresponding
Comm Name, using data from the Projects Table - based on the Project Name
(field) that is entered in Issues Table. In other words, when I enter the
Project Name in the Issues Form - or the Issues Table, I would like to
the
Comm Name to automatically appear. The Project Name field is a Combo Box,
while I would like the Comm Name to be a simple text box (which is
locked -
no data entry allowed) in the Issues Table or the Issues Form.

2) When the Issues form is open during data entry, I would like the Comm
Name to appear (in a locked field) -when the Project Name is entered into
the Project Name field (which is a Combo Box). -Of course - as with all of
the other fields (I'm hoping that the control on that field will
automatically populate the Issues Table- based on what is entered into the
Issues Form.

If someone has a solution to this problem. I would be most appreciative.
The
process of populating fields based on data in another field is pretty
frustrating.
Thanks so much in advance.

Sarah

 

[John Vinson]

Hi again...
I just created a Query using the following syntax:

UPDATE Issues
INNER JOIN Projects
ON Issues.Project Name= Projects.Project Name
SET Issues.Comm Name = [Projects].[Comm Name]

however, I'm getting the following error message:

Syntax error (missing operator) in query expression 'Issues.Project Name =
Projects.Project Name'

This is one reason to avoid using special characters - SUCH AS BLANKS
- in table or field names. A blank is meaningful punctuation; Access
is seeing

ON Issues.Project

and thinking you're at the end of something, and it's looking for an
operator (such as an equals sign).

If you must use blanks or other special characters, you must always
enclose names in [brackets]:

UPDATE Issues
INNER JOIN Projects
ON Issues.[Project Name] = Projects.[Project Name]
SET Issues.[Comm Name] = [Projects].[Comm Name]


John W. Vinson[MVP]

 

[newsneakers]

I revised the Update query to the following:
UPDATE Issues INNER JOIN Projects ON Issues.[Project Name] =
Projects.[Project Name] SET Issues.[Community Name] = Projects.[Community
Name];

I had forgotten that when your field name has two words, you must include it
in brackets!

It WORKED!
Thanks You !

Hi,
Thank you, I'll try this.

However, I'm also kind of confused as to where to place this code? Okay so,
I create a "new" query using the SQL view, add the query statement. However
do I need to link anything else on the form - other than linking the Comm
Name field (on the Issues form) to the Comm Name field in the Issues Table?

I'm assuming that the query will cause the Issues Table (Comm Name field) to
be automatically updated when I enter in data in the Issues Table (Project
Name field)? Hopefully when the (control of ) Comm Name field (on the Issues
Form) is linked to the Issues Table (Comm Name field) this will cause it (the
comm name) to appear there when the Project Name is entered into the Issues
Form.

Again, thank you for your help! I'll try it and be back later...

You may need to use an Update Query WITH an inner join.

e.g.

UPDATE t1
INNER JOIN t2
ON t1.CustomerID = t2.CustomerID
SET t1.field = [t2].[field]

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

Hi,
I am customizing the Issues Database Template (from the MS Template page)
http://office.microsoft.com/en-us/templates/TC012186931033.aspx to use
with
projects.
I have two tables and a one form that I am working with. There are (2) two
fields/columns named "Project Name" and "Comm Name", respectively in each
of
the Issues Table and Projects Table that correspond to two fields on the
Issues form. For every Project Name there is a unique Comm Name. The
fields
on the Issues Form are named "Project Name" (formerly the Category field)
and (a newly created field), "Comm Name".

I have tried several ways to do this, using the Controls on the form
itself,
and/or by using queries, but none seem to be working. So, now I'm a bit
confused about whether to use an Update or Inner Join Query? And if either
of
these are the solution, could someone help with the correct syntax!
Grrrrhhh!
Because even after searching books and the online MS Office Assistance -
for
correct syntax for queries - I've tried just about everthing - but
nothing
seems to be working.

I would like to do the following: (basically populate the field from one
table based on control / or field from another table).

1) Populate the "Comm Name" field in the Issues Table with the
corresponding
Comm Name, using data from the Projects Table - based on the Project Name
(field) that is entered in Issues Table. In other words, when I enter the
Project Name in the Issues Form - or the Issues Table, I would like to
the
Comm Name to automatically appear. The Project Name field is a Combo Box,
while I would like the Comm Name to be a simple text box (which is
locked -
no data entry allowed) in the Issues Table or the Issues Form.

2) When the Issues form is open during data entry, I would like the Comm
Name to appear (in a locked field) -when the Project Name is entered into
the Project Name field (which is a Combo Box). -Of course - as with all of
the other fields (I'm hoping that the control on that field will
automatically populate the Issues Table- based on what is entered into the
Issues Form.

If someone has a solution to this problem. I would be most appreciative.
The
process of populating fields based on data in another field is pretty
frustrating.
Thanks so much in advance.

Sarah

 

[newsneakers]

Hi John,
<lol>
We must have posted replies at the same time... I just remembered that rule.
I changed the query statement to include brackets... and it worked just
fine!
Thank you so much!
Sarah

Hi again...
I just created a Query using the following syntax:

UPDATE Issues
INNER JOIN Projects
ON Issues.Project Name= Projects.Project Name
SET Issues.Comm Name = [Projects].[Comm Name]

however, I'm getting the following error message:

Syntax error (missing operator) in query expression 'Issues.Project Name =
Projects.Project Name'

This is one reason to avoid using special characters - SUCH AS BLANKS
- in table or field names. A blank is meaningful punctuation; Access
is seeing

ON Issues.Project

and thinking you're at the end of something, and it's looking for an
operator (such as an equals sign).

If you must use blanks or other special characters, you must always
enclose names in [brackets]:

UPDATE Issues
INNER JOIN Projects
ON Issues.[Project Name] = Projects.[Project Name]
SET Issues.[Comm Name] = [Projects].[Comm Name]


John W. Vinson[MVP]