Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2002
    Location
    TN
    Posts
    8

    Unanswered: ms access 2002 insert/update?

    I have a table that has a list of restaurants and different items and quantities they have ordered. Each item and quantity is listed on a different row and the restaurant information is duplicated. How can I get to a table that has 1 row per restaurant, and fields appended onto the end of the record that have the items and number ordered?
    For example, I have
    Item number restaurant
    turkey breast 3 Olivers
    chicken 2 Olivers
    turkey breast 5 Olivers
    chicken 1 Ali Babba's
    turkey breast 2 Ali Babba's
    and I want

    restaurant Item1 no1 item2 no2 item3 no3
    Olivers turkey breast 3 chicken 2 turkey breast 5
    Ali Babba's chicken 1 turkey breast 2

  2. #2
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    Why would you want a table of this design?

    What are you trying to accomplish with this layout?

    This can be accomplished but is a very poor way to store your data. You probably should store your data in a more normalized design and build an array or string to hold the data that matches the layout you are describing in your post.


    Matt

  3. #3
    Join Date
    Oct 2002
    Location
    TN
    Posts
    8
    I need the data in this format for a dialer I have to feed the info into. So the format the data is stored in is not my major concern actually - I'm limited by the capabilities of my dialer.....

    Originally posted by Rockey
    Why would you want a table of this design?

    What are you trying to accomplish with this layout?

    This can be accomplished but is a very poor way to store your data. You probably should store your data in a more normalized design and build an array or string to hold the data that matches the layout you are describing in your post.


    Matt

  4. #4
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    What method will you use to get the data from the table to the dialer? Could you explain this as well as the dialer - this may help.

    Matt

  5. #5
    Join Date
    Oct 2002
    Location
    TN
    Posts
    8
    I am using a csv file to import to the dialer. The reason I need the info like this is so that I can make one phone call, and be able to reference all of the items the restaurant has ordered. For the dialer, one row equals one phone call - it is not able to take information from multiple rows and apply it to one call. That's why I need the information in this way.


    Originally posted by Rockey
    What method will you use to get the data from the table to the dialer? Could you explain this as well as the dialer - this may help.

    Matt

  6. #6
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    With this table - how do you know where the next order begins?
    The first instance of a new restaurant?

    <For example, I have
    <Item number restaurant
    <turkey breast 3 Olivers
    <chicken 2 Olivers
    <turkey breast 5 Olivers
    <chicken 1 Ali Babba's
    <turkey breast 2 Ali Babba's

  7. #7
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    ExportTableToText("", "TableName", "C:\Export.csv", "", True)

    Outputs:
    Olivers,turkey breast,3,chicken,2,turkey breast,5
    Ali Babba's,chicken,1,turkey breast,2

    Assuming your table structure is as follows:
    "Item","Number","Restaurant"
    "turkey breast",3,"Olivers"
    "chicken",2,"Olivers"
    "turkey breast",5,"Olivers"
    "chicken",1,"Ali Babba's"
    "turkey breast",2,"Ali Babba's"


    Function ExportTableToText(strDatabase As String, strTable As String, strFile As String, strFDelimit As String, DeleteIt As Boolean) As Boolean

    Dim dbsTmp As DAO.Database
    Dim rstTmp As DAO.Recordset
    Dim intFile As Integer
    Dim intCounter As Integer
    Dim strTmp As String
    Dim strTempRestaurant As String

    On Error GoTo PROC_ERR

    If strDatabase = "" Then
    Set dbsTmp = CurrentDb()
    Else
    Set dbsTmp = DBEngine.Workspaces(0).OpenDatabase(strDatabase)
    End If

    ' Set defaults
    If strFDelimit = "" Then
    strFDelimit = ","
    End If

    ' Kill the file if necessary
    On Error Resume Next
    If DeleteIt = True Then
    Kill strFile
    End If
    On Error GoTo PROC_ERR

    ' Open the file
    intFile = FreeFile
    Open strFile For Append As intFile

    ' Open the recordset and loop through it
    Set rstTmp = dbsTmp.OpenRecordset(strTable, dbOpenDynaset)

    With rstTmp
    strTempRestaurant = Nz(.Fields(2).VALUE)
    Do Until .EOF
    If .Fields(2).VALUE <> strTempRestaurant And strTmp <> "" Then
    Print #intFile, strTempRestaurant & strFDelimit & Left(strTmp, Len(strTmp) - 1)
    strTmp = ""
    strTempRestaurant = ""
    strTempRestaurant = .Fields(2).VALUE
    End If

    For intCounter = 0 To .Fields.Count - 2
    strTmp = strTmp & .Fields(intCounter).VALUE
    If intCounter < .Fields.Count Then
    strTmp = strTmp & strFDelimit
    End If
    Next intCounter
    .MoveNext
    Loop
    Print #intFile, strTempRestaurant & strFDelimit & Left(strTmp, Len(strTmp) - 1)
    .Close
    End With

    Close #intFile

    dbsTmp.Close
    Set dbsTmp = Nothing

    ExportTableToText = True

    PROC_EXIT:
    Exit Function

    PROC_ERR:
    ExportTableToText = False
    Resume PROC_EXIT

    End Function

  8. #8
    Join Date
    Oct 2002
    Location
    TN
    Posts
    8
    Yes, the first instance of a new restaurant is how I know it's a new order. Thanks, I think this will work


    Originally posted by Rockey
    ExportTableToText("", "TableName", "C:\Export.csv", "", True)

    Outputs:
    Olivers,turkey breast,3,chicken,2,turkey breast,5
    Ali Babba's,chicken,1,turkey breast,2

    Assuming your table structure is as follows:
    "Item","Number","Restaurant"
    "turkey breast",3,"Olivers"
    "chicken",2,"Olivers"
    "turkey breast",5,"Olivers"
    "chicken",1,"Ali Babba's"
    "turkey breast",2,"Ali Babba's"


    Function ExportTableToText(strDatabase As String, strTable As String, strFile As String, strFDelimit As String, DeleteIt As Boolean) As Boolean

    Dim dbsTmp As DAO.Database
    Dim rstTmp As DAO.Recordset
    Dim intFile As Integer
    Dim intCounter As Integer
    Dim strTmp As String
    Dim strTempRestaurant As String

    On Error GoTo PROC_ERR

    If strDatabase = "" Then
    Set dbsTmp = CurrentDb()
    Else
    Set dbsTmp = DBEngine.Workspaces(0).OpenDatabase(strDatabase)
    End If

    ' Set defaults
    If strFDelimit = "" Then
    strFDelimit = ","
    End If

    ' Kill the file if necessary
    On Error Resume Next
    If DeleteIt = True Then
    Kill strFile
    End If
    On Error GoTo PROC_ERR

    ' Open the file
    intFile = FreeFile
    Open strFile For Append As intFile

    ' Open the recordset and loop through it
    Set rstTmp = dbsTmp.OpenRecordset(strTable, dbOpenDynaset)

    With rstTmp
    strTempRestaurant = Nz(.Fields(2).VALUE)
    Do Until .EOF
    If .Fields(2).VALUE <> strTempRestaurant And strTmp <> "" Then
    Print #intFile, strTempRestaurant & strFDelimit & Left(strTmp, Len(strTmp) - 1)
    strTmp = ""
    strTempRestaurant = ""
    strTempRestaurant = .Fields(2).VALUE
    End If

    For intCounter = 0 To .Fields.Count - 2
    strTmp = strTmp & .Fields(intCounter).VALUE
    If intCounter < .Fields.Count Then
    strTmp = strTmp & strFDelimit
    End If
    Next intCounter
    .MoveNext
    Loop
    Print #intFile, strTempRestaurant & strFDelimit & Left(strTmp, Len(strTmp) - 1)
    .Close
    End With

    Close #intFile

    dbsTmp.Close
    Set dbsTmp = Nothing

    ExportTableToText = True

    PROC_EXIT:
    Exit Function

    PROC_ERR:
    ExportTableToText = False
    Resume PROC_EXIT

    End Function

Posting Permissions

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