Lookup Function (1 Viewer)

[maabbas]

i am looking for some advise how to create a query from Bill of material table,
bill of material table looks like below


FinishedProduct,ItemNumber
64000,7975
64000,7685
64000,7695
64000,6444

6444 is WIP

Same table has Work in process (WIP) information,

FinishedProduct, Item Number
6444, 10
6444, 20
6444, 30

in order to create product 64000 we need following item include WIP, which also have 3 different raw material.

is there any way i can create a query it show both WIP and Finish Bill of material if we select a Finished Product in criteria.

your help will be greatly appreciated.

 

[plog]

Ugh, I can't parse your data. You can't delimit your fields by spaces and include spaces in their names (you shouldn't use spaces in field names at all):

Finished Product Item Number
64000 7975

Is that 4 fields? Or is it 2? Maybe 3? When posting data, seperate your fields by commas (or another character not used in the data nor names):

Field1, Field2, Field3
Dave, 13, 2/2/2009
Sally, 54, 1/6/2019

Please repost. This time include 2 sets of data:

A. Starting data from your tables. Use the format I demonstrated, include table and field names and enough data to cover all cases.

B. Expected results of A. Show us what data you expect to end up with when you feed this query the data from A.

 

[maabbas]

Ugh, I can't parse your data. You can't delimit your fields by spaces and include spaces in their names (you shouldn't use spaces in field names at all):

Finished Product Item Number
64000 7975

Is that 4 fields? Or is it 2? Maybe 3? When posting data, seperate your fields by commas (or another character not used in the data nor names):

Field1, Field2, Field3
Dave, 13, 2/2/2009
Sally, 54, 1/6/2019

Please repost. This time include 2 sets of data:

A. Starting data from your tables. Use the format I demonstrated, include table and field names and enough data to cover all cases.

B. Expected results of A. Show us what data you expect to end up with when you feed this query the data from A.

:banghead: i need to do this, i change the question

 

[plog]

Please post A & B datasets as I requested

 

[maabbas]

Both sets of data are in the same table.

 

[plog]

Nope. Please follow my instructions if you would like me to help.

 

[Micron]

You can't delimit your fields by spaces and include spaces in their names

It's probably [FinishedProduct] and [ItemNumber] (brackets are mine just to show what I think the fields are); poster is just separating with commas rather than using a table or table format. IIRC, being able to insert a table here is one thing that the forum sorely lacks. Anyway, on with it...

If I understand the request, one solution would be to add a field to flag the product as being completed. A simple Boolean field would suffice, but a date field would not only indicate complete, but also when. So maybe ComplDate? If null, it is not complete. If there is a date, it is complete. Or a Yes/No type of field. Then your query can specify complete, or not, or either.