Query to populate other fields

[LG]

I have 2 tables built. 1 reason codes the other is commerical.
In the commercial table it is set up to be a form where processors enter
Name, address, city, state, zip, and R1, R2, R3 all have a column following
for description that would be left blank. I would like to run a query so
that the codes they type in will auto populate the reason in the query only.
I set the query up with adding the commerical table and 3 of the reason code
tables. I tried to join the R1, R2, and R3 from the commercial table and
join them to the reason codes. I get a message that
cannot join memo, ole or hyperlink. How do I get around this since the
desriptions are more than 255 characters.
thank you in advance for any assistance

 

[Fred]

I noticed that nobody answered.

Not sure I understand your post, but taking a guess......


You join your tables/records using one unique field in each table. So,
records are joined only when these two fields are already equal. At least
one of them should be a primary key, maybe both in your case. You don't
join on the fields that you are trying to "cross-load"

Then make an update query that shows the fields from both tables and
cross-load them per your wishess.

 

[LG]

Will this allow the processor to enter for ex #1 in the reason code field on
the form and it will auto populate the reason in the field?
After the form is completed a mail merge has to be done to show not show the
number of the reason code but just the description.
Currently they use excel type in the reason code # and the description
autopopulates. I would like to put this in access so I don't have 20
seperate sheets each month with 20-30 tabs in each. I need to track all
entries and currently we have to cut and paste every 30 days into a master
file.

 

[LG]

Thanks so much for all your guidance. Any preference for books. I am new at
this with many projects going. Most things are coming out of excel and I have
to convert them. I will also need to learn Visual Basic. Any reccomendations

Yes, though its not 'autopopulating' a field in the sense that its assigning
a value to a field, its showing the value from the memo field in the reason
codes table by means of a control bound to that field.

As regards a mail merge that's not a problem; as the source for the merge
you'd use a query which joins the tables and returns the memo field, along
with what other columns you need for the merge, restricting the query to the
current record by referencing a control on the main form bound the primary
key as a parameter. Alternatively you could insert the relevant values from
the form into bookmarks in a Word document. You'll find examples of various
Access to word automation operations, including merging and filling bookmarks,
at:

http://community.netscape.com/n/pfx/forum.aspx?msg=23781.1&nav=messages&webtag=ws-msdevapps


But first consider whether you need to use Word at all. You may well be able
to achieve what you want more easily by means of an Access report.

When moving from Excel to Access its important to understand how a relational
database differs from a workbook. They are very different animals.
Essentially a relational database is a model of a part of the real world and
is made up of tables which represent 'entity types', the columns of the
tables representing the 'attributes' of each entity type. Relationships
between the tables pull them all together into a 'logical model'. Tables are
related by a foreign key column in one referencing the primary key column of
another, e.g. an Orders table might have a CustomerID foreign key column
which references the primary key CustomerID column of a Customers table. A
table can also model a relationship, as would be the case with the
commercial_reasons table in my first reply. A table modelling a relationship
in this way is still modelling an entity type as a relationship type is
really just a special kind of entity type.

Following the principle that when you have three hours to cut down a tree
spend two of them sharpening the axe, some time becoming familiar with
principles of the relational model and studying how other databases are put
together, such as the sample Northwind database which comes with Access, will
save a lot of headaches later on. If you Google Access +Tutorial you should
find plenty of guidance to choose from, or there are many good books on
Access available. Despite the ubiquity of online resources I'm still a fan
of the printed page.

Ken Sheridan
Stafford, England

Will this allow the processor to enter for ex #1 in the reason code field on
the form and it will auto populate the reason in the field?
After the form is completed a mail merge has to be done to show not show the
number of the reason code but just the description.
Currently they use excel type in the reason code # and the description
autopopulates. I would like to put this in access so I don't have 20
seperate sheets each month with 20-30 tabs in each. I need to track all
entries and currently we have to cut and paste every 30 days into a master
file.

Your problems stem from the fundamental flaws in the design of your
commercial table. What you are trying to achieve here is a many-to-many

[quoted text clipped - 58 lines]

desriptions are more than 255 characters.
thank you in advance for any assistance

 

[LG]

Thanks for all the help. Any reccomendations on books. I am new at this and
only been doing access for 2 months and have many projects that need to be
converted. I will also need to learn about macros and visual basics so any
books that you can reccomend would be helpful.

Yes, though its not 'autopopulating' a field in the sense that its assigning
a value to a field, its showing the value from the memo field in the reason
codes table by means of a control bound to that field.

As regards a mail merge that's not a problem; as the source for the merge
you'd use a query which joins the tables and returns the memo field, along
with what other columns you need for the merge, restricting the query to the
current record by referencing a control on the main form bound the primary
key as a parameter. Alternatively you could insert the relevant values from
the form into bookmarks in a Word document. You'll find examples of various
Access to word automation operations, including merging and filling bookmarks,
at:

http://community.netscape.com/n/pfx/forum.aspx?msg=23781.1&nav=messages&webtag=ws-msdevapps


But first consider whether you need to use Word at all. You may well be able
to achieve what you want more easily by means of an Access report.

When moving from Excel to Access its important to understand how a relational
database differs from a workbook. They are very different animals.
Essentially a relational database is a model of a part of the real world and
is made up of tables which represent 'entity types', the columns of the
tables representing the 'attributes' of each entity type. Relationships
between the tables pull them all together into a 'logical model'. Tables are
related by a foreign key column in one referencing the primary key column of
another, e.g. an Orders table might have a CustomerID foreign key column
which references the primary key CustomerID column of a Customers table. A
table can also model a relationship, as would be the case with the
commercial_reasons table in my first reply. A table modelling a relationship
in this way is still modelling an entity type as a relationship type is
really just a special kind of entity type.

Following the principle that when you have three hours to cut down a tree
spend two of them sharpening the axe, some time becoming familiar with
principles of the relational model and studying how other databases are put
together, such as the sample Northwind database which comes with Access, will
save a lot of headaches later on. If you Google Access +Tutorial you should
find plenty of guidance to choose from, or there are many good books on
Access available. Despite the ubiquity of online resources I'm still a fan
of the printed page.

Ken Sheridan
Stafford, England

Will this allow the processor to enter for ex #1 in the reason code field on
the form and it will auto populate the reason in the field?
After the form is completed a mail merge has to be done to show not show the
number of the reason code but just the description.
Currently they use excel type in the reason code # and the description
autopopulates. I would like to put this in access so I don't have 20
seperate sheets each month with 20-30 tabs in each. I need to track all
entries and currently we have to cut and paste every 30 days into a master
file.

Your problems stem from the fundamental flaws in the design of your
commercial table. What you are trying to achieve here is a many-to-many

[quoted text clipped - 58 lines]

desriptions are more than 255 characters.
thank you in advance for any assistance