Reports in access

R

Roger Bell

There are amongst others 3 fields in this data
base;namely:
PERSON'S NAME, TIME PERIOD (DROP DOWN LIST-COMBO EG 1AM
TO 2AM), DAY OF WEEK (DROPDOWN LIST-COMBO). I am trying
to create a report that groups on the time periods down
the left of the page(vertically) and the days of the week
across the top (horixontally), so that the respective
person's name is listed under the respective day. At the
moment I can get the times down and then the days in a
column, but they repeat each time and take up too much
room. Any one can help would be appreciated. Thank you
 
J

John Vinson

There are amongst others 3 fields in this data
base;namely:
PERSON'S NAME, TIME PERIOD (DROP DOWN LIST-COMBO EG 1AM
TO 2AM), DAY OF WEEK (DROPDOWN LIST-COMBO). I am trying
to create a report that groups on the time periods down
the left of the page(vertically) and the days of the week
across the top (horixontally), so that the respective
person's name is listed under the respective day. At the
moment I can get the times down and then the days in a
column, but they repeat each time and take up too much
room. Any one can help would be appreciated. Thank you
Click to expand...

Base the report on a CROSSTAB query, using the time periods as the row
header and DayOfWeek as the columnheader. See Crosstab in the online
help.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
G

Guest

-----Original Message-----


Base the report on a CROSSTAB query, using the time periods as the row
header and DayOfWeek as the columnheader. See Crosstab in the online
help.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
.
Thanks for your reply. I have tried this but it asks for
Click to expand...
a value field which i do not want and i also need the
names to match the days of the respective week. Any
further help thank you
 
J

John Vinson

a value field which i do not want and i also need the
names to match the days of the respective week. Any
further help thank you
Click to expand...

Please post the SQL view of the query, and indicate what's being
prompted for. What's actually stored in your days-of-week field - the
date, or the name Monday, Tuesday etc.?


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
G

Guest

-----Original Message-----


Please post the SQL view of the query, and indicate what's being
prompted for. What's actually stored in your days-of- week field - the
date, or the name Monday, Tuesday etc.?

Thanks John. The individual days are stored in the Day
Click to expand...
of Week field (combo box). I am sorry but I am not sure
what you mean by the SQL view of the query. Thanks again;
Roger
 
J

John Vinson

Thanks John. The individual days are stored in the Day
of Week field (combo box).
Click to expand...

Ummm... No. They're not.

They may be DISPLAYED in a combo box, but a combo box is not a data
storage device. It's a data display device. Unless the Combo is a
ListOfValues type, the data is actually stored somewhere in a table.
I am sorry but I am not sure
what you mean by the SQL view of the query.
Click to expand...

Open the query in design view. Either select SQL on the dropdown
leftmost control in the query design toolbar, or choose View... SQL
from the menu. You'll see a page of text. This is "SQL" - Structured
Query Language - and it's the actual Query; if you post it I or
someone will be able to identify the problem.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
G

Guest

This is what shows in the SQL Crosstab query. I don't
want a value but the system insists:

TRANSFORM Sum([Main Table].[Auto Number]) AS [SumOfAuto
Number]
SELECT [Main Table].[Time of Adoration], [Main
Table].Surname, [Main Table].Firstname, [Main Table].
[Home Phone], [Main Table].Mobile, [Main Table].Captain
FROM [Main Table]
GROUP BY [Main Table].[Time of Adoration], [Main
Table].Surname, [Main Table].Firstname, [Main Table].
[Home Phone], [Main Table].Mobile, [Main Table].Captain
PIVOT [Main Table].[Day of Adoration];

The row source type in properties for the drop down list
is set to "Value List"

Thanks again
 
J

John Vinson

This is what shows in the SQL Crosstab query. I don't
want a value but the system insists:
Click to expand...

What "Value" is it insisting upon? Which field do you have bound to
the Combo Box - Day of Adoration?
TRANSFORM Sum([Main Table].[Auto Number]) AS [SumOfAuto
Number]
Click to expand...

Huh? May I ask why you're summing [Auto Number]? If it's really an
Autonumber type field this sum will be altogether meaningless.
SELECT [Main Table].[Time of Adoration], [Main
Table].Surname, [Main Table].Firstname, [Main Table].
[Home Phone], [Main Table].Mobile, [Main Table].Captain
FROM [Main Table]
GROUP BY [Main Table].[Time of Adoration], [Main
Table].Surname, [Main Table].Firstname, [Main Table].
[Home Phone], [Main Table].Mobile, [Main Table].Captain
PIVOT [Main Table].[Day of Adoration];

The row source type in properties for the drop down list
is set to "Value List"
Click to expand...

You're not using any criteria here; do you want to select all the
records in the table? You might want to try setting the Column
Headings property of the Query to

"Sunday";"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday"

(or whatever days in whatever order you want).

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
G

Guest

Thanks for your patience John

When I create the crosstab query, it states that i must
have only 1 column heading, 1 or more row headings and a
Value. This is the SQL of my latest attempt. It seems to
be working in a fashion, but the names are scattered
under the days of the week: for example:
Sunday Monday etc
1am to 2am Bill Smith
John James
John Goods
ken Someone
sql: TRANSFORM Trim([firstname] & " " & [Surname] & " " &
[Home Phone] & " " & [Captain]) AS Expr1
SELECT [Main Table].[Time of Adoration], [Main
Table].Surname, [Main Table].Firstname, [Main Table].
[Home Phone], [Main Table].Mobile, [Main Table].Captain,
[Main Table].[Day of Adoration]
FROM [Main Table]
WHERE ((([Main Table].[Time of Adoration])="(a) Midnight
to 1 am" Or ([Main Table].[Time of Adoration])="(b) 1 am
to 2 am") AND (([Main Table].[Day of Adoration])="(1)
Sunday" Or ([Main Table].[Day of Adoration])="(2)
Monday"))
GROUP BY [Main Table].[Time of Adoration], [Main
Table].Surname, [Main Table].Firstname, [Main Table].
[Home Phone], [Main Table].Mobile, [Main Table].Captain,
[Main Table].[Day of Adoration]
PIVOT [Main Table].[Day of Adoration] In ("(1)
Sunday","(2) Monday"
Thanks again



What "Value" is it insisting upon? Which field do you
have bound to
the Combo Box - Day of Adoration?
TRANSFORM Sum([Main Table].[Auto Number]) AS [SumOfAuto
Number]
Click to expand...

Huh? May I ask why you're summing [Auto Number]? If it's
really an
Autonumber type field this sum will be altogether
meaningless.




John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
..
 
Top