Populate field in one table with data from field in another table

[Guest]

Access 2003, Windows XP sp2

Problem:
tblJob has a "Cost Center" field. tblInvoice has a "Charge To" field.

I want the tblInvoice field "Charge To" to automatically populate with the
information in the "Cost Center" field in the tblJob.

These tables are related by a mutual primary key but neither of these fields
are related and neither field is a primary field.

Is there an easy way to make the tblInvoice "Charge To" field populate
automatically with the information in the corresponding record entered in the
tblJob, "Cost Center" field?

I don't think the update query is what I am looking for nor is lookup as I
need the record directly associated with both tables by Job#(primary key).

I am hoping this is easy but I can't find an answer. Thanks for any
assistance.

 

[Joseph Meehan]

Access 2003, Windows XP sp2

Problem:
tblJob has a "Cost Center" field. tblInvoice has a "Charge To" field.

I want the tblInvoice field "Charge To" to automatically populate
with the information in the "Cost Center" field in the tblJob.

These tables are related by a mutual primary key but neither of these
fields are related and neither field is a primary field.

Is there an easy way to make the tblInvoice "Charge To" field populate
automatically with the information in the corresponding record
entered in the tblJob, "Cost Center" field?

I don't think the update query is what I am looking for nor is lookup
as I need the record directly associated with both tables by
Job#(primary key).

I am hoping this is easy but I can't find an answer. Thanks for any
assistance.

Since I am not sure from your description exactly how the two fields
might be related I will pass on your stated question. However, I do have to
ask, assuming they are properly related, why do you want to populate a filed
in a table, IF you can just display the same data from another table?
Normally you would not want to do that, unless the data where it is located
now might change and you DON'T want it to changes in the table where you
want to copy the data.

 

[Guest]

Actually the data in the tblInvoice "might" be different from the default
charge code in tblJob. This would be rare but it will happen.

I do not have the fields related. Do I have to? I am new to database design
so the error could be mine. I did create an update query and it did work but
does not work like I need it to.

For the ease of input, I simply want the tblInvoice to automatically display
whatever charge code is entered in the tblJob. If it needs to be changed, I
will allow it to be changed. I guess I am looking to set a "Default Value"
and that value would be whatever value was is entered in the tblJob.Charge
Code field. In other words, the invoice table would automatically direct the
charge according to what was on the Job table; but it will need to allow for
the times when this is not correct and a manual entry will have to be made.
(Like in a country table, default to USA but allow changes).

Thanks for any help. I am usually pretty good at searching for answers. I am
either asking the wrong question or Access can not do what I want it to.

 

[Joseph Meehan]

Actually the data in the tblInvoice "might" be different from the
default charge code in tblJob. This would be rare but it will happen.

I do not have the fields related. Do I have to? I am new to database
design so the error could be mine. I did create an update query and
it did work but does not work like I need it to.

For the ease of input, I simply want the tblInvoice to automatically
display whatever charge code is entered in the tblJob. If it needs to
be changed, I will allow it to be changed. I guess I am looking to
set a "Default Value" and that value would be whatever value was is
entered in the tblJob.Charge Code field. In other words, the invoice
table would automatically direct the charge according to what was on
the Job table; but it will need to allow for the times when this is
not correct and a manual entry will have to be made. (Like in a
country table, default to USA but allow changes).

Thanks for any help. I am usually pretty good at searching for
answers. I am either asking the wrong question or Access can not do
what I want it to.

Well table design is the beginning point of getting a database to
function properly.

How about providing a list of the fields (you can rename them if you
like) for both tables and an explanation of what data is contained in each
filed ( some samples may help ). If you are to do what you want, the data
in those two tables must be related is some way. Maybe you can just
describe how they are related. For example do they both have an invoice
number field? If so does one of the tables have no repeats of that invoice
number? The second table may have repeats.

 

[Guest]

Terrific, thanks for your patience.

tblJob
Fields:
Job Number (Primary Key) one-to-many with same field in tblInvoice
Primary Firm - Business hired to perform work
Cost Center - (the department which will pay for the work_usually)
Date Started - date job was started
Desc of Work - work to be performed
etc. etc. etc.

tblInvoice
Job Number (Primary Key) (many-to-one with same field in tblJob)
Primary Firm - Business hired to perform work (Primary Key)
Invoice Number - number of invoice (Primary Key)
Charge To - this field is the same as "Cost Center" in tblJob
Amount - charges
etc. etc.

Simply stated: We have one Job with many invoices. There are 20 tables in
database but these two will report and track the costs invoiced to each job.
When a department requires work and commits to a job then it is entered and
given a unique Job Number which is used to relate to most of the tables for
different information. What they want is for the Invoice table to
automatically populate to bill the department who requested the work. That is
the fields (Cost Center and Charge To from both these tables). The unique Job
Number is what ties the information together. I have to be able to allow for
the possibility that this Cost Center/Charge To could be different depending
on the circumstances and allow for an override of the populated information.

I have not related these particular fields??? In the tblJob the Cost Center
can only appear once and is unique. Of course there will be many invoices for
the job and most of them will be Charge To this same number.

Again, thank you for your patience. Any suggestions appreciated.

 

[Joseph Meehan]

Terrific, thanks for your patience.

tblJob
Fields:
Job Number (Primary Key) one-to-many with same field in tblInvoice
Primary Firm - Business hired to perform work
Cost Center - (the department which will pay for the work_usually)
Date Started - date job was started
Desc of Work - work to be performed
etc. etc. etc.

tblInvoice
Job Number (Primary Key) (many-to-one with same field in tblJob)
Primary Firm - Business hired to perform work (Primary Key)
Invoice Number - number of invoice (Primary Key)
Charge To - this field is the same as "Cost Center" in tblJob
Amount - charges
etc. etc.

Simply stated: We have one Job with many invoices. There are 20
tables in database but these two will report and track the costs
invoiced to each job. When a department requires work and commits to
a job then it is entered and given a unique Job Number which is used
to relate to most of the tables for different information. What they
want is for the Invoice table to automatically populate to bill the
department who requested the work. That is the fields (Cost Center
and Charge To from both these tables).

Normally there would be no reason and it would be undesirable to store
this data in the second table. However since you indicate it may be changed
in the second table and not the first, I suggest there are several possible
solutions and someone may have a better idea than I have as I have not
personally had this problem often.

You could create a little code in the form used to create and edit the
Invoice table. I would guess you would want to trigger it to run after some
event that would take place after the Job Number was entered in the second
table. You could then populate the fields of the current record with the
data from the first table. You would want to check to make sure there was
no data there previously or you might end up overwriting data that had been
changed previously back to the default value. You might want to make that
some sort of warning with an option to change or not.

 

[Guest]

What would be the Visual Basic language you would use for this event
procedure? I want to create a record of history of field so I would like it
copied to a table every time is updated. I am using the "AfterUpdate" event
from the event tab in properties. But I guess I'm not telling the program
what to do properly. Can you e-mail me a sample event procedure to
(e-mail address removed)?