Difference from previous week - report

[s4]

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!

 

[John W. Vinson]

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.

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]

 

[s4]

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.
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.

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]

 

[John W. Vinson]

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
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.

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]

 

[s4]

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.

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
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.

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]

 

[John W. Vinson]

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]

 

[s4]

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.

 

[John W. Vinson]

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:

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.

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]

 

[s4]

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.

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:

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.

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]

 

[s4]

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

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:

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.

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]

 

[John W. Vinson]

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]

 

[s4]

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.

 

[John W. Vinson]

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]

 

[s4]

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

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:

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.

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]