Need help with Access Relationships for a Query

Hello,



I'm a novice when it comes to Access; however, I've been given the task of creating a database for my company, and I'm really lost on how to properly create the joins between the tables. Essentially, we are manufacturing a finished goods part we'll call "ALPHA". This assembly is produced via a top-level work order, as well as a sub-work orders for lower-level assemblies that combine to make the Alpha part. In this case it's as follows:

WO# 1 = ALPHA SUB ASSY 1 (BATTERY/BOARDS)
WO# 2 = ALPHA SUB ASSY 2 (SENSOR)
WO# 3 = ALPHA FINISHED GOODS ASSEMBLY

So, the components consumed in WO#1 and WO# 2 are put together, along with additional components to create the finished good part, ALPHA, via WO# 3.

Within WO# 1, there are three serial numbers which must be captured
Within WO# 2, there is one serial number which must be captured
Within WO# 3, there is one serial number which must be captured

I have created the three necessary tables - one for each assembly where certain data, such as these serial numbers are entered by the production worker. (There is no part number table needed as each of the three tables are coded with default values).

Here's my issue:

I need to design a query that looks at these tables and pulls the associated serial numbers from each - so that all four of the serial numbers associated with WO# 3 (the final product) can be easily located. I have tried several ways to join fields together, but only seem successful when I join the primary ID to the same ID in the other tables. Problem with that is only the ID numbers are displayed, not the serial numbers, when I run the query.

Here's the table columns I have created:

WO# 1	BatteryID	BatteryWO	PartNum	PartDesc	BrdASN	BrdBSN	BatterySN	Employee
WO# 2	SensorID	SensorWO	PartNum	PartDesc	SensorSN	Employee	ProdDate
WO# 3	ProbeID	ProbeWO	PartNum	PartDesc	ProbeSN	Employee	ShipDate

The 5 bolded items are what I need to capture in a query. Management preferably would like to be able to query off of any of the tables upward or downward (e.g., you can find the associated serial numbers based on the SensorSN, etc.)

I'm not sure if this is all the information you need to help me with this issue, but will happily provide any other information you might need. I would be grateful for any assistance!

Thank you,
Eva Marie

Running on the assumption that your Tables are named Batteries, Sensors, and Probes and that WO# 1, WO# 2, and WO# 3 are your Primary Keys/Foreign Keys, you'd use the following Query:

Code:

SELECT 
  [Batteries].[BrdASN], 
  [Batteries].[BrdBSN], 
  [Batteries].[BatterySN], 
  [Sensors].[SensorsN], 
  [Probes].[ProbesN]
FROM 
  (
      [Batteries] 
    INNER JOIN 
      [Sensors] 
    ON 
      [Batteries].[WO #1] = [Sensors].[WO# 2]
  ) 
INNER JOIN 
  [Probes] 
ON 
  [Sensors].[WO# 2] = [Probes].[WO# 3]
WHERE 
  [Batteries].[WO #1]=<Your Work Order Number HERE>

If your Work Order Number is text, you'll need to place it in single quotes ('<Your Work Order Number HERE>').