Access 2010 Headache! Please help provide relief.
August 21, 2012 12:20 PM Subscribe
Access 2010 stumper! I need to be able to query for members, submembers, and sub-submembers! Oye!
I'm more than a novice with Access 2010 but certainly not an expert. I work with Access on a daily basis yet this problem stumps me.
I have a table that I need to query to retrieve a set of Groups, subgroups and all possible sub-members.
The table is laid out as:
GROUP NAME GROUP LEAD MEMBER TN LAST NAME FIRST NAME
Doctors 5553215255 5553210001 Weir Bob
Doctors 5553215255 5553210002 Garcia Jerry
Doctors 5553215255 5553210003 Hendrix Jimi
Doctors 5553215255 5553210004 Peds Page
Peds 5553210004 3144230009 Cash Johnny
Peds 5553210004 3144230008 Morrison Jim
Peds 5553210004 3144230007 Beethoven Ludwig
Peds 5553210004 3144230911 Emergency Peds Page
Emergency Peds 3144230911 6362341101 Strummer Joe
Emergency Peds 3144230911 6362341101 Mercury Freddie
Emergency Peds 3144230911 6362341101 Presley Elvis
Emergency Peds 3144230911 6362341101 Bopper Big
Fields Defined:
Group Name: a group of people based on a role or other criteria
Group LEAD: this number when paged will send a page to the Members of a given group.
MEMBER TN: The telephone of a given member. A member can be a person or a GROUP LEAD
Last Name: Role or Last name of a person. Sometimes a group name or similar.
First Name: First name of a person
I need a query that when run returns a list of all members of all groups paged.
Suppose I Send a page to Doctors. Paging Doctors also pages all members of Peds, which in turn pages all members of Emergency Peds The result would show the ALL members of Doctors, ALL members of Peds (EXCEPT GROUP NAMES), and ALL Members of Emergency Peds who were paged as a result of the initial page. Notice Peds Page and Emergency Peds Page were left out. Illustrated here:
GROUP NAME GROUP NUMBER MEMBER TN LAST NAME FIRST NAME
Doctors 5553215255 5553210001 Weir Bob
Doctors 5553215255 5553210002 Garcia Jerry
Doctors 5553215255 5553210003 Hendrix Jimi
Peds 5553210004 3144230009 Cash Johnny
Peds 5553210004 3144230008 Morrison Jim
Peds 5553210004 3144230007 Beethoven Ludwig
Emergency Peds 3144230911 6362341101 Strummer Joe
Emergency Peds 3144230911 6362341101 Mercury Freddie
Emergency Peds 3144230911 6362341101 Presley Elvis
Emergency Peds 3144230911 6362341101 Bopper Big
1. One co-worker suggests querying by hand multiple times, saving the results into multiple tables and then appending all the resulting tables into one table. How do I do this? It seems this is a solution I would understand easily at my level.
2. Is there another way besides the above solution?
3. Is there anything else I need to know if I will be comparing a second similar table to the first? MEMBERS of the second may be GROUP LEADS in the first.
MIGHTY BIG thanks in advance.
I'm more than a novice with Access 2010 but certainly not an expert. I work with Access on a daily basis yet this problem stumps me.
I have a table that I need to query to retrieve a set of Groups, subgroups and all possible sub-members.
The table is laid out as:
GROUP NAME GROUP LEAD MEMBER TN LAST NAME FIRST NAME
Doctors 5553215255 5553210001 Weir Bob
Doctors 5553215255 5553210002 Garcia Jerry
Doctors 5553215255 5553210003 Hendrix Jimi
Doctors 5553215255 5553210004 Peds Page
Peds 5553210004 3144230009 Cash Johnny
Peds 5553210004 3144230008 Morrison Jim
Peds 5553210004 3144230007 Beethoven Ludwig
Peds 5553210004 3144230911 Emergency Peds Page
Emergency Peds 3144230911 6362341101 Strummer Joe
Emergency Peds 3144230911 6362341101 Mercury Freddie
Emergency Peds 3144230911 6362341101 Presley Elvis
Emergency Peds 3144230911 6362341101 Bopper Big
Fields Defined:
Group Name: a group of people based on a role or other criteria
Group LEAD: this number when paged will send a page to the Members of a given group.
MEMBER TN: The telephone of a given member. A member can be a person or a GROUP LEAD
Last Name: Role or Last name of a person. Sometimes a group name or similar.
First Name: First name of a person
I need a query that when run returns a list of all members of all groups paged.
Suppose I Send a page to Doctors. Paging Doctors also pages all members of Peds, which in turn pages all members of Emergency Peds The result would show the ALL members of Doctors, ALL members of Peds (EXCEPT GROUP NAMES), and ALL Members of Emergency Peds who were paged as a result of the initial page. Notice Peds Page and Emergency Peds Page were left out. Illustrated here:
GROUP NAME GROUP NUMBER MEMBER TN LAST NAME FIRST NAME
Doctors 5553215255 5553210001 Weir Bob
Doctors 5553215255 5553210002 Garcia Jerry
Doctors 5553215255 5553210003 Hendrix Jimi
Peds 5553210004 3144230009 Cash Johnny
Peds 5553210004 3144230008 Morrison Jim
Peds 5553210004 3144230007 Beethoven Ludwig
Emergency Peds 3144230911 6362341101 Strummer Joe
Emergency Peds 3144230911 6362341101 Mercury Freddie
Emergency Peds 3144230911 6362341101 Presley Elvis
Emergency Peds 3144230911 6362341101 Bopper Big
1. One co-worker suggests querying by hand multiple times, saving the results into multiple tables and then appending all the resulting tables into one table. How do I do this? It seems this is a solution I would understand easily at my level.
2. Is there another way besides the above solution?
3. Is there anything else I need to know if I will be comparing a second similar table to the first? MEMBERS of the second may be GROUP LEADS in the first.
MIGHTY BIG thanks in advance.
If you don't want the "peds page" and "emergency peds page" to show up when querying the different groups - they should not be members of those groups.
Would it accomplish the goal if you just exclude first names that contain "page" from the query?
posted by sarahnicolesays at 12:42 PM on August 21, 2012
Would it accomplish the goal if you just exclude first names that contain "page" from the query?
posted by sarahnicolesays at 12:42 PM on August 21, 2012
I don't understand your data model. Why would a page to doctors also send out a page to emergency peds? Is there another table that establishes group and subgroup membership?
posted by jasper411 at 12:52 PM on August 21, 2012
posted by jasper411 at 12:52 PM on August 21, 2012
Response by poster: Sorry for the confusion.
the groups are
Doctors - paged by phone number 5553215255
Peds - paged by phone number 5553210004. This phone number is tied to a member of Doctors and will get paged when Doctors are paged.
Emergency Peds - paged by 3144230911 which is tied to a member of Peds and will get paged when Peds gets paged
1. 'Doctors' pages people who are members as well as the group 'Peds'.
2. 'Peds', in turn pages people who are members of 'Peds' as well as the group 'Emergency Peds'.
3. 'Emergency Peds' then pages its members (people or groups even though my example only includes people.)
The query should identify all members who were paged as a result of Doctors being paged. My employer would prefer to have only the members of each group shown in my results set without showing the group names. I'm sure I can query the Groups out of the full list if there's no way to exclude them directly in the query.
posted by brokeaspoke at 12:55 PM on August 21, 2012
the groups are
Doctors - paged by phone number 5553215255
Peds - paged by phone number 5553210004. This phone number is tied to a member of Doctors and will get paged when Doctors are paged.
Emergency Peds - paged by 3144230911 which is tied to a member of Peds and will get paged when Peds gets paged
1. 'Doctors' pages people who are members as well as the group 'Peds'.
2. 'Peds', in turn pages people who are members of 'Peds' as well as the group 'Emergency Peds'.
3. 'Emergency Peds' then pages its members (people or groups even though my example only includes people.)
The query should identify all members who were paged as a result of Doctors being paged. My employer would prefer to have only the members of each group shown in my results set without showing the group names. I'm sure I can query the Groups out of the full list if there's no way to exclude them directly in the query.
posted by brokeaspoke at 12:55 PM on August 21, 2012
Response by poster: jasper: due to some systems limitations clever people have figured out how to cascade pages by entering LEAD NUMBERS in with the for certain situations. I don't like the data set either but it's what I've been given and I have no option to re-pull.
posted by brokeaspoke at 1:03 PM on August 21, 2012
posted by brokeaspoke at 1:03 PM on August 21, 2012
Response by poster: A co-worker has said "statment in SQL to "blow out" horizontal records into a vertical matrix". Is this so? ANd would that help?
posted by brokeaspoke at 1:05 PM on August 21, 2012
posted by brokeaspoke at 1:05 PM on August 21, 2012
1. 'Doctors' pages people who are members as well as the group 'Peds'.
2. 'Peds', in turn pages people who are members of 'Peds' as well as the group 'Emergency Peds'.
3. 'Emergency Peds' then pages its members (people or groups even though my example only includes people.)
This is not represented anywhere in the data examples you've given.
posted by sarahnicolesays at 1:10 PM on August 21, 2012
Response by poster: Sorry if I laid this out in a way that's not clear but I see it in my example but probably only because I am dealing with this data all the time. I'll try again using an above example.
GROUP NAME GROUP LEAD MEMBER TN LAST NAME FIRST NAME
Doctors 5553215255 5553210001 Weir Bob
Doctors 5553215255 5553210002 Garcia Jerry
Doctors 5553215255 5553210003 Hendrix Jimi
Doctors 5553215255 5553210004 Peds Page
Peds 5553210004 3144230009 Cash Johnny
Peds 5553210004 3144230008 Morrison Jim
Peds 5553210004 3144230007 Beethoven Ludwig
Peds 5553210004 3144230911 Emergency Peds Page
Emergency Peds 3144230911 6362341101 Strummer Joe
Emergency Peds 3144230911 6362341101 Mercury Freddie
Emergency Peds 3144230911 6362341101 Presley Elvis
Emergency Peds 3144230911 6362341101 Bopper Big
When I page Doctors at 5553215255 it will page its members: 5553210002 Garcia Jerry, 5553210003 Hendrix Jimi, and 5553210004 Peds Page. Peds Page is a group containing members and at least one (sub) group.
Then:
5553210004 Peds Page will page its members: 3144230009 Cash Johnny, 3144230008 Morrison Jim, 3144230007 Beethoven Ludwig and 3144230911 Emergency Peds Page. Emergency Peds page is a group containing members and at least one (sub) group.
Then:
3144230911 Emergency Peds Page will page its members 6362341101 Strummer Joe, 6362341101 Mercury Freddie, 6362341101 Presley Elvis and 6362341101 Bopper Big.
posted by brokeaspoke at 1:26 PM on August 21, 2012
GROUP NAME GROUP LEAD MEMBER TN LAST NAME FIRST NAME
Doctors 5553215255 5553210001 Weir Bob
Doctors 5553215255 5553210002 Garcia Jerry
Doctors 5553215255 5553210003 Hendrix Jimi
Doctors 5553215255 5553210004 Peds Page
Peds 5553210004 3144230009 Cash Johnny
Peds 5553210004 3144230008 Morrison Jim
Peds 5553210004 3144230007 Beethoven Ludwig
Peds 5553210004 3144230911 Emergency Peds Page
Emergency Peds 3144230911 6362341101 Strummer Joe
Emergency Peds 3144230911 6362341101 Mercury Freddie
Emergency Peds 3144230911 6362341101 Presley Elvis
Emergency Peds 3144230911 6362341101 Bopper Big
When I page Doctors at 5553215255 it will page its members: 5553210002 Garcia Jerry, 5553210003 Hendrix Jimi, and 5553210004 Peds Page. Peds Page is a group containing members and at least one (sub) group.
Then:
5553210004 Peds Page will page its members: 3144230009 Cash Johnny, 3144230008 Morrison Jim, 3144230007 Beethoven Ludwig and 3144230911 Emergency Peds Page. Emergency Peds page is a group containing members and at least one (sub) group.
Then:
3144230911 Emergency Peds Page will page its members 6362341101 Strummer Joe, 6362341101 Mercury Freddie, 6362341101 Presley Elvis and 6362341101 Bopper Big.
posted by brokeaspoke at 1:26 PM on August 21, 2012
I'm sorry that you're not getting useful answers here - I understand what you're saying, and it's clear- but it's not actually represented in your data.
Peds Page is not a group. There is nothing in the data that fits with what you are saying. You KNOW how the data is related - but that is irrelevant and arbitrary. It's NOT RELATED in the database in the way you are describing.
posted by sarahnicolesays at 1:31 PM on August 21, 2012
Peds Page is not a group. There is nothing in the data that fits with what you are saying. You KNOW how the data is related - but that is irrelevant and arbitrary. It's NOT RELATED in the database in the way you are describing.
posted by sarahnicolesays at 1:31 PM on August 21, 2012
@sarahnicolesays: Well, "Peds Page" is a group because there are other entries in that table that have its "Member TN" value as the "Group Lead" value.
@OP: Access SQL doesn't do recursive queries as per StackOverflow consensus, which is what you'd need to resolve this structure. You'll need VBA to resolve this in the general case (arbitrarily deep hierarchies) or you can use the ugly self-join kludge that passes for a solution with fixed levels.
Once you have that, telling leaf nodes from internal nodes of the resulting tree is pretty easy since only have to do a single-level self join and count the number of entries where the left."member tn" == right."lead tn" (0 for leaf nodes, more for internals).
posted by themel at 1:40 PM on August 21, 2012
@OP: Access SQL doesn't do recursive queries as per StackOverflow consensus, which is what you'd need to resolve this structure. You'll need VBA to resolve this in the general case (arbitrarily deep hierarchies) or you can use the ugly self-join kludge that passes for a solution with fixed levels.
Once you have that, telling leaf nodes from internal nodes of the resulting tree is pretty easy since only have to do a single-level self join and count the number of entries where the left."member tn" == right."lead tn" (0 for leaf nodes, more for internals).
posted by themel at 1:40 PM on August 21, 2012
Response by poster: When I've said GROUP it's a bit of a misnomer here as that's how my employer refers to these pager numbers that have multiple members. Thanks for your clarifications.
posted by brokeaspoke at 1:41 PM on August 21, 2012
posted by brokeaspoke at 1:41 PM on August 21, 2012
Response by poster: @themel: I think you have the idea. I'll give your solution a try and come back as I'm not sure I understand your solution completely even though I get the idea. I understand the ugly self-join but I'm not sure i get the next part.
posted by brokeaspoke at 1:48 PM on August 21, 2012
posted by brokeaspoke at 1:48 PM on August 21, 2012
Response by poster: Still not getting it. Results yield multiple copies of extra duplicated data or where Group Lead is a 1:1 match with Member TN. Does anyone have any idea what I might be doing wrong?
posted by brokeaspoke at 6:57 PM on August 21, 2012
posted by brokeaspoke at 6:57 PM on August 21, 2012
1. One co-worker suggests querying by hand multiple times, saving the results into multiple tables and then appending all the resulting tables into one table.
This might work, but it seems like way too much work. Instead of making a second table, just append the results to the original table instead. Use Primary Keys on a column to prevent duplicates.
It's NOT RELATED in the database in the way you are describing.
It is in a way because some of the member TNs are also in the Group Lead column.
So, any record with the first name Page is actually a group lead, right? First, make sure you don't have anyone named Page! Then you can write a query that makes a table that pulls all the Docs (because you paged 5553215255, for example) and name it Paged List. Put a primary key on the Member TN Field.
Relate the Member TN on the Paged Table to the Group Lead on the original table in a query. Then pull any records from Paged Table where the First Name = Page. Append those records to Paged Table. At this point the user "Page Emergency Peds" is on your Paged Table. You can run the append query again and it will add the Emergency Peds to your Paged Table without inserting duplicates because of the Primary Key.
posted by soelo at 7:36 PM on August 21, 2012
This might work, but it seems like way too much work. Instead of making a second table, just append the results to the original table instead. Use Primary Keys on a column to prevent duplicates.
It's NOT RELATED in the database in the way you are describing.
It is in a way because some of the member TNs are also in the Group Lead column.
So, any record with the first name Page is actually a group lead, right? First, make sure you don't have anyone named Page! Then you can write a query that makes a table that pulls all the Docs (because you paged 5553215255, for example) and name it Paged List. Put a primary key on the Member TN Field.
Relate the Member TN on the Paged Table to the Group Lead on the original table in a query. Then pull any records from Paged Table where the First Name = Page. Append those records to Paged Table. At this point the user "Page Emergency Peds" is on your Paged Table. You can run the append query again and it will add the Emergency Peds to your Paged Table without inserting duplicates because of the Primary Key.
posted by soelo at 7:36 PM on August 21, 2012
Response by poster: @soelo: "Page" is not consistent. I used it for the sake of example. It can be a descriptor of a floor in our hospitals, a division, anything at all. Some of it is goofy or arbitrary. The GROUP LEAD column is definitely all Group Leads. Then some of the MEMBER TN are leads but some are people (or even devices).
Furthermore, the MEMBER TN in the real table is sometimes shared by multiple people so the combination of a GROUP LEAD, MEMBER TN, LAST NAME, FIRST NAMES are the combination that make for unique values (I don't say unique record as there are some duplicate records because of poor data managent -- this comes from a group who sees Excel spreadsheets as databases).
So I made a copy of the original table and have joined the copy to it by joining GL in the original to MTN in the copy. THis is where I now have trouble. I can't seem to figure out which fields from which table belong in the grid to get the proper results.
Thanks for your comments.
posted by brokeaspoke at 8:13 PM on August 21, 2012
Furthermore, the MEMBER TN in the real table is sometimes shared by multiple people so the combination of a GROUP LEAD, MEMBER TN, LAST NAME, FIRST NAMES are the combination that make for unique values (I don't say unique record as there are some duplicate records because of poor data managent -- this comes from a group who sees Excel spreadsheets as databases).
So I made a copy of the original table and have joined the copy to it by joining GL in the original to MTN in the copy. THis is where I now have trouble. I can't seem to figure out which fields from which table belong in the grid to get the proper results.
Thanks for your comments.
posted by brokeaspoke at 8:13 PM on August 21, 2012
Response by poster: If I join the two tables as described in my last comment and then drag all fields from one of the tables to the grid the query returns the original set of data despite my joining GROUP LEAD in one table to MEMBER TN in the other. This makes me think I need to drag some from each table to the grid but I can't seem to hit on the right combination.
posted by brokeaspoke at 8:19 PM on August 21, 2012
posted by brokeaspoke at 8:19 PM on August 21, 2012
I think your data could use some normalization or optimization. Something needs to tell you if a number will result in more numbers being paged or not. It could be as simple as adding a column that indicates that a record is also a group lead and have only two values, Y and N.
THis is where I now have trouble. I can't seem to figure out which fields from which table belong in the grid to get the proper results.
Use fields from the table that has the member TNs joined because that should only give you the results that have a Group Lead number as a Member TN. If you use the table with Group Leads joined, it will give you all results as they are all member TNs as well. You do not need fields from both tables.
posted by soelo at 9:35 PM on August 23, 2012
THis is where I now have trouble. I can't seem to figure out which fields from which table belong in the grid to get the proper results.
Use fields from the table that has the member TNs joined because that should only give you the results that have a Group Lead number as a Member TN. If you use the table with Group Leads joined, it will give you all results as they are all member TNs as well. You do not need fields from both tables.
posted by soelo at 9:35 PM on August 23, 2012
This thread is closed to new comments.
I need a query that when run returns a list of all members of all groups paged.
You know about group by, right? But where are you getting the "groups paged" info?
You are saying that "doctors" is a value under the group column, but you also say that "peds" should be in a group called doctors. There are no groups/subgroups defined here.
I work with access and sql often- and it seems like this data needs completely rebuilt in order to work with effectively.
posted by sarahnicolesays at 12:36 PM on August 21, 2012