ms query documentation or tutorials? 1

[lalato]

Anyone out there know of any decent MS Query documentation or tutorials? The Help doesn't seem to have what I'm looking for.

Thanks,
--sam

 

[ETID]

Are you familiar with Access?

 

[xlbo]

Good call ETID - the gui interface is very similar to the query builder in Access. In terms of good documentation etc - I've never found any. Then again, there isn't that much to it - it's really just a query builder

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[lalato]

I've used the query builder in Access a few times on some minor databases. However, the MS Query app seems to be a "light" version of the Access Query Builder and it's hard to tell what it's capable of.

For instance... I would like to calculate a field in the Query, then use that calculated field to calculate another field. This doesn't seem possible in MS Query for some reason.

I'm also having a bit of trouble with column headings. It won't let me create them unless they are encapsulated in "quotes". However, every time I go to edit the query, an extra set of ""quotes"" is added to the column headings. Eventually I have to remove the """"quotes"""" by hand.

Thanks for the tip, though, I'll look through my Access stuff to see if I can find anything that might be useful.
--sam

 

[SkipVought]

lalato,

Here's a suggest for the "light" functionality.

In Access, do whatever you want to do and make sure it functions as you desire.

Look at the SQL code, specifically for the calculation of whatever functionality you are not able to do in Query.

THEN, in MS Query, edit your SQL and hand hack the functionality you want. You may have to play around with it. MS Query may not be able to DISPLAY the result in the QBE Grid, but that's OK.



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[xlbo]

Spot on Skip - I've just been getting into doing that myself.

lalato - here's an example of some VBA that was generated by MSQuery - the bits in bold are where I've "amended" teh SQL :

Sub Get_Data()
mSQL = "SELECT Site.Region, count(distinct Site.Prop_Ref_No) as Stockists, PRODUCT_DIM.PRODUCT_TIE_TYPE_DESC, Calendar.Punch_Year_Per, Sum (Product_Sale_Weekly_Sum.Volume_Barrels) as Vol_Pd " & _
"FROM Product_Sale_Weekly_Sum, Site, Calendar, PRODUCT_DIM " & _
"WHERE ( Product_Sale_Weekly_Sum.Product_key=PRODUCT_DIM.PRODUCT_KEY ) " & _
"AND ( Product_Sale_Weekly_Sum.Process_date_key=Calendar.TIME_KEY ) " & _
"AND ( Site.Site_Key=Product_Sale_Weekly_Sum.Site_key ) " & _
"AND ( PRODUCT_DIM.PRODUCT_TIE_TYPE_DESC NOT IN ('CONTAINER', 'GASES', 'N/A', 'OTHER') " & _
"AND Calendar.Punch_Year_Per BETWEEN '" & [SYP] & "' AND '" & [EYP] & "' ) " & _
"AND Calendar.Punch_Year_Per !='2002 14'" & _
"Group BY Site.Region, PRODUCT_DIM.PRODUCT_TIE_TYPE_DESC, Calendar.Punch_Year_Per " & _
"HAVING ( Sum (Product_Sale_Weekly_Sum.Volume_Barrels) > 0 ) " & _
"ORDER BY Site.Region, PRODUCT_DIM.PRODUCT_TIE_TYPE_DESC,Calendar.Punch_Year_Per"

With Sheets("Base&quot
With .QueryTables(1)
.Connection = Array(Array( _
"ODBC;DSN=KnowledgeQuery;Description=Punch Pubs KMS Database;UID=barracg;APP=Microsoft Office XP;WSID=L0001788;DATABASE=KnowledgeQuer" _
), Array("y;LANGUAGE=British;Network=DBMSSOCN;Trusted_Connection=Yes&quot)
.Destination = Range("A1&quot
.CommandText = mSQL
.Name = "Stockists"
.FieldNames = True

.FillAdjacentFormulas = True
.PreserveFormatting = True


.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True

.Refresh BackgroundQuery:=False
End With
End With

So you can tweak the SQL after you have created a basic query to do pretty much what you want - and once you have the code recorded, you don't need to go thru the gui again, just run teh code.....

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[SkipVought]

Geoff,

Your code could be cleaned up some. This is what you need to refresh the query...

    mSQL = "SELECT Site.Region, count(distinct Site.Prop_Ref_No) as Stockists, PRODUCT_DIM.PRODUCT_TIE_TYPE_DESC,  Calendar.Punch_Year_Per, Sum (Product_Sale_Weekly_Sum.Volume_Barrels) as Vol_Pd " & _
    "FROM Product_Sale_Weekly_Sum,  Site,  Calendar,  PRODUCT_DIM " & _
    "WHERE ( Product_Sale_Weekly_Sum.Product_key=PRODUCT_DIM.PRODUCT_KEY  ) " & _
      "AND ( Product_Sale_Weekly_Sum.Process_date_key=Calendar.TIME_KEY  ) " & _
      "AND ( Site.Site_Key=Product_Sale_Weekly_Sum.Site_key  ) " & _
      "AND ( PRODUCT_DIM.PRODUCT_TIE_TYPE_DESC  NOT IN  ('CONTAINER', 'GASES', 'N/A', 'OTHER') " & _
      "AND Calendar.Punch_Year_Per  BETWEEN  '" & [SYP] & "' AND '" & [EYP] & "' ) " & _
      "AND Calendar.Punch_Year_Per !='2002 14'" & _
    "Group BY Site.Region,  PRODUCT_DIM.PRODUCT_TIE_TYPE_DESC, Calendar.Punch_Year_Per " & _
    "HAVING ( Sum (Product_Sale_Weekly_Sum.Volume_Barrels) > 0 ) " & _
    "ORDER BY Site.Region, PRODUCT_DIM.PRODUCT_TIE_TYPE_DESC,Calendar.Punch_Year_Per"
    sConnect = "ODBC;" & _
        "DSN=KnowledgeQuery;" & _
        "Description=Punch Pubs KMS Database;" & _
        "UID=barracg;" & _
        "APP=Microsoft Office XP;" & _
        "WSID=L0001788;" & _
        "DATABASE=KnowledgeQuery;" & _
        "LANGUAGE=British;" & _
        "Network=DBMSSOCN;" & _
        "Trusted_Connection=Yes"

    With Sheets("Base")
        With .QueryTables(1)
          .Connection = sConnect
          .CommandText = mSQL
          .Refresh BackgroundQuery:=False
        End With
    End With

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[xlbo]

Skip - I know but it comforts me to have any settings that are different from the standard ones, specifically laid out in the code and I DO need the name one - that's important actually because otherwise it tends to try and be clever and adds a _1 after the name and then dumps the data to the right of the previous query :-(
'preciate the thought though

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[SkipVought]

That's true INITIALLY, but when you want to refresh the query results, either because the source data has changed and/or the query SQL has changed, these are the only parameters that are required.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[lalato]

Thanks for the tips... I hadn't thought of doing it that way. Now I just have to brush up my SQL skills...

--sam

 

[xlbo]

I know - as I said - the main reason is so that I can see what standard parameters I have changed....

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[SkipVought]

OK. I can understand that.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884