Несколько записей присоединились к Access SQL

Я не уверен, что то, что я хочу сделать, возможно, но если это возможно, это, вероятно, действительно простое решение, которое я просто не могу понять. Но когда дело доходит до определенной сложности, у меня начинает кружиться голова. Пожалуйста, простите мое незнание.

У меня есть база данных, работающая в MS Access 2007 для школы, в которой есть множество таблиц, соединенных друг с другом. Я пытаюсь создать запрос, в котором получаю информацию из нескольких таблиц. Я ищу информацию о продажах и платежах для разных клиентов, извлекая информацию из нескольких разных связанных таблиц. Каждая продажа делится на одну из 4 категорий: плата за курс, плата за регистрацию, плата за книгу и другие. Поскольку у каждого покупателя будет несколько покупок, каждая из них является отдельной записью в таблице «Продажи». Информация о платеже также находится в отдельной таблице.

Мой SQL сейчас выглядит так:

    SELECT StudentContracts.CustomerID, (Customers.CFirstName & " " & Customers.CLastName) AS Name, Customers.Nationality, Courses.CourseTitle, (StudentContracts.ClassesBought + StudentContracts.GiftClasses) AS Weeks, StudentContracts.StartDate, Sales.SaleAmount, SaleType.SaleType, Sales.DueDate,  Payments.PaymentAmount

FROM (
    (
        (Customers INNER JOIN StudentContracts ON Customers.CustomerID = StudentContracts.CustomerID)
            INNER JOIN Payments ON Customers.CustomerID = Payments.CustomerID) 
    INNER JOIN 
        (SaleType INNER JOIN Sales ON SaleType.SalesForID = Sales.SalesForID) 
    ON Customers.CustomerID = Sales.CustomerID) 
INNER JOIN 
    (
        (Courses INNER JOIN Classes ON Courses.CourseID = Classes.CourseID)
     INNER JOIN StudentsClasses ON Classes.ClassID = StudentsClasses.ClassID)
 ON Customers.CustomerID = StudentsClasses.CustomerID;

Это работает и дает мне нужную информацию. Однако я получаю по одной записи для каждой продажи, например:

CustomerID  Name ... SaleAmount  SaleType  PaymentAmount
1           Bob      $600        Course    $1000
1           Bob      $300        RgnFee    $1000
1           Bob      $100        Book      $1000

Мне нужна одна строка для каждого покупателя, но для каждого типа продажи в отдельном столбце в строке с суммой продажи, указанной в поле значения. Как так:

CustomerID  Name ... Course  RgnFee  Book  Others  PaymentAmount
1           Bob      $600    $300    $100          $1000

Может ли кто-нибудь помочь и, возможно, объяснить, что мне нужно / нужно делать?

Заранее спасибо!


sql ms-access multiple-tables
person Eyowzitgoin    schedule 13.08.2012    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Вы можете создать кросс-таблицу из уже созданного вами запроса. Добавьте запрос в таблицу конструктора запросов, выберите перекрестную таблицу из типов запросов и выберите строку или строки, столбец и значение.

Сказать:

TRANSFORM Sum(t.SaleAmount) AS SumOfSaleAmount
SELECT t.ID, t.Name, Sum(t.SaleAmount) AS Total
FROM TableQuery t
GROUP BY t.ID, t.Name
PIVOT t.SaleType

Если вам нужен определенный порядок, вы можете отредактировать лист свойств, включив заголовки столбцов, или вы можете добавить оператор In в SQL. Обратите внимание, что если вы добавляете заголовки столбцов, столбец будет включен для каждого столбца, независимо от того, доступны ли данные, и, что более важно, столбец не будет включен, содержащий данные, если он не указан .

TRANSFORM Sum(t.SaleAmount) AS SumOfSaleAmount
SELECT t.ID, t.Name, Sum(t.SaleAmount) AS Total
FROM TableQuery t
GROUP BY t.ID, t.Name
PIVOT t.SaleType In ("Course","RgnFee","Book","Others");
person Fionnuala    schedule 13.08.2012
comment
Хорошо, так что это работает, но они мне нужны в определенном порядке, поэтому теперь я запускаю запрос, для которого затем я запускаю перекрестный запрос, для которого мне нужно выполнить третий запрос. Это оказывается гораздо менее изящным и простым, чем должно быть. Дайте мне немного времени, чтобы разобраться, не смогу ли я сделать его более элегантным, но спасибо за ответ! Перекрестный запрос сделает свое дело (хотя мне не нужна общая сумма, и поэтому я фактически оставляю это поле за пределами моей итоговой таблицы, но все равно выполняю преобразование). Приветствую и большое спасибо !! - person Eyowzitgoin; 13.08.2012