UtterAccess Discussion Forums

UtterAccess Discussion Forums > Microsoft® Access > Access Queries

Scott_R

Mar 13 2009, 02:30 PM

I want to combine fields and then have them total up. Our sales totals are in 7 seperate fields within a table. I want to combine those fields so there is just a total amount. Here is my SQL code:

SELECT PUBLIC_BOKSUM_DATA.[SLS_SUM#3], PUBLIC_BOKSUM_DATA.[SLS_SUM#4], PUBLIC_BOKSUM_DATA.[SLS_SUM#5], PUBLIC_BOKSUM_DATA.[SLS_SUM#6], PUBLIC_BOKSUM_DATA.[SLS_SUM#7], PUBLIC_BOKSUM_DATA.[SLS_SUM#8], PUBLIC_BOKSUM_DATA.[SLS_SUM#9], PUBLIC_BOKSUM_DATA.[SLS_SUM#10], PUBLIC_BOKSUM_DATA.[SLS_SUM#10], PUBLIC_BOKSUM_DATA.[SLS_SUM#11], PUBLIC_BOKSUM_DATA.[SLS_SUM#12], PUBLIC_BOKSUM_DATA.[SLS_SUM#13], PUBLIC_BOKSUM_DATA.CODE_CAT_PRDT, PUBLIC_BOKSUM_DATA.ID_SLSREP, PUBLIC_BOKSUM_DATA.YR, PUBLIC_TABLES_SLSREP.NAME_SLSREP
FROM PUBLIC_BOKSUM_DATA LEFT JOIN PUBLIC_TABLES_SLSREP ON PUBLIC_BOKSUM_DATA.ID_SLSREP = PUBLIC_TABLES_SLSREP.ID_SLSREP
WHERE (((PUBLIC_BOKSUM_DATA.YR)=[enter year]));

any help would be appreciated.

Thanks,

Scott

truittb

Mar 13 2009, 02:57 PM

You should look at normalizing your table structure and eliminate the repeating groups, i.e. multiple sales fields.

CODE

SELECT D.[SLS_SUM#3]
    + D.[SLS_SUM#4]
    + D.[SLS_SUM#5]
    + D.[SLS_SUM#6]
    + D.[SLS_SUM#7]
    + D.[SLS_SUM#8]
    + D.[SLS_SUM#9]
    + D.[SLS_SUM#10]
    + D.[SLS_SUM#11]
    + D.[SLS_SUM#12]
    + D.[SLS_SUM#13] as SumTotal
    , D.CODE_CAT_PRDT
    , D.ID_SLSREP
    , D.YR
    , P.NAME_SLSREP
FROM PUBLIC_BOKSUM_DATA as D
    LEFT JOIN PUBLIC_TABLES_SLSREP as P
        ON D.ID_SLSREP = P.ID_SLSREP
WHERE D.YR=[enter year];

Scott_R

Mar 16 2009, 08:12 AM

Worked Great! Thanks. I agree about the table, however they are read only tables from our ERP system, can not edit only query on them.

truittb

Mar 16 2009, 08:17 AM

You are welcome.

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.