Simple calculated field problem

[Caroline Brown]

Hi,

I am very new to Access and am trying to do something I think should be
reasonably simple, but can't work out the syntax I need.

My database is to track weight loss. I have only one table at the moment
(this will grow but my problem only involves a single table), which is
called 'Measurements'. The table has only 3 fields, 'Date', 'Weight'
and 'Fat%'.

I would like to be able to track weight loss from one day to the next,
so am trying to make a query that shows, for each day, the values in the
Weight and Fat% fields, and a calculated field that shows the previous
day's weight value (which will allow me to show the weight loss from the
previous day).

I can create a calculated field to show the previous day's date, but not
the weight value that is associated with that date.

Being a very raw newbie to Access, I apologise for the how simple this
problem is to solve, and have been trawling through help files and
tutorials online to try to solve the problem myself, but the only
examples seem to be adding or subtracting dates (which I can do), as
opposed to returning values from different fields associated with those
dates.

If anyone can offer any assistance I would be very grateful. Many thanks,

Caroline Brown

 

[John W. Vinson]

Hi,

I am very new to Access and am trying to do something I think should be
reasonably simple, but can't work out the syntax I need.

My database is to track weight loss. I have only one table at the moment
(this will grow but my problem only involves a single table), which is
called 'Measurements'. The table has only 3 fields, 'Date', 'Weight'
and 'Fat%'.

I would like to be able to track weight loss from one day to the next,
so am trying to make a query that shows, for each day, the values in the
Weight and Fat% fields, and a calculated field that shows the previous
day's weight value (which will allow me to show the weight loss from the
previous day).

I can create a calculated field to show the previous day's date, but not
the weight value that is associated with that date.

It's not all that simple, actually! Part of the problem is that - especially
if you're familiar with spreadsheets - there's a perception that "the row
above" is meaningful. It's not! Tables logically have no order; they are
"sets" or "bags" of records, so there is nothing distinctive about the
previous day's data.

What you need is a *self join query*, a fairly advanced query technique:

SELECT A.[Date], A.Weight, A.[Fat %], .[Weight] - [A].[Weight] AS Change
FROM mytable AS A
INNER JOIN mytable AS B
ON B.[Date] = DateAdd("d", -1, [A].[Date]);

Do note that Date is a reserved word and a bad choice of fieldname - Access
*will* get it confused with the builtin Date() function; and it's best not to
include special characters such as blank or % in fieldnames. If you do so,
always use [brackets].

There are other ways - subqueries, the DLookUp function - to get the previous
date's data. I'm also assuming that you never skip a day - if you do you may
need a somewhat more complex expression to search for the most recent date
previous.

John W. Vinson [MVP]

 

[Caroline Brown]

John,

Many thanks for your help and reply - and my apologies for posting on
the 'forms' board by mistake, I understand that it was probably in the
wrong area - I pressed 'send' before the brain was fully engaged, and
tried to repost on the 'queries board' and delete the original.

I have taken your advice and changed some field names, such that the
table 'Measurements' now has the 3 fields 'MeasureDate', 'Weight' and
'FatPC'. And your assumption that a day will never be skipped is correct.

Using your SQL statement as my framework, my query statement now looks like:

SELECT A.[MeasureDate], A.Weight, A.[FatPC], .[Weight] - [A].[Weight]
AS WtChange
FROM Measurements AS A INNER JOIN Measurements AS B ON B.[MeasureDate] =
DateAdd("d", -1, [A].[MeasureDate]);

This seems to work nicely, however when I go into the query's Design
View, I get an error message that reads:

"Microsoft Office Access can't represent the join expression
B.[MeasureDate]=DateAdd("d", -1, [A].[MeasureDate]) in Design View. (The
error message gives the following suggestions

*One or more fields may have been deleted or renamed.
*The name of one or more fields or tables specified in the join
expression may be misspelled.
*The join may use an operator that isn't supported in Design View, such
as < or >.

I can't see how any of the above issues could be causing the error
message, but was wondering if you knew the cause (I am using Access
2003). Pressing the error message's 'OK' button takes me into Design
View where everything looks OK, but I don't know if the problem that is
causing the error message to appear in the first place may cause other
problems later.

My lack of knowledge is also confusing me on why some of the fields in
the statement have square brackets, such as MeasureDate and FatPC,
whereas Weight does not, and why in the last line there is
B.[MeasureDate] (with the B not enclosed by brackets) but
[A].[MeasureDate] (with the A in brackets). Is there a simple
explanation for the difference?

Again many thanks for your help and I hope you are having a great
festive season.

Cheers,

Caroline Brown

Hi,

I am very new to Access and am trying to do something I think should be
reasonably simple, but can't work out the syntax I need.

My database is to track weight loss. I have only one table at the moment
(this will grow but my problem only involves a single table), which is
called 'Measurements'. The table has only 3 fields, 'Date', 'Weight'
and 'Fat%'.

I would like to be able to track weight loss from one day to the next,
so am trying to make a query that shows, for each day, the values in the
Weight and Fat% fields, and a calculated field that shows the previous
day's weight value (which will allow me to show the weight loss from the
previous day).

I can create a calculated field to show the previous day's date, but not
the weight value that is associated with that date.

It's not all that simple, actually! Part of the problem is that - especially
if you're familiar with spreadsheets - there's a perception that "the row
above" is meaningful. It's not! Tables logically have no order; they are
"sets" or "bags" of records, so there is nothing distinctive about the
previous day's data.

What you need is a *self join query*, a fairly advanced query technique:

SELECT A.[Date], A.Weight, A.[Fat %], .[Weight] - [A].[Weight] AS Change
FROM mytable AS A
INNER JOIN mytable AS B
ON B.[Date] = DateAdd("d", -1, [A].[Date]);

Do note that Date is a reserved word and a bad choice of fieldname - Access
*will* get it confused with the builtin Date() function; and it's best not to
include special characters such as blank or % in fieldnames. If you do so,
always use [brackets].

There are other ways - subqueries, the DLookUp function - to get the previous
date's data. I'm also assuming that you never skip a day - if you do you may
need a somewhat more complex expression to search for the most recent date
previous.

John W. Vinson [MVP]

 

[John W. Vinson]

John,

Many thanks for your help and reply - and my apologies for posting on
the 'forms' board by mistake, I understand that it was probably in the
wrong area - I pressed 'send' before the brain was fully engaged, and
tried to repost on the 'queries board' and delete the original.

No problem... it's best to post in the proper newsgroup but most of us who
volunteer here cover several. I actually didn't even notice!

I have taken your advice and changed some field names, such that the
table 'Measurements' now has the 3 fields 'MeasureDate', 'Weight' and
'FatPC'. And your assumption that a day will never be skipped is correct.

Well... that's optimistic actually. What if you have a three day power outage
and can't even boot up the computer, much less enter data?

Using your SQL statement as my framework, my query statement now looks like:

SELECT A.[MeasureDate], A.Weight, A.[FatPC], .[Weight] - [A].[Weight]
AS WtChange
FROM Measurements AS A INNER JOIN Measurements AS B ON B.[MeasureDate] =
DateAdd("d", -1, [A].[MeasureDate]);

You could actually use

ON .[MeasureDate] = [A].[MeasureDate] - 1

and avoid the use of the VBA DateAdd function.

This seems to work nicely, however when I go into the query's Design
View, I get an error message that reads:

"Microsoft Office Access can't represent the join expression
B.[MeasureDate]=DateAdd("d", -1, [A].[MeasureDate]) in Design View. (The
error message gives the following suggestions

*One or more fields may have been deleted or renamed.
*The name of one or more fields or tables specified in the join
expression may be misspelled.
*The join may use an operator that isn't supported in Design View, such
as < or >.

It's just grumbling about the use of the VBA function: you can use it in SQL
view but not in the design grid view. It'll still be there, it'll still work,
but you won't be able to see the DateAdd. The subtraction will probably suffer
the same problem.

I can't see how any of the above issues could be causing the error
message, but was wondering if you knew the cause (I am using Access
2003). Pressing the error message's 'OK' button takes me into Design
View where everything looks OK, but I don't know if the problem that is
causing the error message to appear in the first place may cause other
problems later.

Do you actually see the DateAdd() expression in design view? I didn't try it
but I'd be a bit surprised!

My lack of knowledge is also confusing me on why some of the fields in
the statement have square brackets, such as MeasureDate and FatPC,
whereas Weight does not, and why in the last line there is
B.[MeasureDate] (with the B not enclosed by brackets) but
[A].[MeasureDate] (with the A in brackets). Is there a simple
explanation for the difference?

Brackets are *ALWAYS* allowed. If the reference is unambiguous and doesn't
contain any blanks or special characters the brackets can be omitted (just to
save typing), but it never hurts to include them.

Again many thanks for your help and I hope you are having a great
festive season.

A Merry Christmas to you!

John W. Vinson [MVP]