Unusual Column Name in Query (1 Viewer)

[RogerCooper]

I have a keyed external SQL server table called dbo_HISTORY that contains a summary of sales data. It has the fields:

INV_NUMBER, LINE_NO, MONTH, YEAR, CUSTOMER_ID, SALESMAN, SEGMENT, NET_SALES, KEY (and other fields not relevant to the question).
(Segment refers to individual markets that a customer might sell in)
(Key is equal to CUSTOMER_ID & SEGMENT and is created to make this query easier)

Our sales department would like a query that shows each customer_id/segment combination on single line that contains the following information:
Total Sales by Year, Year-to-Date Sales by Year, and % change in YTD. I want this to be a query that does not need to be changed each year. HISTORY only contains 5 calendar years (plus the current year)

This requires multiple queries of course.

Query#1: Customer Sales by Year-Segment-FY

TRANSFORM Sum(CCur([NET_SALES])) AS Expr2
SELECT dbo_HISTORY.Customer_ID, dbo_HISTORY.CUSTOMER_NAME, dbo_HISTORY.SALESMAN, dbo_HISTORY.SEGMENT, dbo_HISTORY.Customer_ID_Segment AS [Key]
FROM [Close Information], (Segment INNER JOIN dbo_HISTORY ON Segment.Segment = dbo_HISTORY.SEGMENT) INNER JOIN dbo_Spec_Part ON dbo_HISTORY.PRODUCT = dbo_Spec_Part.PART_ID
WHERE (((dbo_HISTORY.dATE)<=[Period End]) AND ((dbo_Spec_Part.Excluded)="N") AND ((Segment.Excluded)=False))
GROUP BY dbo_HISTORY.Customer_ID, dbo_HISTORY.CUSTOMER_NAME, dbo_HISTORY.SALESMAN, dbo_HISTORY.SEGMENT, dbo_HISTORY.Customer_ID_Segment
ORDER BY dbo_HISTORY.Customer_ID, dbo_HISTORY.Customer_ID_Segment
PIVOT [Year] & " FY";

Query#2: Customer Sales by Year-Segment-YTD

TRANSFORM Sum(CCur([NET_SALES])) AS Expr2
SELECT dbo_HISTORY.Customer_ID_Segment AS K
FROM [Close Information], (Segment INNER JOIN dbo_HISTORY ON Segment.Segment = dbo_HISTORY.SEGMENT) INNER JOIN dbo_Spec_Part ON dbo_HISTORY.PRODUCT = dbo_Spec_Part.PART_ID
WHERE (((dbo_HISTORY.dATE)<=[Period End]) AND ((dbo_Spec_Part.Excluded)="N") AND ((Segment.Excluded)=False) AND ((dbo_HISTORY.MONTH)<=[Month#]))
GROUP BY dbo_HISTORY.Customer_ID_Segment
ORDER BY dbo_HISTORY.Customer_ID_Segment
PIVOT [Year] & " YTD";

Query#3: Customer Sales by Year-YTD-Change%

SELECT [Customer Sales by Year-Segment-YTD-1].Customer_ID, [Customer Sales by Year-Segment-YTD-0].Sales AS [YTD-0], [Customer Sales by Year-Segment-YTD-1].Sales AS [YTD-1], CInt((Nz([Customer Sales by Year-Segment-YTD-0]![Sales])/[Customer Sales by Year-Segment-YTD-1]![Sales]-1)*100) AS [Change%], [Customer Sales by Year-Segment-YTD-1].Key
FROM [Customer Sales by Year-Segment-YTD-1] LEFT JOIN [Customer Sales by Year-Segment-YTD-0] ON [Customer Sales by Year-Segment-YTD-1].Key = [Customer Sales by Year-Segment-YTD-0].Key
WHERE ((([Customer Sales by Year-Segment-YTD-1].Sales)>0))
ORDER BY [Customer Sales by Year-Segment-YTD-1].Customer_ID, [Customer Sales by Year-Segment-YTD-1].Key;

These 3 queries are working correctly
Query#4

SELECT [Customer Sales by Year-Segment-FY].*, [Customer Sales by Year-Segment-YTD].*
FROM [Customer Sales by Year-Segment-FY] LEFT JOIN [Customer Sales by Year-Segment-YTD] ON [Customer Sales by Year-Segment-FY].Key = [Customer Sales by Year-Segment-YTD].K;

This also works correctly. The segment column is displayed as "SEGMENT".

Query#5

SELECT [Customer Sales by Year-Segment-FY].*, [Customer Sales by Year-Segment-YTD].*, [Customer Sales by Year-Segment-YTD-Change%].[Change%] AS [YTD Change%], [Customer Sales by Year-Segment-% of LY].[% of LY]
FROM (([Customer Sales by Year-Segment-FY] LEFT JOIN [Customer Sales by Year-Segment-YTD] ON [Customer Sales by Year-Segment-FY].Key = [Customer Sales by Year-Segment-YTD].K) LEFT JOIN [Customer Sales by Year-Segment-YTD-Change%] ON [Customer Sales by Year-Segment-FY].Key = [Customer Sales by Year-Segment-YTD-Change%].Key) LEFT JOIN [Customer Sales by Year-Segment-% of LY] ON [Customer Sales by Year-Segment-FY].Key = [Customer Sales by Year-Segment-% of LY].Key;

Now the segment column displays as "Customer Sales by Year-Segment-FY.SEGMENT". All I did was add another left join. The added query does not have have any columns named SEGMENT. This is largely a cosmetic issue but I wonder why it is displaying this way. Is there a better way to solve the underlying request.

 

[ebs17]

For me, formatting a SQL statement to read is mandatory.

SELECT
   [Customer Sales by Year-Segment-FY].*,
   [Customer Sales by Year-Segment-YTD].*,
   [Customer Sales by Year-Segment-YTD-Change%].[Change%] AS [YTD Change%],
   [Customer Sales by Year-Segment-% of LY].[% of LY]
FROM
   (
      ([Customer Sales by Year-Segment-FY]
         LEFT JOIN [Customer Sales by Year-Segment-YTD]
         ON [Customer Sales by Year-Segment-FY].Key = [Customer Sales by Year-Segment-YTD].K
      )
      LEFT JOIN [Customer Sales by Year-Segment-YTD-Change%]
      ON [Customer Sales by Year-Segment-FY].Key = [Customer Sales by Year-Segment-YTD-Change%].Key
   )
   LEFT JOIN [Customer Sales by Year-Segment-% of LY]
   ON [Customer Sales by Year-Segment-FY].Key = [Customer Sales by Year-Segment-% of LY].Key

All I did was add another left join

I read from the statement that compared to Query#4, two LEFT JOINs have been added.
Examine the fourth table/query for the Segment field in the SELECT part, which would explain the complainant behavior.

Is there a better way to solve the underlying request.

SELECT
   FY.*,
   YTD.*,
   Chng.[Change%] AS [YTD Change%],
   LY.[% of LY]
FROM
   (
      ([Customer Sales by Year-Segment-FY] AS FY
         LEFT JOIN [Customer Sales by Year-Segment-YTD] AS YTD
         ON FY.Key = YTD.K
      )
      LEFT JOIN [Customer Sales by Year-Segment-YTD-Change%] AS Chng
      ON FY.Key = Chng.Key
   )
   LEFT JOIN [Customer Sales by Year-Segment-% of LY] AS LY
   ON FY.Key = LY.Key

Formatting the statement also helps visibly in terms of readability.

 

[RogerCooper]

I am working in the graphic interface where things are clearer.

 

[ebs17]

When executing a query, however, the SQL statement is executed, not the rows and columns and boxes of the QBE. The statement is the real definition, the QBE is an assistant.
Try deleting and re-creating the query.

 

[RogerCooper]

The problem remains after re-creating the query.

 

[ebs17]

I can't explain why, maybe there's something left somewhere in the features. I'm reluctant to use the QBE, but there are many big proponents. They should think of something.

Try it the other way around. Close the database and reopen it (creation of a defined state). Open a new query and go straight to the SQL view. Paste the clean SQL statement there and save.

SELECT
   FY.*,
   YTD.*,
   Chng.[Change%] AS [YTD Change%],
   LY.[% of LY]
FROM
   ([Customer Sales by Year-Segment-FY] AS FY
      LEFT JOIN [Customer Sales by Year-Segment-YTD] AS YTD
      ON FY.Key = YTD.K
   )
   LEFT JOIN [Customer Sales by Year-Segment-YTD-Change%] AS Chng
   ON FY.Key = Chng.Key

 

[RogerCooper]

Still have the same problem even with "clean" statement.

 

[ebs17]

SELECT
   FY.*,
   YTD.*,
   Chng.[Change%] AS [YTD Change%]
FROM
   ([Customer Sales by Year-Segment-FY] AS FY
      LEFT JOIN [Customer Sales by Year-Segment-YTD] AS YTD
      ON FY.Key = YTD.K
   )
   LEFT JOIN [Customer Sales by Year-Segment-YTD-Change%] AS Chng
   ON FY.Key = Chng.Key

The "clean" statement wasn't clean. There was another field in the SELECT part.

Otherwise, it is good style (and error prevention) never to use the asterisk for everything (except Count), but to list the individual fields by name.

 

[RogerCooper]

I need to use the asterisk, because I don't know in advance what fields will be present. I want to avoid editing this query every year.