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. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 |