Newb: Using criteria from one table to generate a report from another

[jonheese]

First off, I have some working experience with using SQL queries in Java/C++, but I'm relatively new to the Access world, so please be gentle.

The database in question is for a large medical practice. For the purposes of this question, I have two tables: Clients and Visits. Each row in the Clients table is keyed by a unique clientID and has a genderID column (1 = male, 2 = female, 3 = transgender, 4 = unknown). Each row in the Visits table represents each visit by each client to the practice. It is keyed by a unique visitID and references Client's clientID as a foreign key. The Visits table also contains a date field (type Date/Time) that represents the date of the visit.

Now the question:

I want to generate a report that only shows Visit/Client data by year. For instance, I'd like to show the number of males that visited in 2005, the number of females that visited in 2005, etc. I can get a sum of all males in the Clients table easily, but I want to constrain the sum to only those males who have corresponding Visit records whose date falls in 2005.

I suppose I just need to know what to set the reports record source to in order to reference all of the fields from both tables, as well as what should be the control source of one of the tally fields, e.g. total number of males that visited in 2005).

Can anyone help?

Regards,
Jon Heese

 

[GingerR]

You can write a totals query to join the two tables together on the ClientID field. Look in HELP or a text book for Totals Query. Then you can just use this query (or it's SQL equivalent) for the report's Recordsource.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[jonheese]

Well, also I need to know how to constrain the report by year. As in, actual syntax.

Also, the report has other information in it too (I suppose I should've been more complete in my initial post). For instance, I want to show a total number of clients who have at least one row in the visits table which shows a certain diagnosed condition.

For example, if one client clientID 27 has two corresponding entries in the visits table that are from the year 2005, both where he is separately diagnosed as having Influenza, the report should count him once in the total "Clients diagnosed with Influenza in 2005". Normally I could kludge a few SQL statements with OOP glue to show this data, but I have no earthly idea how to do this in Access.

Help?

GingerR, your answer was a little incomplete: I searched in Access help for "totals query" and didn't find much that seemed to show me the way to what I need. Remember that I am very new to Access as compared to, say, PHP/MySQL or Java/PostgreSQL, so I fear I'm going to need a bit more hand-holding here...

Regards,
Jon Heese

 

[Lilliabeth]

You might find this easier than learning to query using Access GUI...

Go to queries and double-click on Create Query in Design View.
Close the Show Table dialog box.
Click on the tool labeled SQL.
Write your query in SQL.

 

[GingerR]

Hi. I'm just trying to get you to figure stuff out yourself. You can figure it out just by looking at the sample databases that are shipped with MS Access, or any text book.

I wrote this basic query, assuming the following:

Table Name: Clients
Field: ClientID (Number)
Field: GenderID (Number, from Genders table)

Table Name: Genders
Field: GenderID (Autonumber)
Field: Gender (Text)

Table Name: Visits
Field: VisitID (Autonumber)
Field: ClientID (Number, from table Clients)
Field: VisitDate (Date)


I clicked on QUERIES in the database window and clicked the word "NEW". I picked to see "DESIGN VIEW". When the SHOW WINDOW screen popped up, I chose all three tables listed above. "JOIN LINES" were already created between the fields whose names match (ClientID, GenderID). I double-clicked on the following fields, which brings them into the "query grid" below:

From table Clients, ClientID
From table Genders, Gender
From table Visits, VisitDate


So this query will show the Clients, their genders and their visit dates. From the toolbar above, pick the red exclamation mark. It will run the query. You will see the resulting dataset.

So you want to see just the YEAR, not the entire visit date. So instead of showing the VisitDate, you want to make a "calculated field". So remove the VisitDate from your "query grid" below, and replace it with this:

VisitYear: Year(VisitDate)

Run the query again (pick the red exclamation mark). You will now see that the VisitDate has been replaced by YEAR instead.

So these are your detail records, meaning it's the same number of records that are in your "visits" table. To group these up, or count them, or do a variety of other things, you'll want to do a TOTALS query.

Here is one example, and you should be able to play around to figure out your exact needs:

Looking at the design of your new query, from the menu pick VIEW+TOTALS. Notice that there is now a new row in you query grid below, a row called "TOTAL", in which Access has automatically put "GROUP BY" into each column. In the ClientID column, change "GROUP BY" to "COUNT", and run the query again. See how now it counts the number of clients who are male, female, etc for each year. See how also it changes the column name (when you run it) to "CountOfClientID". This isn't very cool, so what you can do is force a renaming of this field by doing this:

In design view, in the column where ClientID is, change the top row which says the field name "ClientID" to this:

ClientCount:ClientID

Run it again. See how now the column is called "ClientCount". This is because you have put the lable to the left of the semi-colon.

In order to see what Lillabeth is talking about, in design view of your query, pick from the menu VIEW+SQL VIEW. You can then see something that may be familiar to you. Switch back to the query grid by picking VIEW+DESIGN VIEW.

With this information, hopefully you can fiddle around and figure out your other requirements.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[jonheese]

GingerR,

Yes, I apologize for my hasty response. It's no excuse, but I was in a particularly impatient and stressed-out mood and I was expecting something magical in response to my initial post.

Coincidentally, based on your initial clue, I did actually figure out how to build the query to do what I wanted. Now I seem to be a little stuck on the Report side of things. I haven't really tried much yet, but I've taken a break from it and will get back to it later today, but here's where I am:

The query I built is called "Count Clients Visiting in 2005" and it does just that, returning the count of each client of each gender (as "CountOfclientID1") along with the appropriate genderID (as "genderID"). I made this query the Recordsource of the report, and to return the total number of clients, I use "=Sum([CountOfclientID1])", that was easy.

Now, I haven't yet figured out the syntax to return only the count of one specified genderID, i.e. "Male Clients Visiting in 2005" in the report. I want to continue using the "Total Clients Visiting in 2005" query, but I want the report field to show me the number that appears in the CountOfClientID1 field from the row where genderID is, say, 1 (Male). Is there are way to do that?

Regards,
Jon Heese

 

[GingerR]

I don't really understand your question. Please post your report's recordsource (the sql statement), sample data of the results, and a sample of how you want it to look.



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[jonheese]

Sorry, I realize this may be a bit difficult to follow. Wish I'd thought about posting the particulars before. =)

Bear in mind that I am by no means sure that the way I've built the query is the "best" way (or even that it will work), so please feel free to steer me in an entirely different direction.

Recordsource:

SELECT Clients.genderID, Count(Clients.clientID) AS CountOfclientID1
FROM Clients INNER JOIN Visits ON Clients.clientID = Visits.clientID
WHERE (((Year([date]))=2005))
GROUP BY Clients.genderID;

In the Clients table, there are 4 genderID's to choose from (1=Male, 2=Female, 3=Transgender, 4=Unknown/unreported), so there are four rows in the result set from the query, each representing a gender, with the corresponding count in the second column.

e.g.

genderID | CountOfclientID1
   1     |       258
   2     |       235
   3     |        28
   4     |         7

Now, I have five fields in my report, that I want to show these four values, as well as a total, e.g.:

Male clients visiting in 2005:                      258
Female clients visiting in 2005:                    235
Transgendered clients visiting in 2005:              28
Unknown/unreports gender clients visiting in 2005:    7
Total clients visiting in 2005:                     528

Is that clear? Thank you so much for your help.

Regards,
Jon Heese

 

[GingerR]

Hi. I think you'll find good information in my post above. You might want to read it.

1) I suggest not putting any spaces in the names of any objects (tables, queries, forms, reports, etc) or any field names. i.e. "Count Clients Visiting in 2005". It's not illegal, but can cause problems later when you don't put square brackets around them, which is a pain in the butt.

2) You should not have a field called "DATE". It's a reserved word and will definately cause you problems. I suggest something like "VisitDate" instead.

3) Do you have a table called Genders as I assumed in my earlier post?

Table: Genders
Field: GenderID (Number or Autonumber)
Field: Gender (text, i.e "Male", etc)

If you don't, you should. It'll make it easier to expand the db later when someone decides you should add some other 'gender' to the db. Not that this might be realistic in this case, but it's good practice. Otherwise, throughout your entire DB for the rest of your life, you're going to be typing things like "iif(GenderID = 1,"Male", iif(GenderID = 2, "Female"......etc etc....

Having this table called GENDERS, I suggest you put it directly in to your query, joined on GenderID. That way you get the words "Male", "Female", etc instead of 1,2,3,4.

4) I suggest you rename your "CountOfClientID1" in your query. See my previous post to see how to do it. Not that it's illegal, but just nicer to have something like "ClientCount".

5) Once you change your query to the way I originally suggested up top, you will have these results (more or less)..I suggest leaving VisitYear as GROUP BY (instead of WHERE) so that you can make a Group Footer out of it for your sum.

gender   | ClientCount      |   VisitYear
  Male   |       258        |   2005
 Female  |       235        |   2005
 Trans   |        28        |   2005
 Other   |         7        |   2005

Then in the detail section of your report. Put a text box with the control source of:

=[Gender] & " clients visiting in " & [VisitYear] & ":" & ClientCount

This is basic report design. You get your data, then display it however you want, in this case concatenating the data with other words to form your sentences.

You can put your total in a GROUP FOOTER (based on VisitYear) of the report. Just =SUM(ClientCount) will work. Look up "Sorting and Grouping" for Reports to see how to make a footer on VisitYear.


Here's a question for you: in four months, how will you get a report for 2006? Write a new query and a new report, all exactly the same but for a different year? I suggest you read up on parameter queries. that way you can prompt the user for a year, or even better yet, launch the report from a pretty form where the user picks the year from a combo box and hits the button to get the report. All of these design skills are easy to see in the Northwind sample db.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[jonheese]

GingerR,

Thank you for the information.

I'm greatly embarassed, but the answer to all of your "why didn't you do it this way?" questions is the same (except for the "date" one, and the "no spaces in names", because I didn't know those): Before I sat down to start coding, I presented my database design to my boss (who has no db experience) and our clients (the medical office), and they both thought that the way I designed it (3NF everywhere, more efficient) was too complicated and would take too long to implement and be too hard to for them to maintain (i.e. too many tables that they didn't understand the need for), especially for such a small project. So I said whatever, and figured the less complicated the better.

Honestly, now that assumption is clearly flawed. I'm not really a db programmer, and actually, I haven't been a programmer by title for almost a year; this is just a small project that the IT firm I now work for is doing as a favor for one of our networking clients.

Anyway, I redesigned the tables and queries and now all is good with the gender data in the report. However, the same report also must contain 16 other factors that need to be counted and shown (e.g. # of diagnoses by condition and visit date, # of clients showing certain risk factors by factor and visit date, etc.) all pulling data from the Visits table.

Can I use the same strategy of looping on a certain field to show these other data in the same report as my initial Gender info? I haven't yet figured out a way to do this, but I'm still trying.

By the way, I looked at the Northwind db, and man, what a complicated beast. I'm trying to figure out how to make a simple form with a dropdown box and a button that will generate the report based on the year selected. I'm trying to populate the dropdown with the available years in the Visits table, but I'm having trouble finding a strategy to do this with only unique entries (i.e. now I get 2005, 2005, 2005, 2005, X times, where X is the number of entries in the Visits table). Also, I can make the dropdown box drop, but when I try to click an entry, it's like it's disabled... am I missing something obvious?

Also, in the onClick event for the button, I call

DoCmd.OpenReport "VisitsReport", acViewPreview, , "VisitYear = '2005'"

but I really want to pull the VisitYear parameter from the dropdown box...

DoCmd.OpenReport "VisitsReport", acViewPreview, , "VisitYear = '" & yearCombo.text & "'"

doesn't seem to work.

I know this is a lot of information, and I thank you immensely for all of your help.

Regards,
Jon Heese

 

[jonheese]

Doh! "Group By" years fixed my "2005, 2005, 2005, 2005 X times" problem. Chipping away at it.

Regards,
Jon Heese

 

[jonheese]

Aha,

DoCmd.OpenReport "VisitsReport", acViewPreview, , "VisitYear = '" & yearCombo.Value & "'"

works perfectly. Now I just need to figure out why it won't let me choose an item in the dropdown...

Regards,
Jon Heese

 

[GingerR]

Good for you for plugging along! Believe me, nothing you do will end up being 'simple'

Combo box not working: Is your form's Allow Edits property set to NO? You would know if it is, as you would have physically done that yourself.

I just want to make sure, too, that your drop-down does not have a control source. The "Report Form" should be "unbound", meaning is is not "bound" to a table, meaning that the form itself does not have a Record Source. It's just a form that's going to gather info to be used in reports.

Note on naming conventions:

Typically we prefix our control names with three-letters, something like this:

Text box = txtBlah
Combo box = cboBlah
List box = lstBlah
Option box = optBlah

etc. Not set in stone, some may vary slightly, there's no law about it, but just sort of something the programming community adheres to so the next guy knows what to look for. I'm glad though that you at least renamed the combo box, something which many newbies do not do. There's nothing worse than a code sheet full of references to Text22, Text 23, Text 24, etc....

Here's the full story, I think it began with VB and migrated to MS Access community, which I do not totally adhere to. Do not take what I do as "the best", though...it's just been fine for me and the guys I hand my programming off to. I do not prefix my tables, queries, forms or reports. Only the controls on forms and reports (and not even exactly like these guys suggest) and any vba variables so I know what they are (strMSG, strSQL, intClientCount). Scroll down about half-way until they talk about objects (a table that starts with GRAPH object).

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccess/html/msdn_20naming.asp

Maintaining Tables
As for your clients, and their negative reaction to all of the "unnecessary" tables: If you make a simple, pretty place for them to maintain the tables, they'll be happier. Typically I have a "switchboard" form (don't use the wizard" with buttons like:

Add Data
Maintenance
Reports
Close

For "Maintenance", create a new form "Maintenance", I have done several formats depending on the data. Maybe just an unbound form with a few subforms on it: one for Gender table, one for Years, etc so they can add a gender or a year right there. If there are many of these types of tables to maintain, using a TAB CONTROL (the thing that looks like manila file folders stacked on top of each other) is another solution. Then they are not maintaining the tables directly in the tables; they have a pretty screen to do it with.

Report:
For your report: I'm not sure exactly what you are looking for. If you want, you can write it out in MS Word (draw diagrams or mock-up what you are looking for), zip up your db and send it to me at mitzimeat at hotmail.com (i'm not writing out my real email address so I don't get spammed). I can then maybe do one example for you, and explain it so you can do the rest yourself. What you may need to do, if you are gathering the same kind of info as the Gender example we did above, is make "sub-reports"; so you have one main report, and the gender one we made above will be one sub-report; then you make a few more, and embed them in the main report and set the Master/Child links to VisitYear. Again, maybe I'm just unclear as to what you are looking for.

Ok, so, keep truckin' on.....

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[jonheese]

Thanks for all of your time.

I did set "AllowEdits" to no. Don't remember what I was trying to fix, but oh well. =)

To give you a little background on me, I've was a software developer for 3 years until about a year ago, when I moved across the state. I wrote mainly in Java on Servlets/Apps with modular backends, including standard SQL db plugins. I learned all my good programming habits from that job and I'm very familiar with the benefits of good clear naming schemes and I'm normally very diligent about keeping to conventions. Access is still new to me, and actually, I'm taking a class in .NET this semester, so the "xxxName" naming convention is familiar.

As for making a nice dummy-proof switchboard for the non-techs to use, I was certainly planning on that. I didn't really plan on going to the extent of a "Maintenance" menu to add/remove/rename fields, but if time permits, I suppose that would be best. It's the classic non-tech fallacy of "less complexity means less confusion", when in fact too much simplicity to the point of bad design just makes everything harder.

Your "subreports" idea is exactly what I was thinking. I will send you a sample along with my db and you can let me know what you think. Thank you again for all of the help.

Regards,
Jon Heese

 

[GingerR]

I looked over your db and it's not normalized, so there's nothing more I can do.

Also, as I said before, I would not use the built-in switchboard wizard. It's caused me problems in the past so I just build my own.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html