CODEWITHSUNDEEP
sqlms-accessvbams-access-2010
Mohammed AL Jakry
Mohammed AL Jakry

Reputation: 69

how to get multiple columns values in one row with ms access query

i am working on Access DB for my company, i have a table with these columns:

Co-name Project-Name Date1 Date2 Date3 Date4 Date5 Date6 Date7 Date8 Payment1 Payment2 Payment3 Payment4 Payment5 Payment6 Payment7 Payment8 Deatil1 Deatil2 Deatil3 Deatil4 Deatil5 Deatil6 Deatil7 Deatil8

i want to design a query include the following columns :

Date (the user can enter the range) SUM [summation of the payment during this period] Co-name Project-Name

anyone can help me in this query ?

Upvotes: 0

Views: 4023

Answers (2)

PaulFrancis
PaulFrancis

Reputation: 5809

The answer to your question is Normalization. The current setup you have is not going to help you in any way possible. Quite complicated.

Instead of having Columns you should have rows. So the TOTALS query can work its magic. The setup should be,

Co-Name |   Project-Name    |   DateOfPay   |   Payment |   Details

Instead of having this set up, 

Co-Name |   ProjName    |   Date1   |   Payment1|   Detail1 |   Date2   |   Payment2|   Detail2 |   Date3   |   Payment3|   Detail3
--------+---------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-------------
AAA     |   X-1         |22/05/2014 |   £ 500   |   First   |27/07/2014 |   £ 100   |   Second  |           |           |   
BBB     |   Y-1         |02/08/2014 |   £ 300   |   First   |15/09/2014 |   £ 250   |   Second  |24/10/2014 |   £ 400   |   Third   
CCC     |   X-1         |07/11/2014 |   £ 100   |   First   |           |           |           |           |           |

You should have the set up as,

Co-Name |   Project-Name    |   DateOfPay   |   Payment |   Details
--------+-------------------+---------------+-----------+-------------
AAA     |   X-1             |   22/05/2014  |   £ 500   |   First
AAA     |   X-1             |   27/07/2014  |   £ 100   |   Second
BBB     |   Y-1             |   02/08/2014  |   £ 300   |   First
BBB     |   Y-1             |   15/09/2014  |   £ 250   |   Second
BBB     |   Y-1             |   24/10/2014  |   £ 400   |   Third
CCC     |   Z-1             |   07/11/2014  |   £ 100   |   First

This way, your Code will be as simple as,

SELECT 
    [Co-Name], 
    [Project-Name], 
    Sum(Payment) As TotalPaid
FROM
    theProperTable
WHERE
    DateOfPay BETWEEN [EnterTheStartDate:] And [EnterTheEndDate:]
GROUP BY 
    [Co-Name],
    [Project-Name]

Upvotes: 1

Tom Robinson
Tom Robinson

Reputation: 1910

If you can't change the structure of the table, you can fake it by creating a query that returns a "normalized" version of the table. Save this SQL as a query, named ProjectsNormalized:

    (SELECT [Co-Name], [Project-Name], Date1 As DateOfPay, Payment1 As Payment, Detail1 As Details FROM Projects WHERE Date1 Is Not Null;)
        UNION ALL
    (SELECT [Co-Name], [Project-Name], Date2 As DateOfPay, Payment2 As Payment, Detail2 As Details FROM Projects WHERE Date2 Is Not Null;)
        UNION ALL
    (SELECT [Co-Name], [Project-Name], Date3 As DateOfPay, Payment3 As Payment, Detail3 As Details FROM Projects WHERE Date3 Is Not Null;)
        UNION ALL 
    (SELECT [Co-Name], [Project-Name], Date4 As DateOfPay, Payment4 As Payment, Detail4 As Details FROM Projects WHERE Date4 Is Not Null;);

If Projects contains this data:

enter image description here

then ProjectsNormalized will give you this data:

enter image description here

Now you can do queries on ProjectsNormalized just as though it were a table.

Note: It's important to use UNION ALL and not just UNION, because UNION will discard duplicate records. If you have two equal payments on the same day with the same details, you want to report both of them.

Upvotes: 0

Related Questions