Posts by Will Riley

    Re: Excel query to Sql Server


    How fast is the query in Management Studio? Is there a noticeable difference?


    First thing is to determine where the bottleneck is - it may not be Excel.


    Secondly, I'd look at using ADO as opposed to MSQuery - should be much faster, depending on your query.


    As ever, more details would be more helpful.


    (PS - I moved your post from HAVE-A-CHAT to appropriate forum.... probably why you had no response until now)

    Re: Run SQL Stored Procedure from VBA


    Your code is more than a little confusing, so perhaps an example might be best.


    Re: A ball park figure on costings (not to hire!)


    There's probably a very good reason why this has attracted no replies as yet.


    It's very difficult to say when the requirements are so vague.


    If you put a more detailed set of requirements together, I'm sure someone would be able to estimate the time it would take. Then it's just a matter of the rates people would charge, which could vary considerably!

    Re: Ms query 'select distinct' wont work


    Quote from Mark Pajak;567343

    Following on from this I can repeat what happened - when I remove the fields to be displayed in the results from the query and subquery, but leave the tables joined with the DISTINCT clause still in there onm the 'irn' column, I get the correct number of rows. When I add in the fields I need from that table the number rows increases with duplicates for each extra artist returned. Is that right?


    try this


    [vb]
    SELECT e1.ColMainTitle,
    e1.AssAssociationDate ,
    IIf (e1.LocLocationCode= 'Storage', '', e1.LocLocationCode & ' - ' & e1.LocLevel1 ),
    e1.DimWidth,
    e1.DimHeight,
    e1.DimLengthUnit,
    e1.MatTechnique_tab,
    e1.ColObjectNumber,
    e1.AcqCreditLine,
    e1.Namlast,
    e1.Namfirst & ', ' & e1.Nammiddle ,
    e1.ColArtistRole_tab,
    e1.BioBirthDate,
    e1.BioDeathDate,
    e1.BiocommencementDate,
    e1.BiocompletionDate,
    e1.ColCreationNotes,
    e1.LocLevel1 & ' - ' & e1.LocLevel2 & ' - ' & e1.LocLevel3 & ' - ' & e1.LocLevel4 & ' - ' & e1.LocLevel5,
    e1.LocLevel6,
    e1.ecatalogue_key,
    e1.Multimedia

    FROM
    ((((ecatalog.csv e
    LEFT JOIN
    (SELECT M.* FROM Measurem.csv M WHERE M.DimMeasurementType= 'image') M1
    ON e.ecatalogue_key=M.ecatalogue_key)
    LEFT JOIN
    (SELECT Mul.MulMultiMediaRef_key, Mul.ecatalogue_key, Mul.MulIdentifier,Mul.Multimedia FROM MulMulti.csv Mul WHERE Mul.Multimedia <> '' ) Mul1
    ON Mul.ecatalogue_key=e.ecatalogue_key)
    LEFT JOIN
    (SELECT * FROM Associat.csv A WHERE a.AssAssociationType = 'date') A1
    ON A.ecatalogue_key=e.ecatalogue_key)
    LEFT JOIN
    (SELECT distinct art.irn ,
    art.Namlast,
    art.Namfirst,
    art.Nammiddle,
    art.BioBirthDate,
    art.BioDeathDate,
    art.BiocommencementDate,
    art.BiocompletionDate,
    art.ecatalogue_key
    FROM Artist.csv art) art1
    ON art.ecatalogue_key=e.ecatalogue_key) e1
    order by e1.irn [/vb]

    Re: Make 2010 look like 2003


    Quote

    really can not get on with the format of 2010


    A common complaint when 2007 was launched - for Office 2007 the UI was completely rewritten to move from the old menu based UI to what is now commonly known as the Ribbon UI, with further tweaks in 2010.


    There is no real way to make the revised products that use the Ribbon UI it look like pre-2007 versions so don't waste your time.


    My advice is to do one or both of the following.


    1. Keep using the new versions. It takes 3 to 6 months of constant use to get to grips (depending on how much you use them), but once you do you will inevitably "prefer" the new version.
    2. Install Office 2003 as well, then when you need to do something desperately fast (or something that needs to work in 2003) use that instead.

    Re: Windows 7 or vista..?


    Windows 7 is basically Vista that works as Vista should have done. Call it a major service pack :)


    So, definitely Windows 7 (and if possible, yes go for a 64bit machine)

    Re: Sorting Data -macro Support


    First thing I notice is that the branch data in the data sheet is (in some cases) in a different format to the data you are looking up in the Branch List. So you are not comparing apples with apples, so to speak.


    Take the example of branch 002001, which remains in the data after running the macro, even though it is not in the Branch List...


    It "looks" the same in both sheets, but in the data sheet Excel sees it as 2001 (a number) so this is what your macro looks for in the in the Brach Sheet... it finds it too, as part of the value of brach 142001


    You need to ensure the data formats are the same or you will have incorrect results


    You might get away with amending this line


    Code
    1. '
    2. Set rngFindRange = Sheets("Branch List").Range("A1:A" & lngLastBrnchRow).Find(.Cells(lngLoopRow, 2)[B].Text[/B], LookIn:=xlValues)
    3. '


    Which would change the looked up value from 2001 to 002001

    A man was crossing a road one day when a frog called out to him and said, "If you kiss me, I'll turn into a beautiful princess." He bent over, picked up the frog, and put it in his pocket.


    The frog spoke up again and said, "If you kiss me and turn me back into a beautiful princess, I will tell everyone how smart and brave you are and how you are my hero" The man took the frog out of his pocket, smiled at it, and returned it to his pocket.


    The frog spoke up again and said, "If you kiss me and turn me back into a beautiful princess, I will be your loving companion for an entire week." The man took the frog out of his pocket, smiled at it, and returned it to his pocket.... See more


    The frog then cried out, "If you kiss me and turn me back into a princess, I'll stay with you for a year and do ANYTHING you want." Again the man took the frog out, smiled at it, and put it back into his pocket.


    Finally, the frog asked, "What is the matter? I've told you I'm a beautiful princess, that I'll stay with you for a year and do anything you want. Why won't you kiss me?"


    The man said, "Look, I'm a computer programmer. I don't have time for a girlfriend, but a talking frog is cool."

    Jack wakes up at home with a huge hangover he can't believe. He forces
    himself to open his eyes, and the first thing he sees is a couple of aspirin
    next to a glass of water on the side table. And, next to them, a single red
    rose! Jack sits down and sees his clothing in front of him, all clean and
    pressed Jack looks around the room and sees that it is in perfect order,
    spotlessly clean.


    He takes the aspirins, cringes when he sees a huge black eye staring back at
    him in the bathroom mirror, and notices a note on the table:
    "Honey, breakfast is on the stove, I left early to go shopping-Love you!"


    He stumbles to the kitchen and sure enough, there is hot breakfast and the
    morning newspaper. His son is also at the table, eating. Jack asks
    "Son...what happened last night?"


    "Well, you came home after 3 am, drunk and out of your mind. You broke some
    furniture, puked in the hallway, and got that black eye when you ran into
    the door."


    "So, why is everything in such perfect order, so clean, I have a rose, and
    breakfast is on the table waiting for me?"


    His son replies, "Oh THAT! Mum dragged you to the bedroom, and when she
    tried to take your trousers off, you screamed, "Leave me alone, you tart,
    I'm married!


    Broken furniture £85.26
    Hot Breakfast £4.20
    Red Rose bud £3.00
    Two Aspirins £0.38
    Saying the right thing, at the right time.........Priceless...

    Re: Cell Formula Dependant On List Selection


    Does the user not have the ability to drill down within the Pivot Table itself?


    Seems like you are trying to recreate pivot table functionality without using a pivot table... which sounds kinda daft... or maybe i have misunderstood the question ;)

    Re: Pick Dates Through Checkboxes


    Presumably you are restricting the date selection as only specific dates are available?


    Why not use a listbox control on your userform that has its source as a query that returns the available dates from the relevant table(s) ?

    Re: Convert Range To Pseudo Table


    Create a comma delimited string variable of values (eg varParameter)


    'id1','id2','id3','id4' etc


    from your values and pass these as a single variable as follows



    Obviously you'll need to change your paste location too

    Re: UserForm For SQL Query With Controls As Parameters


    It all depends on your data types. Supply the correct data type to your query and it will work... neither of the examples you give are correct dates, so I assume they are perhaps integers? But maybe they are text...


    You could help by explaining your data source a little more