Report to show only one record at a time.

[tmetford]

I know this is beginner level stuff, please be patient!
I want to create a Report that shows only one record for the purpose of writing a letter to a patient.
At the moment I seem to get lots of records showing (which is what a report is designed to do, I know) but it's not what I want.
I want to have a means to select a patient's last name, drawn from a Patient's table (may be I need to use a Select Query?) and have a report show this name in the manner you'd expect a business letter to be written.
I would then want to be able to add the text I want to say to that patient (may be using an unbound text box?) so that a letter can then be printed and mailed.
What steps do I take to design such a report?
I hope this is clear enough and, ultimately, I want to be able to have related data appear in this report which has been drawn from two other separate tables (i.e. Doctors table and Surgeries table).
I know I have to construct a query to do this but I need help with this too and with designing the database appropriately in the first place to help it all work!
Thank you for your help in anticipation.
Tim Metford

 

[caraewilton]

Hi. Am not the expert in this, you need slurpee or obp but if I understand you correctly it is not to difficult.
Create a query which has the information you want on each patient, then use the report wizard. Follow the promps, inserting all query field. When you get to grouping, group your records according to patients. Carry on following the prompts. Once you have your report in design view, click on the grouping button and change the default setting for you grouping to show group footer. Now in your footer just drop a page break. This should provide you with a print out of all your patients, each on a separate page.

If you want to be able to select only one patient then just modify your query by placing a question mark in the patients account (or what ever other field you use to identify a unique record) field under criteria. When you run the query it will bring up a parameter box where you can enter the account number which in turn will show only the information for that patients. The report you base on this query will also require that you input the account number and in turn bring up only the information for that particular patient.

I am sure that the above mentioned experts can probably give you a better method, but this works and is easy and requires no special VB programming, so perhaps a good place to start.

 

[OBP]

The best way to do this depends on where you want to run the report from, if you want to run it from your current Patient Form form a command button then you can set the Report Command Button to "Filter" the records to the one that you are on.
If you have a special form for just selecting the the patient for the reprot (say from a Combo box) then the you can put the "selection" in the the Report's Query in the Criteria Row similar to carae' question mark.
But it does not ask for the patient because you have already selected one.
Which method did you want to use?

 

[caraewilton]

HI OBP, Cool to "see" you again.
I just found the question mark allowed me to select various options from one report, where as entering the actual criteria meant creating a separate report for each option.
Is there a better way of doing this?

 

[OBP]

Carae, and you.
I normally have a "Search" form where you can "Mix & Match" the way that you want the data to be filtered.
The form can then be used either with a Query or with a VBA SQL type query, but more importantly it can be used on as many different queries, forms and reports as you like.
So you could for instance enter a "Start" and "Finish" Date once and use it to filter as many queries or reports as you like.
Whereas the "Parameter" method you would have to enter the dates in for each one.
I have posted a few examples of Search forms on here, but if you want another one I can always post it again or email it to you.
I could also post the VBA/SQL version as well, but it is quite "heavy" going.
There is also an interesting Search Form created by another user that uses VBA to set the Form's "Filter", which can be used in the a "Current" form to filter by lots of fields at once.

 

[tmetford]

Hi OBP and thank you for your speedy response.
Just to be clear, I'm using MS Access as in MS Office 2007.
I might have asked too broad a question in one go because of inexperience, so I shall try to be more precise.
I have created a criterion:-

Like [Enter first letters of Patient's Surname]&"*" followed by
Like [Enter first letters of Patient's Firstname] & "*"

In order to select a patient's record from the Patient's table. This brings up the correct record which may have answered my original question I think!! !!! Sorry !!!
Anyway, if that's the best way of getting one record only to appear on a report to aid the writing of a letter, then fine but, is there a better way to construct a report to show only one record for this purpose?
I was also interested in the "?" being talked about earlier to help place only one record on the report, but I don't know how or where I would include this character.

The next problem I need to solve is how to design the relationships in my database so that it works well.
I have, at the moment 3 tables, Patients, Doctors and Surgeries. A patient may attend one particular surgery but might see different Doctors at that surgery on different visits. Also, a Doctor may practice at more than one surgery. So I need to create the right relationships between the tables. What relationships would you recommend? For additional information, I need to be able to write a letter addressed to a patient (presumably I would use a report to do this??) and, having selected the patient's record, the report needs to display that patient's doctor and his Title, initials and qualifications, and that doctor's surgery and the surgery's address.
This seems perplexing to me at the moment and any help would be greatly appreciated.
Thanks.
Tim Metford

The best way to do this depends on where you want to run the report from, if you want to run it from your current Patient Form form a command button then you can set the Report Command Button to "Filter" the records to the one that you are on.
If you have a special form for just selecting the the patient for the reprot (say from a Combo box) then the you can put the "selection" in the the Report's Query in the Criteria Row similar to carae' question mark.
But it does not ask for the patient because you have already selected one.
Which method did you want to use?

 

[jimr381]

If you are going to be adding data to it, then you probably want a form laid out like a printed report.

 

[tmetford]

Hi Carae,
Would you tell me how I'd create a search form that enables one to "mix and match" the you want the data filtered? - I need it to be beginner level if possible and I'm using MS Access 2007.
Thanks
Tim
PS, I'm fine about being told to read more of the standard help articles that come with MS Access if what I'm asking for is best acheived by study of the basics!

Carae, and you.
I normally have a "Search" form where you can "Mix & Match" the way that you want the data to be filtered.
The form can then be used either with a Query or with a VBA SQL type query, but more importantly it can be used on as many different queries, forms and reports as you like.
So you could for instance enter a "Start" and "Finish" Date once and use it to filter as many queries or reports as you like.
Whereas the "Parameter" method you would have to enter the dates in for each one.
I have posted a few examples of Search forms on here, but if you want another one I can always post it again or email it to you.
I could also post the VBA/SQL version as well, but it is quite "heavy" going.
There is also an interesting Search Form created by another user that uses VBA to set the Form's "Filter", which can be used in the a "Current" form to filter by lots of fields at once.

 

[caraewilton]

Hi Tmetford. I am not sure of the exact steps using MSAccess2007 (had to uninstall it as my database is in access 2003 and having both was causing havoc). Anyway I will give you a basic run down of what I do, that way you will at least know what to look for. As an aside OBP form filtering method looks like a far better method...

Anyway here goes:

I assume you have a table already set up. If you have several tables (this would be the correct way to do things), just make sure you have linked the tables.

Okay, open up a new query in design view. You should be given a window that will allow you to select what tables you want to draw your information from. Select the relevant tables.

Let us assume you want the following information to appear:
Surname
Name
Address Line 1
Address Line 2
Address Line 3
Postal Code

You would click on each of these, thus getting your fields.

At this stage I would run the query to make sure it is working. If it is, save. Then go back to design view and in the criteria range in the surname field type in a ?

Now if you run the query it will ask you for the parameters. Type in the surname and it should just bring up that particular patients details. If it works, save.

Is this what you want to know? I must add at this stage that this is a really crude manner to achieve what you want but it is really easy. Anyhow, I hope you can find these things in 2007.

 

[tmetford]

Hi Carae
That's great, thanks. I have now created a query that asks for parameters and gives the requested information.
I now want to bring in data from other tables that need to be linked to my Patients table. However I don't know what relationships to make between the different tables to achieve the best design.
I have, at the moment 3 tables, Patients, Doctors and Surgeries. A patient will attend only one particular surgery but might see different Doctors at that surgery on different visits. Also, a Doctor may practice at more than one surgery.
I need to be able to write a letter addressed to a patient (presumably I would somehow use a report to do this??) and, having selected the patient's record, the report needs to display that patient's doctor and his Title, Initials and Qualifications, and that doctor's surgery and the surgery's address.
How do I do this and what relationships would you recommend?
I guess a many (patients) to one (doctor/GP) would be useful, but which fields should be linked?
A friend mentioned creating a "Key Table" comprising of the Key fields of the different tables (PatientID, GPID & SurgeryID) but this has really perplexed me and any help would be greatly appreciated.
Thanks.
Tim Metford

Now if you run the query it will ask you for the parameters. Type in the surname and it should just bring up that particular patients details. If it works, save.

Is this what you want to know? I must add at this stage that this is a really crude manner to achieve what you want but it is really easy. Anyhow, I hope you can find these things in 2007.

 

[OBP]

tmetford, your friend is correct, as the patient may see different Doctors and Doctors see different patients you need his "key table" or as I call it a "linking" table as it links the Doctors and Patients.
IT is correctly termed a Many to Many relationship, which means that the table has a One to Many link from the Doctors Table to the "key table"and a One to Many link from the Patients Table to the "key table" as well.
In that way you can link any patient to any doctor and any doctor to any patient.
If you just want to "Select" a Patient and a Doctor for your report then I would suggest that have a Form with 2 Combo Boxes on for doing the selection and use that form to Filter your Report's Query.
For more general Searching I would use the "Mix & Match" form with a special Query.
I can email you a Database (Access 2000) that you can look at which I created for the Alexandria Hospital Eye Clinic a couple of years ago.

 

[tmetford]

Hi OBP,
Thanks for your confirmation that key table would be a good idea.
I would really appreciate looking at the database you've offered so that I might examine the design of it. Thank you for that.
In a Key table, should I join the Primary Keys of each table or does it not matter which fields are joined?
Does it matter that only one table (the Patients table) has a primary key that has been assigned by Access in an "Autonumber" field? The other two tables have primary keys that I assigned to make sure they were different to the primary key assigned by Access in the patients table and these primary keys are in a "Number" field
Thanks
Tim

tmetford,
I can email you a Database (Access 2000) that you can look at which I created for the Alexandria Hospital Eye Clinic a couple of years ago.

 

[jimr381]

A "junction" table which is what you are trying to make should contain the primary keys of the two tables you are trying to join and any other fields that relate directly to both primary keys. So say I had an employ and projects tables. I would then have a junction table that joins the two. Within the junction table would be the EmployID primary key from the employee table, the PROJETID field from the project table and a field for the employee's time on project called maybe TIMEONPROJECT which shows the employee's percentage of time on the project. If the fields do not relate to both primary keys then they should either be in the tblEXMPLOYEES or tblPROJECT table.

An Autonumber data type on the primary side will always be related to Number data type on the related or many side. Besides this you will have the same data type for each side of the relationship. IE Text primary key field will be related to a text foreign key field.

I do not know where you are at with design principles, so let us know if you do not understand something.

Did you read my post suggesting that you will be using a form for output since you will be putting more data into it after it is generated?

 

[tmetford]

Hi Carae
Thank you for that, I like the detail you give because that's what I need at this stage!

I'm working on the database design using the contributions above but, right now, I want to turn to Line Breaks if you will.

How do I get a line break/carriage return to occur in an expression?
For example: I want the details of an address record to appear in a report such that they look like this:

John Smith
14 Whatever Avenue
Whichever Town
CA15 4GB

At the moment the fields of this record shown on the report appear separated unless I use an expression such as

Name: [Firstname] & " " & [Lastname]
Address: [Address_1] & ” “ & [Address_2] & ” “ & [Town] & ” “ & [Postcode]

This much is fine but the address then appears all on one line like this:

John Smith
14 Whatever Avenue Whichever Town CA15 4GB

How do I introduce a line break/carriage return please?
I already have a table set up and I'm working on how to link the other tables in the database. At the moment I want to concentrate on this particular aspect and next I shall need to learn how to design and format a report to make it look like a business letter ready for printing and mailing to a patient.
When you say "....this is a really crude manner to achieve what you want..." what would you do to create a business letter that could be mailed to an individual patient?
Thank you for your help.
Tim

I assume you have a table already set up. If you have several tables (this would be the correct way to do things), just make sure you have linked the tables.
Let us assume you want the following information to appear:
Surname
Name
Address Line 1
Address Line 2
Address Line 3
Postal Code
Now if you run the query it will ask you for the parameters. Type in the surname and it should just bring up that particular patients details. If it works, save.

Is this what you want to know? I must add at this stage that this is a really crude manner to achieve what you want but it is really easy. Anyhow, I hope you can find these things in 2007.

 

[caraewilton]

Hi Tmetford.

Let us have a look at your field question.

Just keep the fields separate. Then you can place each part of the address on it's own line. To do this open up design view of the report and you'll see you can move the fields around and place them as you wish.

Maybe you could explain how you were going to do these letters? Where you going to type the letter in Access?

For the purpose of creating letters I would use access to store the details, but word to write the letter. The mail merge features in word will allow you to gather the required information from access.

To give you an example. I have set up a database for the school I work at. This comprises of tables of students, families, concern slips, grades and a whole lot else. Anyway I have created different queries for different types of letter. For example we send a letter to each parent requesting that they up date their details. So I have a query which draws the parents name, email address and so on. Then I use word 07 to type my letter inserting the relevant fields:

Dear [Parent First Name]

According to our records you contact details are as follows:

[Home Phone Number]
[Work Phone Number]
[Cell Phone Number]
[Fax Number]

[Address Line 1]
[Address Line 2]
[Address Line 3]
[Postal Code]
[City]

[email address]

Please inform of any changes

Kind Regard
Cara

I then email this, and each parent gets an individual letter with their relevant details.

Is this the sort of thing you are after?

Maybe you can let me know about the above questions, then I can think of a plan on the easiest way to proceed.

With regards to linking tables. Do your tables have primary keys? If yes, are they access generated or did you specify them yourself.
You have 3 tables.
Patients
Doctors
Surgeries
Your primary key should be something that is unique to each record in a given table. Patients would most likely be an patient number as no two patients should have the same number, thus it would make a good primary key. In the tables you would need to identify something unique. Surgeries may just be the name of the surgery provided that all the surgeries have different names. (It is better to use numbers than text for key fields
If you can let me know what you have done, then I'll try think of an easy way to link the tables.