I think my ship is sinking!! (1 Viewer)

[cclark9589]

Beginning to get the feeling the mild stroke may have dumbed me down more than first thought.

Got a problem with a database I'm using and for the life of me I can't seem to accomplish what should be a simple task.

I have two related tables, Jobs and tblHours. The fields are as follows:

Jobs:
JobID - PK
FundID
EmpID
YrSelect
AudID
Type, etc.

tblHours:
TCHrsID - PK
TimeCardID
JobID
DateWorked
HrCodeID
BillHours
FundID

The task I am trying to do is that I recently realized that I need to have the FundID in tblHours and now need to find a way to populate that table with the appropriate FundID.

I've searched the forum but haven't found anything specific that solves my problem. I have tried update queries but those haven't worked. I've tried append quiries with no luck. For the life of me I can't get this figured out and need to so that I can move on to other tasks.

Keep in mind that I am not an Access guru and have a very fundamental understanding of Access and programming. Also, please note that I did have a mild stroke a couple of weeks ago apparently more of my brain got toasted more than first thought. Either that or they got me on some drugs that are making me more loopy than I really know.

I'm sure the answer is in here somewhere but darned if I can find it. Your help is much appreciated.


Busted flat in Baton Rouge, wait'n for my brain.....

 

[Trevor G]

Can you post your database, how many records do you have already in the database?

Is it possible that you may need to use a DLookup or something similair !

 

[Brianwarnock]

If fundid is linked to jobid then why do you want to do it as you can get the info from the job table, if not on what basis can you apply a fundid to the TblHours?

If you must do this then read help.

Update one table based on another table

Brian

 

[gemma-the-husky]

it could be worse - if tblHours relates to a tblFunds - and you havent stored that data before (ie at the time you created the tblHours records) - then how can you now identify which fundid it should be?

however, if (say) the fundid can be determined from the jobid, then you do not need to store the fundid in the tblhours - which is what Brain was pointing out!

 

[ColinEssex]

What has the title of this thread got to do with anything meaningful?

Please try to be more helpful with your titles.

Col

 

[Trevor G]

What has the title of this thread got to do with anything meaningful?

Please try to be more helpful with your titles.

Col

I am Saddened by your reply Colin, you have obviously never dealt with someone who has had a stroke. I think you should be more aware of someone who is trying very hard to deal with normal life situations after a stroke when it effects not only your life but everything and everyone around around you.

Asking for help on this forum from people who have knowledge and experience is absolutely brilliant, and when I am sure you have actually helped people is fulfulling.

If you can contribute to a solution that would be more productive.

If possible posting the database would help us understand what you have done so far and a way of creating a solution for you.

 

[cclark9589]

Don't be too hard on Colin, to a degree he is right, a more descriptive title might help more. One thing that has helped me deal with my toasted noodle is my sense of humor which really has gotten me through this. The title was merely that sense of humor coming through.

I'm not sure I need to have FundID in tblHours or not. I don't know sqauat about Access and had to develope this time and billing db on my own out of necessity. I knew that eventually it was going to break and have been tyring to learn more so that I can fix things and maybe avoid a total mess.

One thing I foresee happening is that the billing rates. Presently the billing rate is the same for all the trust funds but that is going to change on 7/1. It is absolutely imperative that I maintain the historical records and so far I've not been able to do that. I did find Bob Larson's sample db, mileage rate and have gotten that to work to some degree.

While working on this I the thought crept into my mind that in order to have the correct billing rate applied I would need to do two things. First, on the subform where the billable hours are entered I would need the FundID for whichever job the hours were being inputted for and secondly I'd have to reference the current date in order to use the correct date. It's because of the first part of this sentence that I figured I needed the FundID in tblhours which is the record source for the subform used to enter the billable hours.

I have absolutely no idea if I need to have the FundID in tblHours or not but think I do and that thought is due to two reasons. First I don't know diddly-squat about Access and two, since having this stroke at times I more dazed and confused than usual when working with Access!

So a couple of questions now. How can I get the FundID into tblHours, with nearly 4000 records and matched up correctly? I have a query that pretty much does this already.

Secondly, once I get this accomplished and I do discover that I just don't need to store that information in tblHours I would think I could simply delete that field and it's contents from the table and be none the worse for wear.

If I get some time today, I will see if I can strip enough from the front and back ends of the db and post it. Oh shoot I have been forgetting. I'm using Access 2007.

And Colin, not to worry, were cool. No offense taken.

Edited to correct tblJobs to tblHours in second paragraph

 

[ColinEssex]

I am Saddened by your reply Colin, you have obviously never dealt with someone who has had a stroke. I think you should be more aware of someone who is trying very hard to deal with normal life situations after a stroke when it effects not only your life but everything and everyone around around you

After 37 years in the NHS and over 25 working with patients I have met almost all types of illness including many neurosurgical and neurologically affected patients.

Trust me, I know more than you think.

Col

 

[dan-cat]

Ask yourself this.

Will the FundId value in the child record (tblHours) ever be different to the FundId value in the parent record (Jobs) when keyed by JobId.

If not then you should strive to not store FundId in two different places. You should only duplicate the key values over different tables. In this case, assumed as JobId.

 

[cclark9589]

Not quite sure what you are asking me or more precisely, what you are suggesting I ask myself and why. Maybe I need to explain the hierarchy.

There are Trust Funds and then there are employers who belong to these Trust Funds. One employer can belong to many Trust Funds. We do payroll audits, Jobs, on these employers. Today we may have a Job, which has a unique JobID that belongs to Trust FundID 12. Tomorrow we might have to do a job on this same employer for Trust FundID 33.

So if I understand what you are saying the answer to what you are asking would be no, the FundID would not change in tblJobs, tblHours or anywhere else unless the job were for a different Trust Fund of which there are currently ten; 12, 15,17, 18, 21, 33, 39, 40, 47 & 101. Each Trust Fund is known by it's fund number (those listed above) where I work and likely will never change. The TrustID are these fund numbers and if you knew the people I work with, you'd understand my logic for breaking convention concerning the Primary key for Trust Funds by not using an autonumber.

What started this whole thing about wanting to get the FundID into the tblHours is that it dawned on me that the way I am grabbing the billing rate and thus creating invoices to bill the Trust Funds for the accounting fees works just fine now because all the Trust Funds have the same billing rate, for now. This will change on July 1 when Fund 12 increases by $6/hr while all the other funds remain at $85 until some point down the road when one two will increase while one or two remain at $85 and so on it goes.

The problem I realized I had was that when I change the rate it changes all rates and thus the billable fees on all jobs including those that have already been billed. This is not a good thing for obvious reasons and so it became clear to me that I had to find a way that I could change one rate and not affect any of the historical amounts or the billing rate for Trust Funds that have not increased.

When I realized this huge problem developing on the horizon it made sense to me that I would need the FundID in tblHours because I would need to know FundID in order to know what billing rate to use on the DateWorked, another field in tblHours.

Does all that rambling make any sense at all?

I've been trying to get Bob Larson's sample Mileage_Rate db to work and have sort of accomplished that but do have some bugs but that's a different post.

In the meantime, between the time I first posted my original question and responding to this reply I did finally find a way to get the FundID into tblHours. I relized I already had a query based on the tables Trusts, Jobs and tblHours. I set the criteria for FundID.Trusts to 12. Next I set criteria for TrustID.Jobs to Is Null and then performed an update and updated TrustID.Jobs to 12. I repeated this for the other funds. It worked and if it turns out that I truly don't need FundID for any reason in tblHours, I can easily peel that field out.

So can someone explain to me, in 1000 words or less, why I shouldn't have FundID in tblHours?