Lookup

[steve111]

HI ,

I have 3 tables
1. allparts
2 parts
3 assembly numbers

I also have a query of which looks at these 3 tables.
when I query a part from the assembly parts table it looks at the description in the allparts table against that part and allocates that description( this is ok
the next part in the query gets the part number from the parts table but I cannot get that description as well from the allparts table

is it possible to do a lookup in the query that looks at the parts part number then gives me the part description from the allparts table

thanks
steve

 

[JLCantara]

What's the difference between Allparts and Parts tables? You don't need lookup in a query: table relations do the job.

 

[steve111]

allparts contains all items to make any assembly
parts table contains parts to manufacture a complete item
I have the table relation but it don't work for this query
can I put the lookup I the report

SELECT parts.[KIT ID] AS [parts_KIT ID], parts.[Level number] AS [parts_Level number], parts.[PART NUMBER] AS [parts_PART NUMBER], allparts.[Part Description], [contract number].[contract number], parts.QUANTITY, [contract number].[group no], [contract number].[qty required], parts.total, parts.weight, parts.[advance orders], parts.ID AS parts_ID, [assembly part numbers].ID AS [assembly part numbers_ID], [assembly part numbers].[KIT ID] AS [assembly part numbers_KIT ID], [assembly part numbers].[PART NUMBER] AS [assembly part numbers_PART NUMBER], [assembly part numbers].[REVISION NUMBER], [assembly part numbers].[WHERE USED], [assembly part numbers].NOTES1, [assembly part numbers].number, [assembly part numbers].QTY, [assembly part numbers].[date generated], [assembly part numbers].[Level number] AS [assembly part numbers_Level number], allparts.Code, [qty required]*[QUANTITY] AS Expr1
FROM [contract number] INNER JOIN (([assembly part numbers] INNER JOIN parts ON [assembly part numbers].[ID] = parts.[KIT ID]) INNER JOIN allparts ON [assembly part numbers].[PART NUMBER] = allparts.[Part Number]) ON [contract number].[contract number id] = parts.[KIT ID]
WHERE ((([contract number].[contract number])=[ enter contract no]));

 

[JLCantara]

2 major points:

1 - Remove the spaces in table and field names: this will simplify you query;
2 - Attach you DB relationships.

Otherwise I cannot help...