Results 1 to 12 of 12

Thread: Help with SQL

  1. #1
    Join Date
    Jul 2011
    Posts
    47

    Unanswered: Help with SQL

    Hi,

    Before I state the problem, I must mention that I posted this question in another forum. I have posted it here as I need to get this working ASAP. Unfortunately for me, the person who was helping me has left for a trip.

    The link:
    Access Help and How-to - Microsoft Office Help With Query Required... - UtterAccess Discussion Forums

    The problem:
    I have a query that seaches 3 tables:

    1) tblclientdetails(FamilyID, First Name, Surname,Status, Typeofwork)
    2) tbllegalaidledger(Code, Minutes, Items, Billed Total,All, FamilyID)
    3) tbllegalaidfiancialledger (Code, Credit, FamilyID)
    FamilyID is a foregin key in the last two tables.

    I need the query to search the tbllegalaidfinancialledger in the CODE for "PA" and display the credit for that, if a particular person does not have a "PA" in tbllegalaidfinacialledger i need to query just to show nothing but still show that person. (i.e. IIf([tbllegalaidfiancialledger.Code]="PA",[Credit],"0"))


    I have uploaded the report I require help on, I have been having problems on the last column as the it is based on a third table.

    I have got a part of the query working the way I need to, at the moment the query returns 0 when there is no value for credit. However, when there is a value for credit it is not returning it on the report but when I run the query the credit value is visible.

    Any further help would be greatly appreciated.

    Regards
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Provide us with some sample data for the three tables, and give the result you expect from the SQL script.

    You are on the SQL Server forum, not the Access forum. The Access SQL dialect Microsoft has blessed the world with, is not standard SQL. You may have to adjust the script somehow to make it work on Access.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jul 2011
    Posts
    47
    Quote Originally Posted by Wim View Post
    Provide us with some sample data for the three tables, and give the result you expect from the SQL script.

    You are on the SQL Server forum, not the Access forum. The Access SQL dialect Microsoft has blessed the world with, is not standard SQL. You may have to adjust the script somehow to make it work on Access.
    Hi Wim,

    I have uploaded a compressed excel worksheet with sample data and the result I need.

    Thanks for the heads up, I don't mind adjusting the script at all. I just need something to work!

    A little bit more info:

    tblclientdetails has a lot more fields, I need all of them. I didn't put them all in.
    The records need to be searched using a criteria. i.e. [Status]="active" and the [Typeofwork]="legalaid"

    there maybe 1 or a a couple of thousands of records related to one record(from tblclientdetails) in tbllegalaidledger.

    in the third table, this is where I have an issue, there may or may not be a related recorded. If there is a related record, I need the actual value "Credit" to be shown. If there is no, I need a "0". Also, the "Code" field has a criteria "PA", i.e. i only need the fields where [Code] = "PA"

    Also, the other problem I have had if i managed to get the some part of it working. I kept getting each "Credit" field which was related repeating more one 10 times.

    If you need any more information, let me know.

    Thanks
    Attached Files Attached Files

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this:
    Code:
    SELECT 1 as SEQ, C.FamilyID, C.FileNumber, C.FirstName, C.Surname, 
    	LAL.Code, LAL.Cost, LAL.Minutes_, LAL.Items, LAL.BilledTotal, LAL.All_
    	, NULL as Code, NULL as Credit
    FROM #ClientDetails as C
    	INNER JOIN #LegalAidLedger as LAL ON
    		C.FamilyID = LAL.FamilyID
    UNION ALL
    SELECT 2 as SEQ, C.FamilyID, C.FileNumber, C.FirstName, C.Surname, 
    	NULL, NULL, NULL, NULL, NULL, NULL, LAFL.Code, LAFL.Credit
    FROM #ClientDetails as C
    	INNER JOIN #LegalAidFiancialLedger AS LAFL ON
    		C.FamilyID = LAFL.FamilyID AND
    		LAFL.Code = 'PA'
    ORDER BY FamilyID, SEQ
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Jul 2011
    Posts
    47
    Hi Wim,

    Thanks for the reply, I'm getting a "join expression not supported". I'll try to get that working and I will let you know if it works.

    Thanks for your help.

  6. #6
    Join Date
    Jul 2011
    Posts
    47
    Hi Wim,

    Can you look at this SQL code and see if it makes sense? I''m getting a "Data-mismatch" error when i run this, can you see any problems?
    This is in Access code:

    SELECT tblClientDetails.FamilyID, tblClientDetails.FileNumber, tblClientDetails.FirstName, tblClientDetails.Surname, tblClientDetails.Status,tblClientDetails.Typeofwor k,
    tblLegalAidLedger.Code, tblLegalAidLedger.Cost, tblLegalAidLedger.Minutes, tblLegalAidLedger.Items, IIf([tblLegalAidLedger.Code] In ("AT","PR","AD","ATC","WA","TR"),([cost]/60)*[Minutes],IIf([tblLegalAidLedger.Code] In ("LO","TE","LI"),[Items]*[cost])) AS [All], tblLegalAidLedger.BilledTotal, NULL as [1Code] , NULL as [Credit]
    FROM tblClientDetails INNER JOIN tblLegalAidLedger
    ON tblClientDetails.FamilyID = tblLegalAidLedger.FamilyID
    WHERE (((tblClientDetails.Status)="Active")
    AND ((tblClientDetails.Typeofwork)="Legal Aid"))
    UNION ALL
    SELECT tblClientDetails.FamilyID, tblClientDetails.FileNumber, NULL, NULL,
    NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, tbllegalaidfiancialledger.Credit
    FROM tblClientDetails LEFT JOIN tbllegalaidfiancialledger
    ON tblClientDetails.FamilyID = tbllegalaidfiancialledger.FamilyID
    ORDER BY tblClientDetails.FileNumber;
    Last edited by rasghar; 12-10-11 at 22:29.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you're missing the ELSE value in your second, nested IIF
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Nov 2011
    Location
    Houston, TX
    Posts
    32
    Quote Originally Posted by rasghar View Post
    Hi Wim,

    Can you look at this SQL code and see if it makes sense? I''m getting a "Data-mismatch" error when i run this, can you see any problems?
    This is in Access code:
    Alright let's get my brain working in Access mode again. Ouch. oh god, it hurts. Let me attempt to translate this in to English.

    "Select the FamilyId, FileNumber, FirstName, Surname, status, type of work (AS k) from the table tblClientDetails, and the Code, Cost, Minutes, and Items from the table tblLegalAidLedger

    If the Code from tblLegalAidLedger is "AT", "PR", "AD", "ATC", "WA", or "TR",
    ???? the [Cost] divided by 60 multiplied by [Minutes] ???
    If the Code is (....)

    okay that's enough. My eyes are bleeding now.

    ----

    Okay, first of all... A clear idea of what exactly it is you want to return in your query - EXACTLY - would be a huge help to us in helping you. From the looks of it, you've only got two tables there, so the actual table definitions, as well as the business logic you're trying to implement would be very useful in helping us help you solve this problem. [EDIT] Derpa derp derp. You did give us the table definitions. FML. lemme looksy at your attachments.

    Second of all, my approach to performing complex operations via SQL in Microsoft Access is to do all within my power to not make thing any more complicated than they absolutely need to be. Perhaps write a query that joins the two tables with all of the values you're going to need to deal with ahead of time, THEN write a much simpler query that just tests and returns the values you need.

    Access doesn't have much of the same powerful capabilities that SQL Server has, but it does have some tools I used to use kind of as work-around, ad-hoc replacements for SQL Server stuff.

    For example, Microsoft Access doesn't have "Stored Procedures" per se. But you know what they DO have? Modules, baby. My second approach would be to create a VBA modules and just programmatically browbeat the database into giving me what I need, and creating a new table with all that data from code, then using said table to populate my report.

    As for your SQL Code itself, I have to ask you... Do you have a ton of columns called "NULL"? Because um... In your Select statement:

    Code:
    SELECT 
         tblClientDetails.FamilyID, 
         tblClientDetails.FileNumber, 
         NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
    ^ What is this? SELECT <insert list of TABLE COLUMNS here>. You seem to be telling it to select a bunch of columns named "NULL", which is not something I... have ever seen or heard of before.

    My point being, If I have a query that's more than ten lines long, and I can not read it clearly back to myself or anyone else in plain english, I'm doing it wrong.
    Last edited by asherman86; 12-11-11 at 15:32.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by asherman86 View Post
    You seem to be telling it to select a bunch of columns named "NULL", which is not something I... have ever seen or heard of before.
    no, those aren't column names, those are values

    well, they're NULL values, which aren't really values, but they are fine right there

    they're acting as placeholder values because all SELECTs in a union have to return the same number of columns

    i'm not sure where the data mismatch is, though
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Don't have a copy Access to test this on, and don't remember if it is required, but I notice that the second IIF is missing the FALSE clause (the ELSE clause).

    Also, do your explicit NULLs in the second SELECT query require aliasing? In other words, AS FirstName and AS Surname, etc. May not be required and, as I said, don't have a copy of Access here to test-with, but that may be an area for clean-up.

    Here's your code adjusted a bit to make it more readable, which makes the issues I've talked about more visible:

    Code:
    SELECT  tblClientDetails.FamilyID
            ,tblClientDetails.FileNumber
            ,tblClientDetails.FirstName
            ,tblClientDetails.Surname
            ,tblClientDetails.Status
            ,tblClientDetails.Typeofwork
            ,tblLegalAidLedger.Code
            ,tblLegalAidLedger.Cost
            ,tblLegalAidLedger.Minutes
            ,tblLegalAidLedger.Items
            ,IIf([tblLegalAidLedger.Code] In ("AT","PR","AD","ATC","WA","TR")
                ,'THEN
                ([cost]/60)*[Minutes]
                ,'ELSE
                IIf([tblLegalAidLedger.Code] In ("LO","TE","LI")
                    ,'THEN
                    [Items]*[cost]
                )
            ) AS [All]
            ,tblLegalAidLedger.BilledTotal
            ,NULL as [1Code]
            ,NULL as [Credit]
    FROM    tblClientDetails
    INNER
    JOIN    tblLegalAidLedger ON
                tblClientDetails.FamilyID = tblLegalAidLedger.FamilyID
    WHERE   tblClientDetails.Status="Active"
            AND tblClientDetails.Typeofwork="Legal Aid"
    
    UNION ALL
    
    SELECT  tblClientDetails.FamilyID
            ,tblClientDetails.FileNumber
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,tbllegalaidfiancialledger.Credit
    FROM    tblClientDetails
    LEFT
    JOIN    tbllegalaidfiancialledger ON
                tblClientDetails.FamilyID = tbllegalaidfiancialledger.FamilyID
    ORDER
    BY      tblClientDetails.FileNumber;
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by PracticalProgram View Post
    ...I notice that the second IIF is missing the FALSE clause (the ELSE clause).
    i already caught that a few posts ago

    Quote Originally Posted by PracticalProgram View Post
    Also, do your explicit NULLs in the second SELECT query require aliasing?
    nope

    union query results are created with column names as defined by the first SELECT in the union

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    r937,

    Oops. Had read your post but must have skimmed it without picking-up your point. Don't worry, I'm not jassi.singh's twin bother.

    Ken
    Ken

    Maverick Software Design

    (847) 864-3600 x2

Posting Permissions

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