Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Basic steps to change Access to SQL

Posted on 2008-10-03
6
Medium Priority
?
192 Views
Last Modified: 2012-05-05
I have access code that does the following
1) I run a Make table query filtering the data based on what the users enter and create table1
2) Table1 has all the fields I need to run the report as well as 1 blank field.
3) Then I run another Make table query filtering the data based on what the users enters to get all the records with 'rooms' and thats in table2
4) In my code I read table 2, I gather all the rooms in a string and I update the blank field in table1 with the string.
Now I have all I need to run the report. It all works fine, but I'm doing this in access and it's my understanding since I'm linked to SQL server that it would be faster to take more advantage of SQL code.
So based on the above, how do I do that?
1) I created a pass through query that created a table in sql.  Since you can't filter using a pass thru query and you can't create a blank field (if you can do either please tell me how) I'm pretty
much just creating a copy of the original table because nothing is filtered out.  Obviously there's a better way.
In general what would be my steps?
Thanks
0
Comment
Question by:BobRosas
  • 3
  • 3
6 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 22636872
First, you can certainly add a blank fields and filters in a passthru query.   But before going there, my hunch is that we can probably help write a query that returns the result set you want without the make table steps.

We need to see your access queries to move forward on this.

 
0
 

Author Comment

by:BobRosas
ID: 22636964
Does this mean I'll be able to update the 'result set' with the room info (step 4 above)?
I've attached my query but not the code I use to update the make table with rooms.  
Just let me know if you need more code!
Thank you so much!  
SELECT DISTINCT tblDaily.DailyId, tblDaily.QCReportNo, tblDaily.VersionId, tblDaily.DailyDate, tblWorkRelease.Facility, tblWorkRelease.Elevation, tblWorkRelease.Area, tblControl.[SubContract No], tblDaily.AmbientTaken, tblDaily.ActionTaken, tblDaily.Comments, tblDaily.CertifiedBy, tblDaily.Supervisor, tblDaily.Shift, tblControl.[Project No], tblControl.[Project Name], "" AS RoomByVrsn INTO tblMTDaily
FROM tblControl RIGHT JOIN (tblWorkRelease RIGHT JOIN (tblQCReportNo RIGHT JOIN tblDaily ON tblQCReportNo.QCReportNo = tblDaily.QCReportNo) ON tblWorkRelease.WorkReleaseId = tblQCReportNo.WorkReleaseID) ON tblControl.ControlId = tblWorkRelease.ControlId
WHERE (((tblWorkRelease.WorkReleaseId)=[Forms]![frmPrevDailyDFT]![txtReleaseNo]));

Open in new window

0
 
LVL 42

Expert Comment

by:dqmq
ID: 22637105
>Does this mean I'll be able to update the 'result set' with the room info (step 4 above)?

Piece of cake.  

I need to know what you are filtering in Step 3
I need to know the details of the string you are constructing for Step 4



0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:BobRosas
ID: 22653296
I apologize for my long delay.  We had a power outage on Friday...need I say more.

Anyway, the query I use to filter the data in step 3 is attached.

The code for how I get the string in Step 4 is also attached.
In short what I'm doing is looping thru the records (query in step 3) and creating a string with roomNo until QCReport No and TestDescription no longer matche.  Then I save the created string to the blank field in Table1 (from Step 1 & 2) to the record where the QCReportNo and TestDescription match.

Step 3 filter is...
SELECT DISTINCT tblDaily.QCReportNo, tblDaily.VersionId, tblWorkRelease.WorkReleaseId, tblTestsPerformed.TestDescription, tblRooms.RoomNo, tblTestsPerformed.Results
FROM tblWorkRelease RIGHT JOIN (tblQCReportNo RIGHT JOIN (tblDaily INNER JOIN (tblTestsPerformed INNER JOIN tblRooms ON tblTestsPerformed.TestDescId = tblRooms.TestDescId) ON tblDaily.DailyId = tblTestsPerformed.DailyId) ON tblQCReportNo.QCReportNo = tblDaily.QCReportNo) ON tblWorkRelease.WorkReleaseId = tblQCReportNo.WorkReleaseID
WHERE (((tblWorkRelease.WorkReleaseId)=[Forms]![frmPrevDailyDFT]![txtReleaseNo]))
ORDER BY tblDaily.VersionId, tblTestsPerformed.TestDescription, tblRooms.RoomNo;
 
 
Step 4 code
Set rs = qdf.OpenRecordset
    Set rs2 = CurrentDb.OpenRecordset("tblMTTestRooms", DB_OPEN_DYNASET)
    With rs
        Do While Not .EOF
        strTestDescription = rs!TestDescription  'this saves off test description
        strTestDescriptionCur = rs!TestDescription 'this keeps field so when eof code still works
        strRoomNo = strRoomNo & rs!RoomNo & ", "
        rs.MoveNext
        strTestDescriptionCur = rs!TestDescription 'this keeps field so when eof code still works
        If strTestDescription <> strTestDescriptionCur Then
            With rs2
                .Edit
                !RoomNo = Left$(strRoomNo, Len(strRoomNo) - 2)  'deletes trailing comma
                .Update
                rs2.MoveNext
                strRoomNo = ""
            End With
        Else
        End If
        Loop
    End With

Open in new window

0
 
LVL 42

Accepted Solution

by:
dqmq earned 1000 total points
ID: 22654708
Wow! You do realize that VBA code is likely to update the wrong row in tblMTTstRooms?  I mean there is nothing in the code that establishes a relationship between the RoomNo's you are concatenating and the row you are updating????  But I digresss...

The better way to do that in SQL Server is to concatenate your RoomNo's in a function.  Then you can update the table directly using the function.  The attached code is not exact because I do not know all the column names in your table, but it should give the idea.  You also need to add additional parms to the function if you want to break on QCReportNo  (I did not see a break like that in your VBA Code!).








 
Update YOURTABLE
   Set RoomNo=dbo.ConcatRoomNo(TestDescription)
 
Create dbo.function (@TestDescription varchar(max))
  varchar(max)
as
BEGIN
  DECLARE @ANS VARCHAR(MAX)
  SELECT @ANS = ISNULL(@ANS+ ', ','')  + RoomNo
    FROM yourtable WHERE TestDescription=@TestDescription
RETURN @ANS
END
 
 
 
 
 
 
 
 

Open in new window

0
 

Author Closing Comment

by:BobRosas
ID: 31502870
You are correct about updating the wrong row.  I'm going to try and fix that now so I'll close this question and post more later.
Thanks again.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question