Help building a simple database

[skier500000]

I am attempting to build a database with the following tables and fields that will Auto Populate a form when the user is prompted to enter an eight digit number, then save the form data to another table to query for analysis.

Table Name Field(s) Key

Material Material# PK
Product Product PK
Customer Customer PK
Blender Blender PK
TestLab Lab 1/Lab2 No Key


Lookup
Material #: FK to Same Above
Product FK to Same Above
Customer FK to Same Above
Batch #: Unbound
Wt. KG Unbound
Blender #: Ubnound
Ext. Test 1 From Table Above
Ext. Test 2 From Table Above
Time In System Time
Date In System Date
Time Out System Time
Date Out System Date
Comments Unbound
Allergens Yes/No for remaining fields
Kosher
USDA
Sieves
APC
Coliform
E-Coli
Staph
Salmonella-25g
Salmonella-375g-1
Salmonella-375g-2
Yeast-Mold
Listeria
Thermophillic Spores
Fecal Enterococci
Staph Enterotoxin
CEM Fat
Karl Fischer Moisture
TBHQ
BHT
MSG%
Acidic Acid%
Citric Acid %
BHT%
TBHQ%
pH
Extraneous Matter
GMO
Total Butterfat
Total Fat
Scoville Heat Units
Total Sugar
Protien Sulfites
Vitamin A
Vitamin C

I have not had any success thusfar, can someone give me some guidance?

[maxamis4]

More info is needed.

Summary:

You want a form that will allow a user to enter data. From this form you want the user to submitt the data into another table? is this correct??

Please explain thanks

[skier500000]

Yes, I want the form to populate everything but Batch#, Belnder #, WT KG, Comments and Time and Date Out.
The user will enter those fields.

Thanks for the help

[skier500000]

Can anyone help me do this?
I can not seem to get it to work.

[MMcCarthy]

OK, we need the information clarified differently.

Firstly give the metadata for each table involved. Here is an example of how to post table MetaData :
Table Name=tblBookings

Field; Type; IndexInfo
 Field; Type; IndexInfo
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

Next we need the SQL of the query that this form is build on?

Thirdly, do you just want to find an existing record when the user enters the number? If so have a look at this tutorial ...

Example Filtering on a Form

Mary

[skier500000]

Below is the sql statement of the query.
SELECT Material.MaterialID, Lookup.Technician, Lookup.[Material #], Lookup.Customer, Lookup.Product, Lookup.[Batch#], Lookup.KGs, Lookup.[Blender#], Lookup.[Test Lab 1], Lookup.[Test Lab 2], Lookup.[Time In], Lookup.[Date In], Lookup.[Time Out], Lookup.DateOut, Lookup.Comments, Lookup.Allergens, Lookup.Kosher, Lookup.USDA, Lookup.Sieves, Lookup.APC, Lookup.Coliform, Lookup.[E-Coli], Lookup.Staph, Lookup.[Salmonella-25g], Lookup.[Salmonella-375g-1], Lookup.[Salmonella-375g-2], Lookup.[Yeast-Mold], Lookup.Listeria, Lookup.[Thermophillic Spores], Lookup.[Fecal Enterococci], Lookup.[Staph Enterotoxin], Lookup.[CEM Fat], Lookup.[Karl Fischer Moisture], Lookup.TBHQ, Lookup.BHT, Lookup.[MSG%], Lookup.[Acidic Acid%], Lookup.[Citric Acid %], Lookup.[BHT%], Lookup.[TBHQ%], Lookup.pH, Lookup.[Extraneous Matter], Lookup.GMO, Lookup.[Total Butterfat], Lookup.[Total Fat], Lookup.[Scoville Heat Units], Lookup.[Total Sugar], Lookup.Protien, Lookup.Sulfites, Lookup.[Vitamin A], Lookup.[Vitamin C]
FROM Material INNER JOIN Lookup ON Material.MaterialID = Lookup.[Material #];

The data definitions are listed above with Table Name, Field Name and Key Definition.

I hope this is enough information.