Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old November 7th, 2006, 05:28 PM
Authorized User
 
Join Date: Nov 2006
Location: Seattle, WA.
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default looping through a table

I have a table that lists Medications and each Medication has a specific Medication number.
i.e.
MED1
MED1NR
MED2
MED2NR
MED3
MED3NR
MED4
MED4NR
MED5
MED5NR
MED6
MED6NR
MED6
MED7NR
MED8
MED8NR
MED9
MED9NR

The Medications can occupy different fields, i.e. MED X in MED1 and MED1NR=2, MED Z in MED4 and MED.

What I would like to do is collapse all of these different Meds so that they fill from Left to Right. In the example above MED4 Z would then be listed in MED2 and MED4NR would be listed in MED2NR.

I'm trying to figure out the best way to do this.

Tks,

John




Reply With Quote
  #2 (permalink)  
Old November 8th, 2006, 09:27 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I totaly don't understand your table structure. Is it like this:

tblMed
MedID - PK
MedName - text
MedNum - text - shouldn't be number field

Then your records in your post would be:

MedID MedName MedNum
1 Med1 Med1NR
2 Med2 Med2NR
3 Med3 Med3NR
...

Is this what you have, or something like it?

If so, what do you mean "The Medications can occupy different fields"?

Also, what do you mean by "collapse all of these different Meds so that they fill from Left to Right"?

Are you trying to concatenate a string with med names? Are you trying to redesing the table?

Happy to help.


mmcdonal
Reply With Quote
  #3 (permalink)  
Old November 8th, 2006, 02:21 PM
Authorized User
 
Join Date: Nov 2006
Location: Seattle, WA.
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

This is for a study that I'm working on. We receive information from our project manager concerning medications that respondents take. They can take from 1 up to 9 different medications. Each medication has an associated medication number. This infomation is loaded into a data collection software (CASES) where we verify the medications that the respondent is taking.

For this example; Lets say that Respondent A is takeing Three Medications.

1. Medication X with a Medication number of 2
2. Medication Y with a Medication number of 3
3. Medication Z with a Medication number of 6

During the conduct of the interivew, we verify that the respondent is taking these medications and if they are taking any [u]new</u> medications.

In the interview it looks something like this;

We show that you are taking the following Medications; Are you still taking them?

REPORTED MEDS
1. MED1 (This fills as X) 1=YES, 5=NO
2. MED2 (This fills as Y) 1=YES, 5=NO
3. MED3 (This fills as Z) 1=YES, 5=NO
4. MED4
5. MED5
ANY NEW MEDS
6. MED6 1=YES, Enter Medication Name and Medication Number
7. MED7 1=YES, Enter Medication Name and Medication Number
8. MED8 1=YES, Enter Medication Name and Medication Number
9. MED9 1=YES, Enter Medication Name and Medication Number

If the respondent answers 1 (YES) to these and then indicates that they are also taking Medication R, we would add that new med as MED6 and then look up the MedNr.

I have some code within the instrument that fills the following variables if the respondent answered yes to taking any meds;

So for the example, I would have;

MED X in FUMED1 with the MedNR (2) in FUMED1NR.
MED Y in FUMED2 with the MedNR (3) in FUMED2NR.
MED Z in FUMED3 with the MedNR (6) in FUMED3NR.
MED R in FUMED6 with the MedNR (8) in FUMED6NR.

FUMED1 (MED1)
FUMED2 (MED2)
FUMED3 (MED3)
FUMED4 (MED4)
FUMED5 (MED5)
FUMED6 (MED6) NEW
FUMED7 (MED7) NEW
FUMED8 (MED8) NEW
FUMED9 (MED9) NEW

FUMED1NR
FUMED2NR
FUMED3NR
FUMED4NR
FUMED5NR
FUMED6NR
FUMED7NR
FUMED8NR
FUMED9NR

I have a table in Access; Table name (MEDS) with the following structure that corresponds to what was collected during the interview;
Col1 Col2 Col3 Col4 Col5
STUDYID FUMED1 FUMED1NR FUMED2 FUMED2NR etc

STUDYID=PK,TEXT,WIDTH=8
FUMED1-FUMED9=TEXT WIDTH=25
FUMED1NR-FUMED9NR=TEXT, WIDTH=2

FUMED1
FUMED1NR
FUMED2
FUMED2NR
FUMED3
FUMED3NR
FUMED4
FUMED4NR
FUMED5
FUMED5NR
FUMED6
FUMED6NR
FUMED7
FUMED7NR
FUMED8
FUMED8NR
FUMED9
FUMED9NR

When I import the data, the medicatons that I mentioned above would populate the fields;
MED X in FUMED1,
MED Y in FUMED2,
MED Z in FUMED3 and
MED R in FUMED6 along with their applicable MED1NR fields.

We will be using this information for a follow-up data point when we call back in a few months.

In Access I want to be able to populate those medications in the columns from left to right. i.e.;

FUMED1 = X
FUMED1NR = 2

FUMED2 = Y
FUMED2NR = 3

FUMED3 = Z
FUMED3NR = 6

FUMED4 = R
FUMED4NR = 8

As you can see, if the respondent said no to any of these medications, then that MED wouldn't be listed. So, I could end up with various meds in various fields.

If the respondent in the given example didn't take MED1 or MED2 but took MED3 and MED6

Then the update would look something like this
FUMED1 = Z
FUMED1NR = 6
FUMED2 = R
FUMED2NR = 8
FUMED3
FUMED3NR
FUMED4
FUMED4NR
And so on.


This is a long winded e-mail but the bottom line is I want to take any medications/medication numbers and populate those columns starting from the left where those columns are null.
I hope this helps.

Tks,

John



Reply With Quote
  #4 (permalink)  
Old November 8th, 2006, 02:56 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I think you will need to code this. It is also not a good table structure. You should have a patient (subject) table, a medication table, and a junction table that allows you to assign n number of meds to a patient. Then you can query based on many parameters.

It soulds like what you are going to need to do to populate your table with this info:

Col1 Col2 Col3 Col4 Col5
STUDYID FUMED1 FUMED1NR FUMED2 FUMED2NR etc

Or do you want to change it to another format?

Try this: Send the starting table(s) with structure and sample data, and then send how you want it to look after your code runs. Then I can figure out how to do this.



mmcdonal
Reply With Quote
  #5 (permalink)  
Old November 8th, 2006, 10:02 PM
Authorized User
 
Join Date: Nov 2006
Location: Seattle, WA.
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks so much!!!

Unfortunately, having three seperate tables is not an option.

Would it be possible to send you a screen shot of the table, etc. directly?

Tks,

John


Reply With Quote
  #6 (permalink)  
Old November 9th, 2006, 08:42 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Yes. You can email it to me. I think if you send a shot of the table with some anonymous records as it looks now, and some anonymous records as you would like it to look after this process would help.



mmcdonal
Reply With Quote
  #7 (permalink)  
Old November 9th, 2006, 11:30 AM
Authorized User
 
Join Date: Nov 2006
Location: Seattle, WA.
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

What's your e-mail address?

If you don't want to post it here.

You can send it to my address;

parkjv1@comcast.net

Tks,

John

Reply With Quote
  #8 (permalink)  
Old December 11th, 2006, 07:30 PM
Authorized User
 
Join Date: Nov 2006
Location: Seattle, WA.
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Here is the final result for this issue. I take care of deleting the contents of the three table FuMeds, Data, Target with three seperate delete queries prior to implementing this code.

Many thanks to Michael McDonald


DoCmd.SetWarnings False
'Append the Medications that were pulled from CTAPBL (CASES) to the 1wk table for processing
DoCmd.OpenQuery "CTAP1Wk_QryApndBLMeds"

'Delcare variables As (Type) with the Dimension (Dim) statement
Dim cn As New ADODB.Connection
Dim rsSource As New ADODB.Recordset
Dim rsTarget As New ADODB.Recordset
Dim rsData As New ADODB.Recordset
Dim sSQLSource, sSQLTarget, sSQLData As String
Dim SQLArray As Variant
Dim i, N As Integer

'Turn Warnings On to the user
DoCmd.SetWarnings True

'Create SQL Statements for recordsets
sSQLSource = "SELECT * FROM CTAPBLTo1Wk_tblFuMed"

'sSQLSource = "SELECT * FROM CTAPBLTo1Wk_tblFuMed" 'Source Table
sSQLTarget = "SELECT * FROM CTAPBLTo1Wk_tblTarget" 'Target Table
sSQLData = "SELECT * FROM CTAPBLTo1Wk_tblData" 'Data Table

'Create connection to the local database
Set cn = New ADODB.Connection
With cn
    .Provider = "Microsoft.Jet.OLEDB.4.0;"
    .ConnectionString = "Data Source=" & _
      CurrentProject.Path & "\Survey_fe.mdb"
    .CursorLocation = adUseClient 'Specify the location of the cursor:Client-side or Server Side
    .Open
End With

'Create a recordset for the Source table, Open recordset on Source table with blank columns
'Use the Set keyword when assigning a value to an object variable

Set rsSource = New ADODB.Recordset 'Create recordset Source...tblFuMed
    rsSource.CursorType = adOpenDynamic 'type of cursor that is automatically updated to show additions, deletions and edits to the recordset
    rsSource.LockType = adLockOptimistic 'type of lock placed on the records during editing.
    rsSource.Open sSQLSource, cn 'Open the table...tblFuMed

'Create recordset for the Target table, Open recordset on Target table with blank columns
'Use the Set keyword when assigning a value to an object variable

Set rsTarget = New ADODB.Recordset 'Create recordset Target...tblTarget
    rsTarget.CursorType = adOpenDynamic 'type of cursor that is automatically updated to show additions, deletions and edits to the recordset
    rsTarget.LockType = adLockOptimistic 'type of lock placed on the records during editing.
    rsTarget.Open sSQLTarget, cn 'Open the table....tblTarget

'Open recordset on Data table with blank columns
'Use the Set keyword when assigning a value to an object variable

Set rsData = New ADODB.Recordset 'Create recordset Data...tblData
    rsData.CursorType = adOpenDynamic 'type of cursor that is automatically updated to show additions, deletions and edits to the recordset
    rsData.LockType = adLockOptimistic 'type of lock placed on the records during editing.
    rsData.Open sSQLData, cn 'Open the table....tblData

'Count the number of records in the Source table (tblFuMed), if it doesn't equal 0....Then
'Loop through each field when we encounter a FuMedNr(x) then add it to the tblTarget Table

If rsSource.RecordCount <> 0 Then
    rsSource.MoveFirst 'Move to the first record
    Do Until rsSource.EOF 'End of File
        i = 0 'delare the Array
        'Resize the array using the ReDim statement
        ReDim SQLArray(9) 'changed from 18 when we included mednames
        Do Until i = UBound(SQLArray) + 1
            If rsSource.Fields(i) <> "" Then
                SQLArray(i) = rsSource.Fields(i)
            End If
        i = i + 1
        Loop

        i = 0 'declare the array
        N = 0 'declare the array
        rsTarget.AddNew 'Add new data to Target table
        rsData.AddNew 'Add new data to Data table
        rsData.Fields(0) = SQLArray(0)
        Do Until i = UBound(SQLArray) + 1 'Repeat untill the current upper bound of the array is reached
            If SQLArray(i) <> "" Then
                rsTarget.Fields(N) = SQLArray(i)
                Select Case N
                'If we include MedNames then Case numbers will change as well as rsData.FIelds(x)
                    Case 1 'Field 1 = FuMed1Nr
                        rsData.Fields(1) = i
                    Case 2 'Field 2 = FuMed2Nr
                        rsData.Fields(2) = i
                    Case 3 'Field 3 = FuMed3Nr
                        rsData.Fields(3) = i
                    Case 4 'Field 4 = FuMed4Nr
                        rsData.Fields(4) = i
                    Case 5 'Field 5 = FuMed5Nr
                        rsData.Fields(5) = i
                    Case 6 'Field 6 = FuMed6Nr
                        rsData.Fields(6) = i
                    Case 7 'Field 7 = FuMed7Nr
                        rsData.Fields(7) = i
                    Case 8 'Field 8 = FuMed8Nr
                        rsData.Fields(8) = i
                    Case 9 'Field 9 = FuMed9Nr
                        rsData.Fields(9) = i
                End Select
                N = N + 1
            End If
        i = i + 1
        Loop
        rsData.Update 'Update the Data table
        rsTarget.Update 'Update the Target table
    rsSource.MoveNext
    Loop
End If



'DoCmd.Quit
'Display a message
'MsgBox "Records Transferred." & vbCrLf & "Compare Source and Target tables."
MsgBox "Records Transferred. Continue with AddSamp Process"


End Sub


Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Looping ssaranam SQL Server 2005 2 April 17th, 2008 01:40 AM
Looping deepsea007 XSLT 1 June 14th, 2007 12:13 PM
3D Array Looping - Formatting in table jordan23 XSLT 3 April 30th, 2007 11:57 AM
Looping..? dedex C# 2 January 6th, 2005 11:24 PM



All times are GMT -4. The time now is 07:40 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.