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
Solved

Basic steps to change Access to SQL

Posted on 2008-10-03
6
185 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
OPENDATASOURCE 8 42
SQL Server Degrading on Write 13 70
Present Absent from working date rage 11 36
SSRS: Why is Visual Studio stripping these properties? 2 21
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 …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

839 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