HQ double sales for different department

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...

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.

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:

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.