Building a report

[respman]

I have 1 very simple table with 3 columns:
- Date of Test
- Patient Type (Inpatient, Outpatient, Research)
- Test Type (we'll call them "a", "b", "c", etc.)

I've created queries that give me a count of different tests performed on any given day(s) for any given patient type (eg. how many "a" tests were performed on Outpatients on any given date or date range), but what I'd like to do is have a report that will summarize all of the data in a table. In other words, the table in the report will have a column with Test Type, with each of the tests listed below it in rows ("a", "b", "c", ...). Beside each Test Type, I will have a count of how may of these tests were done. The other thing I would like is to set my desired date range at the top of the report by simply clicking on a calendar (start date, end date). I've tried different suggestions, but the closest I can get on the date issue is to be prompted to select a StartDate and EndDate when I open the report (and even that is not working properly). Is it possible to edit my individual data source queries by selecting my date(s) from the report itself? I haven't even started to work on getting the query data into the report table. I hope I have explained myself sufficiently. Any suggestions would be greatly appreciated.

Cheers

 

[JHB]

Post a stripped version with some sample data, (zip it) + a picture/printscreen how you want it the result.

 

[respman]

Thank you for your reply, JHB. I've attached a sample DB and a spreadsheet with roughly how I would like the report to look. As you see, I'd like to set my search date range from within the report itself (I'd like to have a calendar pop-up, so I can just click on the dates, if possible). The result of the report would give me how many times each type of test was performed during the date range. Once I figured all this out, I'd like to add to the table to include the fee for each test (that's a set value) and have the report calculate the totals for each test and then the overall totals. Many thanks for any suggestions.

 

[JHB]

Hmm - what is this, Excel data copied into a table with to many fields?
You can't store multiply test in same field, each test needs one record/row.
If you want to create databases in the future, then you need to some search on the Internet how to arrange data in tables, relationships, creating queries/forms/reports etc. And give up the Excel way storing data, because Excel and databases are really 2 different ways of storing and organising data.
I've made a solution for you, open the only form in the attached database and fill in data the controls, then click the button.

 

[respman]

Thank you so much for taking the time to respond and for the advice. I have a lot of work to do.

 

[respman]

Hi JHB,

I attempted opening the report from the form that you created, but it's not working. If I go into the Properties for the "Open Report" command button and then go into the Visual Basic editor for the event procedure, there is one line that appears to have either a bracket missing or an extra bracket (is the bracket on the end extra or missing a partner?). I obviously know nothing about writing code, so forgive me if I'm mistaken.

& "WHERE [Patient Type]='" & Me.Patienttype & "' AND [Test Date] Between #" & DateValue(Format(Me.StartDate, "m/dd/yyyy")) & "# And #" & DateValue(Format(Me.EndDate, "m/dd/yyyy")) & "#")

The only way the report will open for me is if I just open it directly by double-clicking on it, but the result is for the entire source table and I can't specify a "patient type" or set a date range.

Cheers

 

[JHB]

..
If I go into the Properties for the "Open Report" command button and then go into the Visual Basic editor for the event procedure, there is one line that appears to have either a bracket missing or an extra bracket (is the bracket on the end extra or missing a partner?). I obviously know nothing about writing code, so forgive me if I'm mistaken.

& "WHERE [Patient Type]='" & Me.Patienttype & "' AND [Test Date] Between #" & DateValue(Format(Me.StartDate, "m/dd/yyyy")) & "# And #" & DateValue(Format(Me.EndDate, "m/dd/yyyy")) & "#")

I don't know what happened, but the line of code you show is only part of the code that should be there.
The whole code looks like this! (I have attached the database again, try it change anything.)

Private Sub RunFillTableQuery_Click()
  Dim dbs As Database, rst As Recordset, rstInsert As Recordset, strSplit() As String, x As Integer
  
  Set dbs = CurrentDb
  dbs.Execute ("Delete * FROM [PFT statsWithNumber]")
  dbs.Execute ("Delete * FROM [PFT stats TestTable]")

  dbs.Execute ("INSERT INTO [PFT statsWithNumber] " _
  & "SELECT [PFT stats].* FROM [PFT stats] " _
  & "WHERE [Patient Type]='" & Me.Patienttype & "' AND [Test Date] Between #" & DateValue(Format(Me.StartDate, "m/dd/yyyy")) & "# And #" & DateValue(Format(Me.EndDate, "m/dd/yyyy")) & "#")

  Set rst = dbs.OpenRecordset("PFT statsWithNumber")
  If Not rst.EOF Then
    Set rstInsert = dbs.OpenRecordset("PFT stats TestTable")
    Do
      strSplit = Split(rst![Tests], ";")
      For x = 0 To UBound(strSplit)
        rstInsert.AddNew
        rstInsert![Tests] = RTrim(strSplit(x))
        rstInsert![NoPatient Type] = rst![NoPatient Type]
        rstInsert.Update
      Next
      rst.MoveNext
    Loop Until rst.EOF
    DoCmd.OpenReport "TheReport", acViewPreview, , , , Me.Patienttype & ";" & Me.StartDate & ";" & Me.EndDate
  End If
End Sub

 

[respman]

Working now. I really appreciate the time you spent helping me. You're a good person!!

 

[JHB]

You're welcome - good luck.