Update columns from selecting 1 Field

[Crabman1985]

I Have a table called Projects
In the Table I have:
JobNumber
CustomerName
SiteName
SiteLocation
SiteContact

I have another table called JobCards
In the Table I have:
JobCardNumber
DateIssued
WorkRequired

I will be using the JobCards the most and don't want to add the same information from Projects over and over when adding a new record.
There would be multiple JobCards per JobNumber

How would this be done,?
I want to select the JobNumber and then pull data from the other columns, CustomerName, SiteName, SiteLocation, SiteContact.

 

[arnelgp]

create a Form with Subform.
the Main form will hold the Fields from Projects table.
while the Subform will hold the JobCards.
the Subform will have a Master/Child Link Field to JobNumber/JobCardNumber.

 

[arnelgp]

see this demo and open Form frmProjectJobCard.

 

[cheekybuddha]

the Subform will have a Master/Child Link Field to JobNumber/JobCardNumber.

If JobCardNumber and JobNumber are not the same, eg a JobNumber will have more than one JobCard, then you must add a foreign key field to the JobNumber to table JobCards (eg JobNumberFK).

Then, link the mainform/subform on JobNumber -> JobNumberFK

 

[cheekybuddha]

the Subform will have a Master/Child Link Field to JobNumber/JobCardNumber.

Ah, I see you added a JobID pk/fk and linked on that, Arnel

 

[GaP42]

Perhaps the issue is that the relationship os not correctly defined - looking at the OP and the attributes, it would seem that, because the Project appears to hold the foreign Key (KF) to JobCards that a Job consists of many Projects. However looking closely at the attributes in each it would seem that a Project consists of many Job Cards. If the latter then the ProjectID should appear as the FK in JobCards, and no FK to JobCard is needed in Projects. (Perhaps JobNumber is in fact (meant to be) the ProjectID)

 

[Mark_]

Unless you are in a fairly small business, I'd take a look at putting "SiteName", "SiteLocation", and "SiteContact" into a separate file that can be selected per Jobcard and Project. That way the default location is the same as the project, but when you get those "Oh, we also need something at a second+ location" you don't need to create a new project for them.

Think "Our business has 5 sites. We have one job, but you need to do work at all 5 sites".

 

[mike60smart]

I Have a table called Projects
In the Table I have:
JobNumber
CustomerName
SiteName
SiteLocation
SiteContact

I have another table called JobCards
In the Table I have:
JobCardNumber
DateIssued
WorkRequired

I will be using the JobCards the most and don't want to add the same information from Projects over and over when adding a new record.
There would be multiple JobCards per JobNumber

How would this be done,?
I want to select the JobNumber and then pull data from the other columns, CustomerName, SiteName, SiteLocation, SiteContact.

I think a more detailed description of your process is required.

Does 1 Customer have 1 or more Projects and each project can have 1 or more Job Sites?