Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...
MS Access Forum / Forms Programming / June 2007

Tip: Looking for answers? Try searching our database.

report from check boxes

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Jenny - 18 Jun 2007 20:04 GMT
I have a table with many fields, but I want to use the following fields to
pull data into a report: State, county, PT, ST, OT, VRE.  State & County are
a list of all states/counties.  PT, ST, OT and VRE are yes/no.  The are
filled in by a check box via form.  I'm trying to create a report from a form
based on combo boxes to show selected data.  
Right now, the report works for choosing state(s) or counties.  However, how
can I rewrite this code to be able to choose PT from a dropdown and only show
those records that have PT checked yes?

Here is my code thus far:

Private Sub Command162_Click()
   
   Dim dbs As Database, SqlStr As String
   Dim qdf As QueryDef
   Set dbs = CurrentDb
   Set qdf = dbs.QueryDefs("qryCSDReport")
   Dim sqlWhereString As String
   Dim sqlWhereTrue As Integer
   Dim sqlStart As String
   Dim stateString As String
   Dim countyString As String
   Dim specialtyString As String
   
   
   
   SqlStr = "SELECT [Facility Name] , [Tax ID], STATE, City, [Street
Address], STATE, Zip, County, [Phone #] , [Physical Therapy (PT)],
[Occupational Therapy (OT)], [Speech Therapy (ST)] , MRSA, VRE, TB,
[HIV/AIDS], [Private Rooms], [Total Parental Nutrition (TPN)], [Central
Lines], [PICC Lines] , [Continuous Heparin Infusion], [Intrathecal
Medications], Chemotherapy, [Computerized Clinical Information], [Amputation
Recovery], [Peritoneal Dialysis], Hospice, [HIV/ARC Dementia], Hemodialysis,
[Alzheimer/ Dementia Care], TBI, [Complex Care], [Cognitive Therapy], Chemo,
[Cardiac Rehab], [Behavioral Management], Bariatric, [Bariatric Weight
Limit], [Wall Oxygen/Suction Set-Ups], [Weaning/Decannulation], [Trach - New
and Chronic] , Vents, [Adult Day], [Respite Care], MS, [24 hour admissions],
Pediatric, [Palliative Care] , [IV Pain Meds], [PCA Pumps], [Wound Care
Team], [Whirlpool Therapy (Full Tank)], [Whirlpool Therapy (Extremity Tank)],
[Wound VAC Therapy] , [Estin/Pulse Lavage:]" & _
   "FROM tblCSD "
             
       
   For i = 0 To Me.STATE.ListCount - 1
       If (Me.STATE.Selected(i) = True) Then
           stateString = stateString & "State = '" & Me.STATE.Column(0, i)
& "' Or "
       End If
   Next i
   
   If Len(stateString) > 0 Then
       sqlWhereTrue = 1
       sqlWhereString = sqlWhereString & " (" & Left(stateString,
Len(stateString) - 4) & ") AND "
   End If
   
   
         
   For i = 0 To Me.County.ListCount - 1
   If (Me.County.Selected(i) = True) Then
           countyString = countyString & "County = '" & Me.County.Column(0,
i) & "' Or "
       End If
   Next i
   
   If Len(countyString) > 0 Then
       sqlWhereTrue = 1
       sqlWhereString = sqlWhereString & " (" & Left(countyString,
Len(countyString) - 4) & ") AND "
   End If
     
   For i = 0 To Me.Speciality.ListCount - 1
       If (Me.Speciality.Selected(i) = True) Then
         Speciality = specialityString & "Speciality = '" &
Me.Speciality.Column(0, i) & "' Or "
       End If
   Next i
   
   If Len(specialityString) > 0 Then
       sqlWhereTrue = 1
       sqlWhereString = sqlWhereString & " (" & Left(specialityString,
Len(specialtyString) - 4) & ") AND "
   End If
       
   If sqlWhereTrue = 1 Then
       SqlStr = SqlStr & " WHERE " & Left(sqlWhereString,
Len(sqlWhereString) - 5)
   End If
   
   CurrentDb.QueryDefs("qryCSDReport").SQL = SqlStr
   DoCmd.OpenQuery ("qryCSDReport")
           
End Sub
Reply to this Message
Graham Mandeno - 18 Jun 2007 22:22 GMT
Hi Jenny

You just need to inspect the value of the checkbox and, if it is checked,
add more to your sqlWhereString.  For example,

   If chkPT <> 0 Then
       sqlWhereTrue = 1
       sqlWhereString = sqlWhereString & " (PT<>0) AND "
   End If

Do the same for the other three checkboxes.
Signature

Good Luck  :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

>I have a table with many fields, but I want to use the following fields to
> pull data into a report: State, county, PT, ST, OT, VRE.  State & County
[quoted text clipped - 95 lines]
>
> End Sub
Reply to this Message
Jenny - 19 Jun 2007 02:24 GMT
please forgive me as I am a total hack... but, does this mean that on my
report creator form I would need to show check boxes for each field, (i.e.
PT, OT...).  Currently I have it as a combo box based on a query to list all
the available choices.
thanks in advance.

> Hi Jenny
>
[quoted text clipped - 106 lines]
> >
> > End Sub
Reply to this Message
Graham Mandeno - 19 Jun 2007 02:56 GMT
Hi Jenny

Don't apologise! We all have to start learning somewhere :-)

Would you never want to filter records that were both PT *and* OT, for
example?

Then, sure, you could use a combo box (or even an option group with four
radio buttons).

Using a combo box, for simplicity make the bound column contain the name of
the field ("PT", "ST", etc):
   If Not IsNull cboSelectFilter Then
       sqlWhereTrue = 1
       sqlWhereString = sqlWhereString & " (" & cboSelectFilter & "<>0) AND
"
   End If

Just a thought:  are these four fields mutually exclusive?  In other words,
a single record should not have more than one of the boxes checked?  If so,
you should really just use a single field with a text value ("PT", "ST",
etc) or a numeric value (1="PT", 2="ST", etc).
Signature

Good Luck  :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

> please forgive me as I am a total hack... but, does this mean that on my
> report creator form I would need to show check boxes for each field, (i.e.
[quoted text clipped - 120 lines]
>> >
>> > End Sub
Reply to this Message
Jenny - 19 Jun 2007 14:03 GMT
No, they are not exclusive.  There are actually 46 different categories that
I want to filter by and there could be any number of combinations thereof. I
only listed the four in this example for simplicity.  I had wanted to make it
that they could choose more than one at a time to filter on, but since there
are so many options will that just make my report take an eternity to run?  
What do you suggest?

> Hi Jenny
>
[quoted text clipped - 142 lines]
> >> >
> >> > End Sub
Reply to this Message
Graham Mandeno - 20 Jun 2007 01:34 GMT
Hi Jenny

Hmmm... what I *really* suggest is that you take a step back and take a look
at your design.

One of the principle rules of good database design is that you should not
have repeating fields (or groups of fields) serving the same function.  For
example, if you have a Customer table and each customer can have a number of
contact people, it might be tempting to add nine fields:
   Contact1Name, Contact1Email, Contact1Phone
   Contact2Name, Contact2Email, Contact2Phone
   Contact3Name, Contact3Email, Contact3Phone

However, this introduces several problems:
   - what is a customer has more than three contacts?
   - how do you find which customer is associated with a given contact
email?
   ... and many more besides

Related to this is the rule that you must not store data in field names.
This is effectively what you are doing with your categories in the CSD
table.  I note you have field names such as:
   [Physical Therapy (PT)]
   [Occupational Therapy (OT)]
   [Speech Therapy (ST)]
   ... an so on

These field names are actually data.  What are you going to do when someone
decides to add a new category?  You will need to add a new field to your
table, and then modify the design of every one of your forms and reports!!

Instead, these categories should be stored in a separate table:
   CategoryID or CategoryCode    (primary key)
   CategoryName

The primary key field could be either an AutoNumber (CategoryID) or a short
text field (e.g. "PT").  (For the purpose of this discussion, let's say you
go for the autonumber option)

Now, you have two tables (CSD and Categories) with a many-to-many
relationship (one CSD can fall into many categories and many CSDs can fall
into one category).

In a relational database, you can have only one-to-one or one-to-many
relationships.  To create a many-to-many relationship you need a third
table, called a "junction table".

A junction table is the "many" side of *two* one-to-many relationships, one
with each of the other tables.  So your junction table (CSD_Categories)
needs only two fields: CSDID and CategoryID.  [Note that your CSD table must
have a primary key - if it does not have one already, add an autonumber
field named "CSDID"]

You then create two one-to-many relationships between tblCSD and
CSD_Categories (with cascade deletes) and between Categories and
CSD_Categories.

Now, each category that a CSD belongs to will have one corresponding record
in the junction table.  In effect, each marked checkbox becomes one junction
record.

Now, getting back to your report, you can select the categories from a
multi-select listbox same as the state and county.

Dim vItem as Variant
With lstSelectCategories
   If .ItemsSelected.Count > 0 then
       For Each vItem in .ItemsSelected
           sqlCategories = sqlCategories & .ItemData(vItem) & ","
       Next
       ' remove the last comma
       sqlCategories = Left(sqlCategories, Len(sqlCategories)-1)
       sqlWhere = 1
       sqlWhereString = sqlWhereString _
       & "(CSDID in (Select CSDID from CSD_Categories where CategoryID in
(" _
       & sqlCategories & ")) AND "
   End If
End With

I think I've given you enough to digest for now!  I know it might seem a bit
scary, but believe me, if you get the design right from the start then it
will pay off in the long run :-)

Signature

Good Luck  :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

> No, they are not exclusive.  There are actually 46 different categories
> that
[quoted text clipped - 171 lines]
>> >> >
>> >> > End Sub
Reply to this Message
Jenny - 20 Jun 2007 14:58 GMT
Whoa!  Thanks... that's a bunch to chew on!  I will give it a shot.  Thanks
for all your help.

> Hi Jenny
>
[quoted text clipped - 255 lines]
> >> >> >
> >> >> > End Sub
Reply to this Message
Jenny - 20 Jun 2007 18:04 GMT
Graham -
First let me say... thank you...thank you...thank you...thank you...!!!  You
have been very helpful.  I am running into a run-time error of "Object
Required" on this line of the code:  .ItemsSelected.Count > 0 Then

Any thoughts?
Here is the entire code I have in there now.

Private Sub Command162_Click()
   
   Dim dbs As Database, SqlStr As String
   Dim qdf As QueryDef
   Set dbs = CurrentDb
   Set qdf = dbs.QueryDefs("qryCSDReport")
   Dim sqlWhereString As String
   Dim sqlWhereTrue As Integer
   Dim sqlStart As String
   Dim stateString As String
   Dim countyString As String
   Dim specialtyString As String
   
   
   
   SqlStr = "SELECT [Facility Name] , [Tax ID], STATE, City, [Street
Address], STATE, Zip, County, [Phone #] , [Physical Therapy (PT)],
[Occupational Therapy (OT)], [Speech Therapy (ST)] , MRSA, VRE, TB,
[HIV/AIDS], [Private Rooms], [Total Parental Nutrition (TPN)], [Central
Lines], [PICC Lines] , [Continuous Heparin Infusion], [Intrathecal
Medications], Chemotherapy, [Computerized Clinical Information], [Amputation
Recovery], [Peritoneal Dialysis], Hospice, [HIV/ARC Dementia], Hemodialysis,
[Alzheimer/ Dementia Care], TBI, [Complex Care], [Cognitive Therapy], Chemo,
[Cardiac Rehab], [Behavioral Management], Bariatric, [Bariatric Weight
Limit], [Wall Oxygen/Suction Set-Ups], [Weaning/Decannulation], [Trach - New
and Chronic] , Vents, [Adult Day], [Respite Care], MS, [24 hour admissions],
Pediatric, [Palliative Care] , [IV Pain Meds], [PCA Pumps], [Wound Care
Team], [Whirlpool Therapy (Full Tank)], [Whirlpool Therapy (Extremity Tank)],
[Wound VAC Therapy] , [Estin/Pulse Lavage:]" & _
   "FROM CSD "
             
       
   For i = 0 To Me.STATE.ListCount - 1
       If (Me.STATE.Selected(i) = True) Then
           stateString = stateString & "State = '" & Me.STATE.Column(0, i)
& "' Or "
       End If
   Next i
   
   If Len(stateString) > 0 Then
       sqlWhereTrue = 1
       sqlWhereString = sqlWhereString & " (" & Left(stateString,
Len(stateString) - 4) & ") AND "
   End If
   
   For i = 0 To Me.County.ListCount - 1
   If (Me.County.Selected(i) = True) Then
           countyString = countyString & "County = '" & Me.County.Column(0,
i) & "' Or "
       End If
   Next i
   
   If Len(countyString) > 0 Then
       sqlWhereTrue = 1
       sqlWhereString = sqlWhereString & " (" & Left(countyString,
Len(countyString) - 4) & ") AND "
   End If
   
   For i = 0 To Me.Speciality.ListCount - 1
       If (Me.Speciality.Selected(i) = True) Then
         Speciality = specialityString & "Speciality = '" &
Me.Speciality.Column(0, i) & "' Or "
       End If
   Next i
   
   If Len(specialityString) > 0 Then
       sqlWhereTrue = 1
       sqlWhereString = sqlWhereString & " (" & Left(specialityString,
Len(specialtyString) - 4) & ") AND "
   End If
   
   If sqlWhereTrue = 1 Then
       SqlStr = SqlStr & " WHERE " & Left(sqlWhereString,
Len(sqlWhereString) - 5)
   End If
   
   
   Dim vItem As Variant
   With lstSelectCategories
   If .ItemsSelected.Count > 0 Then
   For Each vItem In .ItemsSelected
   sqlCategories = sqlCategories & .ItemData(vItem) & ","
   Next
   ' remove the last comma
   sqlCategories = Left(sqlCategories, Len(sqlCategories) - 1)
   sqlWhere = 1
   sqlWhereString = sqlWhereString_ & "(CSDID in (Select CSDID from
CSD_Categories where SpecialitesID in (" & sqlCategories & ")) AND "
   End If
   End With
       
   CurrentDb.QueryDefs("qryCSDReport").SQL = SqlStr
   DoCmd.OpenQuery ("qryCSDReport")
           
End Sub
Reply to this Message
Graham Mandeno - 21 Jun 2007 04:57 GMT
Hi Jenny

You do have a multi-select listbox named "lstSelectCategories", don't you?
That's what the "With" statement is referring to.

Also, you have added the block of code to the wrong place.  It should be
BEFORE the bit that says "If sqlWhere = 1 Then".

Have you re-done the design with the Categories table and the junction table
as I suggested?  I'm impressed that you did it so quickly!
Signature

Good Luck  :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

> Graham -
> First let me say... thank you...thank you...thank you...thank you...!!!
[quoted text clipped - 103 lines]
>
> End Sub
Reply to this Message
Jenny - 21 Jun 2007 21:21 GMT
I wouldn't be impressed as I probably did it wrong.
I did create the junction table.  Am I supposed to delete the "PT, OT, (46
total fields of specialites) from my original table now that I have the
junction table?
Here is my code now... I am receiving the error, "Syntax Error (missing
operator) in query expression 'specialitesID in ()'.
Private Sub Command162_Click()
   
   Dim dbs As Database, SqlStr As String
   Dim qdf As QueryDef
   Set dbs = CurrentDb
   Set qdf = dbs.QueryDefs("qryCSDReport")
   Dim sqlWhereString As String
   Dim sqlWhereTrue As Integer
   Dim sqlStart As String
   Dim stateString As String
   Dim countyString As String
   Dim specialtylistString As String
       
   
   SqlStr = "SELECT [Facility Name] , [Tax ID], [Street Address], City,
STATE, Zip, County, [Phone #] , [Physical Therapy (PT)], [Occupational
Therapy (OT)], [Speech Therapy (ST)] , MRSA, VRE, TB, [HIV/AIDS], [Private
Rooms], [Total Parental Nutrition (TPN)], [Central Lines], [PICC Lines] ,
[Continuous Heparin Infusion], [Intrathecal Medications], Chemotherapy,
[Computerized Clinical Information], [Amputation Recovery], [Peritoneal
Dialysis], Hospice, [HIV/ARC Dementia], Hemodialysis, [Alzheimer/ Dementia
Care], TBI, [Complex Care], [Cognitive Therapy], Chemo, [Cardiac Rehab],
[Behavioral Management], Bariatric, [Bariatric Weight Limit], [Wall
Oxygen/Suction Set-Ups], [Weaning/Decannulation], [Trach - New and Chronic] ,
Vents, [Adult Day], [Respite Care], MS, [24 hour admissions], Pediatric,
[Palliative Care] , [IV Pain Meds], [PCA Pumps], [Wound Care Team],
[Whirlpool Therapy (Full Tank)], [Whirlpool Therapy (Extremity Tank)], [Wound
VAC Therapy] , [Estin/Pulse Lavage:]" & _
   "FROM CSD"
             
   For i = 0 To Me.STATE.ListCount - 1
       If (Me.STATE.Selected(i) = True) Then
           stateString = stateString & "State = '" & Me.STATE.Column(0, i)
& "' Or "
       End If
   Next i
   
   If Len(stateString) > 0 Then
       sqlWhereTrue = 1
       sqlWhereString = sqlWhereString & " (" & Left(stateString,
Len(stateString) - 4) & ") AND "
   End If
   
             
   For i = 0 To Me.County.ListCount - 1
   If (Me.County.Selected(i) = True) Then
           countyString = countyString & "County = '" & Me.County.Column(0,
i) & "' Or "
       End If
   Next i
   
   If Len(countyString) > 0 Then
       sqlWhereTrue = 1
       sqlWhereString = sqlWhereString & " (" & Left(countyString,
Len(countyString) - 4) & ") AND "
   End If
   
   
   For i = 0 To Me.SpecialtyList.ListCount - 1
       If (Me.SpecialtyList.Selected(i) = True) Then
         specialtylistString = specialtylistString & "Specialtylist = '" &
Me.SpecialtyList.Column(0, i) & "' Or "
       End If
   Next i
   
   If Len(specialtylistString) > 0 Then
       sqlWhereTrue = 1
       sqlWhereString = sqlWhereString & " (" & Left(specialtylistString,
Len(specialtylistString) - 4) & ") AND "
   End If
   
   
   Dim vItem As Variant
   With SpecialtyList
   If Me.SpecialtyList.ItemsSelected.Count > 0 Then
   For Each vItem In Me.SpecialtyList.ItemsSelected
   sqlCategories = sqlCategories & Me.SpecialtyList.ItemData(vItem) & ","
   Next
   ' remove the last comma
   sqlCategories = Left(sqlCategories, Len(sqlCategories) = -1)
   sqlWhere = 1
   sqlWhereString = sqlWhereString_ & "(CSDID in (Select CSDID from
CSD_Categories where SpecialtiesID in (" & sqlCategories & "))) AND "
   End If
   End With
   
   If sqlWhereTrue = 1 Then
       SqlStr = SqlStr & " WHERE " & Left(sqlWhereString,
Len(sqlWhereString) - 5)
   End If
         
   CurrentDb.QueryDefs("qryCSDReport").SQL = SqlStr
   DoCmd.OpenQuery ("qryCSDReport")
           
End Sub

> Hi Jenny
>
[quoted text clipped - 113 lines]
> >
> > End Sub
Reply to this Message
Graham Mandeno - 22 Jun 2007 03:50 GMT
Hi Jenny

Yes, you no longer have any need for the 46 specialty fields. However, if
you already have data in them, then that data must first be transferred to
the new junction table.  Let me know if this is the case and I'll tell you
how to do it.

It would help me at this point if you list exactly the names of your three
tables and their fields (not counting the "specialty fields" which will
disappear).

Let's say they are as follows (note that it is recommended you don't use any
spaces or non-alphanumeric characters in object names, including table and
field names):

Table CSD
   FacilityID (autonumber)
   FacilityName
   TaxID
   StreetAddress
   City
   State
   Zip
   County
   Phone

Table Specialties
   SpecialtyID (autonumber)
   SpecialtyName (e.g. "Physical Therapy"
   SpecialtyAbbrev (e.g. "PT") [this might be useful for column headings
etc]

Table CSD_Specialties
   FacilityFK (FK stands for "foreign key")
   SpecialtyFK

Now, I understand you want to create a query with the CSD details and then
46 extra columns, one for each specialty, like a spreadsheet.  To do this
from the normalised structure, you need to use a crosstab query.

You *could* create a crosstab query from scratch in code, but I think it
would be much easier to create one and save it, then filter it for your
report.  So, create a query with SQL similar to this:

TRANSFORM Count(CSD_Specialties.FacilityFK)
SELECT CSD.FacilityID, CSD.FacilityName, CSD.TaxID, CSD.StreetAddress,
   CSD.City, CSD.State, CSD.Zip, CSD.County, CSD.Phone
FROM CSD LEFT JOIN (Specialties RIGHT JOIN CSD_Specialties
   ON Specialties.SpecialtyID = CSD_Specialties.SpecialtyFK)
   ON CSD.FacilityID = CSD_Specialties.FacilityFK
GROUP BY CSD.FacilityID, CSD.FacilityName, CSD.TaxID, CSD.StreetAddress,
   CSD.City, CSD.State, CSD.Zip, CSD.County, CSD.Phone
PIVOT Specialties.SpecialtyAbbrev;

That *should* give you something that looks very similar to the unnormalised
table that you have at present.  Save the query as "qryCSDwithSpecialties".

Now, to generate your filtered view of the query, you just need Select *
with a WHERE clause:

=============== start code ==================
Const cAND = " AND "
Const cOR = " OR "
Dim SqlStr As String
Dim sqlWhereString As String
Dim strTemp as String
Dim vItem As Variant

   SqlStr = "SELECT * FROM qryCSDwithSpecialties"
   strTemp = ""
   With Me.StateList
       If .ItemsSelected.Count > 0 Then
           For Each vItem In .ItemsSelected
               strTemp = strTemp & "State = '" & .ItemData(vItem) & "' " &
cOR
           Next
           ' remove the last OR
           strTemp = Left(strTemp , Len(strTemp ) - Len(cOR) )
           sqlWhereString = sqlWhereString & "(" strTemp & ")" & cAND
       End If
   End With

   strTemp = ""
   With Me.CountyList
       If .ItemsSelected.Count > 0 Then
           For Each vItem In .ItemsSelected
               strTemp = strTemp & "County = '" & .ItemData(vItem) & "' " &
cOR
           Next
           ' remove the last OR
           strTemp = Left(strTemp , Len(strTemp ) - Len(cOR) )
           sqlWhereString = sqlWhereString & "(" strTemp & ")" & cAND
       End If
   End With

   strTemp = ""
   With Me.SpecialtyList
       If .ItemsSelected.Count > 0 Then
           For Each vItem In .ItemsSelected
               strTemp = strTemp & .ItemData(vItem) & ","
           Next
           ' remove the last comma
           strTemp = Left(strTemp, Len(strTemp) - 1)
           sqlWhereString = sqlWhereString _
               & "(FacilityID in (Select FacilityFK from CSD_Categories " _
               & "where SpecialtyFK in (" & strTemp & ")))" & cAND
       End If
   End With

   If Len(sqlWhereString) > 0 Then
       ' remove last AND and append to SQL string
       SqlStr = SqlStr & " WHERE " _
           & Left(sqlWhereString, Len(sqlWhereString) - Len(cAND))
   End If

   CurrentDb.QueryDefs("qryCSDReport").SQL = SqlStr
   DoCmd.OpenQuery ("qryCSDReport")

End Sub
======== end code =======================
Signature

Good Luck  :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

>I wouldn't be impressed as I probably did it wrong.
> I did create the junction table.  Am I supposed to delete the "PT, OT, (46
[quoted text clipped - 218 lines]
>> >
>> > End Sub
Reply to this Message
Jenny - 22 Jun 2007 16:26 GMT
Yes, I do need help transferring the data to the junction table.
Here are the tables and fields:
Table CSD
FaciltyName
TaxID
StreetAddress
City
County
State
Zip
Phone#
Products
Notes
CSDID (autonumber)

Table Categories
SpecialtyList
Physical Therapy (PT)
Occupational Therapy (OT)
Speech Therapy (ST)
MRSA
VRE
TB
HIV/AIDS
Private Rooms
Total Parental Nutrition (TPN)
Central Lines
PICC Lines
Continuous Heparin Infusion
Intrathecal Medications
Chemotherapy
Computerized Clinical Information
Amputation Recovery
Peritoneal Dialysis
Hospice
HIV/ARC Dementia
Hemodialysis
Alzheimer/ Dementia Care
TBI
Complex Care
Cognitive Therapy
Chemo
Cardiac Rehab
Behavioral Management
Bariatric
Bariatric Weight Limit
Wall Oxygen/Suction Set-Ups
Weaning/Decannulation
Trach - New and Chronic
Vents
Adult Day
Respite Care
MS
24 hour admissions
Pediatric
Palliative Care
IV Pain Meds
PCA Pumps
Wound Care Team
Whirlpool Therapy (Full Tank)
Whirlpool Therapy (Extremity Tank)
Wound VAC Therapy
Estin/Pulse Lavage:

Table CSD_Categories
CSDID
SpecialtiesID

****What is foreign key?  I have these fields as just numbers.  Are they
supposed to be something else?

Thanks for all your help!!!  

> Hi Jenny
>
[quoted text clipped - 268 lines]
> >> > and Chronic] , Vents, [Adult Day], [Respite Care], MS, [24 hour
> >> > admissions],
Reply to this Message
Graham Mandeno - 24 Jun 2007 23:39 GMT
Hi Jenny

First, "what is a foreign key?"

When two tables are related, the relationship is between a unique key
(usually the primary key) in one table, and another field that contains data
of the same type in the second table.  That "other" field is called the
"foreign key" (FK).  If the FK is itself a unique index (allowing no
duplicates) then the relationship is one-to-one.  More commonly, the FK
field does not have this restriction, so the relationship is one-to-many.
Your relationships here, between your CSD and Specialties tables and your
junction table, are one-to-many.

Now, the data type and size of the FK field must always be identical to its
related primary key.  The one exception is when the PK is an autonumber.  In
this case, the FK must be a long integer (a Number of size "Long").

In my last post I named the fields in the junction table <something>FK to
help you to understand the difference between the primary keys (xxxxID) and
the foreign keys.  I'm sorry if you found that confusing :-)

OK, so do you have this table?

>> Table Specialties
>>     SpecialtyID (autonumber)
>>     SpecialtyName (e.g. "Physical Therapy"
>>     SpecialtyAbbrev (e.g. "PT")

>> Table CSD_Specialties
>>     CSDFK (long integer)
>>     SpecialtyFK (long integer)

The first task is to populate the Specialties table.  Just type in the
specialty names (and abbreviations if you want to use them)  into the table
in datasheet view:

1    Physical Therapy        PT
2    Occupational Therapy OT
3    Speech Therapy        ST
... and so on for the 46 records

Note that you don't type in the numbers - they are generated for you
automatically.

Now, to transfer the data you need to create a query to insert a record into
CSD_Specialties for each record in CSD that has [Physical Therapy (PT)]
checked.  After running this query, you modify it for the second specialty
and re-run in, and so on.

The SQL of the query needs to look like this:

INSERT INTO CSD_Specialties (CSDFK, SpecialtyFK)
   SELECT CSDID, 1 FROM CSD
       WHERE [Physical Therapy (PT)] <> 0;

When you run it, it should say "xxx records inserted into table".

Then you change the "1" to "2" and [Occupational Therapy (OT)] and run it
again.

Your junction table will now be fully populated.
Signature

Good Luck  :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

> Yes, I do need help transferring the data to the junction table.
> Here are the tables and fields:
[quoted text clipped - 368 lines]
>> >> > and Chronic] , Vents, [Adult Day], [Respite Care], MS, [24 hour
>> >> > admissions],
Reply to this Message
Jenny - 25 Jun 2007 16:21 GMT
Thanks Graham!
I got the junction table filled.
I'm working on the code in the filtered view of the query and running into
an error.  I pasted in your code, but I get a "compile error: Method or data
member not found" for Me.State.  You had "Me.Statelist" in your code, but I
don't have a field called this so I tried just Me.State. and still get the
above error.

Any thoughts?
Reply to this Message
Graham Mandeno - 25 Jun 2007 22:07 GMT
Hi Jenny

This should be the name of the *listbox* where you select the state(s) you
wish to filter on.  It has nothing to do with the name of the field in your
CSD table.  In fact, I think you should give it a different name to avoid
confusion, for example, "lstStateFilter".
Signature

Good Luck  :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

> Thanks Graham!
> I got the junction table filled.
[quoted text clipped - 7 lines]
>
> Any thoughts?
Reply to this Message
Jenny - 26 Jun 2007 18:51 GMT
duh, I should have known that.  sorry.
Now, I'm getting a syntax error on:
sqlWhereString = sqlWhereString & "("strTemp & ")" & cAND

any thoughts why?
Reply to this Message
Douglas J. Steele - 26 Jun 2007 19:06 GMT
You're missing an ampersand between the opening parenthesis and the word
strTemp:

sqlWhereString = sqlWhereString & "(" & strTemp & ")" & cAND

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> duh, I should have known that.  sorry.
> Now, I'm getting a syntax error on:
> sqlWhereString = sqlWhereString & "("strTemp & ")" & cAND
>
> any thoughts why?
Reply to this Message
Jenny - 26 Jun 2007 20:55 GMT
I am so close I can taste it.  Both the state and county filters work now.  
I am getting this error message when I try to filter on specialtylist.
Syntax error. in query expression '(CSDID in (Select CSDFK from
CSD_Categories where SpecialtiesFK in (PT)))'.

I have a table CSD,Categories and a junction table of CSD_Categories that
holds all the speciatlies (ie PT).

In case you need it... here is the entire code:
Private Sub CommandCSDreport_Click()
Const cAND = " AND "
Const cOR = " OR "
Dim SqlStr As String
Dim sqlWhereString As String
Dim strTemp As String
Dim vItem As Variant

SqlStr = "SELECT * FROM qryCSDwithCategories"
strTemp = ""
With Me.STATE
If .ItemsSelected.Count > 0 Then
For Each vItem In .ItemsSelected
strTemp = strTemp & "State = '" & .ItemData(vItem) & "'" & cOR
Next
' remove the last OR
strTemp = Left(strTemp, Len(strTemp) - Len(cOR))
sqlWhereString = sqlWhereString & "(" & strTemp & ")" & cAND
End If
End With

strTemp = ""
With Me.County
If .ItemsSelected.Count > 0 Then
For Each vItem In .ItemsSelected
strTemp = strTemp & "County = '" & .ItemData(vItem) & "' " & cOR
Next
' remove the last OR
strTemp = Left(strTemp, Len(strTemp) - Len(cOR))
sqlWhereString = sqlWhereString & "(" & strTemp & ")" & cAND
End If
End With

strTemp = ""
With Me.SpecialtyList
If .ItemsSelected.Count > 0 Then
For Each vItem In .ItemsSelected
strTemp = strTemp & .ItemData(vItem) & ","
Next
' remove the last comma
strTemp = Left(strTemp, Len(strTemp) - 1)
sqlWhereString = sqlWhereString & "(CSDID in (Select CSDFK from
CSD_Categories " & "where SpecialtiesFK in (" & strTemp & ")))" & cAND
End If
End With

If Len(sqlWhereString) > 0 Then
' remove last AND and append to SQL string
SqlStr = SqlStr & " WHERE " _
& Left(sqlWhereString, Len(sqlWhereString) - Len(cAND))
End If

CurrentDb.QueryDefs("qryCSDReport").SQL = SqlStr
DoCmd.OpenQuery ("qryCSDReport")

End Sub

Thanks for all your help!!
Reply to this Message
Douglas J. Steele - 26 Jun 2007 22:18 GMT
Since what's being shown as a value for SpecialtiesPT is text, presumably
you need

strTemp = ""
With Me.SpecialtyList
 If .ItemsSelected.Count > 0 Then
   For Each vItem In .ItemsSelected
     strTemp = strTemp & "'" & .ItemData(vItem) & "',"
   Next
   ' remove the last comma
   strTemp = Left(strTemp, Len(strTemp) - 1)
   sqlWhereString = sqlWhereString & "(CSDID in ( " & _
     "Select CSDFK from CSD_Categories " & _
     "where SpecialtiesFK in (" & strTemp & ")))" & cAND
 End If
End With

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> I am so close I can taste it.  Both the state and county filters work now.
> I am getting this error message when I try to filter on specialtylist.
[quoted text clipped - 63 lines]
>
> Thanks for all your help!!
Reply to this Message
Graham Mandeno - 26 Jun 2007 23:07 GMT
Hi Jenny

Is SpecialtiesFK a text field or a numeric field?

Is the PK of your Specialties table a text field or a numeric (or
autonumber) field?

[The answer to these should both be the same!!]

If the answer is "numeric" then your combo box properties are wrong.

The RowSource should be:
Select SpecialtyID, SpecialtyName from Specialties order by SpecialtyName;

ColumnCount should be 2
ColumnWidths should be 0 (this hides the first column)
BoundColumn should be 1

This will give you a list of numbers in your subquery - for example:
(CSDID in (Select CSDFK from CSD_Categories where SpecialtiesFK in (1,3,5)))

If the answer is "text" then you have a slightly different design from what
I've suggested, by no matter.  Because your "IN" list is now a list of
strings, not numbers, you must wrap each one in quotes:

   For Each vItem In .ItemsSelected
       strTemp = strTemp & "'" & .ItemData(vItem) & "',"
   Next

[Note the two extra single quotes: one in double quotes by itself and one
just before the comma]

It might help if in your next post (I'm sure there *will* be a next one
<smile>) you list the field names and data types of all the fields in your
three tables.  Then we will know we're singing from the same hymn sheet :-)
Signature

Good Luck  :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

> I am so close I can taste it.  Both the state and county filters work now.
> I am getting this error message when I try to filter on specialtylist.
[quoted text clipped - 63 lines]
>
> Thanks for all your help!!
Reply to this Message
Jenny - 29 Jun 2007 03:50 GMT
Your not going to believe this... but that was it.  It works!  Thank you so
much!  Is there a easy way to change the filter from displaying 1's to Yes's
or something?  I know I've taken a lot of your time already, so if your done
with me let me know.  I am very appreciative of all your help!

> Hi Jenny
>
[quoted text clipped - 99 lines]
> >
> > Thanks for all your help!!
Reply to this Message
Graham Mandeno - 30 Jun 2007 04:41 GMT
Hi Jenny

You can make a number display as Yes/No using a format.

The number format has four parts, separated by semicolons.  They are:
1. format for a positive number
2. format for a negative number
3. format for zero
4. format for null

So, for example, a format string like this:

   "Yes";"Yes";"No";"No"

will show "Yes" if the number is non-zero, or "No" if it is zero or null.
If you just want to see the yeses, then use:

   "Yes";"Yes";" "

Signature

Good Luck  :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

> Your not going to believe this... but that was it.  It works!  Thank you
> so
[quoted text clipped - 114 lines]
>> >
>> > Thanks for all your help!!
Reply to this Message
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.