Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

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

Discussion in 'Information Technology' started by OfficeMiner, Jul 28, 2006.

  1. OfficeMiner

    OfficeMiner
    Expand Collapse
    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.
     
  2. Loading...


  3. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    OfficeMiner wrote:
    > 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.

    --
    Joseph Meehan

    Dia duit
     
  4. OfficeMiner

    OfficeMiner
    Expand Collapse
    Guest

    Re: Populate field in one table with data from field in another ta

    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" wrote:

    > OfficeMiner wrote:
    > > 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.
    >
    > --
    > Joseph Meehan
    >
    > Dia duit
    >
    >
    >
     
  5. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    Re: Populate field in one table with data from field in another ta

    OfficeMiner wrote:
    > 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" wrote:
    >
    >> OfficeMiner wrote:
    >>> 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.
    >>
    >> --
    >> Joseph Meehan
    >>
    >> Dia duit


    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.

    --
    Joseph Meehan

    Dia duit
     
  6. OfficeMiner

    OfficeMiner
    Expand Collapse
    Guest

    Re: Populate field in one table with data from field in another ta

    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" wrote:

    > OfficeMiner wrote:
    > > 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" wrote:
    > >
    > >> OfficeMiner wrote:
    > >>> 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.
    > >>
    > >> --
    > >> Joseph Meehan
    > >>
    > >> Dia duit

    >
    > 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.
    >
    > --
    > Joseph Meehan
    >
    > Dia duit
    >
    >
    >
     

Share This Page