Results 1 to 15 of 15
  1. #1
    Join Date
    Apr 2003
    Posts
    280

    Unanswered: How do you display data horizontally on a report?

    Here a report that show the data vertically by grouping the data together.

    http://www.members.cox.net/tripnotic...CodeReport.jpg

    I would like to show it horizontally. Here is a sample

    http://www.members.cox.net/tripnotic...odeReport2.jpg

    There is a code for each one. Example
    Ultra = 1
    Very = 2
    High = 3
    Medium = 4
    Low = 5

    The percentage is taken by total of the row and divided by each column for it.

    I would like to display a zero for the column that doesn't have any for a specific month.

    Here is my query.

    SELECT mwtrn.[Procedure Code], Facility.FacilityName, DatePart("m",[Date From]) AS MonthFrom, DatePart("m",[Date To]) AS MonthTo, (DatePart("d",[Date To])-DatePart("d",[Date From]))+1 AS NumberofDays, DatePart("yyyy",[Date From]) AS YearFrom, DatePart("yyyy",[Date To]) AS YearTo, Month.MonthText, MWPRO.[Code 3], mwtrn.[Date From], mwtrn.[Date To]

    FROM [Month], (Facility INNER JOIN mwcas ON Facility.FacilityCode = mwcas.Facility) INNER JOIN (MWPRO INNER JOIN mwtrn ON MWPRO.[Code 1] = mwtrn.[Procedure Code]) ON mwcas.[Case Number] = mwtrn.[Case Number]

    GROUP BY mwtrn.[Procedure Code], Facility.FacilityName, DatePart("m",[Date From]), DatePart("m",[Date To]), (DatePart("d",[Date To])-DatePart("d",[Date From]))+1, DatePart("yyyy",[Date From]), DatePart("yyyy",[Date To]), Month.MonthText, MWPRO.[Code 3], Facility.FacilityID, Month.MonthValue, mwtrn.[Date From], mwtrn.[Date To]

    HAVING (((DatePart("m",[Date From]))=[Forms]![ExpenseForm]![cboMonth]) AND ((DatePart("m",[Date To]))=[Forms]![ExpenseForm]![cboMonth]) AND ((DatePart("yyyy",[Date From]))=[Forms]![ExpenseForm]![cboYear]) AND ((DatePart("yyyy",[Date To]))=[Forms]![ExpenseForm]![cboYear]) AND ((MWPRO.[Code 3])="1" Or (MWPRO.[Code 3])="2" Or (MWPRO.[Code 3])="3" Or (MWPRO.[Code 3])="4" Or (MWPRO.[Code 3])="5") AND ((Facility.FacilityID) Like [Forms]![ExpenseForm]![lstFacilityID]) AND ((Month.MonthValue)=[Forms]![ExpenseForm]![cboMonth]));

    i hope my explanation is clear. Thanks in advance.

  2. #2
    Join Date
    Apr 2003
    Posts
    280
    Ok, I create a query that will work but I was wondering, can I put a custom query in a report.

    SELECT Count(qryProcedureCode.[Procedure Code]) AS [CountOfProcedure Code], qryProcedureCode.[Code 3]
    FROM qryProcedureCode
    GROUP BY qryProcedureCode.[Code 3]
    HAVING (((qryProcedureCode.[Code 3])="2"));

  3. #3
    Join Date
    Apr 2003
    Posts
    280
    Dang, creating custom query will not work for me. Anyone got any ideas to make the data to display horizontally?

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    The only way that I know of is to design the table structure to reflect the results in a horizontal fashion ... BTW, just showing a report doesn't impart the tricks used to generate it ... Generally you can't do this if the # of columns is unknown but, if they are finite then you can plan for a MAX and proceed accordingly ... Another thought would be for a subreport that does the same thing ...

  5. #5
    Join Date
    Apr 2003
    Posts
    280
    The column is finite. I did some searching on google and someone did much about the subreports. I not very familiar with the subreport. I have try it though but I can't seem to display it horizontally. How do you go about doing it?

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    DOH! Try a tabular subreport ... or a crosstab report ... I've not used them myself but they can be done ...

  7. #7
    Join Date
    Apr 2003
    Posts
    280
    Originally posted by M Owen
    DOH! Try a tabular subreport ... or a crosstab report ... I've not used them myself but they can be done ...
    I have try it and it won't work for me. I'm doing VBA now, I think this is the best way to get what I want exactly but I can't connect to the query via VBA recordset method.

    Dim conndb as ADODB.Connection
    Dim rsqryProCode as ADODB.Recordset

    Set conndb = CurrentProject.Connection
    Set rsqryProCode = New ADODB.Recoredset

    rsqryProCode.Open "qryProcedureCode", conndb

    The error is" Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

    I can't see what the problem is with this connection code. I manually click on the query and it work but the VBA code doesn't. I have been using this for a half a year now and I cna't what wrong with it.

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Lans,

    I do believe that you have to return a recordset on a connection Execute to run a "stored procedure" in Access.

  9. #9
    Join Date
    Apr 2003
    Posts
    280
    It just an 'Select' query, so it should work with the code. Anyways, what do you mean by a return recordset?

  10. #10
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    .Execute returns an optional "Records Affected" recordset as a result of running an SQL action query.

  11. #11
    Join Date
    Apr 2003
    Posts
    280
    Originally posted by M Owen
    .Execute returns an optional "Records Affected" recordset as a result of running an SQL action query.
    Sorry, i 'm still confuse with this. CAn you write me a sample code for it. conndb.Execute(CommandText as String, [RecordsAffected], [Options as Long = -1]) as Recordset <----what do i have to put in here?

  12. #12
    Join Date
    Apr 2003
    Posts
    280
    Well, I have gone a different route. I would have a column for each ProcedureCode in the query like iif([Code 3] = 5), 1, 0), then I would add this up. HOpe this make sense to anyone is reading this and interested in doing this.

  13. #13
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    You don't "put" anything there ... That is the return value (it returns a recordset) ... So:

    Dim MyRecSet As ADODB.Recordset

    MyRecSet=conndb.Execute "qryProcedureCode", ,AdCmdText

  14. #14
    Join Date
    Apr 2002
    Location
    Illinois
    Posts
    133

    Re: How do you display data horizontally on a report?

    Did you ever find a solution?
    SteveH

  15. #15
    Join Date
    Apr 2003
    Posts
    280

    Re: How do you display data horizontally on a report?

    Originally posted by SteveH
    Did you ever find a solution?
    Yes, I did..

    here an example of my data look like.

    ProcedureCode FacilityName Code 3
    PT2423 North 1
    OT2342 West 2
    ST2342 EAST 3
    OT2342 South 4
    ST5353 West 1

    I would create a query for it with a custom field.
    1=Ultra, 2 = Very, 3=High, 4=Medium

    In the query, I would have an custom field for Ultra, Very, High, and Medium.

    Ultra: IIf([Code 3] = "1"), 1,0) Very:IIf([Code 3] = "2"), 1,0), etc..
    This code is saying, if the field for Code 3 is equal to 1 then put a number 1 else put 0.

    Do this for the others for the custom field.. Once I'm done with it, I would add it up for each rows and group it by facility name on the report. I hope this is clear for you

Posting Permissions

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