New Access User Confused

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am very confused. Everyone tells me NOT to use the Lookup Wizard as it
causes problems - fine.

However, I have two tables - one of those tables is called "Companies" and
it has list of companies in this table. My next table has a field in it
called "Company" and what I want to do is query the Companies table so the
user can choose a valid company.

Seeing as I am new and not that good at access what is the easiest way to do
this without using Lookup Wizard as everyone tells me this causes problems.

Thanks for helping a newbie.

Ty
 
Ty said:
I am very confused. Everyone tells me NOT to use the Lookup Wizard
as it causes problems - fine.

However, I have two tables - one of those tables is called
"Companies" and it has list of companies in this table. My next
table has a field in it called "Company" and what I want to do is
query the Companies table so the user can choose a valid company.

Seeing as I am new and not that good at access what is the easiest
way to do this without using Lookup Wizard as everyone tells me this
causes problems.

Thanks for helping a newbie.
Click to expand...
The Company field in your "next" table should contain only the ID of the
company from your companies table. I'm assuming that the company table has a
companyID, name, address, phone, etc.

Build a query relating the two. It would contain Next.company,
companies.companyname, companies.companyContact, etc.

Use that query in your forms and reports. Now when you enter the companyID
into next.company the name and contact will fill automatically. If teh
contact name chanes you only have to make the change in one place.

On your form, instead of having the next.company field, add a combobox that
lists the company and has next.company as it's focus.
Open the query this combo is based on and sort on the company name to
complete the task.

It takes less time to do this than to read it (the second time you do it).

The Lookup Wizard is a really good idea but causes more problems than it
solves in it's current form.
 
My table of companies only has ID and Company Name. Sorry, but I am more
confused now then I was before.
 
I am very confused. Everyone tells me NOT to use the Lookup Wizard as it
causes problems - fine.

However, I have two tables - one of those tables is called "Companies" and
it has list of companies in this table. My next table has a field in it
called "Company" and what I want to do is query the Companies table so the
user can choose a valid company.

Seeing as I am new and not that good at access what is the easiest way to do
this without using Lookup Wizard as everyone tells me this causes problems.

Thanks for helping a newbie.

Ty
Click to expand...

Your Companies table should have at least 2 fields (more is OK if
needed), CompanyID and CompanyName.

Your other table can have a field named [Company]. It should be a
Number datatype, Long Integer field size.

Set the relationship of the 2 tables with the Companies CompanyID
field being one to many with the Company field in the other table.


On the form used for data entry, add a combo box.
Set it's Rowsource to something like:
"Select Companies.CompanyID, Companies.CompanyName From Companies
Order by CompanyName;"
Set the combo box bound column to 1.
Set the Column count to 2.
Set the column widths to 0";1"
Set the control source to the Company field.
Set the AutoExpand property to Yes.
Set the LimitToList property to Yes.

Select the company name in the combo box and the CompanyID number will
be stored in the [Company] field in your table.

When you need to display the actual company name in a query, show
both tables in the query grid, using the Companies CompanyName field
instead of the Company field wherever you wish to show the actual name
of the company.
 
Since I wrote most of the article original article
(http://www.mvps.org/access/lookupfields.htm) I guess I should explain a
solution.

First of all, in all applications, users should be working in forms, not
tables or queries.. That is unless there is only 1 user and will never be
more than 1. In that case, and for the simplest of uses only you might be
able to get away with a lookup field without running into the problems
listed in the URL above.

In a form, (even a datasheet form) you can use a combobox as a lookup bound
to the underlying data field. A wizard can also help you with setting it up.
Work on your form in form view, then switch to datasheet view, if that's
easier.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
don't suppose I could ask you more questions - cause I am just not getting
this.

my email is: microsoft at keyssi dot com

fredg said:
I am very confused. Everyone tells me NOT to use the Lookup Wizard as it
causes problems - fine.

However, I have two tables - one of those tables is called "Companies" and
it has list of companies in this table. My next table has a field in it
called "Company" and what I want to do is query the Companies table so the
user can choose a valid company.

Seeing as I am new and not that good at access what is the easiest way to do
this without using Lookup Wizard as everyone tells me this causes problems.

Thanks for helping a newbie.

Ty
Click to expand...

Your Companies table should have at least 2 fields (more is OK if
needed), CompanyID and CompanyName.

Your other table can have a field named [Company]. It should be a
Number datatype, Long Integer field size.

Set the relationship of the 2 tables with the Companies CompanyID
field being one to many with the Company field in the other table.


On the form used for data entry, add a combo box.
Set it's Rowsource to something like:
"Select Companies.CompanyID, Companies.CompanyName From Companies
Order by CompanyName;"
Set the combo box bound column to 1.
Set the Column count to 2.
Set the column widths to 0";1"
Set the control source to the Company field.
Set the AutoExpand property to Yes.
Set the LimitToList property to Yes.

Select the company name in the combo box and the CompanyID number will
be stored in the [Company] field in your table.

When you need to display the actual company name in a query, show
both tables in the query grid, using the Companies CompanyName field
instead of the Company field wherever you wish to show the actual name
of the company.
Click to expand...
 
don't suppose I could ask you more questions - cause I am just not getting
this.

my email is: microsoft at keyssi dot com

fredg said:
I am very confused. Everyone tells me NOT to use the Lookup Wizard as it
causes problems - fine.

However, I have two tables - one of those tables is called "Companies" and
it has list of companies in this table. My next table has a field in it
called "Company" and what I want to do is query the Companies table so the
user can choose a valid company.

Seeing as I am new and not that good at access what is the easiest way to do
this without using Lookup Wizard as everyone tells me this causes problems.

Thanks for helping a newbie.

Ty
Click to expand...

Your Companies table should have at least 2 fields (more is OK if
needed), CompanyID and CompanyName.

Your other table can have a field named [Company]. It should be a
Number datatype, Long Integer field size.

Set the relationship of the 2 tables with the Companies CompanyID
field being one to many with the Company field in the other table.

On the form used for data entry, add a combo box.
Set it's Rowsource to something like:
"Select Companies.CompanyID, Companies.CompanyName From Companies
Order by CompanyName;"
Set the combo box bound column to 1.
Set the Column count to 2.
Set the column widths to 0";1"
Set the control source to the Company field.
Set the AutoExpand property to Yes.
Set the LimitToList property to Yes.

Select the company name in the combo box and the CompanyID number will
be stored in the [Company] field in your table.

When you need to display the actual company name in a query, show
both tables in the query grid, using the Companies CompanyName field
instead of the Company field wherever you wish to show the actual name
of the company.
Click to expand...
Click to expand...

You're welcome to ask as many questions as you wish.... so long as
it's here in the newsgroup.
 
sometimes it *is* hard to picture something from written explanations. since
you posted your email address, i sent you a small demo database showing the
concepts that were explained here, and a Word doc to talk you thru the demo.

if you have further questions, come on back to the newsgroups and post. you
don't want to limit yourself to a dialogue with one person when you can
solicit help from many experienced and expert Access users and developers
here.

hth


Ty said:
don't suppose I could ask you more questions - cause I am just not getting
this.

my email is: microsoft at keyssi dot com

fredg said:
I am very confused. Everyone tells me NOT to use the Lookup Wizard as it
causes problems - fine.

However, I have two tables - one of those tables is called "Companies" and
it has list of companies in this table. My next table has a field in it
called "Company" and what I want to do is query the Companies table so the
user can choose a valid company.

Seeing as I am new and not that good at access what is the easiest way to do
this without using Lookup Wizard as everyone tells me this causes problems.

Thanks for helping a newbie.

Ty
Click to expand...

Your Companies table should have at least 2 fields (more is OK if
needed), CompanyID and CompanyName.

Your other table can have a field named [Company]. It should be a
Number datatype, Long Integer field size.

Set the relationship of the 2 tables with the Companies CompanyID
field being one to many with the Company field in the other table.


On the form used for data entry, add a combo box.
Set it's Rowsource to something like:
"Select Companies.CompanyID, Companies.CompanyName From Companies
Order by CompanyName;"
Set the combo box bound column to 1.
Set the Column count to 2.
Set the column widths to 0";1"
Set the control source to the Company field.
Set the AutoExpand property to Yes.
Set the LimitToList property to Yes.

Select the company name in the combo box and the CompanyID number will
be stored in the [Company] field in your table.

When you need to display the actual company name in a query, show
both tables in the query grid, using the Companies CompanyName field
instead of the Company field wherever you wish to show the actual name
of the company.
Click to expand...
Click to expand...
 
Ty, don't forget to check the other questions or your answers.

sometimes it *is* hard to picture something from written explanations. since
you posted your email address, i sent you a small demo database showing the
concepts that were explained here, and a Word doc to talk you thru the demo.

if you have further questions, come on back to the newsgroups and post. you
don't want to limit yourself to a dialogue with one person when you can
solicit help from many experienced and expert Access users and developers
here.

hth


Ty said:
don't suppose I could ask you more questions - cause I am just not getting
this.

my email is: microsoft at keyssi dot com

fredg said:
On Sun, 9 Jan 2005 12:09:02 -0800, Ty wrote:

I am very confused. Everyone tells me NOT to use the Lookup Wizard as it
causes problems - fine.

However, I have two tables - one of those tables is called "Companies" and
it has list of companies in this table. My next table has a field in it
called "Company" and what I want to do is query the Companies table so the
user can choose a valid company.

Seeing as I am new and not that good at access what is the easiest way to do
this without using Lookup Wizard as everyone tells me this causes problems.

Thanks for helping a newbie.

Ty

Your Companies table should have at least 2 fields (more is OK if
needed), CompanyID and CompanyName.

Your other table can have a field named [Company]. It should be a
Number datatype, Long Integer field size.

Set the relationship of the 2 tables with the Companies CompanyID
field being one to many with the Company field in the other table.


On the form used for data entry, add a combo box.
Set it's Rowsource to something like:
"Select Companies.CompanyID, Companies.CompanyName From Companies
Order by CompanyName;"
Set the combo box bound column to 1.
Set the Column count to 2.
Set the column widths to 0";1"
Set the control source to the Company field.
Set the AutoExpand property to Yes.
Set the LimitToList property to Yes.

Select the company name in the combo box and the CompanyID number will
be stored in the [Company] field in your table.

When you need to display the actual company name in a query, show
both tables in the query grid, using the Companies CompanyName field
instead of the Company field wherever you wish to show the actual name
of the company.
Click to expand...
Click to expand...
Click to expand...
 
Ty said:
My table of companies only has ID and Company Name. Sorry, but I am
more confused now then I was before.
Click to expand...
My solution is fundamental to the way a database like Access is properly
used.

There is a massive amount of help available in Help and you should spend
some time reading about relational databases and looking at the Northwind
database for examples.

You MUST know what a query is, how to create one and why they are essential.

Even if you just have a company ID and name what I wrote is the proper way
to obtain the information.
 
Back
Top