A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Weekly Charts in Reports



 
 
Thread Tools Display Modes
  #11  
Old November 14th, 2005, 02:58 PM
SusanV
external usenet poster
 
Posts: n/a
Default Weekly Charts in Reports

Is the layout correct? Are you just looking to change the column and row
names or the data in the cells?

"Neil M" wrote in message
...
the report is now display (i created a new report to be sure) but it still
leaves me with the problem where I want to display the date/times in chart
form (weekly format)
at the moment its just a list (which I can do without the extra coding)

Any suggestions or ideas on how I would do this?

Neil

"Neil M" wrote in message
...
Right here the layout -
I got a report named REPORT which is linked to "Qry_Report" query

Here is the SQL for the Query;
TRANSFORM First(TBL_Main.Staff) AS FirstOfStaff
SELECT TBL_Main.Date, TBL_Main.Room
FROM TBL_Main
GROUP BY TBL_Main.ID, TBL_Main.Date, TBL_Main.TimeIn, TBL_Main.Room
PIVOT TBL_Main.TimeOut;

Is there a special way to display the times, staff (names) and other info

on
the REPORT, as it appears that if I select field list on the report
design
only the actual dates and data is shown and not the field names that I
can
recognise like Staff, TimeIn, TimeOut, etc..

Hope this explains it a little better.


"SusanV" wrote in message
...
Sorry should have stated what control in the report is using "staff" -

not
field.

"SusanV" wrote in message
...
What field is using "Staff" as a record source (view via Properties
of
fields)? What is the equivalent field in your crosstab query? Change

the
source to the appropriate field.
--
hth,
SusanV


"Neil M" wrote in message
...
thanks. got the crosstab query done and it appears to be running ok

but
when i link it to my report it states that it doesn't recognise

'staff'
as a
valid field name or expression.
any suggestions on how (lame man's terms) i link it to the report -

as
the
report field name list does not have staff on it.

Thanks,


Neil


"SusanV" wrote in message
...

Create a query containing all the data you will need to display,

either
from
a table or tables, or an existing query. Save it and while in
Design
View,
change it from a select query to a Crosstab query either in the

Query
Menu
or from the toolbar.

Choose "Row Heading" for the times
Choose "Column Heading" for the dates
Choose Value for the Name, and set the "Total" option from GroupBy

to
Either
First or Last.

Open the query in datasheet view and you should see the layout you

are
looking for. Save the query, and use that query as the data source

for
your
report.

Here's a link to a site with a tutorial in case I wasn't clear:



http://www.jmu.edu/computing/ittrain...crosstab.shtml


--
hth,
SusanV



"Neil M" wrote in message
...
sorry can you eplain a bit more what you mean and how roughly to

do
this.
thanks.


"SusanV" wrote in message
...
Hi Neil,

I think if you use as the report source a crosstab query you'll

be
able
to
get the report you're looking for.
--
hth,
SusanV

"Neil M" wrote in message
...
Hiya all,

I have yet another problem I can't seem to get my head around.

Here's the problem...

Part of the same database is a system to book of rooms

dependent
on
days.
The database works fine and I can print normal 'listing' type
reports
which
will make do for now.

But what I really would like to do is print off a report where

it
has
the
times and dates like a chart
(like below

ROOM (header)

DATE DATE DATE DATE DATE DATE

DATE
(Mon Sun)
TIMES
in hours NAME
here
from
8am to
8pm?

Get my drift, so the block of staff names appear in the times

theyt
have
the
room booked.

Anyone got any suggestions on how to do this. I have found an
example
database for this sort of thing (Doctors, etc) but I can't
seem

to
get
my
head around the rather complicated coding and reporting. I

need
to
understand how its done not just how to do it.

Thanks,


Neil




















  #12  
Old November 16th, 2005, 10:10 AM
Neil M
external usenet poster
 
Posts: n/a
Default Weekly Charts in Reports

did you have a look at it susan?


"Neil M" wrote in message
...
I have changed that but still getting the same error.
I have uploaded the database so you can physically have a look?

My reports don't seem to be in chart form just in list of room and dates,
etc..

Thanks,

Neil

"SusanV" wrote in message
...
Hi Neil,

For the "staff" problem, change the AS part of the query to whatever you
like - AS simply is giving the calculated field a name. Perhaps:

TRANSFORM First(TBL_Main.Staff) AS StaffName
SELECT TBL_Main.Date, TBL_Main.Room
FROM TBL_Main
GROUP BY TBL_Main.ID, TBL_Main.Date, TBL_Main.TimeIn, TBL_Main.Room
PIVOT TBL_Main.TimeOut;







"Neil M" wrote in message
...
Right here the layout -
I got a report named REPORT which is linked to "Qry_Report" query

Here is the SQL for the Query;
TRANSFORM First(TBL_Main.Staff) AS FirstOfStaff
SELECT TBL_Main.Date, TBL_Main.Room
FROM TBL_Main
GROUP BY TBL_Main.ID, TBL_Main.Date, TBL_Main.TimeIn, TBL_Main.Room
PIVOT TBL_Main.TimeOut;

Is there a special way to display the times, staff (names) and other

info
on
the REPORT, as it appears that if I select field list on the report

design
only the actual dates and data is shown and not the field names that I

can
recognise like Staff, TimeIn, TimeOut, etc..

Hope this explains it a little better.


"SusanV" wrote in message
...
Sorry should have stated what control in the report is using

"staff" -
not
field.

"SusanV" wrote in message
...
What field is using "Staff" as a record source (view via Properties

of
fields)? What is the equivalent field in your crosstab query?

Change
the
source to the appropriate field.
--
hth,
SusanV


"Neil M" wrote in message
...
thanks. got the crosstab query done and it appears to be running

ok
but
when i link it to my report it states that it doesn't recognise
'staff'
as a
valid field name or expression.
any suggestions on how (lame man's terms) i link it to the

report -
as
the
report field name list does not have staff on it.

Thanks,


Neil


"SusanV" wrote in message
...

Create a query containing all the data you will need to display,
either
from
a table or tables, or an existing query. Save it and while in

Design
View,
change it from a select query to a Crosstab query either in the

Query
Menu
or from the toolbar.

Choose "Row Heading" for the times
Choose "Column Heading" for the dates
Choose Value for the Name, and set the "Total" option from

GroupBy
to
Either
First or Last.

Open the query in datasheet view and you should see the layout

you
are
looking for. Save the query, and use that query as the data

source
for
your
report.

Here's a link to a site with a tutorial in case I wasn't clear:




http://www.jmu.edu/computing/ittrain...crosstab.shtml


--
hth,
SusanV



"Neil M" wrote in message
...
sorry can you eplain a bit more what you mean and how roughly

to
do
this.
thanks.


"SusanV" wrote in message
...
Hi Neil,

I think if you use as the report source a crosstab query

you'll
be
able
to
get the report you're looking for.
--
hth,
SusanV

"Neil M" wrote in message
...
Hiya all,

I have yet another problem I can't seem to get my head

around.

Here's the problem...

Part of the same database is a system to book of rooms

dependent
on
days.
The database works fine and I can print normal 'listing'

type
reports
which
will make do for now.

But what I really would like to do is print off a report

where
it
has
the
times and dates like a chart
(like below

ROOM (header)

DATE DATE DATE DATE DATE DATE
DATE
(Mon Sun)
TIMES
in hours NAME
here
from
8am to
8pm?

Get my drift, so the block of staff names appear in the

times
theyt
have
the
room booked.

Anyone got any suggestions on how to do this. I have found

an
example
database for this sort of thing (Doctors, etc) but I can't

seem
to
get
my
head around the rather complicated coding and reporting. I

need
to
understand how its done not just how to do it.

Thanks,


Neil























  #13  
Old November 16th, 2005, 03:47 PM
SusanV
external usenet poster
 
Posts: n/a
Default Weekly Charts in Reports

I'm sorry - I didn't realize there was something you wanted me to view -
uploaded it where?


"Neil M" wrote in message
...
did you have a look at it susan?


"Neil M" wrote in message
...
I have changed that but still getting the same error.
I have uploaded the database so you can physically have a look?

My reports don't seem to be in chart form just in list of room and dates,
etc..

Thanks,

Neil

"SusanV" wrote in message
...
Hi Neil,

For the "staff" problem, change the AS part of the query to whatever
you
like - AS simply is giving the calculated field a name. Perhaps:

TRANSFORM First(TBL_Main.Staff) AS StaffName
SELECT TBL_Main.Date, TBL_Main.Room
FROM TBL_Main
GROUP BY TBL_Main.ID, TBL_Main.Date, TBL_Main.TimeIn, TBL_Main.Room
PIVOT TBL_Main.TimeOut;







"Neil M" wrote in message
...
Right here the layout -
I got a report named REPORT which is linked to "Qry_Report" query

Here is the SQL for the Query;
TRANSFORM First(TBL_Main.Staff) AS FirstOfStaff
SELECT TBL_Main.Date, TBL_Main.Room
FROM TBL_Main
GROUP BY TBL_Main.ID, TBL_Main.Date, TBL_Main.TimeIn, TBL_Main.Room
PIVOT TBL_Main.TimeOut;

Is there a special way to display the times, staff (names) and other

info
on
the REPORT, as it appears that if I select field list on the report

design
only the actual dates and data is shown and not the field names that
I

can
recognise like Staff, TimeIn, TimeOut, etc..

Hope this explains it a little better.


"SusanV" wrote in message
...
Sorry should have stated what control in the report is using

"staff" -
not
field.

"SusanV" wrote in message
...
What field is using "Staff" as a record source (view via
Properties

of
fields)? What is the equivalent field in your crosstab query?

Change
the
source to the appropriate field.
--
hth,
SusanV


"Neil M" wrote in message
...
thanks. got the crosstab query done and it appears to be running

ok
but
when i link it to my report it states that it doesn't recognise
'staff'
as a
valid field name or expression.
any suggestions on how (lame man's terms) i link it to the

report -
as
the
report field name list does not have staff on it.

Thanks,


Neil


"SusanV" wrote in message
...

Create a query containing all the data you will need to display,
either
from
a table or tables, or an existing query. Save it and while in

Design
View,
change it from a select query to a Crosstab query either in the

Query
Menu
or from the toolbar.

Choose "Row Heading" for the times
Choose "Column Heading" for the dates
Choose Value for the Name, and set the "Total" option from

GroupBy
to
Either
First or Last.

Open the query in datasheet view and you should see the layout

you
are
looking for. Save the query, and use that query as the data

source
for
your
report.

Here's a link to a site with a tutorial in case I wasn't clear:




http://www.jmu.edu/computing/ittrain...crosstab.shtml


--
hth,
SusanV



"Neil M" wrote in message
...
sorry can you eplain a bit more what you mean and how roughly

to
do
this.
thanks.


"SusanV" wrote in message
...
Hi Neil,

I think if you use as the report source a crosstab query

you'll
be
able
to
get the report you're looking for.
--
hth,
SusanV

"Neil M" wrote in message
...
Hiya all,

I have yet another problem I can't seem to get my head

around.

Here's the problem...

Part of the same database is a system to book of rooms

dependent
on
days.
The database works fine and I can print normal 'listing'

type
reports
which
will make do for now.

But what I really would like to do is print off a report

where
it
has
the
times and dates like a chart
(like below

ROOM (header)

DATE DATE DATE DATE DATE DATE
DATE
(Mon Sun)
TIMES
in hours NAME
here
from
8am to
8pm?

Get my drift, so the block of staff names appear in the

times
theyt
have
the
room booked.

Anyone got any suggestions on how to do this. I have found

an
example
database for this sort of thing (Doctors, etc) but I can't

seem
to
get
my
head around the rather complicated coding and reporting. I

need
to
understand how its done not just how to do it.

Thanks,


Neil

























  #14  
Old November 17th, 2005, 09:47 AM posted to microsoft.public.access.forms
external usenet poster
 
Posts: n/a
Default Weekly Charts in Reports

np - i have uploaded to the forums (attached to my last message)

thanks.

neil


"SusanV" wrote in message
...
I'm sorry - I didn't realize there was something you wanted me to view -
uploaded it where?


"Neil M" wrote in message
...
did you have a look at it susan?


"Neil M" wrote in message
...
I have changed that but still getting the same error.
I have uploaded the database so you can physically have a look?

My reports don't seem to be in chart form just in list of room and

dates,
etc..

Thanks,

Neil

"SusanV" wrote in message
...
Hi Neil,

For the "staff" problem, change the AS part of the query to whatever
you
like - AS simply is giving the calculated field a name. Perhaps:

TRANSFORM First(TBL_Main.Staff) AS StaffName
SELECT TBL_Main.Date, TBL_Main.Room
FROM TBL_Main
GROUP BY TBL_Main.ID, TBL_Main.Date, TBL_Main.TimeIn, TBL_Main.Room
PIVOT TBL_Main.TimeOut;







"Neil M" wrote in message
...
Right here the layout -
I got a report named REPORT which is linked to "Qry_Report" query

Here is the SQL for the Query;
TRANSFORM First(TBL_Main.Staff) AS FirstOfStaff
SELECT TBL_Main.Date, TBL_Main.Room
FROM TBL_Main
GROUP BY TBL_Main.ID, TBL_Main.Date, TBL_Main.TimeIn, TBL_Main.Room
PIVOT TBL_Main.TimeOut;

Is there a special way to display the times, staff (names) and

other
info
on
the REPORT, as it appears that if I select field list on the report
design
only the actual dates and data is shown and not the field names

that
I
can
recognise like Staff, TimeIn, TimeOut, etc..

Hope this explains it a little better.


"SusanV" wrote in message
...
Sorry should have stated what control in the report is using

"staff" -
not
field.

"SusanV" wrote in message
...
What field is using "Staff" as a record source (view via
Properties
of
fields)? What is the equivalent field in your crosstab query?

Change
the
source to the appropriate field.
--
hth,
SusanV


"Neil M" wrote in message
...
thanks. got the crosstab query done and it appears to be

running
ok
but
when i link it to my report it states that it doesn't recognise
'staff'
as a
valid field name or expression.
any suggestions on how (lame man's terms) i link it to the

report -
as
the
report field name list does not have staff on it.

Thanks,


Neil


"SusanV" wrote in message
...

Create a query containing all the data you will need to

display,
either
from
a table or tables, or an existing query. Save it and while in
Design
View,
change it from a select query to a Crosstab query either in

the
Query
Menu
or from the toolbar.

Choose "Row Heading" for the times
Choose "Column Heading" for the dates
Choose Value for the Name, and set the "Total" option from

GroupBy
to
Either
First or Last.

Open the query in datasheet view and you should see the layout

you
are
looking for. Save the query, and use that query as the data

source
for
your
report.

Here's a link to a site with a tutorial in case I wasn't

clear:





http://www.jmu.edu/computing/ittrain...crosstab.shtml


--
hth,
SusanV



"Neil M" wrote in message
...
sorry can you eplain a bit more what you mean and how

roughly
to
do
this.
thanks.


"SusanV" wrote in message
...
Hi Neil,

I think if you use as the report source a crosstab query

you'll
be
able
to
get the report you're looking for.
--
hth,
SusanV

"Neil M" wrote in message
...
Hiya all,

I have yet another problem I can't seem to get my head

around.

Here's the problem...

Part of the same database is a system to book of rooms
dependent
on
days.
The database works fine and I can print normal 'listing'

type
reports
which
will make do for now.

But what I really would like to do is print off a report

where
it
has
the
times and dates like a chart
(like below

ROOM (header)

DATE DATE DATE DATE DATE

DATE
DATE
(Mon Sun)
TIMES
in hours NAME
here
from
8am to
8pm?

Get my drift, so the block of staff names appear in the

times
theyt
have
the
room booked.

Anyone got any suggestions on how to do this. I have

found
an
example
database for this sort of thing (Doctors, etc) but I

can't
seem
to
get
my
head around the rather complicated coding and reporting.

I
need
to
understand how its done not just how to do it.

Thanks,


Neil



























  #15  
Old November 17th, 2005, 01:22 PM posted to microsoft.public.access.forms
external usenet poster
 
Posts: n/a
Default Weekly Charts in Reports

Sorry but I don't view this via the web - I use a newsreader so the
attachment is not available. Do you have a link I could follow?

Susan

"Neil M" wrote in message
...
np - i have uploaded to the forums (attached to my last message)

thanks.

neil


"SusanV" wrote in message
...
I'm sorry - I didn't realize there was something you wanted me to view -
uploaded it where?


"Neil M" wrote in message
...
did you have a look at it susan?


"Neil M" wrote in message
...
I have changed that but still getting the same error.
I have uploaded the database so you can physically have a look?

My reports don't seem to be in chart form just in list of room and

dates,
etc..

Thanks,

Neil

"SusanV" wrote in message
...
Hi Neil,

For the "staff" problem, change the AS part of the query to whatever
you
like - AS simply is giving the calculated field a name. Perhaps:

TRANSFORM First(TBL_Main.Staff) AS StaffName
SELECT TBL_Main.Date, TBL_Main.Room
FROM TBL_Main
GROUP BY TBL_Main.ID, TBL_Main.Date, TBL_Main.TimeIn, TBL_Main.Room
PIVOT TBL_Main.TimeOut;







"Neil M" wrote in message
...
Right here the layout -
I got a report named REPORT which is linked to "Qry_Report" query

Here is the SQL for the Query;
TRANSFORM First(TBL_Main.Staff) AS FirstOfStaff
SELECT TBL_Main.Date, TBL_Main.Room
FROM TBL_Main
GROUP BY TBL_Main.ID, TBL_Main.Date, TBL_Main.TimeIn,
TBL_Main.Room
PIVOT TBL_Main.TimeOut;

Is there a special way to display the times, staff (names) and

other
info
on
the REPORT, as it appears that if I select field list on the
report
design
only the actual dates and data is shown and not the field names

that
I
can
recognise like Staff, TimeIn, TimeOut, etc..

Hope this explains it a little better.


"SusanV" wrote in message
...
Sorry should have stated what control in the report is using
"staff" -
not
field.

"SusanV" wrote in message
...
What field is using "Staff" as a record source (view via
Properties
of
fields)? What is the equivalent field in your crosstab query?
Change
the
source to the appropriate field.
--
hth,
SusanV


"Neil M" wrote in message
...
thanks. got the crosstab query done and it appears to be

running
ok
but
when i link it to my report it states that it doesn't
recognise
'staff'
as a
valid field name or expression.
any suggestions on how (lame man's terms) i link it to the
report -
as
the
report field name list does not have staff on it.

Thanks,


Neil


"SusanV" wrote in message
...

Create a query containing all the data you will need to

display,
either
from
a table or tables, or an existing query. Save it and while in
Design
View,
change it from a select query to a Crosstab query either in

the
Query
Menu
or from the toolbar.

Choose "Row Heading" for the times
Choose "Column Heading" for the dates
Choose Value for the Name, and set the "Total" option from
GroupBy
to
Either
First or Last.

Open the query in datasheet view and you should see the
layout
you
are
looking for. Save the query, and use that query as the data
source
for
your
report.

Here's a link to a site with a tutorial in case I wasn't

clear:





http://www.jmu.edu/computing/ittrain...crosstab.shtml


--
hth,
SusanV



"Neil M" wrote in message
...
sorry can you eplain a bit more what you mean and how

roughly
to
do
this.
thanks.


"SusanV" wrote in message
...
Hi Neil,

I think if you use as the report source a crosstab query
you'll
be
able
to
get the report you're looking for.
--
hth,
SusanV

"Neil M" wrote in message
...
Hiya all,

I have yet another problem I can't seem to get my head
around.

Here's the problem...

Part of the same database is a system to book of rooms
dependent
on
days.
The database works fine and I can print normal 'listing'
type
reports
which
will make do for now.

But what I really would like to do is print off a report
where
it
has
the
times and dates like a chart
(like below

ROOM (header)

DATE DATE DATE DATE DATE

DATE
DATE
(Mon Sun)
TIMES
in hours NAME
here
from
8am to
8pm?

Get my drift, so the block of staff names appear in the
times
theyt
have
the
room booked.

Anyone got any suggestions on how to do this. I have

found
an
example
database for this sort of thing (Doctors, etc) but I

can't
seem
to
get
my
head around the rather complicated coding and reporting.

I
need
to
understand how its done not just how to do it.

Thanks,


Neil





























  #16  
Old November 17th, 2005, 01:40 PM posted to microsoft.public.access.forms
external usenet poster
 
Posts: n/a
Default Weekly Charts in Reports

ok ill upload to my private space and let you know tomorrow morning.
thanks.

"SusanV" wrote in message
...
Sorry but I don't view this via the web - I use a newsreader so the
attachment is not available. Do you have a link I could follow?

Susan

"Neil M" wrote in message
...
np - i have uploaded to the forums (attached to my last message)

thanks.

neil


"SusanV" wrote in message
...
I'm sorry - I didn't realize there was something you wanted me to

view -
uploaded it where?


"Neil M" wrote in message
...
did you have a look at it susan?


"Neil M" wrote in message
...
I have changed that but still getting the same error.
I have uploaded the database so you can physically have a look?

My reports don't seem to be in chart form just in list of room and

dates,
etc..

Thanks,

Neil

"SusanV" wrote in message
...
Hi Neil,

For the "staff" problem, change the AS part of the query to

whatever
you
like - AS simply is giving the calculated field a name. Perhaps:

TRANSFORM First(TBL_Main.Staff) AS StaffName
SELECT TBL_Main.Date, TBL_Main.Room
FROM TBL_Main
GROUP BY TBL_Main.ID, TBL_Main.Date, TBL_Main.TimeIn,

TBL_Main.Room
PIVOT TBL_Main.TimeOut;







"Neil M" wrote in message
...
Right here the layout -
I got a report named REPORT which is linked to "Qry_Report"

query

Here is the SQL for the Query;
TRANSFORM First(TBL_Main.Staff) AS FirstOfStaff
SELECT TBL_Main.Date, TBL_Main.Room
FROM TBL_Main
GROUP BY TBL_Main.ID, TBL_Main.Date, TBL_Main.TimeIn,
TBL_Main.Room
PIVOT TBL_Main.TimeOut;

Is there a special way to display the times, staff (names) and

other
info
on
the REPORT, as it appears that if I select field list on the
report
design
only the actual dates and data is shown and not the field names

that
I
can
recognise like Staff, TimeIn, TimeOut, etc..

Hope this explains it a little better.


"SusanV" wrote in message
...
Sorry should have stated what control in the report is using
"staff" -
not
field.

"SusanV" wrote in message
...
What field is using "Staff" as a record source (view via
Properties
of
fields)? What is the equivalent field in your crosstab query?
Change
the
source to the appropriate field.
--
hth,
SusanV


"Neil M" wrote in message
...
thanks. got the crosstab query done and it appears to be

running
ok
but
when i link it to my report it states that it doesn't
recognise
'staff'
as a
valid field name or expression.
any suggestions on how (lame man's terms) i link it to the
report -
as
the
report field name list does not have staff on it.

Thanks,


Neil


"SusanV" wrote in message
...

Create a query containing all the data you will need to

display,
either
from
a table or tables, or an existing query. Save it and while

in
Design
View,
change it from a select query to a Crosstab query either in

the
Query
Menu
or from the toolbar.

Choose "Row Heading" for the times
Choose "Column Heading" for the dates
Choose Value for the Name, and set the "Total" option from
GroupBy
to
Either
First or Last.

Open the query in datasheet view and you should see the
layout
you
are
looking for. Save the query, and use that query as the data
source
for
your
report.

Here's a link to a site with a tutorial in case I wasn't

clear:






http://www.jmu.edu/computing/ittrain...crosstab.shtml


--
hth,
SusanV



"Neil M" wrote in message
...
sorry can you eplain a bit more what you mean and how

roughly
to
do
this.
thanks.


"SusanV" wrote in message
...
Hi Neil,

I think if you use as the report source a crosstab query
you'll
be
able
to
get the report you're looking for.
--
hth,
SusanV

"Neil M" wrote in message

...
Hiya all,

I have yet another problem I can't seem to get my head
around.

Here's the problem...

Part of the same database is a system to book of rooms
dependent
on
days.
The database works fine and I can print normal

'listing'
type
reports
which
will make do for now.

But what I really would like to do is print off a

report
where
it
has
the
times and dates like a chart
(like below

ROOM (header)

DATE DATE DATE DATE DATE

DATE
DATE
(Mon Sun)
TIMES
in hours NAME
here
from
8am to
8pm?

Get my drift, so the block of staff names appear in

the
times
theyt
have
the
room booked.

Anyone got any suggestions on how to do this. I have

found
an
example
database for this sort of thing (Doctors, etc) but I

can't
seem
to
get
my
head around the rather complicated coding and

reporting.
I
need
to
understand how its done not just how to do it.

Thanks,


Neil































  #17  
Old November 17th, 2005, 02:27 PM posted to microsoft.public.access.forms
external usenet poster
 
Posts: n/a
Default Weekly Charts in Reports

If you uploaded it to the forum, I should be able to get it there - which
forum? Can't you send a link?



"Neil M" wrote in message
...
ok ill upload to my private space and let you know tomorrow morning.
thanks.

"SusanV" wrote in message
...
Sorry but I don't view this via the web - I use a newsreader so the
attachment is not available. Do you have a link I could follow?

Susan

"Neil M" wrote in message
...
np - i have uploaded to the forums (attached to my last message)

thanks.

neil


"SusanV" wrote in message
...
I'm sorry - I didn't realize there was something you wanted me to

view -
uploaded it where?


"Neil M" wrote in message
...
did you have a look at it susan?


"Neil M" wrote in message
...
I have changed that but still getting the same error.
I have uploaded the database so you can physically have a look?

My reports don't seem to be in chart form just in list of room and
dates,
etc..

Thanks,

Neil

"SusanV" wrote in message
...
Hi Neil,

For the "staff" problem, change the AS part of the query to

whatever
you
like - AS simply is giving the calculated field a name. Perhaps:

TRANSFORM First(TBL_Main.Staff) AS StaffName
SELECT TBL_Main.Date, TBL_Main.Room
FROM TBL_Main
GROUP BY TBL_Main.ID, TBL_Main.Date, TBL_Main.TimeIn,

TBL_Main.Room
PIVOT TBL_Main.TimeOut;







"Neil M" wrote in message
...
Right here the layout -
I got a report named REPORT which is linked to "Qry_Report"

query

Here is the SQL for the Query;
TRANSFORM First(TBL_Main.Staff) AS FirstOfStaff
SELECT TBL_Main.Date, TBL_Main.Room
FROM TBL_Main
GROUP BY TBL_Main.ID, TBL_Main.Date, TBL_Main.TimeIn,
TBL_Main.Room
PIVOT TBL_Main.TimeOut;

Is there a special way to display the times, staff (names) and
other
info
on
the REPORT, as it appears that if I select field list on the
report
design
only the actual dates and data is shown and not the field names
that
I
can
recognise like Staff, TimeIn, TimeOut, etc..

Hope this explains it a little better.


"SusanV" wrote in message
...
Sorry should have stated what control in the report is using
"staff" -
not
field.

"SusanV" wrote in message
...
What field is using "Staff" as a record source (view via
Properties
of
fields)? What is the equivalent field in your crosstab
query?
Change
the
source to the appropriate field.
--
hth,
SusanV


"Neil M" wrote in message
...
thanks. got the crosstab query done and it appears to be
running
ok
but
when i link it to my report it states that it doesn't
recognise
'staff'
as a
valid field name or expression.
any suggestions on how (lame man's terms) i link it to the
report -
as
the
report field name list does not have staff on it.

Thanks,


Neil


"SusanV" wrote in message
...

Create a query containing all the data you will need to
display,
either
from
a table or tables, or an existing query. Save it and while

in
Design
View,
change it from a select query to a Crosstab query either
in
the
Query
Menu
or from the toolbar.

Choose "Row Heading" for the times
Choose "Column Heading" for the dates
Choose Value for the Name, and set the "Total" option from
GroupBy
to
Either
First or Last.

Open the query in datasheet view and you should see the
layout
you
are
looking for. Save the query, and use that query as the
data
source
for
your
report.

Here's a link to a site with a tutorial in case I wasn't
clear:






http://www.jmu.edu/computing/ittrain...crosstab.shtml


--
hth,
SusanV



"Neil M" wrote in message
...
sorry can you eplain a bit more what you mean and how
roughly
to
do
this.
thanks.


"SusanV" wrote in message
...
Hi Neil,

I think if you use as the report source a crosstab
query
you'll
be
able
to
get the report you're looking for.
--
hth,
SusanV

"Neil M" wrote in message

...
Hiya all,

I have yet another problem I can't seem to get my
head
around.

Here's the problem...

Part of the same database is a system to book of
rooms
dependent
on
days.
The database works fine and I can print normal

'listing'
type
reports
which
will make do for now.

But what I really would like to do is print off a

report
where
it
has
the
times and dates like a chart
(like below

ROOM (header)

DATE DATE DATE DATE DATE
DATE
DATE
(Mon Sun)
TIMES
in hours NAME
here
from
8am to
8pm?

Get my drift, so the block of staff names appear in

the
times
theyt
have
the
room booked.

Anyone got any suggestions on how to do this. I have
found
an
example
database for this sort of thing (Doctors, etc) but I
can't
seem
to
get
my
head around the rather complicated coding and

reporting.
I
need
to
understand how its done not just how to do it.

Thanks,


Neil

































  #18  
Old November 18th, 2005, 11:13 AM posted to microsoft.public.access.forms
external usenet poster
 
Posts: n/a
Default Weekly Charts in Reports

i use outlook express to view the newsgroups, forum name is
microsoft.public.access.forms


I have also uploaded it to;
http://neil.mansell.users.btopenworld.com/RBS.zip


thanks.


"SusanV" wrote in message
...
If you uploaded it to the forum, I should be able to get it there - which
forum? Can't you send a link?



"Neil M" wrote in message
...
ok ill upload to my private space and let you know tomorrow morning.
thanks.

"SusanV" wrote in message
...
Sorry but I don't view this via the web - I use a newsreader so the
attachment is not available. Do you have a link I could follow?

Susan

"Neil M" wrote in message
...
np - i have uploaded to the forums (attached to my last message)

thanks.

neil


"SusanV" wrote in message
...
I'm sorry - I didn't realize there was something you wanted me to

view -
uploaded it where?


"Neil M" wrote in message
...
did you have a look at it susan?


"Neil M" wrote in message
...
I have changed that but still getting the same error.
I have uploaded the database so you can physically have a look?

My reports don't seem to be in chart form just in list of room

and
dates,
etc..

Thanks,

Neil

"SusanV" wrote in message
...
Hi Neil,

For the "staff" problem, change the AS part of the query to

whatever
you
like - AS simply is giving the calculated field a name.

Perhaps:

TRANSFORM First(TBL_Main.Staff) AS StaffName
SELECT TBL_Main.Date, TBL_Main.Room
FROM TBL_Main
GROUP BY TBL_Main.ID, TBL_Main.Date, TBL_Main.TimeIn,

TBL_Main.Room
PIVOT TBL_Main.TimeOut;







"Neil M" wrote in message
...
Right here the layout -
I got a report named REPORT which is linked to "Qry_Report"

query

Here is the SQL for the Query;
TRANSFORM First(TBL_Main.Staff) AS FirstOfStaff
SELECT TBL_Main.Date, TBL_Main.Room
FROM TBL_Main
GROUP BY TBL_Main.ID, TBL_Main.Date, TBL_Main.TimeIn,
TBL_Main.Room
PIVOT TBL_Main.TimeOut;

Is there a special way to display the times, staff (names)

and
other
info
on
the REPORT, as it appears that if I select field list on the
report
design
only the actual dates and data is shown and not the field

names
that
I
can
recognise like Staff, TimeIn, TimeOut, etc..

Hope this explains it a little better.


"SusanV" wrote in message
...
Sorry should have stated what control in the report is using
"staff" -
not
field.

"SusanV" wrote in message
...
What field is using "Staff" as a record source (view via
Properties
of
fields)? What is the equivalent field in your crosstab
query?
Change
the
source to the appropriate field.
--
hth,
SusanV


"Neil M" wrote in message
...
thanks. got the crosstab query done and it appears to be
running
ok
but
when i link it to my report it states that it doesn't
recognise
'staff'
as a
valid field name or expression.
any suggestions on how (lame man's terms) i link it to

the
report -
as
the
report field name list does not have staff on it.

Thanks,


Neil


"SusanV" wrote in message
...

Create a query containing all the data you will need to
display,
either
from
a table or tables, or an existing query. Save it and

while
in
Design
View,
change it from a select query to a Crosstab query either
in
the
Query
Menu
or from the toolbar.

Choose "Row Heading" for the times
Choose "Column Heading" for the dates
Choose Value for the Name, and set the "Total" option

from
GroupBy
to
Either
First or Last.

Open the query in datasheet view and you should see the
layout
you
are
looking for. Save the query, and use that query as the
data
source
for
your
report.

Here's a link to a site with a tutorial in case I wasn't
clear:







http://www.jmu.edu/computing/ittrain...crosstab.shtml


--
hth,
SusanV



"Neil M" wrote in message

...
sorry can you eplain a bit more what you mean and how
roughly
to
do
this.
thanks.


"SusanV" wrote in message
...
Hi Neil,

I think if you use as the report source a crosstab
query
you'll
be
able
to
get the report you're looking for.
--
hth,
SusanV

"Neil M" wrote in

message

...
Hiya all,

I have yet another problem I can't seem to get my
head
around.

Here's the problem...

Part of the same database is a system to book of
rooms
dependent
on
days.
The database works fine and I can print normal

'listing'
type
reports
which
will make do for now.

But what I really would like to do is print off a

report
where
it
has
the
times and dates like a chart
(like below

ROOM (header)

DATE DATE DATE DATE DATE
DATE
DATE
(Mon Sun)
TIMES
in hours NAME
here
from
8am to
8pm?

Get my drift, so the block of staff names appear in

the
times
theyt
have
the
room booked.

Anyone got any suggestions on how to do this. I

have
found
an
example
database for this sort of thing (Doctors, etc) but

I
can't
seem
to
get
my
head around the rather complicated coding and

reporting.
I
need
to
understand how its done not just how to do it.

Thanks,


Neil



































  #19  
Old November 18th, 2005, 01:26 PM posted to microsoft.public.access.forms
external usenet poster
 
Posts: n/a
Default Weekly Charts in Reports

Good morning Neil,

I went through all the previous posts in this thread and saw no attachment,
but no bother - I downloaded your db and took a look. I changed the layout
of the query and think I have what you're looking for - I've emailed you the
zipped mdb so you can see the actual query and report layout.

Here's the SQL I used for the Crosstab Query:

TRANSFORM Last(TBL_Main.Staff) AS LastOfStaff
SELECT TBL_Main.Room, TBL_Main.TimeIn, TBL_Main.TimeOut
FROM TBL_Main
GROUP BY TBL_Main.Room, TBL_Main.TimeIn, TBL_Main.TimeOut
PIVOT TBL_Main.Date;

The output in the report, ordered by Room and then by Time In:

Room TimeIn TimeOut 1/1/2005 1/3/2005 1/4/2005 2/4/2005
__________________________________________________ _____
Common 9:00 10:00 NEIL NEIL
Common 10:00 12:00
NEIL
JobShop 9:00 10:00 NEIL NEIL



--
hth,
SusanV



"Neil M" wrote in message
...
i use outlook express to view the newsgroups, forum name is
microsoft.public.access.forms


I have also uploaded it to;
http://neil.mansell.users.btopenworld.com/RBS.zip


thanks.


"SusanV" wrote in message
...
If you uploaded it to the forum, I should be able to get it there - which
forum? Can't you send a link?



"Neil M" wrote in message
...
ok ill upload to my private space and let you know tomorrow morning.
thanks.

"SusanV" wrote in message
...
Sorry but I don't view this via the web - I use a newsreader so the
attachment is not available. Do you have a link I could follow?

Susan

"Neil M" wrote in message
...
np - i have uploaded to the forums (attached to my last message)

thanks.

neil


"SusanV" wrote in message
...
I'm sorry - I didn't realize there was something you wanted me to
view -
uploaded it where?


"Neil M" wrote in message
...
did you have a look at it susan?


"Neil M" wrote in message
...
I have changed that but still getting the same error.
I have uploaded the database so you can physically have a look?

My reports don't seem to be in chart form just in list of room

and
dates,
etc..

Thanks,

Neil

"SusanV" wrote in message
...
Hi Neil,

For the "staff" problem, change the AS part of the query to
whatever
you
like - AS simply is giving the calculated field a name.

Perhaps:

TRANSFORM First(TBL_Main.Staff) AS StaffName
SELECT TBL_Main.Date, TBL_Main.Room
FROM TBL_Main
GROUP BY TBL_Main.ID, TBL_Main.Date, TBL_Main.TimeIn,
TBL_Main.Room
PIVOT TBL_Main.TimeOut;







"Neil M" wrote in message
...
Right here the layout -
I got a report named REPORT which is linked to "Qry_Report"
query

Here is the SQL for the Query;
TRANSFORM First(TBL_Main.Staff) AS FirstOfStaff
SELECT TBL_Main.Date, TBL_Main.Room
FROM TBL_Main
GROUP BY TBL_Main.ID, TBL_Main.Date, TBL_Main.TimeIn,
TBL_Main.Room
PIVOT TBL_Main.TimeOut;

Is there a special way to display the times, staff (names)

and
other
info
on
the REPORT, as it appears that if I select field list on the
report
design
only the actual dates and data is shown and not the field

names
that
I
can
recognise like Staff, TimeIn, TimeOut, etc..

Hope this explains it a little better.


"SusanV" wrote in message
...
Sorry should have stated what control in the report is
using
"staff" -
not
field.

"SusanV" wrote in message
...
What field is using "Staff" as a record source (view via
Properties
of
fields)? What is the equivalent field in your crosstab
query?
Change
the
source to the appropriate field.
--
hth,
SusanV


"Neil M" wrote in message
...
thanks. got the crosstab query done and it appears to
be
running
ok
but
when i link it to my report it states that it doesn't
recognise
'staff'
as a
valid field name or expression.
any suggestions on how (lame man's terms) i link it to

the
report -
as
the
report field name list does not have staff on it.

Thanks,


Neil


"SusanV" wrote in message
...

Create a query containing all the data you will need to
display,
either
from
a table or tables, or an existing query. Save it and

while
in
Design
View,
change it from a select query to a Crosstab query
either
in
the
Query
Menu
or from the toolbar.

Choose "Row Heading" for the times
Choose "Column Heading" for the dates
Choose Value for the Name, and set the "Total" option

from
GroupBy
to
Either
First or Last.

Open the query in datasheet view and you should see the
layout
you
are
looking for. Save the query, and use that query as the
data
source
for
your
report.

Here's a link to a site with a tutorial in case I
wasn't
clear:







http://www.jmu.edu/computing/ittrain...crosstab.shtml


--
hth,
SusanV



"Neil M" wrote in message

...
sorry can you eplain a bit more what you mean and how
roughly
to
do
this.
thanks.


"SusanV" wrote in message
...
Hi Neil,

I think if you use as the report source a crosstab
query
you'll
be
able
to
get the report you're looking for.
--
hth,
SusanV

"Neil M" wrote in

message

...
Hiya all,

I have yet another problem I can't seem to get my
head
around.

Here's the problem...

Part of the same database is a system to book of
rooms
dependent
on
days.
The database works fine and I can print normal
'listing'
type
reports
which
will make do for now.

But what I really would like to do is print off a
report
where
it
has
the
times and dates like a chart
(like below

ROOM (header)

DATE DATE DATE DATE
DATE
DATE
DATE
(Mon Sun)
TIMES
in hours NAME
here
from
8am to
8pm?

Get my drift, so the block of staff names appear
in
the
times
theyt
have
the
room booked.

Anyone got any suggestions on how to do this. I

have
found
an
example
database for this sort of thing (Doctors, etc) but

I
can't
seem
to
get
my
head around the rather complicated coding and
reporting.
I
need
to
understand how its done not just how to do it.

Thanks,


Neil





































 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Charts in Access Reports Bill Setting Up & Running Reports 4 September 13th, 2005 02:46 AM
Send Access reports via email - password protect [email protected] Setting Up & Running Reports 2 May 25th, 2005 12:28 PM
Blank Pages at the beginning of Parent Report with 5 child or sub reports Cyndy Sheehan Setting Up & Running Reports 1 March 4th, 2005 11:36 PM
Distribute dynamically charts with linked data as stand-alone charts Richard Charts and Charting 2 March 31st, 2004 05:16 PM
Creating a large number of charts automatically (repost) Francine Kubaka Charts and Charting 0 September 21st, 2003 09:16 PM


All times are GMT +1. The time now is 01:28 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.