Query Setup Help

  • Thread starter Thread starter esi
  • Start date Start date
E

esi

I have a tblpayroll table with the following fields:

tblpayroll
Period
Dept.
Hours Code
Hours

I also have 2 other tables

tbldept
dept.

and

tblperiod
period

I want to be able to see hours for every period and every dept. If a period
or dept has no hours, I want to see a "blank" in the query results. I can
do this with left joining 1 table but, not two and I have not been able to
setup 2 queries based on the way I want my data results.
 
Hi,

Try doing the following:

1) Create new query with dept and period tables. This
should give you every combination of dept and period.

2) Create second query. As input to this query, use query
created above and payroll table. Join query to table by
period and dept. Make sure you left join from query to
table, because you want everything from dept/period query.

Hope this helps.
 
Les,

I'm still not getting all the records I was expecting specifically, the
"blank" records.
 
Did you get all depts and periods from the first query?
You should have.

The second query should have left joins on dept and period
fields, from query to payroll table.

Maybe you can post sql, if you still have problems.
 
Here is my sql for Q1

SELECT tblMonth.Month, tblMonth.Period, tblDepartments.[Department Number]
FROM tblMonth, tblDepartments;

and Q2

SELECT Q1.Period, Q1.[Department Number], Sum(tblPayroll2004.Hrs) AS
SumOfHrs
FROM Q1 LEFT JOIN tblPayroll2004 ON (Q1.[Department Number] =
tblPayroll2004.Dept) AND (Q1.Period = tblPayroll2004.Period)
WHERE (((tblPayroll2004.Code)="OTP"))
GROUP BY Q1.Period, Q1.[Department Number];

I wouls expect to see some blank fields for dept numbers w/ no "OTP" pay
code for some periods. This query is eliminating all the blanks. Thanks
for the help.
 
Hi,
Whenever you use left joins, things don't work as you
would expect, when you have criteria based on the
table/query on the right.
Keep Q1 as is. Make a new Q2, based on your payroll
table. The only criteria you want here is Code="OTP".
Then, make a new Q3, which would left join Q1 and Q2 by
dept and period. Group and total the same way you did
before.
-----Original Message-----
Here is my sql for Q1

SELECT tblMonth.Month, tblMonth.Period, tblDepartments. [Department Number]
FROM tblMonth, tblDepartments;

and Q2

SELECT Q1.Period, Q1.[Department Number], Sum (tblPayroll2004.Hrs) AS
SumOfHrs
FROM Q1 LEFT JOIN tblPayroll2004 ON (Q1.[Department Number] =
tblPayroll2004.Dept) AND (Q1.Period = tblPayroll2004.Period)
WHERE (((tblPayroll2004.Code)="OTP"))
GROUP BY Q1.Period, Q1.[Department Number];

I wouls expect to see some blank fields for dept numbers w/ no "OTP" pay
code for some periods. This query is eliminating all the blanks. Thanks
for the help.
Also,
Be sure you select the dept and period from the query,
not the payroll table.
Click to expand...


.
Click to expand...
 
Thanks Les....it worked perfect.

Les said:
Hi,
Whenever you use left joins, things don't work as you
would expect, when you have criteria based on the
table/query on the right.
Keep Q1 as is. Make a new Q2, based on your payroll
table. The only criteria you want here is Code="OTP".
Then, make a new Q3, which would left join Q1 and Q2 by
dept and period. Group and total the same way you did
before.
-----Original Message-----
Here is my sql for Q1

SELECT tblMonth.Month, tblMonth.Period, tblDepartments. [Department Number]
FROM tblMonth, tblDepartments;

and Q2

SELECT Q1.Period, Q1.[Department Number], Sum (tblPayroll2004.Hrs) AS
SumOfHrs
FROM Q1 LEFT JOIN tblPayroll2004 ON (Q1.[Department Number] =
tblPayroll2004.Dept) AND (Q1.Period = tblPayroll2004.Period)
WHERE (((tblPayroll2004.Code)="OTP"))
GROUP BY Q1.Period, Q1.[Department Number];

I wouls expect to see some blank fields for dept numbers w/ no "OTP" pay
code for some periods. This query is eliminating all the blanks. Thanks
for the help.
Also,
Be sure you select the dept and period from the query,
not the payroll table.


-----Original Message-----
Les,

I'm still not getting all the records I was expecting
specifically, the
"blank" records.


message
Hi,

Try doing the following:

1) Create new query with dept and period tables. This
should give you every combination of dept and period.

2) Create second query. As input to this query, use
query
created above and payroll table. Join query to table by
period and dept. Make sure you left join from query to
table, because you want everything from dept/period
query.

Hope this helps.

-----Original Message-----
I have a tblpayroll table with the following fields:

tblpayroll
Period
Dept.
Hours Code
Hours

I also have 2 other tables

tbldept
dept.

and

tblperiod
period

I want to be able to see hours for every period and
every
dept. If a period
or dept has no hours, I want to see a "blank" in the
query results. I can
do this with left joining 1 table but, not two and I
have
not been able to
setup 2 queries based on the way I want my data
results.


.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top