 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / January 2007Percent change
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 >I have a table that has the following fields: > Date [quoted text clipped - 25 lines] > right before the Max Date? > Thanks for any help you can give! 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! > 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. [quoted text clipped - 43 lines] > > right before the Max Date? > > Thanks for any help you can give! 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 > Don't need to sort by last name--these are animals, with only a first > name... [quoted text clipped - 61 lines] >> > right before the Max Date? >> > Thanks for any help you can give! Thanks for your help! With your tips and a little bit of fiddling, it now does what I want it to do. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.