look up error message

[sansui]

I am running electronic tube production unit. I developed a ms access database to track my production flow.



Production goes through 3-phases, (1)Fabrication (2)Testing & (Issuing).

Each product is identified by unique sl.no.

I created 4-tables (1)product entry (2)Fabrication (3)Testing & (4)Issuing.

Product entry table contains only one field of text type (product sl.no), that is its primary key.

Second table contains sl.no(primary key), fab sent date,fab receive date, clearance_for_testing (yes/no field).

Third table contains sl.no(primary key), test agent, Test sent date,test receive date, test sticker,clearance_for_supply (yes/no field).

Fourth table contains sl.no(primary key), supplier, supply date.


PROBLEM:

sl.no of second table should take a 'look up' on a query. That query should contains a field that has all sl.nos of products which entered in first table except those

which were sent for fabrication (fabrication sent date field not null).

sl.no of third table should take a 'look up' on a query. That query should contains a field that has all sl.nos of products which entered in second table and cleared for testing except those which were sent for testing (test sent date field not null).

sl.no of fourth table should take a 'look up' on a query. That query should contains a field that has all sl.nos of products which entered in third table and cleared for supply except those which were sent for supply (supply date field not null).

Someone help? When I went for look-up creation, the error states "No valid field values can be found. You may have selected a query that uses the table you are adding the look-up to. Please select a new source."

[orange]

Fabrication and Testing sound like Activities/Processes/Functions. From a system point of view these would deal with Things/Entities. For example, when fabricating a chassis, we need some metal rails, some side panels and some nuts and bolts.
In database it is usually the things and how things are related that make up the database design.
Can you post a jpg of your tables and relationships? -- you may have to zip that file.

If you mean lookup at the table level, that is not considered a good practice. See this for explanation.

Here is a free data model for a typical manufacturing process. This one is specific to paper manufacturing.

Good luck

[sansui]

Can you post a jpg of your tables and relationships? -- you may have to zip that file.

If you mean lookup at the table level, that is not considered a good practice.

Good luck

Picture shows 4-tables. Unique product serial numbers are entered in the product list table. And there will be 3-forms, one for fabrication data entry, other for testing data entry and last one for supply entry. slno of text type serve as primary key in all tables. ClearanceForTesting/supply fields are Yes/No fields.

Challenges:
Slno field in fabrication table should be a drop list containing all the products from product list table that are never sent for fabrication (fabrication sent date field is null).

Slno field in the testing table should be a drop list containing all the products from fabrication table that are passed ClearnceForTesting and never sent for testing (test start date field is null).

Slno field in the supply table should be a drop list containing all the products from testing table that are passed ClearedForSupply and never sent to supplier (supplied date field is null).

Finally I would like to generate a report which states how many products supplied in a particular period, how many products at under testing status, status of after fabrication items, etc......

[orange]

I think your concept of table is incorrect.

You may have 1 table with a list of potential serial numbers.
Also in that table are fields to represent:
FabStartDate
FabEndDate
TestStartDate
TestEndDate
TestAgent <---------------if this is a person you may get id from a list of People
SupplierID <-----------------assumes you have a list of Suppliers
SuppliedDate

Now as you move through your Processes

EG Fabrication
On a Form you could have a combo of SerialNumbers that have Null/empty FabricationStartDate.
When that Product SerialNumber starts into Fabrication , you update the FabricationStartDate.
When it completes Fabrication and has passed anyInspection, you could update the FabricationEndDate.

On a Testing Form, you could SElect all Serial numbers with a null/empty TestStartDate
etc.
etc

You would NOT have 4 tables. Only 1 in this set up.

You identify records suitable for a process by means of Queries. (untested)

Ready for Fabrication
SELECT SERIALNUMBER FROM PRODUCTS
WHERE Len(FabricationStartDate &"") = 0

Ready For Testing
SELECT SERIALNUMBER FROM PRODUCTS
WHERE Len(TestStartDate &"") = 0

Ready For Supply
SELECT SERIALNUMBER FROM PRODUCTS
WHERE Len(SuppliedDate &"") = 0

Good luck with your project.