need some assistance

[WillemTWC]

Good Afternoon. I have a beginner problem. i have 2 tables. 1 table has 3 fields F1 F2 F3. my second table has 4 fields F1 F2 F3 F4. Table 2 (with four fields) has one field in common with Table 1 which i created a relationship so that my table looks like this in pic 1

Now this works all well and i have no issues with this. These too tables are linked so that i can input certain criteria for certain codes. Which also works well when i make the input form. All of this works perfectly!

These two input tables i need to access via another form to input a Job card(new form)(table 3). ***additional info the Table 1 consist of 500 codes and all the codes have specific instructions***
What will be the easiest way to create that third form to be able to make a job card...? in pic2 i have started with a job card and managed to get a combo box in and linked too all the codes. I just cant seem to figure out how i will get the "sub table with instructions" to show on the page so that it correlates with the combo box - which is linked to all 500 codes, so when i choose a code from the combo box it will display the instructions in another list underneath it.

Basically pull in all info from those two Tables add some more Fields to create a job card and then safe into a new table to manage jobs. (or something like that)

[ranman256]

you can make a JobCardBuild form.
user picks the record from 1 (or 2 )tables
click 'accept' button
run append queries to add the data to the actual tJobCard table, based on the picks in th build form.

[orange]

Willem,

Step back from your tables and forms and tell us in simple, plain English---no database terms---
What exactly is a JobCard? What is its purpose in your organization?
Who uses one?
We do not know you or your business. So telling us you have 2 tables with fields F1,F2... doesn't give us any context to understand your set up. And I don't recognize an issue when you say "All of this works perfectly!"

Good luck.

[WillemTWC]

yeah sorry i have been looking at this problem for so long my eyes are going wonky
Ok layout - the company makes handles and accessories . i have one person that handles all manufacturing process of parts. the job card will go from workstation to workstation as the job progresses. to make things easier to manage i want to create a simple form that he completes to create a printed job card as well as a easy table that we can keep track of what Job is where. On the form i want him to enter various fields of text , but one fields will be combo box to just select what item he wants to go onto this card. with that item there are certain instruction and not two are the same. so when he selects one of the 500 items from the combo box the form must populate all relevant instructions to the job card. now the 500 items are pre-loaded into a table and the instructions for each item is linked to different items. so i have a job form that has a combo box but i do not know how to get the instructions linked to items onto that form.

what i have done is i created a table with partcode and all info linked to that partcode and another table that issues instructions for each code. some items have one instructions some has 4 lines. so i created a a relationship between the two tables.

[orange]

Thanks that is helpful.

Job, Part, PartType, Item(is that a Process step or??)

A question for you - you have mentioned Items and Instructions for Items.

Could you give us a few sample records for these?
And perhaps a few sample JobCards.

[WillemTWC]

will it be possible i can send you screen shots of the form i made , with explanation of what the form does and then a second one with what needs to be done ?

[orange]

You could post a copy o f your database with private info removed. Then tell us specifically what the current problem is --what you want, what you have and details of what you have tried and results obtained.

[WillemTWC]

very easy. The form is basically used to input details of the part with a sub table-for the instructions that only pertains to the part. it has a relation ship between the part code in both tables. what i would like to do now is create a form that will create a job card. Job card consist of A date , job number , a part code ( with instructions) and 1 or two more fields with dates in different departments which isnt important now.

What i would like to do is on the job card my manager should
1 fill in the date and job number
2 click on a combo box which takes him through all the codes in the table ( which he selects one)
when he selects a code , the instructions related to that code must show underneath it
3 he will fill in more fields as the job card travels from station to station(which is not relevant now cause i cant get the first and second part to work)

once all the fields are populated with info that was gotten out of both tables and some extra info like date and job number and so i want to safe that info into a new table so that i can keep track of where the orders are ie production , sand blasting , dip , assembly and so on, after i have printed a copy of information that was on screen.

[orange]

So, you don't want to post a copy of your database.
Please post a jpg of your tables and relationships.

It is often better to describe the entire process --even the parts you don't have all details for at this time-- so you have the complete picture to work with. For those parts you know exist, but have no details, just identify a box with the Process Name(to be determined). That way, the process doesn't get lost. It stays in the picture and any discovered details/info can be added to the box or referenced via comment etc.

This is the business in general as I read your posts and graphics. This may be helpful. If you have all the logic identified, you can test your logic/model with some sample test data.

Hypothetically:

If you have a list of all of your Products ,and 
 For each Product a list of all Parts that go into the manufacture of each Product
   And for each Part in that Product, the current instructions and Dimensions/Specifications for that Part
     Each Instruction and Specification is a step in the manufacture of that Part
      One or more manufacturing steps occur(s) at an identified workstation.
      
      
      When you have an order to build a Product, you go to your existing data and
      "Build a Job Card to "
       identify all Parts required for that Product, and
       for each required Part the instructions and dimensions, and you know
       the sequence of steps involved to build the Part(s) and the Workstation(s) participating in the process
       the Job Card would include a Status to identify which steps have been completed/are yet to be completed.

I see an analogy between your Job Card and a traditional PickList
A job card is used to indicate  a schedule ofwhich items should be built in your facility to fulfill  orders. 
Generally, a job card shows part information and the sequence of build instructions and specifications information, quantities of parts to build, workstation location and completion status.

[WillemTWC]

i did insert 3 pics. one was the form and two was the tables that was involved in this process of adding a item and instructions. Tables are small and part code is the field in both tables that has the relationship.

[orange]

Willem,
I am attaching a jpg of what I understand your JobCard to be "sort of". I don't know where Department fits nor Station. My guess is you do not yet have enough tables to represent your business and processes. The key step in an easy to use, maintainable database is to get your tables and relationships designed and tested to meet your requirements. Your measurements data is not atomic and would be stored as text --depending on your longer term "automation plans" that may be a shortcoming.


This uses the tblPart and tblInstruction info from your pictures, but I have used autonumber PKs in these tables. Access has some reserved words that could cause you grief and are best avoided.

I think there is a step in your development that has not been described. If you have a List of all Instructions, and a List of all Parts, then as ranman said earlier and I tried to say in my hypothetical stuff, you could select a Part, and for each Instruction that applies, click a button to create a record in a table that identified all Instructions for that Part. You may need other tables to identify Part, Instruction and Workstation etc. Some of this will get clarified as you test your evolving model.

You know what you are trying to do better than any readers.
Good luck.

[WillemTWC]

if you add the two together this is what i have done... the job card form is incomplete cause i havent been able to get the data on it from the tables cept for the combo box. i want to see the instructions on that form and add a few more text items. you will see the add command button which is the only way i read up thus far how to store the data into a new table. i recon its bad coding thus far as i am still a beginner and im using hints off Youtube

[orange]

Part of your problem is that the Instruction table does not have a Primary Key.
With relational database, Every table should have a Primary Key which uniquely identifies each record in that table.

In the second database (add), you need to include the Part table. The Part table is the rowsource of the combo --if I'm understanding.
Also, I recommend using DAO.database and DAO.recordset in your vba.

Here is a link to a lot of information on Database Planning and Design. The tutorials for RogersAccessLibrary are a great learning exercise if you work through them. You will learn things to be used with any database design.

[WillemTWC]

ok i understand that the table layout is not so good... and i recon with relational tables it will be hard. With the sort of setup you gave me will i be able to get to build an input form and a job card form ?

[orange]

ok i understand that the table layout is not so good... Your table layout depends on your requirement --which has not been described in detail yet. It is a poor practice to design by trial and error. You are admittedly new to database so there is some learning to do. I can assure you that jumping to a physical database without analysis and design and testing is a long journey.
Try doing one of the tutorials--you will learn.

and i recon with relational tables it will be hard. Not necessarily, especially if you do the analysis and build a model.

With the sort of setup you gave me will i be able to get to build an input form and a job card form ? Not necessarily. Possible but I believe there are missing pieces in your plan/set up at this time.