Data Type Question

S

Sandy

I have two table tbl_Repairers and tbl_Job_Details related on a one to many
on RepairerID field.

tbl_Repairers
RepairerID - Autonumber - Primary Key
RepName - Text

tbl_Job_Details
RepairerID - Number - LookUp on RepName.
<Other Fields>

When I set up a query using the RepairerID field from the tbl_Job_Details,
the values in the field show as text but if I want to run a query on one
repairer e.g. "AT" then I get a data mismatch. If the criteria is entered as
'1' then I get the results I want. How can I adjust the query such that if I
want to see all the jobs by AT then that is what I type in the query - not
1.

Sandy
 
S

Sandy

I am using lookup fields and your link shows a very interesting article
Arvin.

How would you suggest controlling the input to the 'many' sided field?

Sandy
 
A

Arvin Meyer [MVP]

Use a query (which is all a lookup field is) but expose the query join and
the resulting text field. You can probably copy and paste the SQL of the
lookup field into a query SQL window and go to the query's design view,
adding both the ID field and the text field to the query, then use whichever
you want to do the look up. You can also add the SQL to the combo box
rowsource property if it isn't there already.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
 
S

Sandy

Arvin

I think I generally get what you mean but "expose the query join and
the resulting text field" - can you elaborate or point me to an example
somewhere?
I am fairly new to this stuff I'm afraid.

Sandy
 
A

Arvin Meyer [MVP]

So, lets start by using the Northwind.mdb sample database that comes with
Access. Open the database by going to Help >>> Sample Databases >>>
Northwind Sample Database.

After closing the forms, you should be left with the database window. Go to
the Tables window and select the Order Details table. Now copy and paste it
or use Save As to a new name, I used name tblOD which put it at the end of
the table list. Open the new table in Design View and look at the ProductID
field. Click on the Lookup tab and change Combobox to textbox. Save and
close.

Now open a query and add the new table. Add the Products table. Notice how
they automatically link on ProductID. Now add all the fields from the new
table, and the Product Name field from the Products table. Now open the
query, and you'll see everything you need to for a report. If you add a
criteria in the Product Name column (in Design View) you'll get only those
orders with that product, without having to know the ProductID.

If you open the Orders form, you'll see the ProductID combo box in the
subform which stores the ProductID but displays the Product Name, just like
your original lookup, but without any confusion.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
 
S

Sandy

Thank you Arvin, well explained.
Sandy

Arvin Meyer said:
So, lets start by using the Northwind.mdb sample database that comes with
Access. Open the database by going to Help >>> Sample Databases >>>
Northwind Sample Database.

After closing the forms, you should be left with the database window. Go
to the Tables window and select the Order Details table. Now copy and
paste it or use Save As to a new name, I used name tblOD which put it at
the end of the table list. Open the new table in Design View and look at
the ProductID field. Click on the Lookup tab and change Combobox to
textbox. Save and close.

Now open a query and add the new table. Add the Products table. Notice how
they automatically link on ProductID. Now add all the fields from the new
table, and the Product Name field from the Products table. Now open the
query, and you'll see everything you need to for a report. If you add a
criteria in the Product Name column (in Design View) you'll get only those
orders with that product, without having to know the ProductID.

If you open the Orders form, you'll see the ProductID combo box in the
subform which stores the ProductID but displays the Product Name, just
like your original lookup, but without any confusion.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
Click to expand...
 
Top