SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Feb 2001
    Location
    forest of dean UK
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need Help With SELECT

    I'm having problems with getting the results from my database these two queries result in the correct info I need but how do I do this in ASP? I have no worries about the date side of things but I need the eventual result of Technician name and the number of machines they have done.

    Can anyone help?

    Here's the two queries

    (This bit is technician_machines_qry)


    SELECT Machine_Data_tbl.Technician_Number, Technician_tbl.Technician_Name, Machine_Data_tbl.Machine_Serial_Number, Sum(Machine_Data_tbl.Quantity) AS SumOfQuantity, Machine_Data_tbl.Machine_Type_Number, Machine_Data_tbl.Date_Reported
    FROM Technician_tbl INNER JOIN Machine_Data_tbl ON Technician_tbl.Technician_Number = Machine_Data_tbl.Technician_Number
    GROUP BY Machine_Data_tbl.Technician_Number, Technician_tbl.Technician_Name, Machine_Data_tbl.Machine_Serial_Number, Machine_Data_tbl.Machine_Type_Number, Machine_Data_tbl.Date_Reported
    HAVING (((Machine_Data_tbl.Date_Reported)=[Forms]![Open_Report_frm]![Start Date]));

    ...................................................

    (This bit actually produces the report)

    SELECT technician_machines_qry.Technician_Name, Sum(technician_machines_qry.Machine_Type_Number) AS SumOfMachine_Type_Number
    FROM technician_machines_qry
    GROUP BY technician_machines_qry.Technician_Name;
    ........................................................

    Can anyone re-write this little lot in to a sql statement that I can use to glean my info from?

    Any suggestions would be most helpful

    Thanks Col

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,294
    Mentioned
    123 Post(s)
    Tagged
    1 Thread(s)

    Re: Need Help With SELECT

    Couldn't you just do this?

    Code:
    SELECT T.Technician_Name, 
               Sum(M.Machine_Type_Number) As  
               SumOfMachine_Type_Number 
      FROM Technician_tbl T,
               Machine_Data_tbl M 
    WHERE T.Technician_Number = M.Technician_Number 
    GROUP BY T.Technician_Name
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  3. #3
    SitePoint Member
    Join Date
    Feb 2001
    Location
    forest of dean UK
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks dave I'll try it out let you know how I get on

    Cheers Col.

  4. #4
    SitePoint Enthusiast pinkstar's Avatar
    Join Date
    May 2001
    Location
    in a gallaxy far, far away
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    weird error message

    I'm working with two databases as well, but getting the following error message:

    Microsoft JET Database Engine error '80004005'

    Could not find file 'C:\WINNT\system32\master.mdb'.

    Here is my code:

    <%
    Set Recordset2 = Server.CreateObject("ADODB.Recordset")
    Recordset2.ActiveConnection = MasterPath
    Recordset2.Source = "SELECT * FROM master.master, members.Members WHERE master.master.Field1 = members.Members.AcctNo"
    Recordset2.CursorType = 0
    Recordset2.CursorLocation = 2
    Recordset2.LockType = 3
    Recordset2.Open
    Recordset2_numRows = 0
    %>

    What am I doing wrong that it's looking in an entirely different place for my database?

    Thanks!
    pb.

  5. #5
    SitePoint Member
    Join Date
    Feb 2001
    Location
    forest of dean UK
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not Really sure pinkstar still a bit green in this game, is master path the DSN? I'm not actually pulling from two Dbs just comparing two tables so not so complicated. Hope some one can give you an answer. Dave maxwell seems like a guy that knows his stuff THANKS DAVE it worked a TREAT!!!!!!!!!

    Take it easy
    Col.

  6. #6
    SitePoint Enthusiast pinkstar's Avatar
    Join Date
    May 2001
    Location
    in a gallaxy far, far away
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    oooohh....

    Two *tables* not two databases... I see...

    Thanks for the reply Col, and the encouraging words. I did some more research after I posted, and found that I may be able to successfully accomplish querying two databases with data shaping.

    Wish me luck!

    Toni

    PS: Anyone know what data shaping is?
    pb.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •