CODEWITHSUNDEEP
sqlms-accessmultiple-tables
Eyowzitgoin
Eyowzitgoin

Reputation: 129

Multiple records joined Access SQL

I'm not sure if what I want to do is possible but if it is possible, it's probably a really easy solution that I just can't figure out. Once things get to a certain complexity though, my head starts spinning. Please forgive my ignorance.

I have a database running in MS Access 2007 for a school which has a plethora of tables joined to each other. I'm trying to create a query in which I get information from several tables. I'm looking up sales and payment information for different customers, pulling info from several different linked tables. Each sale is broken down into one of 4 categories, Course Fee, Registration Fee, Book Fee and Others. Because each customer will have multiple purchases, each one is a separate entry in the Sales table. The payment information is also in its own table.

My SQL currently looks like this:

    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;

This works and brings up the information I need. However, I am getting one record for each sale as in:

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

What I need is one line for each customer but each sale type in it's own column in the row with the sale amount listed in its value field. As so:

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

Can anyone help and possibly explain what I should/need to be doing?

Thanks in advance!

Upvotes: 1

Views: 216

Answers (1)

Fionnuala
Fionnuala

Reputation: 91376

You can create a cross tab from the query you have already created. Add the query to the Query Design Grid, choose Crosstab from query types, and select a Row or rows, Column and Value.

Say:

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

If you want a certain order, you can edit the property sheet to include column headings, or you can add an In statement to the SQL. Note that if you add column headings, a column will be included for each column, whether or not data is available, and more importantly, a column will not be included that has data, if it is not listed.

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");

Upvotes: 1

Related Questions