Multiple Table Field Addition query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

x01kgb - 28 Jun 2005 16:13 GMT

Hey all,
teaching myself access and have a question about a certain query:
The following is the SQL although I created the query in design view, can
anybody point me in a direction of getting this query to show data as of now
nothing is showing:

SELECT tblAMParttime_HPW.EmployeeID, tblEmployees.FirstName,
tblEmployees.LastName, tblBegin_End_Date.WeekBegin,
[tblAMParttime_HPW].[Monday_Hours]+[tblDayshift_HPW].[Monday_Hours]+[tblMidnight_HPW].[Monday_Hours]+[tblNightWeekend_HPW].[Monday_Hours]
AS TotalMonday,
[tblAMParttime_HPW].[Tuesday_Hours]+[tblDayshift_HPW].[Tuesday_Hours]+[tblMidnight_HPW].[Tuesday_Hours]+[tblNightWeekend_HPW].[Tuesday_Hours]
AS TotalTuesday,
[tblAMParttime_HPW].[Wednesday_Hours]+[tblDayshift_HPW].[Wednesday_Hours]+[tblMidnight_HPW].[Wednesday_Hours]+[tblNightWeekend_HPW].[Wednesday_Hours]
AS TotalWednesday,
[tblAMParttime_HPW].[Thursday_Hours]+[tblDayshift_HPW].[Thursday_Hours]+[tblMidnight_HPW].[Thursday_Hours]+[tblNightWeekend_HPW].[Thursday_Hours]
AS TotalThursday,
[tblAMParttime_HPW].[Friday_Hours]+[tblDayshift_HPW].[Friday_Hours]+[tblMidnight_HPW].[Friday_Hours]+[tblNightWeekend_HPW].[Friday_Hours]
AS TotalFriday,
[tblAMParttime_HPW].[Saturday_Hours]+[tblDayshift_HPW].[Saturday_Hours]+[tblMidnight_HPW].[Saturday_Hours]+[tblNightWeekend_HPW].[Saturday_Hours]
AS TotalSaturday,
[tblAMParttime_HPW].[Sunday_Hours]+[tblDayshift_HPW].[Sunday_Hours]+[tblMidnight_HPW].[Sunday_Hours]+[tblNightWeekend_HPW].[Sunday_Hours] AS TotalSunday
FROM tblEmployees INNER JOIN (tblBegin_End_Date INNER JOIN
(((tblAMParttime_HPW INNER JOIN tblDayshift_HPW ON tblAMParttime_HPW.ID =
tblDayshift_HPW.ID) INNER JOIN tblMidnight_HPW ON tblAMParttime_HPW.ID =
tblMidnight_HPW.ID) INNER JOIN tblNightWeekend_HPW ON tblAMParttime_HPW.ID =
tblNightWeekend_HPW.ID) ON (tblBegin_End_Date.WeekBegin =
tblNightWeekend_HPW.Begin_Date) AND (tblBegin_End_Date.WeekBegin =
tblMidnight_HPW.Begin_Date) AND (tblBegin_End_Date.WeekBegin =
tblDayshift_HPW.Begin_Date) AND (tblBegin_End_Date.WeekBegin =
tblAMParttime_HPW.Begin_Date)) ON (tblEmployees.EmployeeID =
tblNightWeekend_HPW.EmployeeID) AND (tblEmployees.EmployeeID =
tblMidnight_HPW.EmployeeID) AND (tblEmployees.EmployeeID =
tblDayshift_HPW.EmployeeID) AND (tblEmployees.EmployeeID =
tblAMParttime_HPW.EmployeeID);

I have 4 hour tables (crappy I know) that I want to sum the hours for by day
and then total the hours for the week. Running this query doesn't produce an
error but gives no data, I would think at least with an error that would lead
me to some more information!
Thanks in advance for any advice.
x01kgb

Reply to this Message

x01kgb - 28 Jun 2005 20:26 GMT

> Hey all,
> teaching myself access and have a question about a certain query:
> The following is the SQL although I created the query in design view, can
> anybody point me in a direction of getting this query to show data as of now
> nothing is showing:

Anybody?
Is this even possible?
Please....

Reply to this Message

Duane Hookom - 28 Jun 2005 23:15 GMT

I don't think anyone wants to attempt to sort out your "spreadsheet-like"
table structure and multiple joins.

You might want to check some database design links from Jeff Conrad's web
site
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#Database
Design101.

Signature

Duane Hookom
MS Access MVP
--

>> Hey all,
>> teaching myself access and have a question about a certain query:
[quoted text clipped - 6 lines]
> Is this even possible?
> Please....

Reply to this Message

John Vinson - 29 Jun 2005 00:56 GMT

>I have 4 hour tables (crappy I know) that I want to sum the hours for by day
>and then total the hours for the week. Running this query doesn't produce an
>error but gives no data, I would think at least with an error that would lead
>me to some more information!

I'm GUESSING that you might have NULL values in some of these fields.
Anything plus NULL is NULL. If that's the case, use NZ([fieldname]) to
convert null values to 0 before adding.

Duane's concern about normalization is very well taken - remember the
old dictum, "if you find yourself in a hole, the first thing to do is
quit digging" and step back, and consider normalizing your table
structure.

John W. Vinson[MVP]

Reply to this Message

x01kgb - 29 Jun 2005 01:30 GMT

> >I have 4 hour tables (crappy I know) that I want to sum the hours for by day
> >and then total the hours for the week. Running this query doesn't produce an
[quoted text clipped - 11 lines]
>
> John W. Vinson[MVP]

Yeah,
I have two books and read all about normalization...
Started out with 4 tables (like the current structure) normalized to 1 table
then back to 4.
There are no NULLs, all default values of the added fields are defaulted to
zero.
Advise taken I suppose back to the drawing board.
Thanks for being cool and not flaming about some spreadsheet like bs, some
of us learn things the hard way.
x01kgb

Reply to this Message

John Vinson - 29 Jun 2005 06:00 GMT

>I have two books and read all about normalization...
>Started out with 4 tables (like the current structure) normalized to 1 table
[quoted text clipped - 4 lines]
>Thanks for being cool and not flaming about some spreadsheet like bs, some
>of us learn things the hard way.

Well, any table with data in fieldnames - e.g. Monday_Hours,
Tuesday_Hours - is ipso facto in violation of first normal form, no?

You were right to go back to one table: storing data (dayshift, etc.)
in tablenames is even worse than storing data in fieldnames, and
undoubtedly caused your no-data issue (if an employee failed to work
ALL FOUR shifts there'd be no join).

John W. Vinson[MVP]

Reply to this Message

x01kgb - 29 Jun 2005 01:57 GMT

Alright,
went back to 1 table for hours and added a shift code table.
Works great. Why didn't I think of that earlier?
Sorry about the flame comment as well...
Be prepared highly likely more stupid questions to appear shortly!
Thanks to the mvp's who responded.
;)

Reply to this Message