HELP! DLookup and my report wont work?? 1

[tonywilliams]

I have posted a number of posts in the Access newsgroups concerning my
problem with DLookup. I have had a number of the experts with helpful
suggestions but I still can't get it to work! This is what I am trying to
do:

I have a table called tblmaintabs which holds year to date data for a
number of companies which they supply on a quarterly basis.
The field txtDomfacsoleqtr is one of the fields that holds part of this
data. The txtmonthlabel is also a field in my table and is the date of the
current
quarter, eg March 2004
The report I'm trying to produce is based on my table and a query which
calculates the previous quater date and should do two things.
The query is linked to my table on txtmonthlabel and calculates the previous
quarter using this expression.
DateAdd("m",-3,[txtmonthlabel]) and is txtqtr1 in my Dlookup formula below.

Firstly I want to compare the data for the current YTD with the previous
quarter's YTD. I was using this DLookup expression to find the data for the
previous quarter.
=DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel]= [txtqtr1]")
This value I want to call prevqtrvalue
However this formula calculates the value of txtDomfacsoleqtr for the first
company, which is mathematically correct, and shows the same value for all
the other companies.
I then wanted to calculate the difference between the two to arrive at the
quarter's data in other words Current YTD minus PrevQuarter's YTD=Quarterly
change. using something like this
Quarterly change= [txtDomfacsoleqtr]-[prevqtrvalue]
I have also tried using my full DLookup expression instead of preqtrvalue.
Although the calculation doesn't create any Access error messages, the
caculation just deducts the same value of prevqtrvalue for each company
rather than calculating the value of prevqtrvalue relative to that company.

Having got these figures in the detail section I then wanted to total both
lots of data to arrive at the total figures for all the companies.

Am I going about this the wrong way? I've tried all sorts of things over the
last three days and don't seem to be getting anywhere. Can someone point me
in the right direction? I'm happy to learn I just need guidance on where to
go, I'm getting desperate!.

I have also posted this in the Microsoft Access group for which I apologise
for cross posting in advance, but I seem to have problems seeing my posts on
the Microsoft newsgroups so I'm trying this one as well. Thanks in
anticipation
Tony

 

[dhookom]

I have seen your posts in other locations. Please provide some sample records and expected display. If find your questions very confusing when you use statements like [red]"The txtmonthlabel is also a field in my table and is the date of the current quarter, eg March 2004"[/red].

Is this field a date field? Do you have lots of records per quarter/month? Please provide some sample records to end all the confusion.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[tonywilliams]

Thanks for the reply Duane. I'm so lost in this that I think my explanations are not as good as they should be.
First the practical issue, what am I getting and what am I trying to do. I have a database that holds data of statistics on performance from various companies. They provide us with year to date data on various categories of business. So each company provides stats like this
December 2004
Company1
Category1YTD £200,000 Category2YTD £250,000 Category3YTD £300,000
Company2
Category1YTD £150,000 Category2YTD £1000,000 Category3YTD £250,000
and so on for 40 companies

They provide the data each quarter, March, June, September and December. The quarterly date is held in the field in my table as txtmonthlabel (sorry about the use of the word label here, that's how the database was set up orgionally)Each category is a field in my table (tblmaintabs) an example of a field name in my example is txtDomfacsoleqtr

What we need to do first is to compare the data for each company to the data they provided last quarter for each category.

So with the above December YTD data we would want to compare the December YTD data with the YTD data each company provided in September for each category.

So when I'm running a report in Decemeber 2004 I created a query based on my table to give me the data for each company and category and another linked query which gives me the date of the previous quater. I used the DateAdd function in this second query to identify the date of the previous quarter which I called txtqtr1. That seems to work OK.

I then used the DLookup function to calculate the value of the YTD data for each category for the previous quarter. This value I called prevqtrvalue. This is where I start going wrong because although it produces a figure for each category, it produces the same figure for each company, so for example for Company2 and Company3 Category1YTD I get the same figure as for Company1 Category1YTD.

When I've got the previous quarters' YTD value I then want to find the difference between the two sets of quarterly data. So for example I want
(December 2004),(Company1),(Category1YTD) MINUS (September 2004),(Company1),(Category1YTD)EQUALS Value for quarter

I really hope that explains it a little better. As I said I think having been struggling with this for 3 days now I am beginning to get lost and wondering whether in fact I'm going about this the right way at all. It seems a simple thing to do, ie take two figures compare them and then subtract one from the other and I suppose if this was a spreadsheet it would be easy, but we want a database for other reasons.
I would really appreciate your help here Duane, what am I missing?
Thanks
Tony

 

[dhookom]

I would not call this sample records"
[red]
December 2004
Company1
Category1YTD £200,000 Category2YTD £250,000 Category3YTD £300,000
Company2
Category1YTD £150,000 Category2YTD £1000,000 Category3YTD £250,000
[/red]
I don't know if:
-your data comes in that unstructured
-what your field names are
-what your table names are
-what your field types are
Tony, I would expect to see something like"
[tt]
ReportDate Company Category Amount
(date) (text) (text) (currency)
12/1/2004 CompA Clothing 2310.34
12/1/2004 CompA Food 940.00
12/1/2004 CompA Utilities 892.50
12/1/2004 CompB Clothing 310.34
12/1/2004 CompB Food 1940.00
12/1/2004 CompB Utilities 8923.50
9/1/2004 CompA Clothing 2045.00
9/1/2004 CompA Food 234.10
[/tt]
Then, you provide something similar to this only it would show how you would expect your sample data to appear on the report.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[tonywilliams]

Okay thanks Duane for sticking with me here is what I think you are looking for:
This is my data:
Return Date Company Amount
(My field name)[txtmonthlabel][txtcompany][txtDomfacsoleqtr]
(Field Type) (date) (text) (Fixed Number)
(In December they would provide this data)
12/1/2004 CompA 231,000
12/1/2004 CompB 150,000
12/1/2004 CompC 250,000
12/1/2004 CompD 175,000
(In September they would provide this data)
9/1/2004 CompA 175,000
9/1/2004 CompB 75,000
9/1/2004 CompC 150,000
9/1/2004 CompD 75,000


I want to run the report in December to get this:

12/1/2004 9/1/2004 Difference
CompA 231,000 175,000 66,000
CompB 150,000 75,000 75,000
CompC 250,000 150,000 100,000
CompD 175,000 75,000 100,000

Total 806,000 475,000 331,000

Is that any clearer? I apologise for this confusion I can only blame my inexperience (and age, at 60 the old grey cells don't work as well)

Thanks again Duane
Tony

 

[dhookom]

I would create a Crosstab query that has the amounts for the current quarter and the previous 4 quarters.
TRANSFORM Sum(Amount) AS SumOfAmount
SELECT Company
FROM tblTW
GROUP BY Company
PIVOT "Q" & DateDiff("q",[Return Date],Date()) In ("Q0","Q1","Q2","Q3","Q4");
[tt]
Company Q0 Q1 Q2 Q3 Q4
CompA 231000 175000
CompB 150000 75000
CompC 250000 150000
CompD 175000 75000
[/tt]
This allows you to create a report based on the same fields/columns everytime. You can subtract on quarter from another in the report as well as create your totals.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[tonywilliams]

Thanks Duane I've never looked at Crosstab queries but I used your code and got some results, thanks that was a step in the right direction. HOWEVER, (and there is always one isn't there?)
1. Having run the query it appears that your code gives 5 qtrs and that Q0 is the latest quarter and Q4 is the figure for the current quarter date of the previous year, so Q0 is March 2005 and Q4 is March 2004. Am I right in this assumption? I've tried it with actual data and that seems to be the case.
2. I have calculated the actual quarter diiference as [Q0]-[Q1] and that works just fine and I've worked out how to sum these by using =Sum([q0]) etc in the report footer and also the total of the difference using =Sum([q0])-Sum([q1])
3. What happens in the next quarter eg June 2005, Am I right in thinking that Q0 is always the latest date? If so how would we run an historic report eg if we wanted to run the report for say September 2004. Where would we put the criteria question? I've tried adding the return date (txtmonthlabel) to the query and using "between and" but that only gives me the figure for Q4.
4. As I mentioned earlier the companies in fact provide a number of figures. To include them all on one report, would I create seperate queries for each value and then join all the queries together in a new query on say the return date and base the report on this latter query?
5. Finally my user has now had a change of mind! The report they want now looks like this
CompanyA
Quarter ended Year to date
12/1/2004 12/1/2003 12/1/2004 12/1/2003
Field1 4,000 3,500 25,000 20,000
Field2 5,000 6,000 30,000 25,000
and so on for each company with totals at the end
From what you have taught me so far I can get
Quarter ended 12/1/2004 and Year to date 12/1/2004 and Year to date 12/1/2003. What I can't get is Quarter ended 12/1/2003

This last one has really thrown me. Am I into going back to square one on this and rethinking the whole database or is there a solution?

I appreciate change the specification is anoying, my grey hairs are now a lighter shade of grey, but both my users and myself are learning here and with very little financial resource are trying to do this ourselves as we cannot afford an external expert. I'm having to learn as I go along and feel very grateful to the MVP's like yourself who are prepared to help begiiners like me. I'm too old to go back to school!
Thanks Duane
Tony

 

[tonywilliams]

Duane I think I've answered my question 5 by adding Q5 to the query I can use [Q4]-[Q5] to arrive at the Quater ended 12/1/2003, I think!!
Tony

 

[tonywilliams]

Duane just to confirm that I've cracked question 5 and the reformatting of the report. I would really welcome your views on Questions 3 and 4 and then if I crack that the botlle of champange is out!!
Thanks
Tony

 

[dhookom]

Tony,
If you don't want all of the quarters relative to todays date which is "Date()" then think about how you could change the following to reference some other date:

PIVOT "Q" & DateDiff("q",[Return Date],Date()) In ("Q0","Q1","Q2","Q3","Q4");

If you can't figure this out, I have answered similar questions many times. Search google

http://www.google.com/search?query=hookom+crosstab+Report+Mth0

Regarding "provide a number of figures", what exactly do you mean? I didn't see these in the sample records you provided. It may be that you could add another field as a Row Heading.



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]