help with sql

[ahau]

Hi,

I don't know if this can be done.

I have two tables, job and subjob.
Job
====
(schema)..,Industry_No, Client_No, Job_No, Job_Name, ...
(record#1)..,01,01,00,Administration
(record#2)..,01,01,01,Marketing
(rec#3)..,01,01,02,Distribution
....

Sub Job
=======
(schema)..,Industry_No, Client_No, Job_No, SubJob_No, SubJob_Name..
(rec#1)..,01,01,00,00,
(rec#2)..,01,01,01,00,
(rec#3)..,01,01,01,01,Children Marketing
....

Sub_Job table won't have the job name if the subjob_no is 00. It'd only keep the name if there is a subjob_no there. The job table has the job name there.

So when printing a report, what i'll be using the subjob table as it has a complete list of jobs there. My question is if there's a way to check in the criteria if the subjob_name is empty or null, go looking up the job table to get the job_name in the query builder.

I've got troubled when printing a report as some of the job headings are incorrect as follows

010100 - Administration
...
010101 - Administration

Thank you for your help in advance

 

[PHV]

Something like this (SQL code) ?
SELECT S.Industry_No, S.Client_No, S.Job_No, S.SubJob_No, Nz(S.SubJob_Name, J.Job_Name) AS NameOfJob
FROM [Sub Job] S INNER JOIN Job J
ON S.Industry_No=J.Industry_No AND S.Client_No=J.Client_No AND S.Job_No=J.Job_No

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ahau]

Hi PHV,

Thank you for your support.

Hmm... Not quite working yet as it still hasn't given me the list of records that have subjobs with it. All it gives are a list of jobs only and the job headings are wrong too.
Note: 01010000 - the first two digits represents industry_no(01), followed by client_no(01), followed by job_no(00), and subjob_no(00).

For example:
01010000 - Administration
01010100 - Administration (should be Marketing)
...

then, there should be 01010101 in the timesheet table but it doesn't show up in the report.

Maybe i should give the actual sql code that i copied and pasted on the notepad. The reason I gave a few codes before was i thought i could get some hint from you and worked it out myself.

I hope i don't scare you with it. It involves 5 tables there.
Here is the SQL code
SELECT [Time Sheet].Staff_ID, Staff_register.First_Name, Staff_register.Last_Name, [Time Sheet].Industry_No, [Time Sheet].Client_No, [Time Sheet].Job_No, [Time Sheet].SubJob_No, [Time Sheet].WorkDate, [Time Sheet].Schedule_ID, [Time Sheet].Time, [Time Sheet].TimeSheet_ID, [Job Schedule].Rate, [SubJob Register].SubJob_Name, [Client Register].Client_Name
FROM ((([Time Sheet] INNER JOIN [Job Schedule] ON [Time Sheet].Schedule_ID = [Job Schedule].Schedule_ID) INNER JOIN Staff_register ON ([Job Schedule].Staff_ID = Staff_register.Staff_ID) AND ([Time Sheet].Staff_ID = Staff_register.Staff_ID)) INNER JOIN [SubJob Register] ON ([Time Sheet].Industry_No = [SubJob Register].Industry_No) AND ([Time Sheet].Client_No = [SubJob Register].Client_No) AND ([Time Sheet].Job_No = [SubJob Register].Job_No) AND ([Time Sheet].SubJob_No = [SubJob Register].SubJob_No)) INNER JOIN [Client Register] ON ([SubJob Register].Industry_No = [Client Register].Industry_No) AND ([SubJob Register].Client_No = [Client Register].Client_No)
WHERE ((([Time Sheet].Staff_ID) Like [Forms]![FM_Reports]![CB_Staff_ID]) AND (([Time Sheet].Industry_No) Like [Forms]![FM_Reports]![CB_Industry]) AND (([Time Sheet].Client_No) Like [Forms]![FM_Reports]![CB_Client]) AND (([Time Sheet].Job_No) Like [Forms]![FM_Reports]![CB_Job]) AND (([Time Sheet].SubJob_No) Like [Forms]![FM_Reports]![CB_SubJob]) AND (([Time Sheet].WorkDate)>=[Forms]![FM_Reports]![TB_Start_Date] And ([Time Sheet].WorkDate)<=[Forms]![FM_Reports]![TB_End_Date]));

I've been trying to look into this but unable to find the fault there.

Your help is greatly appreciated. Thank you in advance

 

[Golom]

Without data samples it's really not possible to ascertain exactly why it's giving you incorrect results. Formatting is a wonderful thing however.

SELECT T.Staff_ID, F.First_Name, F.Last_Name, 
       T.Industry_No, T.Client_No, T.Job_No, T.SubJob_No, 
       T.WorkDate, T.Schedule_ID, T.Time, T.TimeSheet_ID, 
       J.Rate, S.SubJob_Name, C.Client_Name

FROM ((([Time Sheet] T 
         INNER JOIN [Job Schedule] J    ON T.Schedule_ID = J.Schedule_ID) 
         INNER JOIN Staff_register F    ON  J.Staff_ID = F.Staff_ID 
                                        AND T.Staff_ID = F.Staff_ID) 
         INNER JOIN [SubJob Register] S ON  T.Industry_No = S.Industry_No 
                                        AND T.Client_No = S.Client_No 
                                        AND T.Job_No = S.Job_No 
                                        AND T.SubJob_No = S.SubJob_No) 
         INNER JOIN [Client Register] C ON  S.Industry_No = C.Industry_No 
                                        AND S.Client_No = C.Client_No

WHERE T.Staff_ID         Like [Forms]![FM_Reports]![CB_Staff_ID] 
  AND T.Industry_No      Like [Forms]![FM_Reports]![CB_Industry] 
  AND T.Client_No        Like [Forms]![FM_Reports]![CB_Client] 
  AND T.Job_No           Like [Forms]![FM_Reports]![CB_Job] 
  AND T.SubJob_No        Like [Forms]![FM_Reports]![CB_SubJob] 
  AND T.WorkDate         BETWEEN [Forms]![FM_Reports]![TB_Start_Date] 
                             AND [Forms]![FM_Reports]![TB_End_Date]

You can now see what's going on more clearly than with the full text-flow version of the SQL.

PS: It will probably take quite a bit more than that to scare PHV.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[ahau]

Hi Golom,

Thank you for your reply. yes, you're right about it that i should put it in a nice format so that people can understand it. Thanks for doing that for me.

Ok, i'll give some data samples for the five tables.
timesheet table
===============
T_ID Staff_ID WorkDate EntryDate Ind_No Cli_No Job_No SubJob_No Schedule_ID Time TID
7 01 16/11/2004 24/11/2004 10:57:02 AM 00 01 01 00 Standard0405 2 7
8 01 18/11/2004 24/11/2004 11:03:40 AM 00 01 01 00 Standard0405 2 8
9 01 19/11/2004 24/11/2004 11:04:47 AM 00 01 01 00 Standard0405 2 9

Job Schedule table
==================
JSchedule_ID Schedule_ID Staff_ID Rate
10 CHW0405 01 $20
11 CHW0405 02 $30

Staff register table
====================
Staff_ID F_Name L_Name M_Name Position
01 Michael Klim Daniel Consulting
02 Michelle Klim Lisa office Secretary

SubJob Register table
=====================
Job_ID Ind_No Cli_No Job_No SubJob_No SubJob_Name
56 00 01 00 00
60 00 01 00 01 Marketing
81 00 01 01 00
82 00 01 01 01 Administration
106 00 01 02 00
26 00 01 03 00

Job Register table
==================
Job_ID Ind_No Cli_No Job_No Job_Name
56 00 01 00 JobA
60 00 01 00 JobB
81 00 01 01 JobC
82 00 01 01 JobD
106 00 01 02 JobR
26 00 01 03 JobF

Client Register table
=====================
Cli_Id Ind_No Cli_No Cli_Name
1 00 01 MGU Corp
2 00 02 McDonnald
3 00 03 WorldOne tel
I hope the above data samples would help you to help me figure out where the problem is. Note, you may notice that the job Register is pretty much identical to subjob Register. The only difference is that the subjob table keeps whatever you may find in the job register. It has subjob which is important when printing out a whole list of all the jobs. But notice that in the subjob table doesn't keep the job_name if it has no subjob. To find out the jobname, i have to write some code to lookup the job Register table to get the jobname.

Your help is greatly appreciated. Thank you in advance

 

[lespaul]

and what results do you want/should you get from this data set?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[ahau]

Hi Lespaul,

Thank you for your support. I know that this is not a very good design of database. But because this database has been set up by someone before me and amazingly, everything works including other reports except this report called "Job Work Hours" that i don't get what i want. What happen is that i want a report that can tell me
1. job number (i.e. consist of industry_no, client_no, job_no and subjob_no)
2. job name (eg. marketing, roller replacement, etc)
3. a list of who have been working on this job
4. how much we spent on this job in terms of hours as well as dollars. (this is when we need "job schedule" table as it has the cost for every staff in relation to the jobs they work on)

The problem i'm having is that i can get a whole list of jobs and a list of staff work on the job accordingly but there is two problems:
1. the job name is incorrect.
eg. 01010000 - Administration --> correct
(job no) (job name)
01010100 - Administration --> incorrect, based on the table, it should be Marketing
01010200 - Administration --> incorrect (from now, i can tell that the job name has been stuffed up and it follows the first heading of the job name i.e. Administration. I notice this happening to the rest of the job. If the first job name is Marketing, then the rest of the job name is Marketing regardless of the job_no.

2. The subjob doesn't show up. Some jobs have subjobs
eg. 01010000 ---> this tells me there is no subjob for the job 010100.
01010101 --> the job 010101 has a subjob
so, it only shows 01010100 but missed out 01010101.

I have two tables for this. One's called Job_Register and the other one is SubJob_Register as later described below

Anyway, these are the tables i have been using to execute the sql query to produce this report. Everyone should have timesheet to record the no. of hours, jobs they work on, etc.

(trying to make it simple. Some fields of the tables may be ignored as they are not important)
Timesheet
=========
Timesheet_ID, Staff_ID, WorkDate, Industry_No, Client_No, Job_No, SubJob_No, Schedule_ID, Time

Staff_register
==============
Staff_ID, First_Name, Last_Name

Job Schedule
============
JSchedule_ID
Schedule_ID
Staff_ID
Rate

Client Register
===============
Client_ID
Industry_No
Client_No
Client_Name

SubJob Register
===============
Job_ID
Industry_No
Client_No
Job_No
SubJob_No
Subjob_Name

Job Register
============
Job_ID
Industry_No
Client_No
Job_No
job_Name

Job Register table is not used here because i think the subjob register table would provide pretty much all the jobs and the subjobs.

I'd be really grateful if anyone might be able to help get this query to produce the report i want. I can't think of anything that might cause the problem.

I hope i explain it well.

Thank you in advance

 

[ahau]

Hi again,

The more i investigated the problem, This is what i found.

Remember the problem with the subjob_no that doesn't show up in the report. It seems to me that if the job has a subjob for example

Person A works on job 00010100 for 3 hrs.
Person A works on job 00010101 for 4 hrs.

In the report, it shows as follows;

00010100 - Administration
A 7hrs $--

instead of;

00010100 - Administration
A 3hrs $--
00010101 - Marketing
A 4hrs $--

I don't understand why it crams it all up together. it treats 00010101 as if it's 00010100. The good thing is that i thought i lost the information there but it actually shows there but as one with 00010100.

Also, when i run the sql query that i copied and pasted on the earlier thread, it shows the 00010101 4hrs there, but it doesn't appear in the report. I have made sure that i show all of them, industry_no, client_no, job_no and subjob_no. (Weird)

Your help is greatly appreciated.

Thank you in advance

 

[ahau]

Hi All,

I believe the problem is fixed already. It's not to do with the sql query but the report itself. because the report has so many of the groupheaders and it's my first time dealing with this advanced report formatting, hence i made a lot of mistakes there.

Anyway, thanks guys for your help.