Relationship between Tabls

[Tex Hart]

I have a Table Titled "Job Ticket"

I have a Table titled "Delivery Ticket"

I have a table sitled "customer information"

In the Delivery Ticket Table / Layout, I want to enter the "job ticket" record number into a field, and then have related information off that same "job ticket" fill 3 other fields.

for Instance, I enter in the record number in field A in my "delivery ticket." That triggers field B, C and D to automatically fill with information off that same ticket.

1 more time: I enter the record number, the desciption, customer PO price automatically fill in on 3 other fields.

[LaRetta]

Hello there Tex Hart - welcome to FM Forums!!

"1 more time: I enter the record number, the desciption, customer PO price automatically fill in on 3 other fields."

Well, there are two ways to 'populate' data from one table to another. I was unsure if the customer PO price came from Jobs or Customers; in fact, I was unsure of many specifics, so I produced a simple file showing two techniques. I'm confident you can adapt or question us further (see attached sample file).

Sometimes related information is fine just being displayed. Other times it needs to plant within the Delivery record (such as price). Because in future when a job price changes, you don't want your Delivery price to change. However, the job description may be fine leaving it as currently listed so you might notice that the job description is actually the field from the Job_ticket table itself (just placed on your delivery form). This is the power of relating records. You can 'look up' information and plant it within your other table or you can just 'display the current information' by cross-placing your fields. This saves having to duplicate much of your data (by displaying it instead).

If you select the JobID in Deliveries, it will look up the price (and place it in the delivery price field) and will simply display the job description (yellow) through the relationship. I hope this moves you forward and we are all here if you have further questions.

There are many wonderful people here willing to assist.

ps. I didn't include the Customers table but the theory is exactly the same ... cross-place related fields which don't need to be planted and look up fields which do.

LaRetta

Job_Tickets.zip

Edited November 11, 2009 by Guest
Added ps