Access SQL query

[Linda4me2]

Hello,
I have the following query and it is not working. Please help!

Thank you in advance!

SELECT [t_DivOrg].[Division Name], [t_DivOrg].[Region Name], t_DivOrg.[District], [HGC YTD].[Store Num], [HGC YTD].[Cert Amt], [HGC YTD].[Full Name], [HGC YTD].[Week], [HGC YTD].[Month], [HGC YTD].[Year], [HGC YTD].[Ticket No], [HGC YTD].[ManagerName], [HGC YTD].[Team-Supervisor],"22" AS Type, "CC" AS Org, "1" AS Count

FROM (([HGC YTD] LEFT JOIN [Fiscal Week lookup] ON [HGC YTD].[Order Dt/Time] = [Week lookup].Date) INNER JOIN [MASTER] ON [HGC YTD].[Entered By] = [MASTER].[GC ID]) LEFT JOIN t_DivOrg ON [HGC YTD].[Store Num] = t_DivOrg.Store;

UNION ALL SELECT [t_DivOrg].[Division Name], [t_DivOrg].[Region Name], t_DivOrg.[District], [EGC YTD].[Store Number], [EGC YTD].[Amount], [EGC YTD].[Full Name], [EGC YTD].[Week], [EGC YTD].[Month], [EGC YTD].[Year], [EGC YTD].[Ticket/Order Number], [EGC YTD].[ManagerName], [EGC YTD].[Team-Supervisor], "33" AS Type, "CC" AS Org, "1" AS Count

FROM (([EGC YTD] LEFT JOIN [Fiscal Week lookup] ON [EGC YTD].[Creation Date]=[Week lookup].Date) RIGHT JOIN [MASTER] ON [EGC YTD].[Approved/Declined by]=[MASTER].LAP) LEFT JOIN t_DivOrg ON [EGC YTD].[Store Number] = t_DivOrg.Store

GROUP BY [t_DivOrg].[Division Name], [t_DivOrg].[Region Name], t_DivOrg.[District];

 

[jdraw]

What exactly does NOT WORKING mean? Error messages?
You should adopt a naming convention that does not allow spaces or special characters in names. It will save you a lot of effort and errors in future.

 

[spikepl]

Further to jdraw's comments, using reserved words as date, month, year, week as field names is not a brilliant idea and will likely cause problems. See http://allenbrowne.com/AppIssueBadWord.html

 

[MSAccessRookie]

Hello,
I have the following query and it is not working. Please help!

Thank you in advance!

SELECT [t_DivOrg].[Division Name], [t_DivOrg].[Region Name], t_DivOrg.[District], [HGC YTD].[Store Num], [HGC YTD].[Cert Amt], [HGC YTD].[Full Name], [HGC YTD].[Week], [HGC YTD].[Month], [HGC YTD].[Year], [HGC YTD].[Ticket No], [HGC YTD].[ManagerName], [HGC YTD].[Team-Supervisor],"22" AS Type, "CC" AS Org, "1" AS Count

FROM (([HGC YTD] LEFT JOIN [Fiscal Week lookup] ON [HGC YTD].[Order Dt/Time] = [Week lookup].Date) INNER JOIN [MASTER] ON [HGC YTD].[Entered By] = [MASTER].[GC ID]) LEFT JOIN t_DivOrg ON [HGC YTD].[Store Num] = t_DivOrg.Store; - (Need to remove this)
UNION ALL SELECT [t_DivOrg].[Division Name], [t_DivOrg].[Region Name], t_DivOrg.[District], [EGC YTD].[Store Number], [EGC YTD].[Amount], [EGC YTD].[Full Name], [EGC YTD].[Week], [EGC YTD].[Month], [EGC YTD].[Year], [EGC YTD].[Ticket/Order Number], [EGC YTD].[ManagerName], [EGC YTD].[Team-Supervisor], "33" AS Type, "CC" AS Org, "1" AS Count

FROM (([EGC YTD] LEFT JOIN [Fiscal Week lookup] ON [EGC YTD].[Creation Date]=[Week lookup].Date) RIGHT JOIN [MASTER] ON [EGC YTD].[Approved/Declined by]=[MASTER].LAP) LEFT JOIN t_DivOrg ON [EGC YTD].[Store Number] = t_DivOrg.Store

GROUP BY [t_DivOrg].[Division Name], [t_DivOrg].[Region Name], t_DivOrg.[District];

Assuming there are no typos in the code that has been provided, I have the following observations:

• The Query is a UNION Query in two parts.
• Both parts SELECT 14 distinct Items that appear to be able to contain related information of the same types.
• The FROM Statements appear to be compatible.
• Both Parts end in a Semi-Colon, when only the first part should.
• There are no aggregate items (Count(), SUM, etc), so I do not see the reason for using GROUP BY here. If you are trying to sort the results, ORDER BY will do that, and would apply to the entire UNION as well.