Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2004
    Location
    Lorain, OH
    Posts
    11

    Unanswered: Relationships in a Crosstab Query

    Hello-

    I am having a problem with the count values in a crosstab query. The values are counting seven for every one occurence of data in the table. I believe the problem may have to do with the relationships of the tables. ]

    I have four tables involved: a Workers table, an Errors table (for worker pulls in a warehouse), a ShiftInfo Table and a DayOfWeekList Table in which the numerical values of 1 through 7 are associated with the names of the days of the week.

    The Row values in the crosstab query are based upon the worker name. I did have a LastFirstMiddle concatenation field in mind that requires the use of a query calculation of the Last & ", " & First & " " & Middle fields. I have such a query available, but it added additional complexity to resolving the main issue of the count in the value, so it will be addressed next.

    For the Column Heading, I am using the Date field. I wanted to use the DayofWeek field from the DayOfWeekListTable, included here for illustration. I have a DUMMY record for each day of the week so there will be a column for each day in the query result that will be passed on to a predefined report. I want a Sunday, Monday, ... through Saturday column. I have acheived this in a different query with different tables, but have had trouble here.

    The main issue in this query that I am trying to resolve right now is the Count value field of ErrorID. I am getting a count increase of 7 for EACH single record in the errors table. The relationships are One Worker to Many ErrorsTable; One Shift to Many ErrorsTable; One ErrorTypeAbbr to Many ErrorsTable; and DayOfWeekList is NOT RELATED in the query to the DayOfWeek numeric field in the ShiftInfo table.

    I am including the code below for illustration in SQL.

    PARAMETERS StartingDate DateTime, EndingDate DateTime;
    TRANSFORM Count(ErrorsTable.ErrorID) AS CountOfErrorID
    SELECT Workers.LastName, Workers.FirstName
    FROM DayOfWeekList, Workers INNER JOIN (ShiftInfo INNER JOIN ErrorsTable ON ShiftInfo.ShiftID = ErrorsTable.ShiftID) ON Workers.WorkerID = ErrorsTable.EmployeeID
    WHERE (((ShiftInfo.Date)>=[StartingDate] And (ShiftInfo.Date)<=[EndingDate]))
    GROUP BY Workers.LastName, Workers.FirstName
    PIVOT ShiftInfo.Date;

    Any help would be appreciated.

    Thanks.

    Vince
    VDeluca7@aol.com

  2. #2
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Quote Originally Posted by Vincent DeLuca
    DayOfWeekList is NOT RELATED in the query to the DayOfWeek numeric field in the ShiftInfo table.
    Vince
    If you do not relate tables in a query you will get all combinations of the two tables (try a select query and see the result). So because you have not related DayOfWeekList you are getting all your records duplicated 7 times (because there are 7 records in DayOfWeekList).

    Remove the following from your SQL:
    DayOfWeekList,

    You should then get the correct count with dates as headers.

    If you want days as headers, you need to relate DayOfWeekList to the rest. I can't be sure of your primary and foreign keys but at a guess I would say it should look like:
    Code:
    PARAMETERS StartingDate DateTime, EndingDate DateTime;
    TRANSFORM Count(ErrorsTable.ErrorsID) AS CountOfErrorsID
    SELECT Workers.LastName, Workers.FirstName
    FROM Workers INNER JOIN ((DayOfWeekList INNER JOIN ShiftInfo ON DayOfWeekList.DayOfWeek = ShiftInfo.DayOfWeek) INNER JOIN ErrorsTable ON ShiftInfo.Shift = ErrorsTable.Shift) ON Workers.WorkerID = ErrorsTable.WorkerID
    WHERE (((ShiftInfo.Date)>=[StartingDate] And (ShiftInfo.Date)<=[EndingDate]))
    GROUP BY Workers.LastName, Workers.FirstName
    PIVOT DayOfWeekList.Day;
    Hope that helps
    Chris

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •