 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / January 2008Difference from previous week - report
Hi, I have a report grouped into weeks, and a total amount. I would also like to show the difference between that day's amount and the amount from 7 days ago, basically to see how much the total has increased or decreased. I've tried making the control of a textbox [total] - dlookup("total","table","total - 7") but all I get then is '0'. Thanks in advance! >Hi, >I have a report grouped into weeks, and a total amount. [quoted text clipped - 3 lines] >dlookup("total","table","total - 7") but all I get then is '0'. >Thanks in advance. You're misinterpreting how DLookup works. The first argument is the field that you want to look up (assuming that total is the name of the field); the second argument is the name of the table (I hope it's not "table"!!!); but the third argument is a valid SQL WHERE clause without the word WHERE, a criterion to determine which record to look up. total - 7 will be some number - if the total for the current record is 319 it will be the number 312. That's not going to help you to find the record!!! What's the structure of your table? What field in the table identifies the date of the amount? John W. Vinson [MVP] Hi, thanks for replying. I have a table called TABLEFB, which has fields DATEFB, AMOUNT1(-5) and TOTAL. My report has DATEFB on the left, followed by each of the amounts and TOTAL at the end, and I'd like to have difference on the very right. These are grouped into weeks. At the group footer there's also a subtotal for each column (total for AMOUNT1, total for AMOUNT2 etc. using =sum[amountx] as control source). It's just getting the differece between TOTAL on say 03/01/2008 and 27/12/2008 that I can't find a way to do. Thanks for the help. > >Hi, > >I have a report grouped into weeks, and a total amount. [quoted text clipped - 16 lines] > > John W. Vinson [MVP] >Hi, thanks for replying. >I have a table called TABLEFB, which has fields DATEFB, AMOUNT1(-5) and TOTAL. Then your table is designed incorrectly. You're using spreadsheet logic, and Access is NOT a spreadsheet! "Fields are expensive, records are cheap". If you have multiple amounts for a given DATEFB then you should have *TWO TABLES* in a one to many relationship: one with DATEFB as the primary key, together with any other information that you need about that DATEFB; and a second table with one record - or five records - or seven records - or however many records there need to be, with a single AMOUNT field with one amount per record. Also, the TOTAL field *should simply not exist* in your table. 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. >My report has DATEFB on the left, followed by each of the amounts and TOTAL >at the end, and I'd like to have difference on the very right. These are [quoted text clipped - 3 lines] >It's just getting the differece between TOTAL on say 03/01/2008 and >27/12/2008 that I can't find a way to do. That's because your data structure is wrong. It's really easy with a simple Totals query if you use the properly normalized design above. John W. Vinson [MVP] I'm not trying to do a spreadsheet and can't see the need for two tables. I will get rid of the TOTAL field and replace it with a calculation. I had to use a TOTAL field for use with the DLookup I thought I needed. I still don't know how to work out the difference which is the problem for me. Thanks for the reply. > >Hi, thanks for replying. > >I have a table called TABLEFB, which has fields DATEFB, AMOUNT1(-5) and TOTAL. [quoted text clipped - 32 lines] > > John W. Vinson [MVP] >I'm not trying to do a spreadsheet and can't see the need for two tables. >I will get rid of the TOTAL field and replace it with a calculation. I had >to use a TOTAL field for use with the DLookup I thought I needed. >I still don't know how to work out the difference which is the problem for me. >Thanks for the reply. What are the Amount1-Amount5 fields? Will there EVER - (and never is a very long time) - be an Amount6? If there is, what will you do: redesign your tables, your forms, your queries, and your reports to accomodate it? That to the side... what kind of data is in your date field? A Date/Time? What difference do you want to calculate? Please give an example of the actual data in a couple of records. This can be done with a query (probably a self join) but unless I can tell what the data looks like it's hard to be specific. John W. Vinson [MVP] I don't think it's likely there will be an amount 6, at the time being only about 4 will be used so there are 2 spare just in case anyway. The DATEFB field is Short Date. An example of a record is as follows: DATE FB AMOUNT1 AMOUNT2 AMOUNT3 AMOUNT4 AMOUNT5 TOTAL 10/01/2008 1200 1500 2000 1204 0 (4104) () - ADDED FROM AMOUNT VALUES If I then say had a record a week later with a total of 6234 I'd want my report to say 2130 at the end of the row for that day because that is the difference between the 17/01/08's total and the 10ths total. Thanks for the help. > >I'm not trying to do a spreadsheet and can't see the need for two tables. > >I will get rid of the TOTAL field and replace it with a calculation. I had [quoted text clipped - 12 lines] > > John W. Vinson [MVP] >I don't think it's likely there will be an amount 6, at the time being only >about 4 will be used so there are 2 spare just in case anyway. In that case, your amount 6 will be needed in a month or so... or more likely the next time that you're at the busiest and don't have time to restructure your database to accommodate it. Seriously - *THIS DESIGN IS WRONG*. Your problem with totals is due to the flaw in your design. The many NULL Amounts are due to the flaw in your design. The problem with searching is due to the flaw in your design. You have a one to many relationship. Work *WITH* Access instead of struggling against it, and pull the amounts out into a related table. Seriously... this will make your job easier, not harder. If you would like help migrating your data to the new structure post back, it's not at all hard. >The DATEFB field is Short Date. >An example of a record is as follows: [quoted text clipped - 7 lines] >report to say 2130 at the end of the row for that day because that is the >difference between the 17/01/08's total and the 10ths total. SELECT A.DATEFB, B.DATEFB, (NZ(A.Amount1)+NZ(A.Amount2) + NZ(A.Amount3) + NZ(A.Amount4) + NZ(A.Amount5)) AS ThisWeekAmount, (NZ(B.Amount1)+NZ(B.Amount2) + NZ(B.Amount3) + NZ(B.Amount4) + NZ(B.Amount5)) AS LastWeekAmount, (NZ(A.Amount1)+NZ(A.Amount2) + NZ(A.Amount3) + NZ(A.Amount4) + NZ(A.Amount5))- (NZ(B.Amount1)+NZ(B.Amount2) + NZ(B.Amount3) + NZ(B.Amount4) + NZ(B.Amount5)) AS Difference FROM yourtable AS A INNER JOIN yourtable AS B ON B.DateFB = A.DateFB-7 WHERE A.DateFB = [Enter date:]; THis will prompt for a date (you can use Date() instead of [Enter date:] to always get today's date; total the amounts for that day - the A fields; total the amounts for the date seven days prior - the B fields; and give you the difference. This assumes that there will always be one DateFB value per week, never missing a holiday or weather emergency or other missing data. With the normalized design the query will be considerably simpler. Your choice! John W. Vinson [MVP] I won't need an amount 6 in a month, not for the forseeable future to be honest > >I don't think it's likely there will be an amount 6, at the time being only > >about 4 will be used so there are 2 spare just in case anyway. [quoted text clipped - 44 lines] > > John W. Vinson [MVP] > I won't need an amount 6 in a month, not for the forseeable future to be honest >I don't know hoe the structure affects the retrieving of the previous week's total to work out a difference [quoted text clipped - 47 lines] > > > > John W. Vinson [MVP] >I won't need an amount 6 in a month, not for the forseeable future to be honest <shrug> Ok. I tried. Does the query work? John W. Vinson [MVP] I've finally got it to work. There was some kind of problem with dlookup, so I did it stage by stage. I had a hidden text box that works out the date 7 days ago, then used THAT to do the dlookup. Works fine now. The amounts are grades of fuel, so it is unlikely that I'd need to add them, and I find it a bit confusing linking tables and such. Thanks for your help. > >I won't need an amount 6 in a month, not for the forseeable future to be honest > > <shrug> Ok. I tried. Does the query work? > > John W. Vinson [MVP] > I had a hidden text box that works out the date 7 >days ago Instead of the very simple DateAdd("d", -7, Date()) in the DLookUp criteria? Ok. John W. Vinson [MVP] That's what I tried, but it just kept saying data type mismatch. So I put the datediff in the text box (which worked) and then refered to that in the dlookup. Don't know why it's being so funny about it. > I won't need an amount 6 in a month, not for the forseeable future to be honest > [quoted text clipped - 46 lines] > > > > John W. Vinson [MVP] |
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
|
©2009 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.