Percent change

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table that has the following fields:
Date
Name
Weight
Unit (i.e. g, kg, lbs etc)

I would like a query that returns the percentage change of the weight. For
instance:
10/10/06 167 g
10/17/06 175 g
% change=+4.7%

Or:

10/1/06 135 g
10/3/06 130g
% change= -3.7%

The dates are not necessarily consecutive (weights are not entered daily).
The percentage calculation should be done between matching unit types (I
think the Group By field takes care of that).
Lots of different names (again, I think the Group By field takes care of that)

I know it probably has to do with a subquery, but I can't figure out how to
define the next to last date. Max Date I've got. How do I define the date
right before the Max Date?
Thanks for any help you can give!
 
Access treats "Date" and "Name" as reserved words, so what you get may not
be what you expect. First, change the title of those fields.

Next, "Name" implies that you are putting the entire name in the field. How
do you plan to sort by Last Name? One of the first rules of data
normalization is to put one fact in one field -- if you have FirstName and
LastName, you have two (and need two fields).

For each "Name", you'll have a maximum date (that part it sounds like you
already have). If you then create another query to find the maximum date
where it is NOT the maximum date from the first query, you'll have the
"next" most recent date.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Don't need to sort by last name--these are animals, with only a first name...

Could I get a little more detail on how to "create another query to find the
maximum date where it is NOT the maximum date from the first query, you'll
have the "next" most recent date."?
Thanks!
 
Whether you have no firstname or lastname, naming your field "name" will
confuse Access.

The general concept is that you will first get a list of Most-Recent values
(Max (YourDate)).

Then you will find all dates not in your list of most recent (the query
wizard can help you build an "unmatched" query).

Then you will find the maximum value of THOSE.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Thanks for your help! With your tips and a little bit of fiddling, it now
does what I want it to do.
 
Back
Top