Combobox display order

[jzwp11]

I'm thinkng my solution would be (for the subform on the left) to have a new line in tblAssessments for each Clinical Domain selected...

That would change your whole table structure and all associated forms, queries and reports you have created. Furthermore, it would be in conflict with normalization rules.

I will take a look at the database tonight, and see what I can find

[jzwp11]

Since the main form's recordsource includes a number of tables as evidenced by the query you are using (see below), you have messed up the linkages between the main form and all of the subforms. As I explained earlier, it is best to have each form (main or sub) based on an individual table as you go down the structure to the lowest level table.

SELECT tblClientProfilesInput.MRN, [tblClientProfilesInput].[First Name] & " " & [tblClientProfilesInput].[Last Name] AS [Client Name], tblClientProfilesInput.Age, tblClientProfilesInput.Rm, tblAssessments.dteAssess, tblAssessments.pkAssessmentID, tblClinicalDomains.txtClinicalDomain, tblClientAssessmentCD.Current, tblClientAssessmentCD.pkAssessCDID, tblClientAssessmentCD.fkAssessID, tblClinicalDomains.pkClinicalDomainID, tblClientAssessmentCD.fkCDID
FROM tblClinicalDomains INNER JOIN (tblClientProfilesInput INNER JOIN (tblAssessments INNER JOIN tblClientAssessmentCD ON tblAssessments.pkAssessmentID = tblClientAssessmentCD.fkAssessID) ON tblClientProfilesInput.MRN = tblAssessments.MRN) ON tblClinicalDomains.pkClinicalDomainID = tblClientAssessmentCD.fkCDID
ORDER BY tblClientProfilesInput.MRN, tblClientAssessmentCD.Current;

[Duncan]

Sorry, you are absolutely right, I should not have not mucked around with your original design.

[Duncan]

Thank you so much everything is working fine, just tinkering with format now. The ongoing assessment form is a little sluggish but I put that down to the amount of data that is loading onto the form through the subforms.
As rehabilitation is a goal driven and funded health specialty, it was pointed out to me that each client domain should have client goals attached, doh this should of been the first thing I did. So what I’m currently doing is drafting some client goals that will specifically sit with each clinical domain (like what the issue, cause etc do). I will link them through relationships like the way you originally setup the database. So my question is how did you in the first instance set up the relationships for the CD tables. I think you mentioned using append query’s. I could manually do it the long way, however if you have time would you please be able to steer me in the right direction so I can stick to the same format you have and learn how you did it. This would help enormously because I’m going to have to take the original Excel table with all the data on it to the nurses and there will be a lot of changes in the content within each Clinical Domain. So I know I’m going to have to at some point redo all the tables.

[jzwp11]

If your data for the goals is in an Excel spreadsheet you can import the spread sheet into Access as a table. Then you can use it like any other table. You can create a query that selects the fields you want from the table created from the spreadsheet. You would then change the type of query from a SELECT query to an append query. Access will prompt you for the destination table and then you will have to match the fields of the imported table to the corresponding field of the destination table. Once you have done that, just run the query and the records should be appended.

[Duncan]

Sorry to bother you again. I have two last questions then I the thread should be closed b/c I have harassed you enough and I known forums are generally not supposed to be used this way. You have been extremely gracious with your time and expertise and are an absolute champion thanks heaps.

Thank you for the Append query advice. Can achieve same quite simply with the nursing goals as no two goals are the same hence each CD has completely individualised goals. When I tried to replicate this as a trial like what you did with the tblCDIssues (below) where the same Clinical DomainsID have different IssueID’s it turned out with nothing or data completely wrong. I know I’m missing something small either with the original imported table setup or the way I’m running the query!
pkCDIssueIDfkClinicalDomainIDfkIssueID351359518132232772683833593381
Lastly
I have noticed that the database can fluctuate by several MB with no data input. The simple act of opening forms then closing them with no change to data increases the database size. Then when I save as a NEW database the database can be saved several MB less. Is this database bloating that I have read about. As per advise on the net I have made sure of the following;
Coded record sets are always closed at the end of a function
Track name AutoCorrect Info is unchecked, and
Form can shrink and grow set to no
Is there any other reasons or things I should do or is this fluctuation normal.
Regards

[jzwp11]

I have noticed that the database can fluctuate by several MB with no data input. The simple act of opening forms then closing them with no change to data increases the database size. Then when I save as a NEW database the database can be saved several MB less. Is this database bloating that I have read about. As per advise on the net I have made sure of the following;
Coded record sets are always closed at the end of a function
Track name AutoCorrect Info is unchecked, and
Form can shrink and grow set to no
Is there any other reasons or things I should do or is this fluctuation normal.

As you work with forms & reports in design view, the database size tends to grow, so it is good practice to run the compact & repair utility. Just opening and using a form (form view not design view) should not bloat the size of the database. In fact, some people recommend running the compact & repair on a regular basis.

Thank you for the Append query advice. Can achieve same quite simply with the nursing goals as no two goals are the same hence each CD has completely individualised goals. When I tried to replicate this as a trial like what you did with the tblCDIssues (below) where the same Clinical DomainsID have different IssueID’s it turned out with nothing or data completely wrong. I know I’m missing something small either with the original imported table setup or the way I’m running the query!
pkCDIssueIDfkClinicalDomainIDfkIssueID351359518132232772683833593381

I'm not sure I understand exactly what the issue is with the append query. Can you provide the SQL text of the append query? Are any errors returned? Does the query tell you that it is going to append some # of records?

When you append you cannot append anything into the autonumber field perhaps that might be causing the problem?

[Duncan]

Sorry for the last post. I did have a copy of what I meant in the post however when I sent it the table turned into numbers. I’m not sure if you still have a copy of the database, however I will use an example from the Issues tables to explain.
When I sent the original excel table the issue unable to achieve independently for example related to several clinical domains. When you did the tables the unable to achieve independently issue only appears once in the tblIssues under field txtIssues. Because it’s a one Issue to many CD’s relationship you created the joining table tblCDIssues using an append query. So in the tblCDIssues there are all the clinical domain IDs (1-24) with some having the corresponding unable to achieve independently issue ID from tblIssues . This is what I’m struggling with. How did you automatically create the tblCDIssues with all the CD and Issue IDs in using an append query. Because as it stands I would do this manually. Hope this makes sense.

[jzwp11]

You would first have to append the unique goals into a table. You can do this by querying your raw data (I assume that you have imported an Excel sheet as a table in Access). The SQL text would look something like this (you will have to adjust your field/table names of course)

SELECT DISTINCT goalname
FROM YourImportedDataTable

The above query will only pull the unique goal names. Now change the query type to append and supply the name of the table that will hold the goals (make sure that destination table has an autonumber primary key field). Map the goalname field of the import table to the corresponding field in the destination table and then run the append query.

For the other append queries, start with a SELECT query and bring in the tables that have the information you need. Make any joins you need, select the fields that need to be populated in the destination table. Change the query type to append and map the fields again.

Without seeing your raw data (imported data table) it is hard to say how to structure the append queries since the data contained probably crosses multiple joined tables. I would also need to see the structures of the destination tables. Can you post your DB?

[Duncan]

I have attached two documents. One the source Excel table that you have seen before and two the original Access database that you created from the table. My problem I need help with is how to create the tables in Access like you have as I’m going to have to take the Excel table to the nurses soon and will have to redo all the Access tables again as there will be data changes. I would like to try to do this myself so I can stop harassing you, hence the reason I’m asking how you did it e.g., tblIssues and associated tblCDIssues through an append query (more descriptive the better for the newbee). Regards

[jzwp11]

I did some manipulation of the spreadsheet first by copying the clinical domain name down the column for each section. I also changde the column headers to more appropriate names for Access (no spaces, no special characters, no Access reserved words etc.) Then I imported the spreadsheet into Access as a table. I then set up Select queries based on the imported spreadsheet; one query for each column. I made sure to modify the query to select only unique/Distinct values in each column (in SQL text just add the word DISTINCT to the SELECT clause:

SELECT DISTINCT fieldname1,...

You will have to put in criteria to ignore blank rows (WHERE Not fieldname is null)

Then I changed the query type from SELECT to APPEND and ran it.

Once you have each applicable table populated, then you have to create another query that joins the imported sheet to clinical domains table via the field that holds the name. You also have to add the additional table such as the issues table and join that to the issues column of the imported sheet. SELECT the primary key fields of both the clinical domain table and the issues table. Again, you only want to append unique combinations of names (clinical domain/issues), so you will need the DISTINCT key word. Then change the query type to append and run it.

You will have to repeat this query again for each successive table (causes, clinical indicators, actions/considerations etc.)

[Duncan]

Thank you for the append query and compact and repair info. It took a while however I managed to run the query. I’m continually amazed at the different and divers way Access achieves things. I have read ‘Access 2007 the Bible’ and watched a good basic DVD ‘Microsoft Access 2007 Essentials’ however I’m still unable to run a report that is always successful for the clinical domains.
I’m running the report from a query that puts all the info together client name, domain name, issue, cause etc. The trouble is if a client has more than one issue, cause etc selected or if say, an issue is selected but no cause is selected when originally doing the data entry the report goes loopy of which I think comes for the source being a query. I have read about having reports within reports however like the data entry forms I’m sure it is not that easy given the structure of the database (having little success with this avenue). So I was wondering if you would have time to help with a report that puts all the info together with the ability to select what clients to add to the report.
Another option which would be unreal is the ability to add/leave out issue, cause, clinical indicator etc fields if so desired (only a luxury but would be great if possible).

I have attached an old database that I’m no longer using as I have merged the two databases (clinical domain one and the client/staff database I have been developing thanks to your help with the CD one) thus now quite large.

[jzwp11]

The trouble is if a client has more than one issue, cause etc selected or if say, an issue is selected but no cause is selected when originally doing the data entry the report goes loopy of which I think comes for the source being a query. I have read about having reports within reports however like the data entry forms I’m sure it is not that easy given the structure of the database (having little success with this avenue).

Because of your structure, I think the only way to do it is with subreports. I started the report; it has 2 subreports. I'll let you go through adding the other ones you want. Each subreport is tied to a query. Each subreport has to be linked to the main report via 2 fields. The report name is rptClientHeader

... with the ability to select what clients to add to the report.

Check out the form: frmGetReport. Code behind the button uses the selection in the combo box to filter the report.

DB is attached

[Duncan]

Thank you for the last post re report generation. I’m still on report generation and wondering if you are able to help with two small formatting issues.
1. In the Brain Injury Unit we have sixteen beds. I have generated a report that captures the basic client’s details (rptNursingHandover) that works well. However if there is an empty bed the formatting goes all skewif as there is no data to load under that room number as there is no client etc in that room. This is fine and normal for reports however I’m wondering on a report load event or reports record source which is a query (qryNursingHandoverRm8-10) is there a way I can have the report structure load anyway when the room is empty but it would just have no details (client name etc) in that room as there is no actual client in that room. So basically there are 16 beds and regardless whether there are sixteen clients or not I would like the 16 beds to be evident on the report.
2. Further on my report Form (frmReports) which is working really well from the 4 list boxs you can either select one client, a number of clients or no clients which will print off all the clients. Because of the code (I got of a website) I have to have separate client selection list boxs for each report. Is there a way just to have one client select list box (not 4) that will print the selected report in an afterupdate event from the combobox (Report Selection) at the top of the form.

If you could help it would be really great. Thanks (the reports, queries and form in question are in the Unassigned Objects)

[jzwp11]

1. In the Brain Injury Unit we have sixteen beds. I have generated a report that captures the basic client’s details (rptNursingHandover) that works well. However if there is an empty bed the formatting goes all skewif as there is no data to load under that room number as there is no client etc in that room. This is fine and normal for reports however I’m wondering on a report load event or reports record source which is a query (qryNursingHandoverRm8-10) is there a way I can have the report structure load anyway when the room is empty but it would just have no details (client name etc) in that room as there is no actual client in that room. So basically there are 16 beds and regardless whether there are sixteen clients or not I would like the 16 beds to be evident on the report.

To do this you need to have all of the beds as part of the record source whether or not they are associated with a client. You will need to modify the query that you use for the report's record source such that all the bed records are selected as well as those client records that match (a left join in the query).

2. Further on my report Form (frmReports) which is working really well from the 4 list boxs you can either select one client, a number of clients or no clients which will print off all the clients. Because of the code (I got of a website) I have to have separate client selection list boxs for each report. Is there a way just to have one client select list box (not 4) that will print the selected report in an afterupdate event from the combobox (Report Selection) at the top of the form.

What I would recommend is to have 1 client list box and a combo box with your report name choices. You would then have 1 button that when clicked will pass the list of clients to the code as well as the name of the report to be used in the Docmd:

DoCmd.OpenReport me.cboReport, acViewPreview, , GetCriteriaDetMed()