Rookie needs help

[Dispatcher Scott]

I'll try to explain this the best way I can:

I have created a simple database with a few tables, a couple of queries, a
few reports and 1 form. When I designed the "main" table that holds the
major data, I needed some information to be "looked up" when inputting the
data into the form. So I created a sperate table that holds the information
for the first table.

Then I went back to the first table and created a "lookup field" to search
through. The 2nd table has 2 columns...my question is: When I create a
report, how can I define which of those 2 columns show on the report?

 

[Rick B]

You need to include both tables in your query (upon which your report is
based). You should have the two tables related to each other. In your
query (assuming you have a proper relationship built) you can use any of the
fields from the related table in the query results.

That is rather vague. Let me know if you need a more concise answer.

 

[Dispatcher Scott]

I may not have made myself clear... The problem is that the table that I
want to qry the data from has two columns, "col a" and "col b"

When I run a query it shows the data in "col b" (which is what I want it to
show) but when I run a report from that qry, it shows "col a"

 

[pietlinden]

I'll try to explain this the best way I can:

I have created a simple database with a few tables, a couple of queries, a
few reports and 1 form. When I designed the "main" table that holds the
major data, I needed some information to be "looked up" when inputting the
data into the form. So I created a sperate table that holds the information
for the first table.

Then I went back to the first table and created a "lookup field" to search
through. The 2nd table has 2 columns...my question is: When I create a
report, how can I define which of those 2 columns show on the report?

Oh, stay away from Lookups in tables. they're a disaster. Just join
the two tables in a query, show the fields you want (don't bother with
filters, because you can pass those when you open the report). then
build away. The lesson you should learn from this is that "Lookups
are an abomination"... they're counterintuitive and a PITA to use,
because Access does stuff behind the scenes to make them work. IOW,
if you control it, it's easier to document and understand, because
nothing is hidden. Apologies if you don't like the answer - but I
never ever use lookups for that very reason. (also, see www.mvps.org/access
and find the 10 commandments. It's one of them, so it's not just me.

Pieter

 

[John W. Vinson]

On Wed, 21 Mar 2007 12:37:38 -0700, Dispatcher Scott <Dispatcher

I'll try to explain this the best way I can:

I have created a simple database with a few tables, a couple of queries, a
few reports and 1 form. When I designed the "main" table that holds the
major data, I needed some information to be "looked up" when inputting the
data into the form. So I created a sperate table that holds the information
for the first table.

Then I went back to the first table and created a "lookup field" to search
through. The 2nd table has 2 columns...my question is: When I create a
report, how can I define which of those 2 columns show on the report?

Most Access developers really dislike the "Lookup Field" misfeature: see
http://www.mvps.org/access/lookupfields.htm for a critique. What you're seeing
is just one of the problems.

The Report cannot be based on your table with the lookup field, because -
appearances to the contrary notwithstanding - the looked-up value is NOT in
that table. Instead, create a Query joining your first table and the lookup
table; select the second column (the meaningful one) from the second table,
and all the other fields from the first table.

John W. Vinson [MVP]

 

[Dispatcher Scott]

I see what you're saying, but I don't know enough about access "yet" to know
how to gather the information I need any other way than lookups. The issue
as to why I'm using them is because I have several different people that will
be using this database and I don't want any other information given that what
I want them to put into that field. Plus, the information has a "code" that
represents it, so I feel that a seperate table is needed.

correct me if I'm wrong here, but should access be able to output a column
from two seperate tables in one report?

Rick B said something about relationships and I've tried taking a look at
those, and they look a little too complicated for me to mess with...

I have tried making several different queries and reports for each without
any luck...

 

[John W. Vinson]

I see what you're saying, but I don't know enough about access "yet" to know
how to gather the information I need any other way than lookups. The issue
as to why I'm using them is because I have several different people that will
be using this database and I don't want any other information given that what
I want them to put into that field. Plus, the information has a "code" that
represents it, so I feel that a seperate table is needed.

Then by all means use a "Lookup" - a combo box.

But use it ON A FORM, not in your table.

Base a Form on your main table; use the Combo Box Wizard to put a combo box on
the form, bound to the ID, based on the second table.

correct me if I'm wrong here, but should access be able to output a column
from two seperate tables in one report?

Two tables, ten tables, 32 tables... sure.

The way to do so is to create a Query joining the tables, and base the report
on that Query.

Rick B said something about relationships and I've tried taking a look at
those, and they look a little too complicated for me to mess with...

That's like saying "I read about using expressions in cells in an Excel
spreadsheet but they look a little too complicated..." Relationships are
*absolutely fundamental* to any productive use of Access. Check out the
Database Design 101 links in Jeff's website:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

John W. Vinson [MVP]