|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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.
|
|
OfficeMiner wrote:
[Quoted Text] > 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
|
|
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:
[Quoted Text] > 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
>
>
>
|
|
OfficeMiner wrote:
[Quoted Text] > 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
|
|
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:
[Quoted Text] > 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
>
>
>
|
|
OfficeMiner wrote:
[Quoted Text] > 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.
> 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
--
Joseph Meehan
Dia duit
|
|
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
ellsun[ at ]hotmail.com?
"Joseph Meehan" wrote:
[Quoted Text] > OfficeMiner wrote:
> > 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.
>
> > 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
>
>
>
> --
> Joseph Meehan
>
> Dia duit
>
>
>
|
|
|