Printing a Report with multiple Primary key entries in 1 row

**jimatqsi** · Dec 2 '10, 02:31 PM

Why are there two tables? They both contain the same data, but one has a comment column the other does not. How are these tables related?

If you know how to join these two tables (it's not obvious to us reading your post how that should be done) you can easily make a report to read this data and sort by date/time. But you have multiple readings going across the page. Can there be more than 1 reading at the same date and time? If so, what/who do they represent? There is no other identifying information, so what would these readings mean?

Do the indexed auto-numbers you are using as primary keys have any lasting value? If the table becomes corrupt you could lose those values. That's one reason why using these auto-generated numbers as keys is a very bad idea.

Jim

**kadavu** · Dec 3 '10, 12:43 AM

Jim I would like to show you a picture of the forms in question, but i do not know how to include a picture in this system.

Brian

**jimatqsi** · Dec 3 '10, 05:26 PM

kadavu,
See the series of icons above the place where you type your message. There is an icon for inserting an image into your message.

I doubt a picture of the form will go very far in explaining the relationship between these tables. Please add as much relevant information as you can.

Thanks,
Jim

Jim

**kadavu** · Dec 4 '10, 01:54 AM

Jim i saw the icon to insert an image but it wants the image to be in an url and when i change that to a folder on my computer it just puts the following.
""[IMG]D:\Access Forms.JPG[/IMG]"" without the quotes.
So i will try to explain what i am doing.
4 readings are taken each time and the table "tblReadingDate Time" and a form "frmReadingDate Time" is linked to it, it being the main form, shown in form view.
"tblReading s" and a form "frmReading s subform" is linked to it, it being the sub-form in the main form.
When i open the main form i input the date and time, also i choose to input what i think is the most appropriate values for the other readings 3 or 4 fields.
The table "tblReading s" set up as a sub-form of "tblReadingsDat eTime". The subform in database view gets it's date and time automatically from the date and time imputed in the main form. Then i Tab to the sub-form and input the values for the other 3 fields, Tab and it drops down to a new row (record) and i input the values for the 3 fields etc. There are 4 records for each date and time.
So the entries in the tables would look like this.
"tblReadingDate Time"
ID Date Time Systolic Diastolic Pulse Comments
29 1/10/2010 8:15 152 88 66
30 2/10/2010 10:20 145 86 63 After Run
ETC

"tblReading s"
ID Date Time Systolic Diastolic Pulse
77 1/10/2010 8:15 163 91 65
78 1/10/2010 8:15 155 90 65
79 1/10/2010 8:15 157 89 66
80 1/10/2010 8:15 152 88 67
81 2/10/2010 10:20 150 89 58
82 2/10/2010 10:20 149 91 60
83 2/10/2010 10:20 145 86 63
84 2/10/2010 10:20 146 85 63

And what i am trying to do is print out a report from "tblReading s" like this.

1/10/2010 8:15 163 91 65 155 90 65 157 89 66 152 88 67
2/10/2010 10:20 150 89 58 149 91 58 145 86 63 146 85 63

I can make a query that groups by Date Time and Average of all readings for that date time. But i am stumped as to how to do the other report that i want.

thank you.

Brian

**jimatqsi** · Dec 7 '10, 03:28 PM

Brian,
What you will need to do is make a report that grabs the data from the first table only. Sort the report by date and time of each record in the first table. Put bound text boxes for the id, date, and time. Then put 12 unbound text boxes. Name each of these for the 3 fields you want (I assumed in code you would call them txtsystolic1, txtdiastolic1, txtpulse1, txtsystolic2 ....).

Then, in the format event of the detail section of the report you should have this code, or something similar:

Code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim dbs As dao.Database
Dim rsReadings As dao.Recordset
Dim strSQL As String
Dim strCriteria As String

   On Error GoTo Detail_Format_Error

Set dbs = CurrentDb
strSQL = "Select * from tblReadings where Date=#" & Me!txtDate & "# and Time=#" & Me!txtTime & "#"

Set rsReadings = dbs.OpenRecordset(strSQL, dbReadOnly)

rsReadings.MoveFirst
Me!txtSystolic1 = rsReadings!Systolic
Me!txtDiastolic1 = rsReadings!diastolic
Me!txtPulse1 = rsReadings!pulse

rsReadings.MoveNext

Me!txtSystolic2 = rsReadings!Systolic
Me!txtDiastolic2 = rsReadings!diastolic
Me!txtPulse2 = rsReadings!pulse

rsReadings.MoveNext

Me!txtSystolic3 = rsReadings!Systolic
Me!txtDiastolic3 = rsReadings!diastolic
Me!txtPulse3 = rsReadings!pulse

rsReadings.MoveNext

Me!txtSystolic4 = rsReadings!Systolic
Me!txtDiastolic4 = rsReadings!diastolic
Me!txtPulse4 = rsReadings!pulse

rsReadings.Close
Set rsReadings = Nothing
Set dbs = Nothing

Exit Sub

On Error GoTo 0
   Exit Sub

Detail_Format_Error:

    'MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Detail_Format of VBA Document Reportname "
     Resume Next

End Sub

It's not the keenest report in the world, but based on the database design and forms you've got, you'll have do it like this. You'll have to pay attention to the line where I put a value into strSQL. Obviously "Date" and "Time" are Access keywords so they cannot possibly be the real column names for where you are storing those values. And I assumed I could find those values in me!txtDate and me!txtTime, which is only correct if you say it is correct. I mean it's based on what you name your text fields in the report.

Hope this helps. Good luck!

Jim

**kadavu** · Dec 8 '10, 10:32 AM

Thanks Jim it works to a point, some of the records sets it pulls out are correct, but some of the time it shows the same set of records for a number of different dates. So i think i will give up on this as the data base probably need re-designing, so make do with the reports i can all ready get out such as a from to date of lowest readings and a from to date report of the average of the 4 readings for each date.

Thank you again it has taught me alot.

Brian