Errors when using 'fields' from one query in another query. (1 Viewer)

[wmphoto]

All the following queries are based on a table which contains commissions for my business.

The first one adds together fields containing charges, expenses, etc to create calculated fields containing the total commission fee and the deposit due.

The second one is a crosstab query (though the same error appears if in select mode) which shows total fees by commission type and month. This uses the calculated fields from the first query.

When I try to open the first query, I get a box saying 'enter parameter value' for those calculated fields based only on real fields from the underlying table (but not for calculated fields with other calculated fields within their calculations). If I don't enter any value and click OK, it still presents the query with the correct values in the calculated fields.

When I try to open the second query it says 'The Microsoft Access database engine does not recognise '[TEE_Travel]' as a valid field name or expression.' This is the name of the first of those fields mentioned above which cause the enter parameter value box to appear. The query then doesn't open, and the help button doesn't help much, it just suggests that I have mistyped it, or something about the query parameters dialogue box I don't understand.

Any help would be appreciated.

 

[vbaInet]

It would be useful to post the SQL statements of both queries enclosing them in Code tags (http://www.access-programmers.co.uk/forums/showthread.php?p=1009015#post1009015)

 

[wmphoto]

It would be useful to post the SQL statements of both queries enclosing them in Code tags (http://www.access-programmers.co.uk/forums/showthread.php?p=1009015#post1009015)

SELECT Commissions.[Agreement Form], Commissions.[Advertiser Name], Clients.[Business Name], Commissions.[Agreement Date], Commissions.[Commission ID], [Agreement Types].[Agreement Text], Commissions.[Other Agreements], Commissions.[Base Licences (Standard)], Commissions.[Additional Licences (Standard)], [Base Licences (Standard)]+[Additional Licences (Standard)] AS [Total Licences (Standard)], Commissions.[Base Licences (Commercial)], Commissions.[Additional Licences (Commercial)], [Base Licences (Commercial)]+[Additional Licences (Commercial)] AS [Total Licences (Commercial)], Commissions.[Base Licences (Media)], Commissions.[Additional Licences (Media)], [Base Licences (Media)]+[Additional Licences (Media)] AS [Total Licences (Media)], Commissions.[Brief - Detail], Commissions.[Liabilities - Model Releases], Commissions.[Liabilities - Property Releases], Commissions.[LIabilities - Other], Commissions.[Tariff Name], Commissions.[Base Rate], Commissions.Discount, Commissions.Surcharge, Commissions.[Estimated Rush Charges], Commissions.[Additional Shooting Hours Price], Commissions.[Additional Consultation Hours Price], Commissions.[Additional Standard Price], Commissions.[Additional Commercial Price], Commissions.[Additional Media Price], [Base Rate]+[Discount]+[Surcharge]+[Estimated Rush Charges]+[Additional Shooting Hours Price]+[Additional Consultation Hours Price]+[Additional Standard Price]+[Additional Commercial Price]+[Additional Media Price] AS [Commission Fee], Commissions.[Commission Deposit], Commissions.[Mileage Credit], Commissions.[Mileage Rate per Mile], Sum(Appointments.[Est Mileage]) AS [Est Mileage], [Est Mileage]*[Mileage Rate per Mile] AS TEE_Travel, IIf([TEE_Travel]-[Mileage Credit]>0,[TEE_Travel]-[Mileage Credit],0) AS TEE_Chargeable_Travel, Sum(Appointments.[Est Expenses - Event Fees]) AS [Est Expenses - Event Fees], Sum(Appointments.[Est Expenses - Equipment]) AS [Est Expenses - Equipment], Sum(Appointments.[Est Expenses - Hire of Models]) AS [Est Expenses - Hire of Models], Sum(Appointments.[Est Expenses - Hire of Studio / Space]) AS [Est Expenses - Hire of Studio / Space], Sum(Appointments.[Est Expenses - Set / Props / Make-up]) AS [Est Expenses - Set / Props / Make-up], [Est Expenses - Event Fees]+[Est Expenses - Equipment]+[Est Expenses - Hire of Models]+[Est Expenses - Hire of Studio / Space]+[Est Expenses - Set / Props / Make-up] AS TEE_Photography, Sum(Appointments.[Est Expenses - Insurance]) AS [Est Expenses - Insurance], Sum(Appointments.[Est Expenses - Other]) AS [Est Expenses - Other], [Est Expenses - Insurance]+[Est Expenses - Other] AS TEE_Miscellaneous, [Commission Deposit]+[TEE_Photography]+[TEE_Miscellaneous]+[TEE_Chargeable_Travel] AS [Total Deposit Due], Commissions.[Delivery Date]
FROM [Agreement Types] INNER JOIN ((Clients RIGHT JOIN Commissions ON Clients.[Client ID] = Commissions.Client) LEFT JOIN Appointments ON Commissions.[Commission ID] = Appointments.Commission) ON [Agreement Types].[Agreement Type ID] = Commissions.[Agreement Type]
GROUP BY Commissions.[Agreement Form], Commissions.[Advertiser Name], Clients.[Business Name], Commissions.[Agreement Date], Commissions.[Commission ID], [Agreement Types].[Agreement Text], Commissions.[Other Agreements], Commissions.[Base Licences (Standard)], Commissions.[Additional Licences (Standard)], [Base Licences (Standard)]+[Additional Licences (Standard)], Commissions.[Base Licences (Commercial)], Commissions.[Additional Licences (Commercial)], [Base Licences (Commercial)]+[Additional Licences (Commercial)], Commissions.[Base Licences (Media)], Commissions.[Additional Licences (Media)], [Base Licences (Media)]+[Additional Licences (Media)], Commissions.[Brief - Detail], Commissions.[Liabilities - Model Releases], Commissions.[Liabilities - Property Releases], Commissions.[LIabilities - Other], Commissions.[Tariff Name], Commissions.[Base Rate], Commissions.Discount, Commissions.Surcharge, Commissions.[Estimated Rush Charges], Commissions.[Additional Shooting Hours Price], Commissions.[Additional Consultation Hours Price], Commissions.[Additional Standard Price], Commissions.[Additional Commercial Price], Commissions.[Additional Media Price], [Base Rate]+[Discount]+[Surcharge]+[Estimated Rush Charges]+[Additional Shooting Hours Price]+[Additional Consultation Hours Price]+[Additional Standard Price]+[Additional Commercial Price]+[Additional Media Price], Commissions.[Commission Deposit], Commissions.[Mileage Credit], Commissions.[Mileage Rate per Mile], [Est Mileage]*[Mileage Rate per Mile], IIf([TEE_Travel]-[Mileage Credit]>0,[TEE_Travel]-[Mileage Credit],0), [Est Expenses - Event Fees]+[Est Expenses - Equipment]+[Est Expenses - Hire of Models]+[Est Expenses - Hire of Studio / Space]+[Est Expenses - Set / Props / Make-up], [Est Expenses - Insurance]+[Est Expenses - Other], [Commission Deposit]+[TEE_Photography]+[TEE_Miscellaneous]+[TEE_Chargeable_Travel], Commissions.[Delivery Date]
HAVING (((Commissions.[Agreement Form])="CA01"));

Thanks for your response. Here are the queries in the same order as in the text.

TRANSFORM Sum([CA01 Query].[Commission Fee]) AS [SumOfCommission Fee]
SELECT (Year([Success Date])-2011)*12+Month([Success Date])-5 AS [Serial Month]
FROM Commissions INNER JOIN [CA01 Query] ON Commissions.[Commission ID] = [CA01 Query].[Commission ID]
WHERE (((Commissions.Status)="Enq. Successful" Or (Commissions.Status)="Active"))
GROUP BY Commissions.[Commission ID], Commissions.Status, Commissions.[Success Date], (Year([Success Date])-2011)*12+Month([Success Date])-5
PIVOT Commissions.[Base Rate Name];

 

[vbaInet]

What are the names of the queries?

 

[wmphoto]

What are the names of the queries?

The first one is CA01 query
The second is Sales Figures - successful stage

 

[vbaInet]

1. In your first query, remove all the fields that make reference to [TEE_Travel], but leave the actual [TEE_Travel] in there. That is leave this [Est Mileage]*[Mileage Rate per Mile] AS TEE_Travel but remove all those that use [TEE_Travel] as part of their calculation. Save and close this query

2. Now, create a new query and base it on the CA01 query you've just changed and include all the fields from it. Include the removed calculated fields from step 1 in this query. Save and close it.

3. Finally, base your crosstab query on the query in step 2.

 

[wmphoto]

Thanks, so what your saying in general is avoid using fields calculated in query X as part of further calculated fields in the same query. If you need to do multiple stages of calculation, use separate queries for each stage?

 

[vbaInet]

Especially for Crosstab queries. There are some SELECT queries where it will error too, but if it doesn't error keep it all in one query.

In essence, don't use alias fields for further calculation in the same query. Perform the calculation in a separate query. So now the alias will no longer be seen as an alias but a field.

 

[wmphoto]

Thanks a lot, I did a fair bit of tidying up as I was trying to do too much from that CA01 query anyway, but I've made sure no fields are created and used in expressions within the same query and the crosstab works fine now. This would be a good FAQ point.

 

[vbaInet]

Good to hear!

I may consider making an FAQ of this.