One-to-many problem, multiple row per record (1 Viewer)

[lostwithaccess]

It seemed that I was opening my report in report view, yet the filter was set to open it in print preview, so it was the changeover that was causing the menu to open.

I am now trying to make another report to show all information related to each person stored in the database, the report will then have another frmFilter to filter by last name.

The problem I have got is how to pull all the relevant fields from the multiple tables and display them in one place. I tried making a query by building practically a copy of my relationship table, pulling all the relevant fields, but when I ran my query it had no records in it, it seems to be filtering out records that do not have an entry in each field.

I have heard a bit about subreports in other parts of the forum, but even when I try taking my query back a stage so it only querys 1 table, it is still filtering out records.

My aim is to have an page displaying all related information for whichever staff member you choose.

 

[jzwp22]

...but when I ran my query it had no records in it, it seems to be filtering out records that do not have an entry in each field.

If a joined field (foreign key) is null then that record will not be included in the recordset returned by the query. You can adjust the join type between the tables in the query grid to select all records in one table and only those records in the other that match rather than selecting records where only matched records are returned. If you double click the join line between the two tables in design grid view, you will see 3 options from which to choose.

 

[lostwithaccess]

I didn't even know that option was there! Thanks very much. Only the 3rd option would work. I had caused a slight problem by having a field from another table in the query, but removed that and it's all fine.

Because I have related information stored across a number of tables, should I query each in the same way then add all the information to a Report as sub reports? (never done this before)

I presume the existing relationships will carry through so that when I filter by name all the records in the sub reports that are not related will be filtered away?

 

[jzwp22]

I presume the existing relationships will carry through so that when I filter by name all the records in the sub reports that are not related will be filtered away?

That should be the case.

Because I have related information stored across a number of tables, should I query each in the same way then add all the information to a Report as sub reports? (never done this before)

It depends on your relationships & what you want to show in your report. If the primary table of the subreport is also the one side of multiple one-to-many relationships then you may need a subsubreport. You will have to experiment a little to see if the report you get meets what you want to see.

 

[lostwithaccess]

I have spent a fair amount of time learning/building my subreports, which although is not particularly attractive, it is holding most of the right information.

However the problem I am having is that when I add a new record, the relevant subreport is updating to show this, but also duplicating the whole subreport below. I am now running out of ideas of where to look to correct the problem. The data is from a related table as there can be more than one piece of data related to each main record.

I am not sure of the best way to explain this, but here is the sql of the query if that helps:

SELECT tblStaffInstitutionNatureOfRelationship.[fkStaffInstitutionID], tblStaffInstitutionNatureOfRelationship.[pkStaffInstNatureRelateID], tblNatureOfRelationship.txtNatureOfRelationship
FROM tblNatureOfRelationship INNER JOIN tblStaffInstitutionNatureOfRelationship ON tblNatureOfRelationship.pkNatureOfRelationshipID=tblStaffInstitutionNatureOfRelationship.fkNatureOfRelationshipID;

And the zipped database is attached. The report is rptStaffDetails-Main

 

[jzwp22]

I'll have to take a look at it tonight since I only have Access 2003 here at work. Is there anything you still want in the report that you do not currently have?

 

[lostwithaccess]

Thank you, that should be everything in there now, but if it is not very clear (I will need to tidy up the format) the report needs to hold everything that could be entered in frmNewStaffEntry, to give a 'full staff record' as such.
Many thanks

 

[jzwp22]

I ended up rebuilding the report for the nature of relationships. I could not figure out what was going on with the one you had. Something was not linked properly as best as I could tell. I did not know what you wanted the report to look like for sure, so I did the best that I could. The DB is attached.

 

[lostwithaccess]

Thank you, that's great, I'm still working on what the report will look like exactly, but now all the info is there its just a case of making it clear to read then building in a name filter.

How did you manage to get the overall size of the database down so much too? It was getting fairly big and the report was getting slow so its much better now.

I owe you a beer!

 

[jzwp22]

When you manipulate forms and reports the database size tends to grow. I usually run the compact and repair utility on a regular basis during development.

 

[lostwithaccess]

Now my DB is very nearly finished, I have had a last minute request to integrate a filter to separate out 4 departments involved.

Currently the data in my reports represents all 4 departments.

Do you know if there is an easyish way to create a filter from my menu screen to apply to all my reports or would it be easier / better to make a combo box filter on each report?

If I had know this originally I expect I would have designed my DB a little differently.

 

[jzwp22]

What I normally do is to use an unbound form that has a combo box that has the different departments and then use that to filter the report's record source when the report is opened via a command button. If you don't have a table that holds the departments as records, you can use a query to get the departments from another table. You will want to use the Distinct keyword in the query if the table includes multiple records for each department

SELECT DISTINCT department
FROM sometable

You can use code similar to what is shown in post #63 to accomplish what you want to do.

I generally prefer not use combo boxes at the report level, but that is just my preference.

 

[lostwithaccess]

Opening on another computer / network access

Very nearly done, but typically one more question...

The idea was (with my limited but increasing) db knowledge to store the database on a shared drive so people access it as and when necessary, all being trusted with read/write access.

But having just tried to view a copy of my database on another computer and it not working I have been finding out about 'split databases' and 'FE and BE'.

As is pretty obvious by now I have never done or heard of this before, but for now I just wanted to check if this is actually what is causing the current problem of opening on another computer.

What happened was: I copied my db onto another computer, but when I open it, my filter form that is coded to open 'on open' of my report will not open, nor will it open directly from the navigation bar, the only way I can get it to open is to open it in design view, then form view from there.

I understand that I will have to learn how to split my database for networked access, but do you think this is the root of the problem I get when copying the db across computers?

I hope this is clear, many thanks in advance

 

[jzwp22]

What happened was: I copied my db onto another computer, but when I open it, my filter form that is coded to open 'on open' of my report will not open, nor will it open directly from the navigation bar, the only way I can get it to open is to open it in design view, then form view from there.

Does the report itself open or does it just hang?

Will the form open if you try to open it from the navigation bar right after you open the database for the first time (before trying to open the report)?

Since the report is impacted by the printer, is there a default printer set up on the computer to which you copied the database?

Other than that, I'm not sure what the problem could be.

With respect to splitting the database, there is a utility within Access that will do it for you. You would put the back end file (tables only) that the utility creates on your server and then you would provide each user a copy of the front end file (all your forms, reports, queries, macros, modules) that they would put on their local hard drive.

 

[lostwithaccess]

I found out what the problem was - I have been developing the database on a pc with dual monitors - whereas the other pc's I tried copying it onto had a single monitor. The form was opening on the 'imaginary monitor', opening it in design view snaps it back to the main screen. I have now set Auto Center property to yes for my forms.

 

[jzwp22]

Glad to hear that you found and solved the problem. Good luck as you move forward.

 

[lostwithaccess]

I have a query selecting all my "staff institution" records together, then a report based on this, but with code in the report filter property leaving me with only the records I want, which works - and these are the only records I ever want this report to show (priority institutions).

But what I have been trying unsuccessfully to do is add a further combo box filter (in a popup form), the problem is when the form filter is applied it ignores my previous filter and pulls results from all records, rather than my pre-filtered range. Is there a way to make this work?

 

[jzwp22]

I would include the original filter together with what is chosen via the combo box in the pop up form. How are you accomplishing the first filter, code? and the second?

 

[lostwithaccess]

Hope this is the right information -

The filter in properties of the report is -

(((qryPriorityPartnerships.txtInstitutionName)="A" Or (qryPriorityPartnerships.txtInstitutionName)="B" Or (qryPriorityPartnerships.txtInstitutionName)="C" Or (qryPriorityPartnerships.txtInstitutionName)="D" Or (qryPriorityPartnerships.txtInstitutionName)="E" Or (qryPriorityPartnerships.txtInstitutionName)="F" Or (qryPriorityPartnerships.txtInstitutionName)="G" Or (qryPriorityPartnerships.txtInstitutionName)="H" Or (qryPriorityPartnerships.txtInstitutionName)="I" Or (qryPriorityPartnerships.txtInstitutionName)="J" Or (qryPriorityPartnerships.txtInstitutionName)="K" Or (qryPriorityPartnerships.txtInstitutionName)="L" Or (qryPriorityPartnerships.txtInstitutionName)="M" Or (qryPriorityPartnerships.txtInstitutionName)="N" Or (qryPriorityPartnerships.txtInstitutionName)="O"))


Code from my pop-up form is:

Private Sub cmdDeptFilter_Click()
'On Error GoTo Err_cmdDeptFilter_Click

'Dim strSQL As String
'Dim stDocName As String
'stDocName = "View Priority Partnerships"


'If Nz(Me.cboDept, "") <> "" Then
' strSQL = strSQL & "Discipline='" & Me.cboDept & "' AND "
'End If


'Strip Last " And "
'If strSQL <> "" Then

' strSQL = Left(strSQL, (Len(strSQL) - 5))
' Debug.Print strSQL
'Set the Filter property
' DoCmd.OpenReport stDocName, acPreview
' Reports![View Priority Partnerships].Filter = strSQL
' Reports![View Priority Partnerships].FilterOn = True
' RunCommand acCmdZoom75
'blnNotOpenMenu = True
'Else
' MsgBox "No criteria specified, opening the report unfiltered"
' DoCmd.OpenReport stDocName, acPreview
' RunCommand acCmdZoom75
'End If
'Exit_cmdDeptFilter_Click:
' Exit Sub

'Err_cmdDeptFilter_Click:
' MsgBox Err.Description
' Resume Exit_cmdDeptFilter_Click

'End Sub

Not sure how to include the first filter with the second?
And second filter is without ' obviously, just had to take it out until I can get it to work

 

[jzwp22]

I'm not sure why each line of your code is commented out (single apostrophe at the beginning of each line)?


You can combine the two filters since they essentially are the WHERE clause of the query.

Private Sub cmdDeptFilter_Click()
On Error GoTo Err_cmdDeptFilter_Click

Dim strSQL As String
Dim stDocName As String
stDocName = "View Priority Partnerships"
Dim strWHERE as string


strSQL= "(((qryPriorityPartnerships.txtInstitutionName)='A' Or"
strSQL= strSQL & " (qryPriorityPartnerships.txtInstitutionName)='B' Or"
strSQL= strSQL & " (qryPriorityPartnerships.txtInstitutionName)='C' Or"
strSQL= strSQL & " (qryPriorityPartnerships.txtInstitutionName)='D' Or"
strSQL= strSQL & " (qryPriorityPartnerships.txtInstitutionName)='E' Or"
strSQL= strSQL & " (qryPriorityPartnerships.txtInstitutionName)='F' Or"
strSQL= strSQL & " (qryPriorityPartnerships.txtInstitutionName)='G' Or"
strSQL= strSQL & " (qryPriorityPartnerships.txtInstitutionName)='H' Or"
strSQL= strSQL & " (qryPriorityPartnerships.txtInstitutionName)='I' Or"
strSQL= strSQL & " (qryPriorityPartnerships.txtInstitutionName)='J' Or"
strSQL= strSQL & " (qryPriorityPartnerships.txtInstitutionName)='K' Or"
strSQL= strSQL & " (qryPriorityPartnerships.txtInstitutionName)='L' Or"
strSQL= strSQL & " (qryPriorityPartnerships.txtInstitutionName)='M' Or"
strSQL= strSQL & " (qryPriorityPartnerships.txtInstitutionName)='N' Or"
strSQL= strSQL & " (qryPriorityPartnerships.txtInstitutionName)='O')) AND "

If Nz(Me.cboDept, "") <> "" Then
  strSQL = strSQL & "Discipline='" & Me.cboDept & "' AND "
End If



strSQL = Left(strSQL, (Len(strSQL) - 5))

'the rest of the code should be the same

If someone selects something from the combo box, the above code will add that selection to the strSQL if nothing is selected, the strSQL would just have the original filter