HQ double sales for different department

We have 4 stores each with a different department, I tried running a report in HQ for one of the departments and it shows the totals for the correct department but it also show the same totals in another one of the departments like it doubles does anyone know what the problem is? It show items from the department I wanted to see in the correct department but also in a different department with the same transaction numbers. Please help!!!!!

Reply to
Claudia Stevens
Loading thread data ...

Not sure exactly what you have, but in almost all cases, your JOIN statements in HQ Reports need to join ON an ID Field AND StoreID...

Reply to
Glenn Adams [MVP - Retail Mgmt]

Reply to
Claudia Stevens

Most of the tables in HQ need ID + Store ID to make a unique join - if you join TransactionEntry to Transaction on TransactionEntry.TransctionNumber Transaction.TransactionNumber, you may get multiple results, because each store can use the same range of TransactionNumbers. To get a unique join you would have to use:

FROM [Transaction] INNER JOIN TransactionEntry ON [Transaction].TransactionNumber = TransactionEntry.TransactionNumber AND [Transaction].StoreID = TransactionEntry.StoreID

If you didn't include the StoreID in the Join Above, you could end up with the same transaction entry record showing up as part of multiple transactions.

That sort of sounds like what you are describing in your post.

Reply to
Glenn Adams [MVP - Retail Mgmt]

This is what I show for Department sales report. Is it wrong?

Begin ReportSummary ReportType = reporttypeSales ReportTitle = "Department Sales Report" PageOrientation = pageorientationLandscape OutLineMode = True Groups = 1 GroupDescription = "" DisplayLogo = True LogoFileName = "MyLogo.bmp" ProcedureCall = "" TablesQueried FROM TransactionEntry WITH(NOLOCK) INNER JOIN [Transaction] WITH(NOLOCK) ON TransactionEntry.TransactionNumber [Transaction].TransactionNumber AND TransactionEntry.StoreID = [Transaction].StoreID LEFT JOIN Item WITH(NOLOCK) ON TransactionEntry.ItemID = Item.ID LEFT JOIN Department WITH(NOLOCK) ON Item.DepartmentID Department.ID LEFT JOIN Category WITH(NOLOCK) ON Item.CategoryID = Category.ID LEFT JOIN Store WITH(NOLOCK) ON TransactionEntry.StoreID = Store.ID

"Glenn Adams [MVP - Retail Mgmt]" wrote:

Reply to
Claudia Stevens

Looks right to me. Try reindexing your HQ Database, but you may just have data that looks the same by coincidence - remember that multiple stores can use the same transaction number, so having transaction "1001" show up under multiple stores isn't neccessarilly wrong.

Reply to
Glenn Adams [MVP - Retail Mgmt]

BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.