Can I do this in a query?

  • Thread starter Thread starter Rebecca
  • Start date Start date
R

Rebecca

I have a table with a column containing a date.
I need to read this table and insert a new column
for 'weekending date'. This must be calculated from the
date column based on a Sun-Sat week. can this be done in a
query? If so, how?
Or do I need to write some VBA code to loop thru the table
and do this?
After this is done, I then need to create a crosstab query
based on the weekending date.

I have no information on Access other than the Help
screens and it takes forever to learn anything from them.
 
You can already derive the WeekEndingDate in the Query from your DateField.

In your SELECT Query, create a Calculated Column with the following
expression:

WeekEndingDate: DateAdd("d", (7-Weekday(DateField)) MOD 7, DateField)

and this Column will show the week-ending date corresponding to the value in
your DateField.

It seems to me that you want to store the WeekEndingDate in the Table. My
recommendation (backed up by the Relational Database Design Theory) in NOT
to store it and simply work it out whenever you need it. Storing calculated
values simply slows your database down, waste storage space and *creates
inconsistencies* in your database later.

Another the point is that if you want to use Access seriously, you need to
get a few Access books. It is pretty hard to use Access without some
theoretical understanding and plenty of examples.
 
Thanks for the advice.
My company wont pay for computer books (will any company?)
And at $40 each I wont be buying any myself.
 
Don't know in your area but in my are area, "investing in
people" (i.e. employees) is a buzz word for some time now
and there is no problem getting companies to pay for books
that relate to the employee's functions. Some companies
send people to training courses that cost AUS$2,500.00 per
week (+ the employee salary).

OTOH, it is an investment in yourself and think of
potential increase in salary with the current employer and
your job prospect in the future.

Personally, I probably spend over AUS$2,000.00 a year on
computer books (average AUS$100.00 a book).

HTH
Van T. Dinh
MVP (Access)
 
Back
Top