Query Join Help

[esi]

Running the folowing query but, I'm not getting the desired results.
Essentially, I'm not geting all the records I should. It seem to be a
problem with the joins on the queries that are used in this query. I would
have expected to see some "blank" results in some fields.

SELECT tblMonth.Month, tblMonth.Period, tblDepartments.[Department Number],
qryTotalPayroll.Total_Hrs, qryTotalProdPayroll.Total_ProdHrs,
qryTotalREGPayroll.Total_REGHrs, qryTotalOVTPayroll.Total_OVTHrs,
qryTotalNonProdPayroll.Total_NonProdHrs, qryTotalPayroll.Total_Earn,
qryTotalProdPayroll.Total_ProdEarn, qryTotalREGPayroll.Total_REGEarn,
qryTotalOVTPayroll.Total_OVTEarn, qryTotalNonProdPayroll.Total_NonProdEarn
FROM tblMonth INNER JOIN (((((tblDepartments INNER JOIN qryTotalPayroll ON
tblDepartments.[Department Number] = qryTotalPayroll.Dept) INNER JOIN
qryTotalProdPayroll ON tblDepartments.[Department Number] =
qryTotalProdPayroll.Dept) INNER JOIN qryTotalNonProdPayroll ON
tblDepartments.[Department Number] = qryTotalNonProdPayroll.Dept) INNER JOIN
qryTotalOVTPayroll ON tblDepartments.[Department Number] =
qryTotalOVTPayroll.Dept) INNER JOIN qryTotalREGPayroll ON
tblDepartments.[Department Number] = qryTotalREGPayroll.Dept) ON
(tblMonth.Period = qryTotalREGPayroll.Period) AND (tblMonth.Period =
qryTotalOVTPayroll.Period) AND (tblMonth.Period =
qryTotalNonProdPayroll.Period) AND (tblMonth.Period =
qryTotalPayroll.Period) AND (tblMonth.Period = qryTotalProdPayroll.Period)
WHERE (((tblDepartments.[Department Number]) Like "68*" Or
(tblDepartments.[Department Number])="049115"));

 

[Tom Ellison]

Dear Esi:

Since I don't know much about this situation, and you don't say much,
I can't give you any direct solution. But I'll offer a technique you
may want to try that may begin to give you some help.

Rather than build a rather long query and then wonder why it doesn't
work the way you want, build a small simple query, then add to it a
little at a time, making sure you have the information you expect.

When adding an INNER JOIN, look to see if the number of rows returned
is reduced. If it does, and this is not expected and desirable, then
something is wrong, either with your logic, or with the referential
integrity of the data.

Usually there will be a table that is "primary" to the query, then
other tables that are on the "one" side of a one-to-many relationship
to this one. This "most dependent" table is the best place to start.

Start out very simple:

SELECT * FROM Table1 T

and record the nubmer of rows returned. Add any filters based solely
on this table, and record the number of rows again.

Use table Aliases and keep the text of the SQL readable.

Add the first JOIN and run again. Note if the number of rows returned
increases or decreases. Was this expected? If the change in the
number of rows returned was not what you expected, write some quick
scratch queries to figure out what is wrong. Do not proceed till you
have found and fixed the problem. In many, many cases you will not
expect any change in the number of rows except when adding filters.

The last thing to do is to select the columns you want to see. This
is usually not worth the effort till you have every issue about the
number of rows returned solved.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Running the folowing query but, I'm not getting the desired results.
Essentially, I'm not geting all the records I should. It seem to be a
problem with the joins on the queries that are used in this query. I would
have expected to see some "blank" results in some fields.

SELECT tblMonth.Month, tblMonth.Period, tblDepartments.[Department Number],
qryTotalPayroll.Total_Hrs, qryTotalProdPayroll.Total_ProdHrs,
qryTotalREGPayroll.Total_REGHrs, qryTotalOVTPayroll.Total_OVTHrs,
qryTotalNonProdPayroll.Total_NonProdHrs, qryTotalPayroll.Total_Earn,
qryTotalProdPayroll.Total_ProdEarn, qryTotalREGPayroll.Total_REGEarn,
qryTotalOVTPayroll.Total_OVTEarn, qryTotalNonProdPayroll.Total_NonProdEarn
FROM tblMonth INNER JOIN (((((tblDepartments INNER JOIN qryTotalPayroll ON
tblDepartments.[Department Number] = qryTotalPayroll.Dept) INNER JOIN
qryTotalProdPayroll ON tblDepartments.[Department Number] =
qryTotalProdPayroll.Dept) INNER JOIN qryTotalNonProdPayroll ON
tblDepartments.[Department Number] = qryTotalNonProdPayroll.Dept) INNER JOIN
qryTotalOVTPayroll ON tblDepartments.[Department Number] =
qryTotalOVTPayroll.Dept) INNER JOIN qryTotalREGPayroll ON
tblDepartments.[Department Number] = qryTotalREGPayroll.Dept) ON
(tblMonth.Period = qryTotalREGPayroll.Period) AND (tblMonth.Period =
qryTotalOVTPayroll.Period) AND (tblMonth.Period =
qryTotalNonProdPayroll.Period) AND (tblMonth.Period =
qryTotalPayroll.Period) AND (tblMonth.Period = qryTotalProdPayroll.Period)
WHERE (((tblDepartments.[Department Number]) Like "68*" Or
(tblDepartments.[Department Number])="049115"));

 

[Stephen Rasey]

You say that you expect some blank records. That tells me you are needing
some OUTER JOINS. These are tricky.

Like Tom said. Start small with a two table - one join query. Save that
query as M01qry
then make another query that uses Table C and Join it with M01qry. Call it
M02.
Keep building them up and checking your result set each step of the way.

Advice: use Aliases. In SQL they will look like:
tblDepartments as D INNER JOIN qryTotalPayroll AS QP
Everywhere in the query you replace tblDepartments. with D. and
qryTotalPayrol. with QP.

If I can see the query in the designer (not all SQL can be seen in the
designer), I click the Table object, and in the properties window, I type in
the ALIAS. All the terms that use that table get changed automatically.

Stephen Rasey
Houston
http://wiserways.com
http://excelsig.org