Results 1 to 15 of 15
  1. #1
    New Lounger
    Join Date
    Dec 2010
    Location
    Stevens County, WA, USA
    Posts
    20
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Want an Access 2010 report with fields combined

    Hi,
    My database has a table " tblItemsWProcesses " and table "tblItemListWNames" that I would like to generate a report that shows the Surname field (txtLastName) and Given Name field "txtGivenName" and all the Item number fields "lngItemNumber" associated with the name. I'm using a combined field "txtConcatNames" to link the two databases. The names are coming from a variety of historical records and entries may only have surname, or surname with initials, or surname with first name, or surname with the full name. I can't assume the given name, so have to allow for the variations.

    I would like to have the report look like an index in a book "Jenkins, D. P. 250, 270, 3245, 4510," . Some names, like the example given, will have hundreds of entries.

    Is it doable or should I just go for a report that shows the item numbers in column format? When we are done, there should be about 22,000 images with anywhere from zero to a 100 or so names.

    I'm using Access 2010.

    Thanks.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    So you want to display all the lngItemNumbers that correspond with a specific person listed after their name.

    Are the ItemNumbers clearly identified with a specifc person? Given what you say about the various forms of names, it sounds like the connection between ItemNumbers and People may not be consistent.

    There is a custom function Contact that will bring together a field from a number of related records. An example and some discussion can be found in the thread begining with this post. But for it to work you do need a consistent unique identifier for the field that links things together.

    If you like you could post a sample db, with just a bit of data from the two tables (with names changed perhaps).
    Regards
    John



  3. #3
    New Lounger
    Join Date
    Dec 2010
    Location
    Stevens County, WA, USA
    Posts
    20
    Thanks
    1
    Thanked 0 Times in 0 Posts
    John,
    I'm off to an important meeting this morning, but will review the instructions for uploading a portion of the database. As these are historical records from 100 years ago, I don't have to worry too much about privacy.

    The txtConcatNames field is unique to the tblNames. tblNames is the one-side to the many on tblItemListWNames. Because of how my partner in this project uploaded pdf files (more than one page per item), there is a txtConcatItemsPg field that is the one side of tblItemsWProcesses and many to tblItemListWNames. Someone I know who has experience with SQL recommended that, and it is working well. Access would consider any entry without something in the given name field as nothing particularly and so I couldn't create a link. I've added the phrase NotStated or Company (we have businesses listed also) to the txtConcatNames field to make it clearer.

    My prior Access experience was dealing with living people who all had something common to put them in the database. This has been way different. Add in a 5 year gap in using Access and the new style to Access, and it has been a real challenge.

    Sue

  4. #4
    New Lounger
    Join Date
    Dec 2010
    Location
    Stevens County, WA, USA
    Posts
    20
    Thanks
    1
    Thanked 0 Times in 0 Posts
    John,
    Here is a sample of the database. Hopefully this works. Any suggestions on improving it are very welcome. I would rather fix something now, then try to do it with a lot more data later.

    Thank you.

    Sue
    Attached Files Attached Files

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Thanks

    Does tblItemsWProcesses have anything to do with the report you want?

    I can get a query to return data like this using the concat function and just the table tblItemListWNames.
    Does this look like what you want? If so I will post the db back up.
    ConcatIDs.gif


    I think it would be better if the Concatenated Name field had a comma and space after the surname.
    Akers, Ben
    Alfred, NotStated etc

    Just for readability.
    Regards
    John



  6. #6
    New Lounger
    Join Date
    Sep 2011
    Location
    Charmhaven NSW
    Posts
    12
    Thanks
    1
    Thanked 0 Times in 0 Posts
    hi John
    what is the concat funtion . . cant find it in MS help . ..

    I have used this routine (below) in other simialr situations . . a bit clunky - but it works

    I added a field called codelist to the tblNamesList. (text - but could be memo)

    Then created a query that does the same as your report - but saves it in field - codelist

    First run a query to blank out the codelist field

    SQL is . . UPDATE tblNamesList SET tblNamesList.codelist = Null;

    then run the update query to fill in the codes

    UPDATE tblItemsWProcesses INNER JOIN (tblNamesList INNER JOIN tblItemListWNames ON tblNamesList.txtConcatNames = tblItemListWNames.txtConcatNames) ON tblItemsWProcesses.txtConcatItemPg = tblItemListWNames.txtConcatItemPg SET tblNamesList.codelist = [codelist] & IIf(Len([codelist])>0,", ","") & [lngItemNumber];

    if the code is pasted into the SQL of a query - it should work
    the last bit . . [codelist] & IIf(Len([codelist])>0,", ","") & [lngItemNumber];
    checks if the comma needs to be appended to the previous code - or not if its the first code.

    caveat. . while in - select - mode i set the sort to ascending on name and then [lngItemNumber] . . my version produced a list in ascending order . . but you may need to check the consistancy . .
    cheers
    paul

  7. #7
    New Lounger
    Join Date
    Dec 2010
    Location
    Stevens County, WA, USA
    Posts
    20
    Thanks
    1
    Thanked 0 Times in 0 Posts
    John,
    All I need for the report are the txtConcatNames, txtLastName, txtGivenName, and txtConcatItemPg, which are all in tblItemListWNames. I hope to use the txtConcatNames to be the unique to generate in the report but hidden and use txtLastName and txtGivenName for display.

    txtConcatNames (Hidden)
    txtLastName and txtGivenName txtConcatItemPg

    Would look like:
    AustinCG (Hidden)
    Austin, C. G. N1540P1, N1541P1, N1542P1, N1543P1

    For Paul,
    We are using Excel to record the names for insertion into the meta data of the images and then uploading into the database, so I do all the merging using an Excel function like ="N"&B2&"P1" or ="N"&B2&"P"&"E2" if there is more than one page per item. I tried concatenate in Access, but would have had to add the NotStated or Company to the txtGivenName field and I didn't want them to show in the actual report.

    Sue

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by PaulG11 View Post
    hi John
    what is the concat funtion . . cant find it in MS help . ..
    Concat is a custom function. I referred to it, and put in a link, in my first post in this thread.
    Regards
    John



  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Sue

    Attached is a modified version of your database.

    What is new is:
    • The module (Global Code) with the concat function
    • A query qryDistinctNames that just select the distinct names from tblItemListWNames
    • Another query qryConcatIDs that uses the first query and Concat to produce what you want.
    • A report rptConcatIDs based on the second query.
    Attached Files Attached Files
    Regards
    John



  10. #10
    New Lounger
    Join Date
    Dec 2010
    Location
    Stevens County, WA, USA
    Posts
    20
    Thanks
    1
    Thanked 0 Times in 0 Posts
    John,
    Thank you. I'll work with the four new items. I'll let you know how it works. I very much appreciate you sharing your expertise.

  11. #11
    New Lounger
    Join Date
    Dec 2010
    Location
    Stevens County, WA, USA
    Posts
    20
    Thanks
    1
    Thanked 0 Times in 0 Posts
    John,
    The queries worked. I had to fix a problem with apostrophes as they created an error in the query for some reason. If my txtConcatNames had an apostrophe in it, the query wouldn't return the item numbers and an error message would show. Those are fixed and the query returns without errors.

    There is an error with the report. I'm getting a request for Enter Parameter Value for txtLastName, followed by for txtGivenName. If I step through those, the items numbers come up, but not the names.

    Any ideas?

    Sue

  12. #12
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Does the query still have txtlastName and txtGivenName as fields? If not, then you need to change the control source of the controls, and also the sort order of the report.

    Yes, apostrophes will cause probles, as they were used by me in the query to surround text. Glad you sorted that out.
    Regards
    John



  13. #13
    New Lounger
    Join Date
    Dec 2010
    Location
    Stevens County, WA, USA
    Posts
    20
    Thanks
    1
    Thanked 0 Times in 0 Posts
    John,
    The query comes up with expression 1 and 2. I'll look through your sample database to see what I need to change. If I can't figure it out on my own, I'll post again. I'll remember better if I work it out and only ask if I can't.

    Thanks again, I really like having the information in the format. It will really improve the project. You can see what we are doing at www.crossroadsarchive.org and for the images at www.crossroadsarchive.net. We are going to put the report up on the .org site, so I'll probably post down the roads with more questions. If you would like to donate your time to the project it would be greatly appreciated. The donation form is at the .org site under Documents, Contributor forms. In your case, you would use what you normally charge or $15 US, whichever is higher. Just scan it and email me.

  14. #14
    New Lounger
    Join Date
    Dec 2010
    Location
    Stevens County, WA, USA
    Posts
    20
    Thanks
    1
    Thanked 0 Times in 0 Posts
    John,
    I just changed the name of the two fields in the query and it works like a champ. Thanks again.

  15. #15
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Terrific.
    Regards
    John



Posting Permissions

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