Update Query

[tjnichols]

Good evening. Alright now I feel like something off the Adams Family but down to business...

I need to create an update query that updates two fields. One is a fiscal year (FiscalYear) field. The fiscal year runs from July 1st through June 30th. The calendar year (CalendarYear) is your typical year. What I need is an update query that updates both fields based on the ClassDate.

I have attached the database so you can see what I want. The query name that I have started is UpdateFiscalCalendarYears.

 

[Galaxiom]

Since you are already storing a date that can be used to determine CalendarYear and FiscalYear then you would need a good reason to store these values as well since it would breach normalization. Usually these values would be calculated from the date field as required.

You also have normlization errors where you are storing both the age and birthdate. The age should be calculated every time it is required.

You have several fields that should probably be abstracted into related tables.

Several bad field names including the reserved words Project, Date and Memo. I would also recommend you drop spaces and special characters from all names.

 

[tjnichols]

I need to store these values as they will be used in reports and other forms. Can you please help me with my problem versus pointing out issues you think I should fix?

 

[Galaxiom]

Needing to use them in forms and reports does not constitute a reason to store calculated values.

Can you please listen to the advice offered rather than continuing to do things the wrong way?

 

[Galaxiom]

Nice. The OP decided to rebuk my advice on another thread.

Well Jon - I love this site. It has been extremely helpful to me with my Access endeavors. Well mostly - I wish you could block someone from even being able to reply to my posts. Galaxiom isn't helpful and just slams you for not 'doing it the right way'. I understand there are proper ways to do things and while I am willing to listen to them - please help me too rather than educate me.

Prepared to listen. I don't think so. Perhaps they will listen to someone else but I doubt it.

Funnily enough they gave thanks to RuralGuy for the same advice in this thread.

Age is really dynamic and should be calculated every time you need it. It therefore should not be a field in a table but a calculation in a query.

Unfortunately they don't seem to be taking any notice of that advice and are now becoming openly hostile to it. With this kind of ignorance I don't see much point helping them any further since they will never become an accomplished developer.

 

[spikepl]

@tjnichols

The answers provided in this forum are based not on ESP but on what OP writes. If OP proposes to solve a problem by creating new problems (which breach of normalization normally spawn), then the advice is normally to do something else. Again, based on what is written and not based on what OP knows, thinks, wishes or other circumstances not put in writing.

Now you can behave like a moron and get p....off for getting good advice BASED ON THE WRITTEN INFO PROVIDED BY YOU, or you can explain why the advice given does not apply in your circumstances.