Multiple Table Field Addition query

[Guest]

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

 

[Guest]

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....

 

[Duane Hookom]

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#DatabaseDesign101.

 

[John Vinson]

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]

 

[Guest]

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]

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

 

[Guest]

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:

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.

 

[John Vinson]

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.

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]