Problem - Automatically fill a table field from another table (1 Viewer)

G1ZmO · Mar 14, 2008

I have a table which contains these fields

MAIN TABLE
Job_No - Looks up Job table
Source - I want this to be automatically filled from the record associated with the job number from the Job table
Test_Result - Looks up Result table PASS/FAIL etc
Technican - Looks up technican names table

JOB/SOURCE TABLE
Job_No 0001 0002 0003 0004 etc
Source Cust1 Cust2 Cust3 Cust1 etc

Note also that we also have different job numbers for the same customer

I know I can fill in a value on a form from a combo box looking up another table but dont know how to do this in the main table

Any ideas?

Paul

Kempes · Mar 14, 2008

I may have missed the point on this one, but wouldn't creating a query suffice instead of updating field values that are already stored in another table? Wouldn't this cause duplication of the data?

G1ZmO · Mar 14, 2008

Oh I cant get my head round this access stuff!

Ok, when a job from a customer comes in it is assigned a job number.

eg
Job 12461 Customer Blah Blah Ltd
Job 12462 Customer Somethingelse Ltd

I have a table with the Job and Customer names listed

I also have a table of items which are to be tested
Each item is from one of the jobs on the above table
I need to be able to enter only the job number in the table and for the customer to be filled. Now, yes, if a query is a better way of doing it then I should do it that way but I'm not sure how to go about it.

Confused yet again by something which is probably simple.

Thanks

Paul

Kempes · Mar 14, 2008

So basically you want to show all items by Job_No based on existing data.

Try the query wizard. Select fields from your two tables, then choose how the tables relate to each other, Ie, Job_No from the job table and Job_No from the Items table.
if you then go into the design mode, put some text in the criteria like "Enter Job Number" for the job_no field, this will prompt for a job number.

Hope this helps.

G1ZmO · Mar 14, 2008

Do I have to have a customer field in the "Test Items" table?

Does this look about right? (see pic attached)

Thanks

Kempes · Mar 14, 2008

personally, I would have a new table holding all of the customer details and a generate a cust_ID for the PK for each user.

If you add a cust_ID field into the all_items table and link it, away you go!

Does this query return what you expected to see?

G1ZmO · Mar 14, 2008

I see, however the only customer details that are required for each record are the customer name and the Job number which was assigned.

Also worth noting that there can be multiple job numbers for the same customer as the job numbers are assigned as the new batch arrives.

Not entirely sure what you mean about the customer ID field.

I just need to make sure that the correct customer name goes against the record when the job number is selected.

Our current database has a drop down for job number AND customer name so mistakes have been made in the past.

G1ZmO · Mar 14, 2008

Does this query return what you expected to see?

Btw, yes, that does give me what I need.

I just need to ensure that the job/source table is present in every query I think.

Many thanks,

Problem - Automatically fill a table field from another table (1 Viewer)

G1ZmO

Registered User.

Kempes

Registered User.

G1ZmO

Registered User.

Kempes

Registered User.

G1ZmO

Registered User.

Attachments

Kempes

Registered User.

G1ZmO

Registered User.

G1ZmO

Registered User.

Similar threads

Users who are viewing this thread