Updating 2 tables

[ITguy1981]

I have multiple tables. First I have a table [Job List] for a user to enter new job sites which has a "date started" and "date finished" field. I have a [DailyJobs] table which on it's data entry form has 2 subtables [TimeCards] and [Billables]. The relationships work fine for DailyJobs and the timecards and billables record correctly. I have a "date finished" field on my [DailyJobs] table. I would like join the JobList and DailyJobs somehow. I tried to add a "DailyJobID" field to my Job List and setup a relationship, but when I create a DailyJob and save it, it's DailyJobID does not reflect back to the Job List. All working, I would like to create a Job in the Job list, open dailyjobs, select an open job from [job List] and then be able to enter my billables and daily time. When the job is finished I would like to be able to go in to [Job List] enter in the date for date finished. I think I might all reside in a query of my record source for my form for [DailyJob] Data entry. Sorry this is in the Queries forum. Wasn't really sure where to put it.

 

[GinaWhipp]

Hmmm, well, I would have put those Jobs in one table with a field to check if a Daily Job. Not sure how to handle you present situation (or if it's even possible) without knowing the exact table set-up and how they are related. Please post that information so we can help...

 

[ITguy1981]

I have a one-to-many relationship using the field DailyJobID in [DailyJobs] to DailyJobID in [TimeCards]. I also have the same one-to-many relationship using the field DailyJobID between [DailyJobs] and [Billables]. I currently don't have a relationship between the table JobList and DailyJobs. Any relationships i've tried between them doesn't solve my issue.

 

[spikepl]

You have to describe what is JobList vs DailyJobs. You mention that you have an end-date in both, and that makes it totally unclear what the function of each is. Is there more than one DailyJob related to the same item in JobList? So that one job in the joblist has many items in DailyJobs? If that is the case, then the JobListID goes into each item in DailyJobs that belong to the same joblist item.

 

[GinaWhipp]

Okay, let's do this...

tblJobList < This holds all the Jobs

tblDailyJob
DailyJobID (PK)

tblTimeCards < This is where people enter their time
DailyJobID (FK - related to tblDailJob)

tblBillables < ????

So you enter a Daily Job, does it get entered from the Job List table? OR is that a seperate entry?

 

[ITguy1981]

Currently, the Daily Job is a seperate entry. A form is used to create a new job in the job list. Then, I open a form for DailyJobs. The DailyJobs form has fields: DailyJobID, JobNumber, Work Description, Date Finished, and Billed. The field JobNumber has a row source which pulls data from the Job List table. It queries Well#, Customer Name, Rig#, and other fields from the JobList table. I have text boxes on the Daily Job form that fill in automatically by column result of the query. The query along with pulling field info from JobList looks at Date Finished "Is Null". This way when the Daily Job form is opened the user can use a combobox, which is the JobNumber control, and only select Jobs that are not finished from the JobList table. Then the rest of the JobList info fills in to the text boxes. If you want I can send my database to look at my mess. lol

 

[ITguy1981]

What I want is to be able to go in to my JobList, fill in the date finished, and have the date finished also entered in to the DailyJob so it's marked as finished. I use a query to open the DailyJob form that looks for DateFinished "Is Null" so when the user goes to add billables or employee hours to the job they cannot open a DailyJob that has already been completed. So you can see why I would like to find some way to add a finish date to both tables at the same time. I guess if worse comes to worse the user can just enter the finished date manually in both.

 

[spikepl]

Ok - if you do not wish to answer the specific questions then throwing your db at us instead and let us dig out the business rules is not helpful.

 

[GinaWhipp]

Well, IMHO, your tables are going to need adjusting.

A is a job and should be entered in the Job table. You can use *triggers* to say if it is new or not. Then use a JUNCTION table to enter *Daily* information but all Jobs will be from the Jobs table. The Daily JUNCTION table is what you would use for Billings as it would become part of the Detail section.

If that does make sense then upload your database (or a sample) so we can adjust. Because presently, you are going to have to jump thru hoops to get what you want.

 

[ITguy1981]

Sorry, I missed your question about the difference between JobList and Daily Jobs. The company does drilling work at multiple locations for different customers. So, when a job started for a customer the job is put in to the JobList with info about the jobsite and customer. The DailyJob contains a jobnumber, work description, and billed. I use a form for daily job. So dailyjob form is opened, the user can select which job location they worked from JobList which is stored as JobNumber in the DailyJob table. Then they can enter the description of the work they did, enter their billables, and then enter their timecard info. I hope that answers any questions.

 

[ITguy1981]

I appreciate your help. I've not really worked with triggers or junction tables. I wouldn't mind uploading the DB, but i've had others link very small database with an example if you had one handy. I could probably look at it and figure it out and keep you from having to look at what i've going on and sorting it out. Just trying to save you some work helping me. I appreciate it a lot.

 

[ITguy1981]

I've come to realize that if I was more familiar with all of the various ways to do things in Access I could have had a lot better design.

 

[ITguy1981]

I think I might trash the DailyJob and JobList table thing I've got going on and just have the joblist. The user can just enter the two or three fields that were on the DailyJob on the JobList table. I'll then add the billables and timecards to the JobList Form instead. Most of the issue was the user wanted to be able to select the Jobs that were open to enter billing and what not. I'll just use a form with a combobox that will allow the user to select only open jobs in the JobList table. I guess I broke rule number one. Don't use redundant data. The data wasn't so much redundant because the names were different, but they were serving the same role almost. Lets mark this as closed. Thanks for your help on this everyone.

 

[GinaWhipp]

HELLO... we are here to help, so no worries about our time. If you want us to help you can upload, we don't mind or we would not have offered. However, since you decided this thread is closed on to the next one I go...

 

[GinaWhipp]

In case you're still looking...

http://www.access-diva.com/dm5.html