Results 1 to 7 of 7

Thread: Simple Query?

  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Simple Query?

    My Access skills were once mid-level, but they are very rusty and were developed a long time ago under a different version of Access. So please excuse the simple question.

    I have a database that includes several tables, among them tblAllLocated and tblReunionAttendees.

    tblAllLocated contains data on 1,200 members of our Association. Each member has a unique field named Handle. One of its other fields is Status, which can contain the values Living, Deceased, or Other. This field is updated when a member dies or goes into some other situation. The table also contains addresses, email addresses, and other contact information for each member, living or dead. This information is updated as necessary.

    tblReunionAttendees contains data about every member who has attended one of our reunions. It has about 800 records. Each record has the member's Handle and Name, plus the year of the reunion they attended. Members can attend multiple reunions, so many of the members appear several times in tblReunionAttendees.

    I want to create a table or maybe a query named xxxReunionAttendeesPlus that contains everything now in tblReunionAttendees, plus the Status field and some simple contact information from tblAllLocated, for every record that is in it.

    The basic idea is that I want to be able to send postal mail or email to people who have attended reunions, without bothering the families of the deceased ones. I should retain the years of the reunions they attended. (Sometimes I want to send mail or email only to people who attended in certain years. I handle that by exporting xxxReunionAttendeesPlus to Excel and dealing with it there.)

    This seems not to hard to do, but my Access skills aren't up to doing it. Who can help?
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    5 Star Lounger Lugh's Avatar
    Join Date
    Jun 2010
    Location
    Indy
    Posts
    627
    Thanks
    168
    Thanked 77 Times in 68 Posts
    It should be simple in any Access version from this century Lou--which version do you have?

    If you have Query Wizard, click on it, select each of the 2 tables in turn, and add the fields you want from each table. Then finish creating the query, and right-click it to select Design View.

    In Design View, locate the Status field and click in the Criteria row. Enter something like =Living or <>Deceased there. Save your change, and you should be set.

    NB Do you have Primary keys set in each table, and the Relationships set between the tables? Those are essential to make Access work properly.
    Lugh.
    ~
    Windows 10 Pro x64 1607; Office 2016 (365 Home) x32; Win Defender, MBAM Pro

    ASRock H97 Anniversary; Xeon E3-1231V3 (like i7)
    Gigabyte GeForce GTX 970; 12GB Crucial DDR3 1600
    Logitech MX Master mouse; Roccat Isku kb

  3. The Following User Says Thank You to Lugh For This Useful Post:

    Lou Sander (2016-07-15)

  4. #3
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    Got it. Thanks!

    I have Access 2010. With the ribbon, the controls are different from what I was accustomed to when I had some skills (Access 2003?). My cheat sheets are all from the older version, so they are only partially helpful.

    (In case you haven't experienced it, Access skills tend to rust VERY quickly, especially if you've never really mastered them by using it every day. Word and Excel skills are a lot more permanent.)

    I DO have ID set as a primary key in all my tables.

    I think I set the relationships in the query by dragging Handle from one table to the other. The query seems to work, so I guess that did the trick.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  5. #4
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    Well, now something else has come up.

    I have a Crosstab Query for tblReunionAttendees that gives me one row for each person who has attended a reunion.

    The row has a column for the person's Handle and one column for each reunion that we have held. If the person has attended that reunion, a "1" appears in the column. Otherwise, the column is blank.

    I created the query a long time ago, and have no recollection of how I did it. What I'd love to do is to to add a column containing the person's status from tblAllLocated.

    I tried adding tblAllLocated to the Crosstab Query, linking the two tables, and dragging Status into the query. That didn't work at all. Looking in the book Access 2010 Inside Out isn't too helpful -- the stuff about Crosstab Queries is well above my pay grade.

    Any suggestions? (I'm thinking that maybe I could do the crosstab on the Query I made from the above posts, but I don't know if it would work, and I don't want to ruin anything.)
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  6. #5
    5 Star Lounger Lugh's Avatar
    Join Date
    Jun 2010
    Location
    Indy
    Posts
    627
    Thanks
    168
    Thanked 77 Times in 68 Posts
    Quote Originally Posted by Lou Sander View Post
    In case you haven't experienced it, Access skills tend to rust VERY quickly, especially if you've never really mastered them by using it every day. Word and Excel skills are a lot more permanent.
    I've never had any Access skills, just learning enough to achieve each objective which cropped up. I prep data in Excel to put in Access, and then extract data via Access queries into Excel to do my manipulation and reporting.

    I think I set the relationships in the query by dragging Handle from one table to the other. The query seems to work, so I guess that did the trick.
    While it may have worked, that's not how to set Relationships. You set them between tables, not within queries--when you pull tables into a query in design view, you will already see the relationship 'lines' you set between the tables.

    It's a while since I had Access 2010, but Relationships haven't changed much if at all. There is a Relationships button or section in the ribbon, in 2016 it's in the Database Tools tab. Find that and set the relationships between your tables. It sounds to me that tblAllLocated-Handle and tblReunionAttendees-Handle should be a One-to-Many relationship.

    Quote Originally Posted by Lou Sander View Post
    What I'd love to do is to to add a column containing the person's status from tblAllLocated.

    I tried adding tblAllLocated to the Crosstab Query, linking the two tables, and dragging Status into the query. That didn't work at all.
    I've never done crosstabs, but a normal select query should give you what you want, once the relationships are set correctly.

    I don't want to ruin anything.)
    Don't worry about that--just copy your working query [right click in left objects pane] and paste it with a new name, you can play away with the copy.

    Btw I would have made the Reunion table differently. As it is, it shouldn't contain Name, that should be in the Located table. I would have Date and Handle as the two main fields in Reunion [plus an auto-Primary field]. So it looks like:
    2015 Fred
    2015 Betty
    2014 Barney
    2014 Bam Bam
    2014 Pebbles

    You keep all the info unique to each member in one table like Located, all the info about each reunion in another table eg ReunionInfo, and then use the Reunion table outlined above to 'join' or provide the link between the two. I have a simple setup just like that which has been very stable and useful for over a decade.
    Last edited by Lugh; 2016-07-15 at 23:55.
    Lugh.
    ~
    Windows 10 Pro x64 1607; Office 2016 (365 Home) x32; Win Defender, MBAM Pro

    ASRock H97 Anniversary; Xeon E3-1231V3 (like i7)
    Gigabyte GeForce GTX 970; 12GB Crucial DDR3 1600
    Logitech MX Master mouse; Roccat Isku kb

  7. The Following User Says Thank You to Lugh For This Useful Post:

    Lou Sander (2016-07-16)

  8. #6
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    All good stuff! Thanks.

    I looked into it, and I had already set up the proper relationships between the tables. Just hadn't looked at them for a while. For some reason, in the Query the line connecting Handle in the two tables was missing. When I put it in, everything worked.

    I don't remember why I put the names into tblReunionAttendees, but at one time I had a reason for it. I no longer use them in the Query, using the names from tblAllLocated instead. I'm going to leave them in the table for a while, just in case the reason for including them pops up.

    I will handle the Crosstab stuff by starting another thread. If I get nothing there, I'll work on your suggestion.

    One thing more I need:

    Right now, my Query gives me one line for each attendee at each reunion. If Joe was at five reunions, I have five lines for Joe, each one showing the year for one of his reunions.

    To refresh your mind, my query includes Handle and ReunionAttended from tblReunionAttendees, and other info like Status, Name, Email, etc. from tblAllLocated

    I'd like also to have a query with only one line for each person who has attended any reunion. In other words, Joe would be in the result, as would Jim who only attended one reunion, and Max who attended all fifteen of them. This would give me a list with everybody who ever attended a reunion.

    Maybe I could do this in Excel, by exporting the Query results, deleting the Year column, then eliminating duplicates.

    NEW INFORMATION: I did the Excel stuff, and it works. It gives me the flexibility I want, and the ease of working with Excel vs. the relative complexity of Access.
    Last edited by Lou Sander; 2016-07-16 at 14:24. Reason: Result of Excel trial
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  9. #7
    5 Star Lounger Lugh's Avatar
    Join Date
    Jun 2010
    Location
    Indy
    Posts
    627
    Thanks
    168
    Thanked 77 Times in 68 Posts
    Quote Originally Posted by Lou Sander View Post
    It gives me the flexibility I want, and the ease of working with Excel vs. the relative complexity of Access.
    I totally agree, Excel is much easier, quicker and more flexible for manipulation and presenting.

    I'd like also to have a query with only one line for each person who has attended any reunion.
    Glad to see you got this via Excel. Just fyi, this is a case where Access does in fact have an easier solution. You launch the Query Wizard and in the first screen, select the Find Duplicates Query Wizard. Make the 'obvious' selections in tblReunionAttendees, and you end up with each member who attended a reunion listed only once.
    Lugh.
    ~
    Windows 10 Pro x64 1607; Office 2016 (365 Home) x32; Win Defender, MBAM Pro

    ASRock H97 Anniversary; Xeon E3-1231V3 (like i7)
    Gigabyte GeForce GTX 970; 12GB Crucial DDR3 1600
    Logitech MX Master mouse; Roccat Isku kb

Posting Permissions

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