Page 1 of 5 123 ... LastLast
Results 1 to 15 of 61
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Tables in Reports (2002)

    Hi Again,

    I'm wondering if it is possible to set up a table in a report or a calendar looking report. The database I'm working on has a field called [DOW] (for day of week) and field called [client name]. I need the report to show what clients need to be seen on what day of the week. The report needs to open up with columns for each day of the week, with the corresponding client names in the correct column. Is this even possible and if so, how do I start to go about it?

    Thanks,
    Leesha

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Tables in Reports (2002)

    I think you're looking for a crosstab query. Here's an example that presents the customerID for orders from the Northwind Orders table by day of week of order date for each customer for a week between 8/4/94 and 8/11/94. I think this is something like what you're attempting.
    <pre>TRANSFORM First(Orders.CustomerID) AS FirstOfCustomerID
    SELECT Orders.CustomerID, Orders.OrderDate
    FROM Orders
    WHERE Orders.OrderDate Between #8/4/1994# And #8/11/1994#
    GROUP BY Orders.CustomerID, Orders.OrderDate
    PIVOT Format([OrderDate],"ddd") In ("Mon","Tue","Wed","Thu","Fri","Sat","Sun");</pre>

    Charlotte

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Tables in Reports (2002)

    Thanks Charlotte. I'll give this a try. I've yet to have any luck with crosstab queries so this will be interesting, but I'm determined to get this report to work. My next question before I try this........the user will be pushing a button to run the report. I see by your example that there is a date range in the query, which makes sense. How would I go about prompting the user to insert the date range needed so its picked up by the query? I've done this with basic/generic queries and wanted to know if the process is the same or if its possible.

    Thanks,
    Leesha

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Tables in Reports (2002)

    The best way to prompt the user for dates is to use a small form, say frmSelectDates with two text boxes, say txtStartDate and txtEndDate. Set the Format for both to one of the date formats, so that Access will check that the user enters a valid date.
    In the query, you can refer to the text boxes on the form. In Charlotte's example:

    ...
    WHERE Orders.OrderDate Between [Forms]![frmSelectDates]![txtStartDate] And [Forms]![frmSelectDates]![txtEndDate]
    ...

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Tables in Reports (2002)

    Cool! I've done this before but never with a cross tab query so I wasn't sure if the approach was the same.

    Thanks,
    Leesha

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Tables in Reports (2002)

    If you are using this in a crosstab query, it is probably necessary to declare the parameters explicitly, otherwise they may not be recognized.
    - Open the query in design view.
    - Select Query | Parameters...
    - Enter the first parameter exactly as used in the query - in my example [Forms]![frmSelectDates]![txtStartDate]
    - Select Date/Time as data type.
    - Repeat for the other parameter.
    - Click OK.

  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Tables in Reports (2002)

    OK I'll give it a shot. I've got a feeling this could take me days to figure out but its all a learning experience!

    Thanks,
    Leesha

  8. #8
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Tables in Reports (2002)

    Hi Charlotte,

    Well I hope you have the patience of a saint! Here goes. I wasn't sure if the infomation you gave me was to be used in the actual query itself or if it was code to be used in conjunction with the query, as I've never sucessfully done a crosstab query. I am proud to say I did get a crosstab query to run as I wanted so I'm pleased with that. I created a report using the wizard to speed up the process and sure enough it sets up the columns for the day of the week with the corresponding client names in them just as I wanted. The only problem is that client names do no line up side by side. For example, if Monday has 6 clients in it, Tuesday's clients start on row 7 when I actually want them to line up next to Monday and so on. I noted that your suggestion mentioned pivot format however I didn't see pivot format as an option for reports.

    Thanks,
    Alicia

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Tables in Reports (2002)

    What I posted was the SQL for a crosstab query. PIVOT is a keyword in a crosstab query, not a format or an option of reports. To get them in the same rows, you would have to use something else as a row header, like the person who was going to see them that day. If you post the SQL for your crosstab query, we might be able to help tweak it for you.
    Charlotte

  10. #10
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Tables in Reports (2002)

    Hi Charlotte,

    Da, I should've figured that was the SQL code. Brain dead I guess. Anyway, here is the SQL I'm presently using. As I said earlier I would like the the rows in the columns to line up if this possible and also, is there anyway to make the field grow down in the column. For example, long names, or if I include time and other data the staff need on each client. I don't want it to expand across into the next column, but rather down into the same column.

    Thanks!

    Leesha
    TRANSFORM Last([cl_last] & ", " & [cl_first]) AS NAME
    SELECT tblSamVisitReport.care_date
    FROM tblSamVisitReport
    WHERE (((tblSamVisitReport.em_last)="turner"))
    GROUP BY tblSamVisitReport.care_date, tblSamVisitReport.paynm, tblSamVisitReport.from_time, tblSamVisitReport.to_time
    PIVOT tblSamVisitReport.dayofwk In ("MON","TUE","WED","THU","FRI","SAT","SUN");

  11. #11
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Tables in Reports (2002)

    Hi Again,

    Just wanted to save you some time. I played with the sample report I did and when I let the property of "can grow" to yes it did just grow within the column vs across the page so this takes care of one problem. Now to just get the rows to line up. I appreciate your helping on this!

    Leesha

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Tables in Reports (2002)

    I'm not sure of the problem. You want items to line up side by side for the client, the same employee, or what? Keep in mind that a crosstab is a kind of grouping query, so differences like an appointment for the same client for two different days will result in a record for each of those days. Is that what you're dealing with?
    Charlotte

  13. #13
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Tables in Reports (2002)

    The calendar is being run on an individual basis for employees. In the example I sent you I believe the query was being run for an employee by the last name of Turner. He has multiple clients to see during the week. It is the clients that I want to line up in the DOW columns. There will be clients that are sometimes seen daily, so having a record for each of the days is exactly what I need. The query appears to be running the data correctly , with the exception of not lining up staggering the client names in the rows. From what I can tell, If I didn't have to have a "row" setting I'd be OK but there didn't seem to be a way to run the query without defining a row property.

    Leesha

  14. #14
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Tables in Reports (2002)

    Hi Charlotte,

    I've been playing with the query and found that if I took out some of the time related fields the query actually lines up the clients "almost" like I need them. It will work, so I didn't want you to spend time trying to find a solution. What I have now due to your help is really great!

    One last question if I may......this is hard to explain. In the query I have joined the fields together in one column. They include the client's name and their pay source. I have set the property of the control to "can grow" which is working fine. It looks pretty sloppy though when it splits the data in the middle of a word. I'm wondering if there is anyway to code the criteria in query to tell it where to split to another line. For example, to look like this:

    John Doe
    Medicare

    VS looking like this:

    John Doe Med
    icare

    Thanks!

    Leesha

  15. #15
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Tables in Reports (2002)

    No, a crosstab query requires at least one row header. There isn't any way to force the crosstab to return the data the way you want it directly as long as you have the data returned the way you are doing it. I'm going to stick with my example because I have no way to recreate your data to tweak your SQL. The following SQL returns the customer orders for the period by weekday but this time the row heading is the employee ID, and you'll see that orders for the same employee client combination show up in the same row:

    <pre>TRANSFORM First(Orders.CustomerID) AS FirstOfCustomerID
    SELECT Orders.EmployeeID
    FROM Orders
    WHERE (Orders.OrderDate Between #8/4/1994# And #8/11/1994#)
    GROUP BY Orders.EmployeeID
    PIVOT Format([OrderDate],"ddd") In ("Mon","Tue","Wed","Thu","Fri","Sat","Sun");.</pre>


    Again, if you go back to the first SQL I posted and remove the OrderDate from the SELECT portion, you'll see the orders for a customer on the same line for different days.

    <pre>TRANSFORM First(Orders.CustomerID) AS FirstOfCustomerID
    SELECT Orders.CustomerID
    FROM Orders
    WHERE (((Orders.OrderDate) Between #8/4/1994# And #8/11/1994#))
    GROUP BY Orders.CustomerID
    PIVOT Format([OrderDate],"ddd") In ("Mon","Tue","Wed","Thu","Fri","Sat","Sun");</pre>

    Charlotte

Page 1 of 5 123 ... LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •