Totals in Query

[accessjunior]

Hello everyone,

I'm completely new to Access and know nothing about code. I have created a query and simply want to sum up the numbers 3 columns of "NET_QTY_FP", "STOCK_ON_HAND", and "NET_SALES_EXC_VAT_FP", but unfortunately it is tripling all of the amounts in each of the columns. Any idea why? This is the SQL, but, as mentioned before, I know NOTHING about it. I just copied it as it seems to help diagnose problems
Thanks in advance!!


SELECT DISTINCT dbo_BI_ARTICLE_DETAILS_SALES_DATA.RETAIL_WEEK_YEAR, TBL_Calendar.RT_YR, TBL_Calendar.RT_MNTH, dbo_BI_ARTICLE_DETAILS_SALES_DATA.STORE_CODE, TBL_Article_Details.ARG_BusinessUnit, TBL_Article_Details.Division, dbo_BI_ARTICLE_DETAILS_SALES_DATA.ARTICLE_NO, TBL_Article_Details.ModelName, Sum(dbo_BI_ARTICLE_DETAILS_SALES_DATA.NET_QTY_FP) AS SumOfNET_QTY_FP, Sum(dbo_BI_ARTICLE_DETAILS_SALES_DATA.STOCK_ON_HAND) AS SumOfSTOCK_ON_HAND, Sum(dbo_BI_ARTICLE_DETAILS_SALES_DATA.NET_SALES_EXC_VAT_FP) AS SumOfNET_SALES_EXC_VAT_FP
FROM ((dbo_BI_ARTICLE_DETAILS_SALES_DATA LEFT JOIN TBL_Customer_Data ON dbo_BI_ARTICLE_DETAILS_SALES_DATA.REPORTING_UNIT = TBL_Customer_Data.REPORTING_UNIT) LEFT JOIN TBL_Article_Details ON dbo_BI_ARTICLE_DETAILS_SALES_DATA.ARTICLE_NO = TBL_Article_Details.ARTICLE_NO) LEFT JOIN TBL_Calendar ON dbo_BI_ARTICLE_DETAILS_SALES_DATA.RETAIL_WEEK_YEAR = TBL_Calendar.RETAIL_WEEK_YEAR
WHERE (((dbo_BI_ARTICLE_DETAILS_SALES_DATA.STORE_CODE) In ("22715","70208","70218","70262","70498","70508","Z0242","Z0248","Z0249","Z0251","Z0252","Z0253","Z09VA")) AND ((TBL_Article_Details.ARG_BusinessUnit)="FOOTBALL") AND ((dbo_BI_ARTICLE_DETAILS_SALES_DATA.CURRENCY)="ARS"))
GROUP BY dbo_BI_ARTICLE_DETAILS_SALES_DATA.RETAIL_WEEK_YEAR, TBL_Calendar.RT_YR, TBL_Calendar.RT_MNTH, dbo_BI_ARTICLE_DETAILS_SALES_DATA.STORE_CODE, TBL_Article_Details.ARG_BusinessUnit, TBL_Article_Details.Division, dbo_BI_ARTICLE_DETAILS_SALES_DATA.ARTICLE_NO, TBL_Article_Details.ModelName;

 

[namliam]

Well if it is trippling the amounts, then your joins are probably at fault, creating 3 records where you only want one.

Next time when posting code, use the code tag (see my signature) and adding some formatting wont hurt either....

FROM ((dbo_BI_ARTICLE_DETAILS_SALES_DATA 
LEFT JOIN TBL_Customer_Data   ON dbo_BI_ARTICLE_DETAILS_SALES_DATA.REPORTING_UNIT   = TBL_Customer_Data.REPORTING_UNIT) 
LEFT JOIN TBL_Article_Details ON dbo_BI_ARTICLE_DETAILS_SALES_DATA.ARTICLE_NO       = TBL_Article_Details.ARTICLE_NO) 
LEFT JOIN TBL_Calendar        ON dbo_BI_ARTICLE_DETAILS_SALES_DATA.RETAIL_WEEK_YEAR = TBL_Calendar.RETAIL_WEEK_YEAR

Total guess, but most likely candidate would seem to be the Article_Details table.

 

[accessjunior]

Hi namliam,
thanks for your response, and suggestions. I'm don't really understand what to do though. I didn't write any of the code, but just used the Totals option under the Design menu. Why would it triple amounts, and why would the problem be with dbo_BI_ARTICLE_DETAILS_SALES_DATA if this is just a standalone database table?
Thanks!

 

[namliam]

The DBO_ would normally indicate a linked table to a SQL Server, thus it wouldnt be a standalone database...

Even if you would undo the totals option, the triplication should be there already, except you dont see it in the amount, but rather you find 3 times the number of rows.

As to WHY it is triplicating, that answer lies within your data... Like I said a TOTAL GUESS from my side.... but that guess can be very wrong obviously.

Remove the totals and analyse your data, check the indiviual tables involved you will find the trouble there.

 

[accessjunior]

Hello,
I'm still trying to figure out how to fix the issue of the data being multiplied rather than just summed when I use the Totals function. I've pasted the code below, and also have screen shots of the different tables and results.

Basically, I'm trying to just create a query that supplements the "dbo" database with information from a "Articles Data" table and "Month/Year" table, and my expectation (see attachment #3) is to have one line for this specific article that I have filtered for (D87311), have the "Net Sales" column total the max NET_SALES which is $288,43, the NET_QTY sum to the 5 units total, and the "STOCK_ON" column sum to 43 units total.

HELP PLEASE & THANKS!!!

SELECT DISTINCT dbo_BI_ARTICLE_DETAILS_SALES_DATA.RETAIL_WEEK_YEAR, TBL_Calendar.RT_YR, TBL_Calendar.RT_MNTH,
dbo_BI_ARTICLE_DETAILS_SALES_DATA.STORE_CODE,
Last(TBL_Article_Details.PlanSeasonDescription) AS LastOfPlanSeasonDescription,
dbo_BI_ARTICLE_DETAILS_SALES_DATA.ARTICLE_NO,
Max(dbo_BI_ARTICLE_DETAILS_SALES_DATA.NET_SALES_EXC_VAT_FP) AS MaxOfNET_SALES_EXC_VAT_FP,
Sum(dbo_BI_ARTICLE_DETAILS_SALES_DATA.NET_QTY_FP) AS SumOfNET_QTY_FP,
Sum(dbo_BI_ARTICLE_DETAILS_SALES_DATA.STOCK_ON_HAND) AS SumOfSTOCK_ON_HAND
FROM (dbo_BI_ARTICLE_DETAILS_SALES_DATA
LEFT JOIN TBL_Article_Details ON dbo_BI_ARTICLE_DETAILS_SALES_DATA.ARTICLE_NO = TBL_Article_Details.ArticleNo)
LEFT JOIN TBL_Calendar ON dbo_BI_ARTICLE_DETAILS_SALES_DATA.RETAIL_WEEK_YEAR = TBL_Calendar.RETAIL_WEEK_YEAR
GROUP BY dbo_BI_ARTICLE_DETAILS_SALES_DATA.RETAIL_WEEK_YEAR,
TBL_Calendar.RT_YR, TBL_Calendar.RT_MNTH,
dbo_BI_ARTICLE_DETAILS_SALES_DATA.STORE_CODE,
dbo_BI_ARTICLE_DETAILS_SALES_DATA.ARTICLE_NO
HAVING (((dbo_BI_ARTICLE_DETAILS_SALES_DATA.RETAIL_WEEK_YEAR)="272015") AND
((dbo_BI_ARTICLE_DETAILS_SALES_DATA.STORE_CODE)="70508") AND
((dbo_BI_ARTICLE_DETAILS_SALES_DATA.ARTICLE_NO)="D87311"));

 

[namliam]

I cant really fix your data from here. in your tbl_article_details you have 4 times your Article_no D87311 which makes your data duplicate (or even quadruplicate)

Dont know the logic of why you have 4 records there, the difference being SS year and FW year...

Either you need to add the column plan to the join, or make a query so your Article_no becomes unique.