Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2002
    Posts
    51

    Exclamation Unanswered: Challenge for a guru! Create queries on the fly...

    Dont ask why, we just need to do something odd...How can you use the system tables in access to do the following:

    1. write a query on the fly for each table in the database (without knowing the table names ahead of time)

    2. the query also needs to combine all the fields in a table into one single field (ie - queryfield:[field1]&[field2}&[etc...]), again without knowing what the field names are ahead of time

    I have searched news groups and web rings all night. Please help!
    -Thanks in advance!
    When in doubt just ask your self,
    -WWSBD?-
    (what would Sponge Bob do?)

  2. #2
    Join Date
    Aug 2002
    Posts
    17
    Try this Code:


    Sub CreateQueries()
    Dim tdfX As TableDef, qdfX As QueryDef, fldX As Field
    Dim strFields As String

    For Each tdfX In CurrentDb.TableDefs

    Set qdfX = CurrentDb.CreateQueryDef(tdfX.Name & "_Query")
    strFields = ""
    For Each fldX In tdfX.Fields
    strFields = strFields & " & [" & fldX.Name & "]"
    Next fldX

    If strFields <> "" Then
    'remove leading "&"
    strFields = Mid(strFields, 3)

    qdfX.SQL = "SELECT " & strFields & " AS AllFields FROM " & tdfX.Name
    End If
    Next tdfX
    End Sub


    I hope, this is what you were looking for. Please note, that this code iterates through all tables in your Database, including system- and hidden tables.

    Thomas

  3. #3
    Join Date
    Sep 2002
    Posts
    51

    how to?

    I tried this with no luck, but not because your idea is faulty...I am not sure how to do this. Are you using an Access module? Thats what I did, mjust copy-and-pasted it in but I keep getting errors when I try to use it.

    Thanks for the quick response!
    When in doubt just ask your self,
    -WWSBD?-
    (what would Sponge Bob do?)

  4. #4
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    take a look at an application that my team developed.
    we call this dynamic quiering

    Dim From_week As String
    Dim to_week As String
    Dim Trade As String
    Dim Service_Contract As String
    Dim Reef_Dry As String
    Dim BTN As String
    Dim Equ As String
    Dim Sh_Concern As String
    Dim Shipper As String
    Dim Sh_Office As String
    Dim Cn_Concern As String
    Dim Consignee As String
    Dim Cn_Office As String
    Dim Line As String
    Dim RCountry As String
    Dim POR As String
    Dim Load As String
    Dim Discharge As String
    Dim Pod As String
    Dim DCountry As String

    'Set Week Variables
    If Week_From_Combo = "Please Select Week" Or Week_To_Combo = "Please Select Week" Then
    TimeBox = MsgBox("Please Select a Time Range", vbOKOnly, "Unknown Time Period")
    End
    Else
    From_week = "[Forms]![Form:_PCRKMS_Selection]![Week_From_Combo]"
    to_week = " and " & "[Forms]![Form:_PCRKMS_Selection]![Week_to_Combo]"
    End If

    'Set Trade Variable
    If Trade_Combo <> "*" Then
    Trade = " and trade like [Forms]![Form:_PCRKMS_Selection]![trade_combo]"
    Else
    Trade = ""
    End If

    'Set Service Contract Variable
    If Sh_Service_Contract <> "*" Then
    Service_Contract = " and sc like [Forms]![Form:_PCRKMS_Selection]![Sh_Service_Contract]"
    Else
    Service_Contract = ""
    End If

    'Set Reefer Dry Variable
    If Reefer_Dry_Combo <> "*" Then
    Reef_Dry = " and cargo like [Forms]![Form:_PCRKMS_Selection]![Reefer_Dry_Combo]"
    Else
    Reef_Dry = ""
    End If

    'Set BTN Variable
    If BTN_Combo <> "*" Then
    BTN = " and BTN like [Forms]![Form:_PCRKMS_Selection]![BTN_Combo]"
    Else
    BTN = ""
    End If

    'Set Equ Variable
    If Equ_Combo <> "*" Then
    Equ = " and EQU like [Forms]![Form:_PCRKMS_Selection]![Equ_Combo]"
    Else
    Equ = ""
    End If

    'Set Shipper Concern Variable
    If Shipper_Concern <> "*" Then
    Sh_Concern = " and SH_Concern like [Forms]![Form:_PCRKMS_Selection]![Shipper_Concern]"
    Else
    Sh_Concern = ""
    End If

    'Set Shipper Variable
    If Shipper_Combo <> "*" Then
    Shipper = " and shipper like [Forms]![Form:_PCRKMS_Selection]![Shipper_Combo]"
    Else
    Shipper = ""
    End If

    'Set Shipper Office Variable
    If Shipper_Office_Combo <> "*" Then
    Sh_Office = " and Sh_office like [Forms]![Form:_PCRKMS_Selection]![Shipper_Office_Combo]"
    Else
    Sh_Office = ""
    End If

    'Set Consignee Concern Variable
    If Consignee_Concern_Combo <> "*" Then
    Cn_Concern = " and cn_concern like [Forms]![Form:_PCRKMS_Selection]![Consignee_Concern_Combo]"
    Else
    Cn_Concern = ""
    End If

    'Set Consignee Variable
    If Consignee_Combo <> "*" Then
    Consignee = " and consignee like [Forms]![Form:_PCRKMS_Selection]![Consignee_Combo]"
    Else
    Consignee = ""
    End If

    'Set Consignee Office Variable
    If Consignee_Office_Combo <> "*" Then
    Cn_Office = " and cn_office like [Forms]![Form:_PCRKMS_Selection]![Consignee_Office_Combo]"
    Else
    Cn_Office = ""
    End If

    'Set Line Variable
    If Line_Combo <> "*" Then
    Line = " and line like [Forms]![Form:_PCRKMS_Selection]![Line_Combo]"
    Else
    Line = ""
    End If

    'Set RCountry Variable
    If RCountry_Combo <> "*" Then
    RCountry = " and r_country like [Forms]![Form:_PCRKMS_Selection]![RCountry_Combo]"
    Else
    RCountry = ""
    End If

    'Set Por Variable
    If POR_Combo <> "*" Then
    POR = " and Por like [Forms]![Form:_PCRKMS_Selection]![POR_Combo]"
    Else
    POR = ""
    End If

    'Set Load Variable
    If Load_Combo <> "*" Then
    Load = " and Load like [Forms]![Form:_PCRKMS_Selection]![Load_Combo]"
    Else
    Load = ""
    End If

    'Set Discharge Variable
    If Discharge_Combo <> "*" Then
    Discharge = " and discharge like [Forms]![Form:_PCRKMS_Selection]![Discharge_Combo]"
    Else
    Discharge = ""
    End If

    'Set POD Variable
    If POD_Combo <> "*" Then
    Pod = " and pod like [Forms]![Form:_PCRKMS_Selection]![POD_Combo]"
    Else
    Pod = ""
    End If

    'Set DCountry Variable
    If DCountry_Combo <> "*" Then
    DCountry = " and d_country like [Forms]![Form:_PCRKMS_Selection]![DCountry_Combo]"
    Else
    DCountry = ""
    End If

    DoCmd.RunSQL "INSERT INTO Table_PCRKMS_Local_Data ( Trade, Year, Quarter, Month, Week, Shipper, Sh_Concern, Sh_Office, Consignee, Cn_Concern, Cn_Office, Line, SC, [String], R_Country, Receipt, Load, Discharge, Delivery, D_Country, Cargo, BTN, Equ_Type, Equ_Size, Equ_Height, Equ, WGT, Containers, FFE, Freight, rNTV2, rNTV1, [RNTV1/FFE], [RNTV1/Container], [RNTV2/FFE], [RNTV2/Container], RMISFLAG, FMC, FMC_Coast )" & _
    "SELECT dbo_DM_LINE_RKMS_AGGR_TBL.Trade,dbo_DM_LINE_RKMS_A GGR_TBL.Year,dbo_DM_LINE_RKMS_AGGR_TBL.quarter,dbo _DM_LINE_RKMS_AGGR_TBL.month, dbo_DM_LINE_RKMS_AGGR_TBL.Week, dbo_DM_LINE_RKMS_AGGR_TBL.Shipper, dbo_DM_LINE_RKMS_AGGR_TBL.Sh_Concern, dbo_DM_LINE_RKMS_AGGR_TBL.Sh_Office, dbo_DM_LINE_RKMS_AGGR_TBL.Consignee, dbo_DM_LINE_RKMS_AGGR_TBL.Cn_Concern, dbo_DM_LINE_RKMS_AGGR_TBL.Cn_Office, dbo_DM_LINE_RKMS_AGGR_TBL.Line" & _
    ",dbo_DM_LINE_RKMS_AGGR_TBL.SC, dbo_DM_LINE_RKMS_AGGR_TBL.String, dbo_DM_LINE_RKMS_AGGR_TBL.R_Country, dbo_DM_LINE_RKMS_AGGR_TBL.Receipt, dbo_DM_LINE_RKMS_AGGR_TBL.Load, dbo_DM_LINE_RKMS_AGGR_TBL.Discharge, dbo_DM_LINE_RKMS_AGGR_TBL.Delivery, dbo_DM_LINE_RKMS_AGGR_TBL.D_Country, dbo_DM_LINE_RKMS_AGGR_TBL.Cargo, dbo_DM_LINE_RKMS_AGGR_TBL.BTN, dbo_DM_LINE_RKMS_AGGR_TBL.Equ_Type, dbo_DM_LINE_RKMS_AGGR_TBL.Equ_Size, dbo_DM_LINE_RKMS_AGGR_TBL.Equ_Height, dbo_DM_LINE_RKMS_AGGR_TBL.Equ, Sum(dbo_DM_LINE_RKMS_AGGR_TBL.WGT) AS SumOfWGT, Sum(dbo_DM_LINE_RKMS_AGGR_TBL.Containers) AS SumOfContainers, Sum(dbo_DM_LINE_RKMS_AGGR_TBL.FFE) AS SumOfFFE" & _
    ",Sum(dbo_DM_LINE_RKMS_AGGR_TBL.Freight) AS SumOfFreight, Sum(dbo_DM_LINE_RKMS_AGGR_TBL.rNTV2) AS SumOfrNTV2, Sum(dbo_DM_LINE_RKMS_AGGR_TBL.rNTV1) AS SumOfrNTV1," & _
    "IIf([rmisflag]='N',IIf(Sum([ffe])=0,0,Sum([rntv1])/Sum([ffe])),Null) AS [RNTV1/FFE]," & _
    "IIf([rmisflag]='N',IIf(Sum([ffe])=0,0,Sum([rntv1])/Sum([containers])),Null) AS [RNTV1/Container]," & _
    "IIf([rmisflag]='N',IIf(Sum([ffe])=0,0,Sum([rntv2])/Sum([ffe])),Null) AS [RNTV2/FFE]," & _
    "IIf([rmisflag]='N',IIf(Sum([ffe])=0,0,Sum([rntv2])/Sum([containers])),Null) AS [RNTV2/Container]," & _
    "dbo_DM_LINE_RKMS_AGGR_TBL.RMISFLAG, dbo_DM_LINE_RKMS_AGGR_TBL.FMC, dbo_DM_LINE_RKMS_AGGR_TBL.FMC_Coast" & _
    " FROM dbo_DM_LINE_RKMS_AGGR_TBL" & _
    " where week between " & From_week & to_week & Trade & Service_Contract & Reef_Dry & BTN & Equ & _
    Sh_Concern & Shipper & Sh_Office & Cn_Concern & Consignee & Cn_Office & _
    Line & RCountry & POR & Load & Discharge & Pod & DCountry & _
    " GROUP BY dbo_DM_LINE_RKMS_AGGR_TBL.Trade, dbo_DM_LINE_RKMS_AGGR_TBL.Year,dbo_DM_LINE_RKMS_AG GR_TBL.quarter,dbo_DM_LINE_RKMS_AGGR_TBL.month," & _
    "dbo_DM_LINE_RKMS_AGGR_TBL.Week, dbo_DM_LINE_RKMS_AGGR_TBL.Shipper, dbo_DM_LINE_RKMS_AGGR_TBL.Sh_Concern, dbo_DM_LINE_RKMS_AGGR_TBL.Sh_Office, dbo_DM_LINE_RKMS_AGGR_TBL.Consignee, dbo_DM_LINE_RKMS_AGGR_TBL.Cn_Concern, dbo_DM_LINE_RKMS_AGGR_TBL.Cn_Office, dbo_DM_LINE_RKMS_AGGR_TBL.Line, dbo_DM_LINE_RKMS_AGGR_TBL.SC, dbo_DM_LINE_RKMS_AGGR_TBL.String, dbo_DM_LINE_RKMS_AGGR_TBL.R_Country, dbo_DM_LINE_RKMS_AGGR_TBL.Receipt, dbo_DM_LINE_RKMS_AGGR_TBL.Load, dbo_DM_LINE_RKMS_AGGR_TBL.Discharge, dbo_DM_LINE_RKMS_AGGR_TBL.Delivery, dbo_DM_LINE_RKMS_AGGR_TBL.D_Country, dbo_DM_LINE_RKMS_AGGR_TBL.Cargo, dbo_DM_LINE_RKMS_AGGR_TBL.BTN, dbo_DM_LINE_RKMS_AGGR_TBL.Equ_Type, dbo_DM_LINE_RKMS_AGGR_TBL.Equ_Size, dbo_DM_LINE_RKMS_AGGR_TBL.Equ_Height, dbo_DM_LINE_RKMS_AGGR_TBL.Equ, dbo_DM_LINE_RKMS_AGGR_TBL.RMISFLAG, dbo_DM_LINE_RKMS_AGGR_TBL.FMC, dbo_DM_LINE_RKMS_AGGR_TBL.FMC_Coast" & _
    " Order by dbo_DM_LINE_RKMS_AGGR_TBL.Week"

    the dynamic part of this quiery is in the where clause. we didnt know what our end users were going to filter by so we created a vairable for every posibility, and if it was filled in it was used. this is not the exact answer to your problem but hopefully it will get your team thinking in the right way
    Jim

Posting Permissions

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