Print

Please login or register.
1 Hour 1 Day 1 Week 1 Month Forever
Login with username, password and session length

[Martin_1962]

scuse my ignorance,wtf is sql ?  

An (almost) universal way of accessing databases using a supposedly standard language.

Quite simple once you get hang of it

Orginally SQL was a Microsoft / Sybase partnership, but they decided to go their seperate ways.

Some code is specific to certain databses.

SQL was invented in the 1970s pre Microsoft

[Vamps]

scuse my ignorance,wtf is sql ?  

Did you not know

I didn't either,   so pleased I was not alone.

[phil her up]

whats a computer?

[albitz]

SQL - Structured Query Language.  It's a non-procedural programming language for accessing relational databases.  Any the wiser?  

Ian - Try the following:

Select j.JobNo, c.Title, c.FirstName, c.LastName,
      j.CallerID, s.StaffID, j.DateCreated, j.TimeCreated,
      j.StatusID, j.Description
from  Job j INNER JOIN Client c ON j.ClientID = c.ClientID
               INNER JOIN Staff s ON j.TakenBy = s.StaffID

What is CallerID?   Seems to be a foreign key, but not to any of the tables shown.  And I presume that the StaffID equates to the Staff Number and that is what you want to display.  Otherwise the ERD looks fine to me.

Hope it works, but I can’t guarantee the code – don’t do too much of this stuff these days.

I only joined today, and that was just to get my car fixed…

no,sorry,youve lost me  

[Mr Skrunts]

whats a computer?

It's a box with bits in it, with a keyboard and mouse connected to it that you bash and bang to help you feel better about your own inadequacy to use it properly.

[Ian_D]

SQL - Structured Query Language.  It's a non-procedural programming language for accessing relational databases.  Any the wiser?  

Ian - Try the following:

Select j.JobNo, c.Title, c.FirstName, c.LastName,
      j.CallerID, s.StaffID, j.DateCreated, j.TimeCreated,
      j.StatusID, j.Description
from  Job j INNER JOIN Client c ON j.ClientID = c.ClientID
               INNER JOIN Staff s ON j.TakenBy = s.StaffID

What is CallerID?   Seems to be a foreign key, but not to any of the tables shown.  And I presume that the StaffID equates to the Staff Number and that is what you want to display.  Otherwise the ERD looks fine to me.

Hope it works, but I can’t guarantee the code – don’t do too much of this stuff these days.

I only joined today, and that was just to get my car fixed…

I will give that a go after my lunch!  

Sorry, I forgot all about the CallerID, yeh there is another table, but i removed it from the ERD as it was a bit misleading!

Cheers

[Ian_D]

Ive tried all the above, and still not working  

Im totaly lost now  

Will have another play with it now and see what happens....

[Ian_D]

[size=18]YESSSSSSSSSSSSSS![/size]

I 'Think' ive sorted it..

Stupid MS Access connection requires brackets!

Ive now been at this for bloody ages! Started Yesterday at lunch time, and didn’t give up till 3:45am! (15+ hrs of my life wasted! )
Anyway, this morning I continued... and 2 hrs later I've come up with this:

<table border="0" align="center" rules="rows"><b>
<tr align="left">
  <th width="50"><font color="#ff0000">Job</th>
  <th width="150"><font color="#ff0000">Client</th>
  <th width="150"><font color="#ff0000">Caller</th>
  <th width="150"><font color="#ff0000">TakenBy</th>
  <th width="100"><font color="#ff0000">DateCreated</th>
  <th width="100"><font color="#ff0000">TimeCreated</th>
  <th width="100"><font color="#ff0000">Status</th>
  <th width="400"><font color="#ff0000">Description</th>
      </tr></b></font>

            <%
            Set RSDisplay=Server.CreateObject("ADODB.Recordset")
            SQL="Select * FROM (((Job INNER JOIN Client ON Job.ClientID = Client.ClientID)" & _
                                          " INNER JOIN Staff On Job.TakenBy = Staff.StaffID) INNER JOIN Status ON Job.StatusID = Status.StatusID) " & _
                                          "LEFT OUTER JOIN Caller On Job.CallerID = Caller.CallerID"
            RSDisplay.Open SQL,db
            Do While Not RSDisplay.EOF %>

<tr valign="top">
    <td><%=RSDisplay.Fields("JobNo")%></td>
    <td><%=RSDisplay.Fields("ClientTitle")%>&nbsp;<%=RSDisplay.Fields("ClientFirstName")%>&nbsp;<%=RSDisplay.Fields("ClientLastName")%></td>
    <td>
<%
If RSDisplay.Fields("CallerID") <> 0 then
Response.Write(RSDisplay.Fields("CallerFirstName"))
Else
Response.Write("<==")
End If
%></td>
    <td><%=RSDisplay.Fields("SFirstName")%>&nbsp;<%=RSDisplay.Fields("SLastName")%></td>
    <td><%=RSDisplay.Fields("DateCreated")%></td>
    <td><%=RSDisplay.Fields("TimeCreated")%></td>
    <td><%=RSDisplay.Fields("Status")%></td>
    <td><%=RSDisplay.Fields("Description")%></td>
  </tr>


  <% RSDisplay.MoveNext
Loop

RSDisplay.Close
db.close


%>

</table>

fingers crossed now that it keeps working!  

[jereboam]

Congratulations!

I've never seen anything like that, but then I have religiously avoided trying to use MS Access as a real database since I used it for small internal admin system 10 years ago.  It isn't suitable for use in large applications.  

That SQL isn't ANSI standard, by the way, and I don't think it would work in any other context.  I'm really surprised that you can actually use brackets in a FROM clause (other than in a sub-select, which this isn't).  I may ask one of the MS specialists at work - they do this sort of stuff.

Anyway, I'm very glad you got it working.

[Ian_D]

Congratulations!

I've never seen anything like that, but then I have religiously avoided trying to use MS Access as a real database since I used it for small internal admin system 10 years ago.  It isn't suitable for use in large applications.  

That SQL isn't ANSI standard, by the way, and I don't think it would work in any other context.  I'm really surprised that you can actually use brackets in a FROM clause (other than in a sub-select, which this isn't).  I may ask one of the MS specialists at work - they do this sort of stuff.

Anyway, I'm very glad you got it working.

Im just a bit worried how its going to perform when theres a few 100 records in it   :-/

[Ian_D]

Congratulations!

I've never seen anything like that, but then I have religiously avoided trying to use MS Access as a real database since I used it for small internal admin system 10 years ago.  It isn't suitable for use in large applications.  

That SQL isn't ANSI standard, by the way, and I don't think it would work in any other context.  I'm really surprised that you can actually use brackets in a FROM clause (other than in a sub-select, which this isn't).  I may ask one of the MS specialists at work - they do this sort of stuff.

Anyway, I'm very glad you got it working.

Im just a bit worried how its going to perform when theres a few 100 records in it   :-/

Oh Dear...

My server at home has now died on me

Balls! Im at work atm, so cant just reboot it / check internet connection!

Ah well, guess I will have to wait till I get back home.

My Pic has dissaperad too <===  

[jereboam]

I don't think you'll have too much trouble with hundreds of records, or even thousands, but if you need to manage tens of thousands, then Access is probably not the best choice.  

Depends what it's for, and how many users you've got, I suppose.  

[master-sambo]

I work with a relational database where users have access to well over 500,000 records at any one time.  This is using Sybase as the engine.  We run SQL's on them databases all the time, and sure, there is a bit of lag, but they work fine.

[Ian_D]

I don't think you'll have too much trouble with hundreds of records, or even thousands, but if you need to manage tens of thousands, then Access is probably not the best choice.  

Depends what it's for, and how many users you've got, I suppose.  

I did manage to get it to work using 3 or 4 WHERE statments. BUT... I worked out that it was filtering through the data, looping round, and if I had 99 Jobs on the system, it would have to search 96,059,601 records. Unless Ive got the wrong end of the stick somewhere?

[jereboam]

Good grief!

I wouldn't know where to begin tuning that one.  Given that you are actually using dynamic SQL rather than talking straight to the database, I haven't got any idea where to start looking for the execution plan.  Does Access have an optimiser?  Try reading the help files on execution plans and optimisation.

Did I mention I've got 700,000 lines of Java to go through to find the embedded SQL and optimise that?      

I don't even speak Java...