Run-time error 3122 (1 Viewer)

[Ashfaque]

Hi,

Can you correct me in this code?

Set rst = CurrentDb.OpenRecordset("SELECT CDept,CNationalityExp,ProffGroupName, ProffGroupCode, CLastWorkingDate, CCrNumber, Count(T_JobOffer.LetterRefNumber) AS CountOfLetterRefNumber FROM T_JobOffer" & _
" GROUP BY T_JobOffer.CDept, T_JobOffer.CNationalityExp, T_JobOffer.ProffGroupName, T_JobOffer.ProffGroupCode, T_JobOffer.CLastWorkingDate, T_JobOffer.CCrNumber, T_JobOffer.CDept" & _
" HAVING (IsNull([ProffGroupName])=False) AND ((ProffGroupCode)=[Forms]![F_SaudizationPercent]![TxtGrpCode]) AND ((CNo)>0) AND (Isnull([CLastWorkingDate])=True) AND ((CCrNumber)=2051223412) AND ((CDept<>'Operations') And (CDept<>'Top Management'))")

I m counting records of simillar proffgroupname and later to update records in a table.

Thanks in advance..

 

[June7]

Update records with what - aggregate calc? Probably should not do that. Do aggregate calc when needed.

IsNull([ProffGroupName]) is a calculation that is not in the SELECT clause - same for all the expressions using IsNull(). Some or all of the HAVING criteria possibly should be in WHERE clause.

 

[arnelgp]

can you design it first in Query design.

 

[Ashfaque]

Thanks Arnel

This is my basic query which is working fine

SELECT DISTINCTROW T_JobOffer.CDept, T_JobOffer.CNationalityExp, Count(*) AS [Count Of T_JobOffer], T_JobOffer.ProffGroupName, T_JobOffer.ProffGroupCode
FROM T_JobOffer
GROUP BY T_JobOffer.CDept, T_JobOffer.CNationalityExp, T_JobOffer.ProffGroupName, T_JobOffer.ProffGroupCode, T_JobOffer.CNo, T_JobOffer.CLastWorkingDate, T_JobOffer.CCrNumber, T_JobOffer.CDept
HAVING ((Not (T_JobOffer.ProffGroupName) Is Null) AND ((T_JobOffer.ProffGroupCode)=[Forms]![F_SaudizationPercent]![TxtGrpCode]) AND ((T_JobOffer.CNo)>0) AND ((T_JobOffer.CLastWorkingDate) Is Null) AND ((T_JobOffer.CCrNumber)=2051223412) AND ((T_JobOffer.CDept)<>"Operations" And (T_JobOffer.CDept)<>"Top Management"));

And this is my second query based on first query to sum of the records as various conditions.

SELECT DISTINCTROW B_Copy.CDept, B_Copy.CNationalityExp, B_Copy.ProffGroupName, Nz([B_Copy].[ProffGroupCode],0) AS ProffGroupCode, Sum(B_Copy.[Count Of T_JobOffer]) AS [Sum Of Count Of T_JobOffer]
FROM B_Copy
GROUP BY B_Copy.CDept, B_Copy.CNationalityExp, B_Copy.ProffGroupName, B_Copy.ProffGroupCode;

Direct queries are working fine and my plan is to store result of my second query in T_Grpah tbl and use for his graph as his record source.

Thanks,

 

[The_Doc_Man]

What is going on with that error is that in aggregate queries, you are suppressing records according to their membership in a data group. You show sums, counts, averages, min, max, etc. - but not the individual records. The error is telling you that you are trying to do too many things - some of them contradictory - in a single query.

When Access wants to display that query it wants to show the aggregated fields. So if you are grouping on X, you can tell the query builder to show you X because it is in a GROUP BY clause. All the X's in the group are the same X value. You can show the SUM of Y even though not grouping on Y because you have an aggregate function to take the sum of all Y values for the same X. You can name (but not necessarily display) fields that are part of a WHERE or ORDER BY clause in many cases.

BUT ... if you have a field Z that is not part of a GROUP, not included in an aggregate, not part of WHERE, ... then the question is "where do I display it?" You see, it isn't part of the grouping and not aggregated, but you have to suppress records in the group. I.e., Z can have multiple values within a group, but the GROUP BY is compressing several records into one record. There is no place to put multiple Z values in the single aggregate record. This is what the error is telling you.

The usual way to handle this is to separate the aggregation query from any detail query, then rejoin everything in a second layer query that does a JOIN with the aggregate query, perhaps joined via the field being grouped. The values coming from the aggregation query would be duplicated but you could at least see the details and the aggregates at the same time.

 

[bastanu]

Could you please try:

Set rst = CurrentDb.OpenRecordset("SELECT CDept,CNationalityExp,ProffGroupName, ProffGroupCode, CLastWorkingDate, CCrNumber, Count(T_JobOffer.LetterRefNumber) AS CountOfLetterRefNumber FROM T_JobOffer" & _
" GROUP BY T_JobOffer.CDept, T_JobOffer.CNationalityExp, T_JobOffer.ProffGroupName, T_JobOffer.ProffGroupCode, T_JobOffer.CLastWorkingDate, T_JobOffer.CCrNumber, T_JobOffer.CDept" & _
" WHERE [ProffGroupName] Is Not Null AND [ProffGroupCode]=[Forms]![F_SaudizationPercent]![TxtGrpCode] AND CNo>0 AND [CLastWorkingDate] Is Null AND CCrNumber = 2051223412 AND CDept<>'Operations' And CDept<>'Top Management'")

 

[Ashfaque]

Could you please try:

Set rst = CurrentDb.OpenRecordset("SELECT CDept,CNationalityExp,ProffGroupName, ProffGroupCode, CLastWorkingDate, CCrNumber, Count(T_JobOffer.LetterRefNumber) AS CountOfLetterRefNumber FROM T_JobOffer" & _
" GROUP BY T_JobOffer.CDept, T_JobOffer.CNationalityExp, T_JobOffer.ProffGroupName, T_JobOffer.ProffGroupCode, T_JobOffer.CLastWorkingDate, T_JobOffer.CCrNumber, T_JobOffer.CDept" & _
" WHERE [ProffGroupName] Is Not Null AND [ProffGroupCode]=[Forms]![F_SaudizationPercent]![TxtGrpCode] AND CNo>0 AND [CLastWorkingDate] Is Null AND CCrNumber = 2051223412 AND CDept<>'Operations' And CDept<>'Top Management'")

Thanks Bastanu,

Here is the error attached

 

[The_Doc_Man]

If you look at strict SELECT syntax, it is possible that you are tripping over clause order requirements. I think SQL requires GROUP BY to follow, not precede, a WHERE clause, and the expression broke at the last field in your GROUP BY just before the WHERE clause.

SELECT Statement - Microsoft Support

The SELECT statement Instructs the Microsoft Access database engine to return information from the database as a set of records.

support.microsoft.com