Adding input to multiple tables?

  • Thread starter Thread starter Lee Stafford via AccessMonster.com
  • Start date Start date
L

Lee Stafford via AccessMonster.com

I have a challenge that I have been fighting with since I started this
project several months ago.

I have a report that is generated at the end of the month for my
supervisors, but I like to generate it during the month for my own review.
The problem is that there are two fields that require entry at the end of
the month. Until then there is no data for those fields. The way my
queries are working, the report will only generate when all fields have
data that matches the criteria.

The report prints out the number of installs,disconnects,reconnects, etc
for each month. It also calculates from the number of jobs completed for
the month, the percent of jobs that were inspected. That is the problem.
I have tried to make up a table with all the techs and just put a default
value in the required fields. However, throughout the month we may add a
tech and that is the problem I run into with the table. I don't know how
to add the tech to that table when I add the tech to the Tech table.

Can anyone help. I am not sure if I explained the problem very well, it is
pretty deep (I think). Thanks in advance for all the help.

Lee
 
It sounds like the table structure is the issue.

Can I presume you have a table called Techs, and it contains things like
"Name", "addresss", "employee id"
You probably also have a table for "installs" or "job types" and link it by
a foreign key back to the technician performing the work (presuming only one
tech attends)

Does this sound about right?

Could you elaborate on the design if it isn't?
 
I have a Table called TBL_Techs with fields such as TechID (PK),
FirstName, LastName, SupervisorID, SystemID.

The main table is the work order info I input data relevant to individual
workorders that the techs do. I inspect the jobs and they get scores.
They are assigned a certain number of jobs(workorders), and they don't
always complete all of them, so there is also a number of Jobs Completed.
These numbers aren't known until the end of the month. Out of those
numbers I have to calculate the percentage of jobs that I inspect.

TBL_TECH TBL_JOBTYPE TBL_WORKORDER TBL_NUMJOBS
TechID PK JobType PK woNumber PK TechID PK
LastName CorrespondingType JobNumber Month PK
FirstName TechID FK JobsAssigned
System QATechID FK JobsCompleted
District JobType FK
Probationary WODate
SupervisorID FK ReturnTrip
MRO
Street
TBL_QATECH City
QATechID PK State
Lastname Score
FirstName Notes
SupervisorID FK MRONotes
A1
A2
TBL_SUPERVISOR A3
SupervisorID PK A4
LastName etc.
FirstName


I am not sure how to link the TBL_NUMJOBS to any of the other tables. My
queries are very complex and I am trying to reduce the number of them. I
have two crosstab queries because there are several job types and we have
to have an avg score of each type that the tech does, plus a count of each
type the tech does.

This is the first DB I have ever created and I am learning as I go. My
boss is real impressed with it so far, and I am very glad to been able to
do this for the company. I don't want this little problem to be a stopper
for me. I have been working real hard on this, and I am not one to give
up. I am very open to any and all suggestions to help me with this.

Thanks,

Lee S.
 
Those columns did not come out right. Try again.
TBL_TECH
TechID PK
LastName
FirstName
System
District
Probationary
SupervisorID FK


TBL_QATECH
QATechID PK
Lastname
FirstName
SupervisorID FK


TBL_SUPERVISOR
SupervisorID PK
LastName
FirstName

TBL_JOBTYPE
JobType PK
CorrespondingType


TBL_NUMBEROFJOBS
TechID PK
Month PK
JobsAssigned
JobsCompleted

TBL_WORKORDER
woNumber PK
JobNumber
TechID FK
QATechID FK
JobType FK
WODate
ReturnTrip
MRO
Street
City
State
Score
Notes
MRONotes
A1
A2
A3
A4
etc.

Fields A1,A2, etc are all yes/no datatype. There are about 40 or so of
them.
 
I figured out my problem. It was actually very simple, I had just never
looked at the join properties very hard. I finally figured out that I
could use all the records from one table, and only those that match from
the other table. This saved my DB. I wish I had figured this out sooner.
Thanks for trying to help.
 
Back
Top