My table contains various "Date" informations like "OrderDate", "ProductionDate", "ControlDate", "SellDate". In the query, I want to have a field "Status" which says something like "Orderd", "Produced", "Controlled", "Sold". I can derive the status information from the various dates (e.g. if there's a ProductionDate but no ControlDate set, Status is "Produced"). The number of date fields and stati are guaranteed to never change. The date changes are very infrequent, whereas the queries occur all the time. The query contains not just one such derived field but about five.
How big is the performance hit for calculating Status with a VBA routine in the query, compared to creating an extra Status field in the table which is set every time a date changes?
Or is this design basically flawed?
As far as performance hit, you're gonna see a lot smaller hit if you update a single record in a table than if you try to create that field "on the fly" in a query. The question is; how much data are we talking about and how long is it taking to spit out the report? I'd go 50/50 on this; I don't think either method is wrong or worse but if you're looking to save as much time as possible then I'd add a field to the table and update that field whenever one of the dates is input/updated. More coding on the back end, but it'll take milliseconds to run the updates versus deciseconds (or possibly seconds) to create the field in a query.