Solved

Basic steps to change Access to SQL

Posted on 2008-10-03
6
186 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 

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 250 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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

733 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