Loading ...
Sorry, an error occurred while loading the content.

Export

Expand Messages
  • Peter Weibel
    Dear All, [Access 2010.]I designed a database that should allow us to record, process and output hotel contract rates. Recipients want to receive the processed
    Message 1 of 3 , Jul 16, 2012
    View Source
    • 0 Attachment
      Dear All,
      [Access 2010.]I designed a database that should allow us to record,
      process and output hotel contract rates. Recipients want to receive the
      processed rates, offers and conditions in Excel file or "similar
      spreadsheet-like format".
      The data design is fairly simple- Every hotel can have 1-n contracts. -
      Each contract covers 1-n room types and - every room type has 1-n rate
      periods. - Each contract can have special offers- We also record (on
      hotel basis) - Payment term - Child policy - Cancellation
      policy
      Only these are important for the export.
      Data:
      HOTEL TABLE (abbreviated)SELECT tblHOTEL.IDHotelID,
      tblHOTEL.txtHOTELNAME, tblHOTEL.txtDESTINATIONID, tblHOTEL.PaymentFROM
      tblHOTEL;
      CONTRACT TABLE (abbreviated)SELECT tblCONTRACT.idCONTRACTID,
      tblCONTRACT.numHOTELID, tblCONTRACT.memMARKETS,
      tblCONTRACT.txtCONTRACTFROM, tblCONTRACT.txtCONTRACTTO,
      tblCONTRACT.memCHILD, tblCONTRACT.memCOMPULSORYFROM tblCONTRACT;
      ROOM TYPE TABLESELECT tblROOMTYPE.idROOMTYPEID,
      tblROOMTYPE.numCONTRACTID, tblROOMTYPE.txtROOMTYPE,
      tblROOMTYPE.txtBOARDFROM tblROOMTYPE;
      PRICES TABLE (abbreviated)SELECT tblPRICES.idRATESID,
      tblPRICES.numROOMTYPEID, tblPRICES.ValidFrom, tblPRICES.ValidTo,
      tblPRICES.SingleFIT, tblPRICES.TwinFIT, tblPRICES.extraADULTFIT,
      tblPRICES.extraCHILDFITFROM tblPRICES;
      SPECIAL OFFERS TABLESELECT tbSPECIALS.idSPECIALID,
      tbSPECIALS.numCONTRACTID, tbSPECIALS.memSPECIALFROM tbSPECIALS;
      I created a report listing hotels (sorted by destination, contract, room
      type, rate period) which I manage to export to Excel.
      My problem now: I have to add special offers, payment terms, child
      policy - and I have to avoid printing them with every price record. They
      should be listed once. Would that be possible at all? On top of that, I
      wonder if there is any way to avoid Excel cutting off text? I noted that
      each cell has a certain length - text exceeding the limit is cut.
      Is what I am trying to achieve possible in Access at all - can such a
      query be constructed? Or a report that can suitably be exported in a
      format that makes the "harvesting" of this data easy? Or would I have to
      think the other way round and try to start in Excel to fill the
      spreadsheet linking to the Access db...?
      Any help is highly appreciated.
      Cheers,
      Pete




      [Non-text portions of this message have been removed]
    • Duane
      I have done something like this using automation. I have a table [ztblMergeAddresses] for the field name [exmFieldName] and the Excel range [exmTarget]. NOTE:
      Message 2 of 3 , Jul 16, 2012
      View Source
      • 0 Attachment
        I have done something like this using automation. I have a table [ztblMergeAddresses] for the field name [exmFieldName] and the Excel range [exmTarget].

        NOTE: I try to store all of my "variable" information in tables so some of the following code might not make sense without the tables.

        Private Sub cmdMergeToExcel_Click()
        Dim strFile As String
        Dim strSQL As String
        Dim strDelim As String
        strSQL = "SELECT mrfFileName, mrfFileExt, mrfQueryName, mrfFilterField, mrfFilterFieldType, mrfActive, " & _
        "exmFieldName, exmTarget " & _
        "FROM ztblMergeFiles INNER JOIN ztblMergeAddresses ON " & _
        "ztblMergeFiles.mrfMrFID = ztblMergeAddresses.exmMrFID " & _
        "WHERE exmTarget is not Null AND mrfMrFID = 1"

        Dim oXLS As Excel.Application
        Dim oWkb As Object
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim rsComp As DAO.Recordset
        Set db = CurrentDb
        Set rs = db.OpenRecordset(strSQL)
        Select Case rs!mrfFilterFieldType
        Case "D"
        strDelim = "#"
        Case "T", "S"
        strDelim = """"
        Case Else
        strDelim = ""
        End Select
        Set rsComp = db.OpenRecordset("SELECT * FROM [" & rs!mrfQueryName & _
        "] WHERE [" & rs!mrfFilterField & "] = " & strDelim & Me.cmpCmpID & strDelim)
        strFile = rs!mrfFileName
        Set oXLS = CreateObject("Excel.Application")
        oXLS.Workbooks.Open strFile
        Set oWkb = oXLS.Workbooks(1)
        With rs
        .MoveFirst
        Do Until .EOF
        'set the value of the Excel range to the field value
        oWkb.Worksheets(1).Range(!exmTarget) = rsComp(!exmFieldName)
        .MoveNext
        Loop
        .Close
        End With
        rsComp.Close
        Set rsComp = Nothing
        Set rs = Nothing
        Set db = Nothing
        oXLS.Visible = True
        Set oXLS = Nothing
        Set oWkb = Nothing
        End Sub

        Duane

        --- In MS_Access_Professionals@yahoogroups.com, "Peter Weibel" <phuketpeter@...> wrote:
        >
        > Dear All,
        > [Access 2010.]I designed a database that should allow us to record,
        > process and output hotel contract rates. Recipients want to receive the
        > processed rates, offers and conditions in Excel file or "similar
        > spreadsheet-like format".
        > The data design is fairly simple- Every hotel can have 1-n contracts. -
        > Each contract covers 1-n room types and - every room type has 1-n rate
        > periods. - Each contract can have special offers- We also record (on
        > hotel basis) - Payment term - Child policy - Cancellation
        > policy
        > Only these are important for the export.
        > Data:
        > HOTEL TABLE (abbreviated)SELECT tblHOTEL.IDHotelID,
        > tblHOTEL.txtHOTELNAME, tblHOTEL.txtDESTINATIONID, tblHOTEL.PaymentFROM
        > tblHOTEL;
        > CONTRACT TABLE (abbreviated)SELECT tblCONTRACT.idCONTRACTID,
        > tblCONTRACT.numHOTELID, tblCONTRACT.memMARKETS,
        > tblCONTRACT.txtCONTRACTFROM, tblCONTRACT.txtCONTRACTTO,
        > tblCONTRACT.memCHILD, tblCONTRACT.memCOMPULSORYFROM tblCONTRACT;
        > ROOM TYPE TABLESELECT tblROOMTYPE.idROOMTYPEID,
        > tblROOMTYPE.numCONTRACTID, tblROOMTYPE.txtROOMTYPE,
        > tblROOMTYPE.txtBOARDFROM tblROOMTYPE;
        > PRICES TABLE (abbreviated)SELECT tblPRICES.idRATESID,
        > tblPRICES.numROOMTYPEID, tblPRICES.ValidFrom, tblPRICES.ValidTo,
        > tblPRICES.SingleFIT, tblPRICES.TwinFIT, tblPRICES.extraADULTFIT,
        > tblPRICES.extraCHILDFITFROM tblPRICES;
        > SPECIAL OFFERS TABLESELECT tbSPECIALS.idSPECIALID,
        > tbSPECIALS.numCONTRACTID, tbSPECIALS.memSPECIALFROM tbSPECIALS;
        > I created a report listing hotels (sorted by destination, contract, room
        > type, rate period) which I manage to export to Excel.
        > My problem now: I have to add special offers, payment terms, child
        > policy - and I have to avoid printing them with every price record. They
        > should be listed once. Would that be possible at all? On top of that, I
        > wonder if there is any way to avoid Excel cutting off text? I noted that
        > each cell has a certain length - text exceeding the limit is cut.
        > Is what I am trying to achieve possible in Access at all - can such a
        > query be constructed? Or a report that can suitably be exported in a
        > format that makes the "harvesting" of this data easy? Or would I have to
        > think the other way round and try to start in Excel to fill the
        > spreadsheet linking to the Access db...?
        > Any help is highly appreciated.
        > Cheers,
        > Pete
        >
        >
        >
        >
        > [Non-text portions of this message have been removed]
        >
      • Peter Weibel
        Hi Duane, That s an interesting solution. Will study it over the weekend. I have also considered creating a table with all fields required and write a small
        Message 3 of 3 , Jul 16, 2012
        View Source
        • 0 Attachment
          Hi Duane,
          That's an interesting solution. Will study it over the weekend.

          I have also considered creating a table with all fields required and write a small procedure that reads all records into the table, omitting duplicates etc.
          But I shall first have a look at your suggestion. I am back to Access again and new to 2010. Need to warm up.

          cheers,

          Pete

          --- In MS_Access_Professionals@yahoogroups.com, "Duane" <duanehookom@...> wrote:
          >
          > I have done something like this using automation. I have a table [ztblMergeAddresses] for the field name [exmFieldName] and the Excel range [exmTarget].
          >
          > NOTE: I try to store all of my "variable" information in tables so some of the following code might not make sense without the tables.
          >
          > Private Sub cmdMergeToExcel_Click()
          > Dim strFile As String
          > Dim strSQL As String
          > Dim strDelim As String
          > strSQL = "SELECT mrfFileName, mrfFileExt, mrfQueryName, mrfFilterField, mrfFilterFieldType, mrfActive, " & _
          > "exmFieldName, exmTarget " & _
          > "FROM ztblMergeFiles INNER JOIN ztblMergeAddresses ON " & _
          > "ztblMergeFiles.mrfMrFID = ztblMergeAddresses.exmMrFID " & _
          > "WHERE exmTarget is not Null AND mrfMrFID = 1"
          >
          > Dim oXLS As Excel.Application
          > Dim oWkb As Object
          > Dim db As DAO.Database
          > Dim rs As DAO.Recordset
          > Dim rsComp As DAO.Recordset
          > Set db = CurrentDb
          > Set rs = db.OpenRecordset(strSQL)
          > Select Case rs!mrfFilterFieldType
          > Case "D"
          > strDelim = "#"
          > Case "T", "S"
          > strDelim = """"
          > Case Else
          > strDelim = ""
          > End Select
          > Set rsComp = db.OpenRecordset("SELECT * FROM [" & rs!mrfQueryName & _
          > "] WHERE [" & rs!mrfFilterField & "] = " & strDelim & Me.cmpCmpID & strDelim)
          > strFile = rs!mrfFileName
          > Set oXLS = CreateObject("Excel.Application")
          > oXLS.Workbooks.Open strFile
          > Set oWkb = oXLS.Workbooks(1)
          > With rs
          > .MoveFirst
          > Do Until .EOF
          > 'set the value of the Excel range to the field value
          > oWkb.Worksheets(1).Range(!exmTarget) = rsComp(!exmFieldName)
          > .MoveNext
          > Loop
          > .Close
          > End With
          > rsComp.Close
          > Set rsComp = Nothing
          > Set rs = Nothing
          > Set db = Nothing
          > oXLS.Visible = True
          > Set oXLS = Nothing
          > Set oWkb = Nothing
          > End Sub
          >
          > Duane
          >
          > --- In MS_Access_Professionals@yahoogroups.com, "Peter Weibel" <phuketpeter@> wrote:
          > >
          > > Dear All,
          > > [Access 2010.]I designed a database that should allow us to record,
          > > process and output hotel contract rates. Recipients want to receive the
          > > processed rates, offers and conditions in Excel file or "similar
          > > spreadsheet-like format".
          > > The data design is fairly simple- Every hotel can have 1-n contracts. -
          > > Each contract covers 1-n room types and - every room type has 1-n rate
          > > periods. - Each contract can have special offers- We also record (on
          > > hotel basis) - Payment term - Child policy - Cancellation
          > > policy
          > > Only these are important for the export.
          > > Data:
          > > HOTEL TABLE (abbreviated)SELECT tblHOTEL.IDHotelID,
          > > tblHOTEL.txtHOTELNAME, tblHOTEL.txtDESTINATIONID, tblHOTEL.PaymentFROM
          > > tblHOTEL;
          > > CONTRACT TABLE (abbreviated)SELECT tblCONTRACT.idCONTRACTID,
          > > tblCONTRACT.numHOTELID, tblCONTRACT.memMARKETS,
          > > tblCONTRACT.txtCONTRACTFROM, tblCONTRACT.txtCONTRACTTO,
          > > tblCONTRACT.memCHILD, tblCONTRACT.memCOMPULSORYFROM tblCONTRACT;
          > > ROOM TYPE TABLESELECT tblROOMTYPE.idROOMTYPEID,
          > > tblROOMTYPE.numCONTRACTID, tblROOMTYPE.txtROOMTYPE,
          > > tblROOMTYPE.txtBOARDFROM tblROOMTYPE;
          > > PRICES TABLE (abbreviated)SELECT tblPRICES.idRATESID,
          > > tblPRICES.numROOMTYPEID, tblPRICES.ValidFrom, tblPRICES.ValidTo,
          > > tblPRICES.SingleFIT, tblPRICES.TwinFIT, tblPRICES.extraADULTFIT,
          > > tblPRICES.extraCHILDFITFROM tblPRICES;
          > > SPECIAL OFFERS TABLESELECT tbSPECIALS.idSPECIALID,
          > > tbSPECIALS.numCONTRACTID, tbSPECIALS.memSPECIALFROM tbSPECIALS;
          > > I created a report listing hotels (sorted by destination, contract, room
          > > type, rate period) which I manage to export to Excel.
          > > My problem now: I have to add special offers, payment terms, child
          > > policy - and I have to avoid printing them with every price record. They
          > > should be listed once. Would that be possible at all? On top of that, I
          > > wonder if there is any way to avoid Excel cutting off text? I noted that
          > > each cell has a certain length - text exceeding the limit is cut.
          > > Is what I am trying to achieve possible in Access at all - can such a
          > > query be constructed? Or a report that can suitably be exported in a
          > > format that makes the "harvesting" of this data easy? Or would I have to
          > > think the other way round and try to start in Excel to fill the
          > > spreadsheet linking to the Access db...?
          > > Any help is highly appreciated.
          > > Cheers,
          > > Pete
          > >
          > >
          > >
          > >
          > > [Non-text portions of this message have been removed]
          > >
          >
        Your message has been successfully submitted and would be delivered to recipients shortly.