Query Calculation Doesn't Get Put Into Table

  • Thread starter Thread starter Doug MacDonald
  • Start date Start date
D

Doug MacDonald

I have a table named tblPerfArtsCamps as follows:

PerfArtsCampsID AutoNumber
CampDate Date/time
Season Text

*********
I have a query based on tblPerfArtsCamps as follows:

Field: PerfArtsCampsID CampDate Season:
(Conditional statement - see below)
Table: tblPerfArtsCamps tblPerfArtsCamps

********
Conditional Statement:
Season: IIf(DatePart("m",[CampDate])>=10 And DatePart("m",
[CampDate])<=12,CStr(DatePart("yyyy",[CampDate])) & " - "
& CStr(DatePart("yyyy",[CampDate])+1),CStr(DatePart("yyyy",
[CampDate])-1) & " - " & CStr(DatePart("yyyy",[CampDate])))

********
Based on the CampDate I want to calculate the Season which
runs from October to September.

CampDate Season
e.g 9/30/00 -> 1999 - 2000
10/1/99 -> 1999 - 2000
10/1/00 -> 2000 - 2001
etc.

The query generates the season properly but the result
does not get put into table tblPerfArtsCamps.
If I put tblPerfArtsCamps in the Table: cell for Seasons:
I get an error saying there is an extra ")"

How do I get the Season: result to go into the Season
column in the table tblPerfArtsCamps?

I have a .mdb file set up with just the table and the
query. If you would like me to send it to you please
provide an email address.

Thanks

Doug
 
Based on the CampDate I want to calculate the Season which
runs from October to September.

CampDate Season
e.g 9/30/00 -> 1999 - 2000
10/1/99 -> 1999 - 2000
10/1/00 -> 2000 - 2001
etc.

The query generates the season properly but the result
does not get put into table tblPerfArtsCamps.
Click to expand...

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.


John W. Vinson[MVP]
(no longer chatting for now)
 
John,

My reason for wanting to put this in the table is so that
I can later search by Season for reports and counting etc.

Doug
 
John,

My reason for wanting to put this in the table is so that
I can later search by Season for reports and counting etc.
Click to expand...

Fine. Create a Query calculating the season, and use a criterion on
that calculated field. If you assume that you must store a field in
the table in order to search on it, your assumption is incorrect.

John W. Vinson[MVP]
(no longer chatting for now)
 
Back
Top