Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    May 2003
    Location
    Pennsylvania, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Making a Flat File (Access2000)

    I have a database of journal articles with a table for article data linked to a table of author names. The tables are linked in a 1 to many relationship through the unique article number. The article table looks like this:

    ArtNum Title Author1 E-mail1 Author2 E-mail2 Author3 E-Mail3.......

    The author title looks like this:

    ArtNum Name E-mail
    Art Num Name E-mail
    .....

    In the article table, the Author1, Author2, etc. fields are blank. I want to fill them in from the author table and make a flat file. The problem is that there can be from 0 to 6 authors for each article. If I run a simple update query, only the first author gets filled in. I suppose I could mark those entries, then run another query, mark the entries copied, and so on, doing this 6 times.

    Is there any simpler or better way to do this?

    Thanks for the help.

    Don.

  2. #2
    Star Lounger
    Join Date
    Jan 2003
    Location
    Eau Claire, Wisconsin, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making a Flat File (Access2000)

    Don,
    It sounds like you want to show all articles and all authors associated with each article.

    Since you have many articles with many authors, you will need a third table to tie the many-to-many relationship.

    Table 1: tblArticle with fields ArtNum, Art Title
    Table 2: tblCombined with fields ArtNum, AuthorID
    Table 3: tblAuthors with fields AuthorID, Name, Email

    tblArticle is one-to-many with tblCombined which is many-to-one with tblAuthors.

    This setup will allow you to link many articles to many authors in one query or report. Another advantage occurs when there are 7 or 8 authors on one article. You will not have to add author fields to the underlying table.

  3. #3
    Star Lounger
    Join Date
    May 2003
    Location
    Pennsylvania, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making a Flat File (Access2000)

    Jon and Hans:

    Thanks for the replies. Unfortunately, Jon's suggestion won't do what I want. I need to have a table showing ALL authors for EACH article (along with other data for the article), and moreover, each author and its associated data like address, etc. has to be uniquely tagged (I use fields called AU1, AU2, EM1, EM2, etc.). So I need to walk down the list of authors and copy each name and its associated data into the appropriate field of the flat file. The problem is that I don't have any way of knowing how in advance how many authors each article has.

    I'm well aware of the hazards of flat files, but the reason I need one in this case is that the data gets exported to a commercial bibliographic database searching service (Dialog, if you're familiar with searching in the library world) as a tagged ASCII file (I have a perl script that a colleague wrote for me to convert a delimited file to a tagged file). The problem is that the Dialog service (and others of its type that we also send the file to) won't accept duplicate field names in a record, which is why each field has to have a unique identifier. You may ask, "What if an article has 5 authors and you have only allowed for 4 in your database?". We get around that by having a 5th field (yes/no), which is checked if there are more than 4 authors, and then when we do final processing, we convert yes values of AU5 to "et al.", which works just fine (yes, we're willing to lose a few authors' names--very few articles have more than 4 authors).

    Any further suggestions would be welcomed.

    Thanks much.

    Don H.

    PS. Hans, the URL link to wrksmart in your reply doesn't work--it results in a message "This user is no longer in our database".

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Making a Flat File (Access2000)

    Lounger <!profile=wrksmrt>wrksmrt<!/profile> already pointed out why a flat table is not a good idea. If you need flat records for export, you can always create a series of queries for that purpose.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Making a Flat File (Access2000)

    Thanks for pointing out the error in the link - it was a typo (wrksmrt vs wrksmart); I have corrected it.

    Could you live with a series of queries to construct the flat view, as I suggested? That is what I use when I need to export "flat" data.

  6. #6
    Star Lounger
    Join Date
    May 2003
    Location
    Pennsylvania, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making a Flat File (Access2000)

    Hans:

    A series of queries would be fine. I tried to do it by adding yes/no fields Copied1,...Copied4 to my authors table, then running an update query. But now, even though only 1 author for an article gets copied (the last one), ALL of the records in the author table get checked as copied. Presumably, what's happening is that each copy is overwriting the previous one. I don't know how to tell Access to stop updating for a particular author and go on to the next one.

    Don.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Making a Flat File (Access2000)

    Do you happen to have a sequence number in the Author table that specifies the order in which the authors should be listed? (I.e. 1 for the first author, 2 for the second, etc.)

  8. #8
    Star Lounger
    Join Date
    May 2003
    Location
    Pennsylvania, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making a Flat File (Access2000)

    No. The authors are just listed in an arbirary order.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Making a Flat File (Access2000)

    In that case, queries may not be a good idea. Here is VBA code that loops through the records of the authors table and fills the names and e-mail addresses of the authors. You will have to substitute the names of the tables and fields you are using (find & replace should make that easier). I have assumed that ArtNum is numeric.

    Sub FillFlat()
    Const conMaxAuthor = 5

    Dim cnn As ADODB.Connection
    Dim rstArticles As New ADODB.Recordset
    Dim rstAuthors As New ADODB.Recordset
    Dim strSQL As String
    Dim i As Integer

    On Error GoTo ErrHandler

    Set cnn = CurrentProject.Connection

    rstArticles.Open "SELECT DISTINCT ArtNum FROM tblAuthors", cnn, adOpenForwardOnly

    Do While Not rstArticles.EOF
    i = 0
    strSQL = ""

    rstAuthors.Open "SELECT * FROM tblAuthors WHERE ArtNum=" & _
    rstArticles!ArtNum, _
    cnn, adOpenForwardOnly

    Do While Not rstAuthors.EOF
    i = i + 1
    If i = conMaxAuthor Then
    strSQL = strSQL & ", Author" & i & "=" & Chr(34) & "et al." & Chr(34)
    Exit Do
    Else
    strSQL = strSQL & ", Author" & i & "=" & _
    Chr(34) & rstAuthors!Author & Chr(34)
    If Not IsNull(rstAuthors!E_mail) Then
    strSQL = strSQL & ", E_mail" & i & "=" & _
    Chr(34) & rstAuthors!E_mail & Chr(34)
    End If
    End If
    rstAuthors.MoveNext
    Loop

    rstAuthors.Close

    If Not (strSQL = "") Then
    strSQL = "UPDATE tblArticles SET " & Mid(strSQL, 3) & _
    " WHERE ArtNum=" & rstArticles!ArtNum
    MsgBox strSQL
    cnn.Execute strSQL
    End If

    rstArticles.MoveNext
    Loop

    ExitHandler:
    On Error Resume Next
    rstArticles.Close
    rstAuthors.Close
    Set rstAuthors = Nothing
    Set rstArticles = Nothing
    Set cnn = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

  10. #10
    Star Lounger
    Join Date
    May 2003
    Location
    Pennsylvania, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making a Flat File (Access2000)

    Hans:

    Thanks for going to all the work of writing that code. I really appreciate it. However, I was playing around with a series of queries and got the desired results with them. Here's what I did:
    ------------------------------------

    First, add a Y/N field (Copied) to the Authors table. Then,

    1. Run an update query between the Authors and the AU1 field of the Articles table, joined on the Article ID. This will put the last author for an article into AU1.
    2. Run an update query between the same 2 tables, this time joined on AU1 and the Name field of Authors, and update Copied to Yes.
    3. Repeat Step 1 for AU2 and with the criterion Copied = No.
    4. Repeat Step 2. for AU2.

    Then do this as many times as desired (in my case, a total of 4). Finally, run a final query to detect articles with more than 4 authors and update AU5 to Yes (which will eventually be translated into Et Al.).
    -------------------------------------------

    So thanks again for all the help and suggestions. The code gave me the idea for these queries, so it wasn't entirely wasted effort on your part.

    Regards,

    Don.

Posting Permissions

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