Field lookup query question

[Tinbendr]

Access 2003.

I have two tables. Master and IssuePart.

Each table has a report number (Master.OA) that ties all this
together.

I wanted to make the Master table perform a lookup by matching the
report number in IssuePart and bringup a listbox. But the query won't
cooperate.

I can manually enter a report number and get the results I want, but
as soon as I try to change the criteria to the Master report number
field name, it chokes with syntaxs errors and such.

Here's the SQL the Builder produces.

SELECT IssueData.[Part Number], IssueData.[Serial Number] FROM
IssueData WHERE ((ISSUEDATA.[OA NUMBER]=MASTER.OA));

But this will prompt me to enter the Master.OA

How to I make this dynamic, using the current record report number?

Thanks in advance!

 

[Dale Fye]

How do you want to use this? Is this in a form?

You will need to:
1. Either provide a specific value for the query to evaluate the where
clause. The way you have the query written, Access actually thinks that
Master.OA is a parameter that you want to evaluate, thus the prompt.

WHERE ISSUEDATA.[OA NUMBER] = Master.OA

2. Join the Master table to the IssueData table. something like:

Select Master.OA, IssueData.PartNumber, IssueData.SerialNumber
FROM Master INNER JOIN IssueData
ON Master.OA = IssueData.[OA_Number]

Hope this helps.
Dale

 

[Tinbendr]

Thanks very much for your effort.

I guess a little background is needed.

I currently have several Word table and Excel sheets that contain all
this info. I'm constantly having search for history in several of
these files. They are seperated by Fiscal Year. I get calls for
history, usually with just a part number.

I thought that building a master table in Access would be a better
option.

The question at hand has to do with Parts_Issued, Cores_Returned, and
Repair_Orders. While each report has a single Report_Number, the
other three fields may have 1 to many. So I split the three fields
into their own table with the Report number being the link between all
of them.

So the master table, for multiple parts, cores or wo#, will have, "See
Attachment", in the field. What I would really like is to double click
on the "See attachment" and the pertinent info be displayed in a Non-
selectable Listbox. Maybe this needs to be in a form, as you thought?

I'm competent in Excel and Word VBA, but I'm a newbie to Access. It's
a bit different. (I have a Access 97 VBA book, but this book doesn't
talk about relationships that much.)

How do you want to use this? Is this in a form?

No it's in the Master table.

The join doesn't work in the table lookup.