Reports

[Chris Naylor]

Right, this may sound like a bit of an obvious answer but its one I can't
figure out!

I'm working with a fairly complex database, (well complex for me! - maybe
not for some of you guys!) and I need to produce a report where I have one
set of fields down the left hand side (names) then a list of dates (taken
from data in the database) across the top, I then need to be able to add
data cross references to the names and dates (points taken from a table) so
as to get a table where I can clearly see that person X on date Y got Z
points.
In teh end I want it to look something like this (if it comes out!)
DATE1 DATE2 DATE3
Name 3 6 6
NAME 5 7 9


Only trouble is that I can't work out how to format it in such a way as to
do this!

If anyone has any suggestions I would be more than greatful!

Thanks
--
Chris Naylor
**Cut the nonsense to reply**

~My kid gave your honor student the answers to the final exam!



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.530 / Virus Database: 325 - Release Date: 22/10/2003

[YFS DBA]

Re: Reports

Chris, try a cross-tab query. Use the names as row heading, dates as column
headings. Sum the points (it's summing one number, but it works).

An example:

Datatable:

Name Date Points
A 10/11/03 111
B 10/11/03 343
A 12/12/03 189
C 12/20/03 303
C 12/21/03 417

The output from the query looks like this:
Name 10/11/03 12/12/03 12/20/03 12/21/03
A 111 189
B 343
C 303 417

hth

Scott



"Chris Naylor" <neffNONSENSE@p obice.com> wrote in message
news:bn90oa$uis j9$1@ID-155350.news.uni-berlin.de...[color=blue]
> Right, this may sound like a bit of an obvious answer but its one I can't
> figure out!
>
> I'm working with a fairly complex database, (well complex for me! - maybe
> not for some of you guys!) and I need to produce a report where I have one
> set of fields down the left hand side (names) then a list of dates (taken
> from data in the database) across the top, I then need to be able to add
> data cross references to the names and dates (points taken from a table)[/color]
so[color=blue]
> as to get a table where I can clearly see that person X on date Y got Z
> points.
> In teh end I want it to look something like this (if it comes out!)
> DATE1 DATE2 DATE3
> Name 3 6 6
> NAME 5 7 9
>
>
> Only trouble is that I can't work out how to format it in such a way as to
> do this!
>
> If anyone has any suggestions I would be more than greatful!
>
> Thanks
> --
> Chris Naylor
> **Cut the nonsense to reply**
>
> ~My kid gave your honor student the answers to the final exam!
>
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.530 / Virus Database: 325 - Release Date: 22/10/2003
>
>[/color]

[Chris Naylor]

Re: Reports

YFS DBA wrote:[color=blue]
> Chris, try a cross-tab query. Use the names as row heading, dates as
> column headings. Sum the points (it's summing one number, but it
> works).
>
> An example:
>
> Datatable:
>
> Name Date Points
> A 10/11/03 111
> B 10/11/03 343
> A 12/12/03 189
> C 12/20/03 303
> C 12/21/03 417
>
> The output from the query looks like this:
> Name 10/11/03 12/12/03 12/20/03 12/21/03
> A 111 189
> B 343
> C 303 417
>
> hth
>
> Scott[/color]

Its the sort of idea that I was going along for the output, unfortunately I
can't get it to show the points as a single value (ie what each person got
on the specific date) all its show when I use it as a sum is the TOTAL
points under all dates/names that have points in!
Thanks.


--
Chris Naylor
**Cut the nonsense to reply**

~"Since both of its national products, snow and chocolate, melt, the cuckoo
clock was invented solelly in order to give tourists something solid to
remember it by."- Alan Coren - British humorist and writer - on Switzerland





---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.530 / Virus Database: 325 - Release Date: 22/10/2003

[YFS DBA]

Re: Reports

Chris,
Would a name have more than one set of points on the same date? That is, if
"A" had 111 points on 10/11/03 and also 200 points on 10/11/03, the output
would be 311 points for that day.

If there's only one data item per person per day, it should work.

Here's the SQL code I used, change "Table1" to whatever your query or table
is named, also change the field names ("Name", "Points", "Date").

*************** *************** *************
TRANSFORM Sum(Points) AS SumOfPoints
SELECT Name
FROM Table1
GROUP BY Name
PIVOT Format([Date],"Short Date");
*************** *************** *************

Good luck,
Scott

"Chris Naylor" <neffNONSENSE@p obice.com> wrote in message
news:bn95qs$uaj qj$1@ID-155350.news.uni-berlin.de...[color=blue]
> YFS DBA wrote:[color=green]
> > Chris, try a cross-tab query. Use the names as row heading, dates as
> > column headings. Sum the points (it's summing one number, but it
> > works).
> >
> > An example:
> >
> > Datatable:
> >
> > Name Date Points
> > A 10/11/03 111
> > B 10/11/03 343
> > A 12/12/03 189
> > C 12/20/03 303
> > C 12/21/03 417
> >
> > The output from the query looks like this:
> > Name 10/11/03 12/12/03 12/20/03 12/21/03
> > A 111 189
> > B 343
> > C 303 417
> >
> > hth
> >
> > Scott[/color]
>
> Its the sort of idea that I was going along for the output, unfortunately[/color]
I[color=blue]
> can't get it to show the points as a single value (ie what each person got
> on the specific date) all its show when I use it as a sum is the TOTAL
> points under all dates/names that have points in!
> Thanks.
>
>
> --
> Chris Naylor
> **Cut the nonsense to reply**
>
> ~"Since both of its national products, snow and chocolate, melt, the[/color]
cuckoo[color=blue]
> clock was invented solelly in order to give tourists something solid to
> remember it by."- Alan Coren - British humorist and writer - on[/color]
Switzerland[color=blue]
>
>
>
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.530 / Virus Database: 325 - Release Date: 22/10/2003
>
>[/color]

[Chris Naylor]

Re: Reports

YFS DBA wrote:[color=blue]
> Chris,
> Would a name have more than one set of points on the same date? That
> is, if "A" had 111 points on 10/11/03 and also 200 points on
> 10/11/03, the output would be 311 points for that day.
>
> If there's only one data item per person per day, it should work.
>
> Here's the SQL code I used, change "Table1" to whatever your query or
> table is named, also change the field names ("Name", "Points",
> "Date").
>
> *************** *************** *************
> TRANSFORM Sum(Points) AS SumOfPoints
> SELECT Name
> FROM Table1
> GROUP BY Name
> PIVOT Format([Date],"Short Date");
> *************** *************** *************
>
> Good luck,
> Scott
>[/color]

Hi!

Thats how I have it as well
TRANSFORM Sum([cubs/meetings/points link].Points) AS SumOfPoints
SELECT [cubs/meetings/points link].Name
FROM [cubs/meetings/points link]
GROUP BY [cubs/meetings/points link].Name
PIVOT Format([meeting_date],"Short Date");


What it seems to be doing (i havent gone through to check it) is giving a
total for ALL the points that ALL the people have in each entry in the table
(if that makes any sense?)

I'll keep trying to sort it, but if anyone has any ideas then I'd be
greatful!

Thanks
--
Chris Naylor
*Cut the nonsense to reply*
"A door is what a dog is perpetually on the wrong side of."- Ogden Nash



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.530 / Virus Database: 325 - Release Date: 22/10/2003

[YFS DBA]

Re: Reports

Odd...when your run the query, does it give a sum for each name, as
illustrated here:

Name Total Of Points
A 300
B 343
C 720

When what you want is:

Name 10/11/03 12/12/03 12/20/03 12/21/03
A 111 189
B 343
C 303 417

Are any columns hidden? Possible problem in Name, Date or Points
formatting?

I used your transform on my data (renaming my table and fields) and got the
correct results - curiouser and curiouser.

My one hope was the PIVOT formatting of the date field, but it's not that.
I've shot my bolt - let's hope there's someone who's had the same problem!

Regards,

Scott
[color=blue]
> Hi!
>
> Thats how I have it as well
> TRANSFORM Sum([cubs/meetings/points link].Points) AS SumOfPoints
> SELECT [cubs/meetings/points link].Name
> FROM [cubs/meetings/points link]
> GROUP BY [cubs/meetings/points link].Name
> PIVOT Format([meeting_date],"Short Date");
>
>
> What it seems to be doing (i havent gone through to check it) is giving a
> total for ALL the points that ALL the people have in each entry in the[/color]
table[color=blue]
> (if that makes any sense?)
>
> I'll keep trying to sort it, but if anyone has any ideas then I'd be
> greatful!
>
> Thanks
> --
> Chris Naylor
> *Cut the nonsense to reply*
> "A door is what a dog is perpetually on the wrong side of."- Ogden Nash
>
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.530 / Virus Database: 325 - Release Date: 22/10/2003
>
>[/color]

[Chris Naylor]

Re: Reports

YFS DBA wrote:[color=blue]
> Odd...when your run the query, does it give a sum for each name, as
> illustrated here:
>
> Name Total Of Points
> A 300
> B 343
> C 720
>
> When what you want is:
>
> Name 10/11/03 12/12/03 12/20/03 12/21/03
> A 111 189
> B 343
> C 303 417
>
> Are any columns hidden? Possible problem in Name, Date or Points
> formatting?[/color]

I redid my original query that the transfrom was based on using a different
variant of the name - this time using the fields forename and surname
(instead of the field name which is a 'made up' field that concatenates
surname and forename to give their full name)

It works now, it never crossed my mind that the problem could be down to
where I got the name data from!

Your help is greatly appreciated!

Thanks
--
Chris Naylor
*Cut the nonsense to reply*
When man 60 marry girl 25, like buying book for someone else to read.


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.530 / Virus Database: 325 - Release Date: 22/10/2003

[YFS DBA]

Re: Reports

Chris;

lol - thanks! I'll have to remember that problem if it happens to me!

Scott
[color=blue]
> I redid my original query that the transfrom was based on using a[/color]
different[color=blue]
> variant of the name - this time using the fields forename and surname
> (instead of the field name which is a 'made up' field that concatenates
> surname and forename to give their full name)
>
> It works now, it never crossed my mind that the problem could be down to
> where I got the name data from!
>
> Your help is greatly appreciated!
>
> Thanks
> --
> Chris Naylor
> *Cut the nonsense to reply*
> When man 60 marry girl 25, like buying book for someone else to read.
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.530 / Virus Database: 325 - Release Date: 22/10/2003
>
>[/color]