Trying to calculate data from two tables (1 Viewer)

[dmorgan20]

I am using query builder to pull some figures.

The following shows the data I need:

SELECT [_tbl_Structure].[User Name], tbl_Genesys_Daily.Field7, tbl_Genesys_Daily.Field8, Count(tbl_plans_yesterday.[User ID]) AS [CountOfUser ID], [field7]+[field8] AS calls
FROM (_tbl_Structure LEFT JOIN tbl_Genesys_Daily ON [_tbl_Structure].[User ID] = tbl_Genesys_Daily.Field5) LEFT JOIN tbl_plans_yesterday ON [_tbl_Structure].[User ID] = tbl_plans_yesterday.[User ID]
GROUP BY [_tbl_Structure].[User Name], tbl_Genesys_Daily.Field7, tbl_Genesys_Daily.Field8, [field7]+[field8];

What I am trying to then do it add more to the builder to divide one by the other and come up with the below; any help on why this is not working is appreciated.

SELECT [_tbl_Structure].[User Name], tbl_Genesys_Daily.Field7, tbl_Genesys_Daily.Field8, Count(tbl_plans_yesterday.[User ID]) AS [CountOfUser ID], [field7]+[field8] AS calls, Sum(Nz([tbl_plans_yesterday].[User ID]/[Calls],0)) AS PlanEff
FROM (_tbl_Structure LEFT JOIN tbl_Genesys_Daily ON [_tbl_Structure].[User ID] = tbl_Genesys_Daily.Field5) LEFT JOIN tbl_plans_yesterday ON [_tbl_Structure].[User ID] = tbl_plans_yesterday.[User ID]
GROUP BY [_tbl_Structure].[User Name], tbl_Genesys_Daily.Field7, tbl_Genesys_Daily.Field8, [field7]+[field8];

 

[Mark_]

What error are you getting?
Which field(s) are specifically giving you an issue?

 

[plog]

First--seriously your fields are named [Field 7] and [Field 8]? That's doing anyone who has to come after you, as well as your future self, a huge disservice. Give data appropriate names.

Second, and very similar--do not use non-alphanumeric characters in names. It's just going to make coding and querying that much harder to debug. Remove the spaces from your field names and do not start names with underscores.

Third, and to your problem---you can't reference a calculated field's name in the same query it is calculated. Your query has no idea what the field [calls] is because it doesn't exist in the underlying datasource.

So, either save the query that works, then use it as the data source in another query that does the further calculation or do algebraic replacement. That is, instead of [calls] in the subsequent calculation, use what [calls] is comprised of:

a= b + c
d = a + f
d = (b+ c) + f