Query to generate separate lists from same field

[dave1947]

I have a field containing phone numbers with a phonetype field that links to another table listing phone types (home, cell, work, fax etc.) and I want to generate a list of each person's phone numbers in the order home, cell, work, emergency contact. When I enter the phone type criteria in each column in query design and then run the query, the only phone numbers listed are the ones that match the type selected in the last (rightmost) column in the query. Have I made this over-normalized by having a phone type table, rather than including the text for phone type in the phone table? Should I create separate queries for each phone type and then combine the queries in a form or report? I want be able to search for a person's phone numbers, and be able to add or change phone numbers for new or existing records.

[June7]

What do you mean by 'phone type criteria in each column' - there should only be one phone number column and one phone type column. Provide example of table structure and the query SQL statement.

[John_G]

Hi -

Can you provide a more detailed description of your table structure? What does the phone information in the Person record look like? If there is just one field, and it contains a phone type, then you can only link to one record.

What are the fields in the Phone table, and what field(s) do you use to relate to the Person table?

If you are never going to search using actual phone numbers (i.e. "Who has this phone number?"), then this might be a case where normalization causes more problems than it solves; there are not that many different type of phone numbers.

John

[dave1947]

The query is intended to pull a name from a person table, a column for phone number from the phone table with the criteria for home phone, another column for phone number from the phone table with the criteria for cell phone, and another column for phone number from the phone table with the criteria for work phone. There is a join table that links person and phone number and phonetype_fk with a linked phone type table. I'm just getting into learning query construction so I'm fumbling along.

tried to run the query just now and got an error statement that there was a conflict and then it opened the following script:
SELECT [MemFirstName] & " " & [MemLastName] AS Member, [oldICENullsTbl].ICENullYN, ICENamesTbl.ContactName, PhoneTypesTbl.PhoneType, [PhoneAreaCode] & "-" & [PhoneExchange] & "-" & [PhoneNumber] AS [Phone Number], PhonesTbl.PhoneExtension
FROM (MembersTbl INNER JOIN ((((oldICEMembersJTbl INNER JOIN ICENamesTbl ON [oldICEMembersJTbl].ICEID=ICENamesTbl.ICEID) INNER JOIN ICEPhonesTbl ON ICENamesTbl.ICEID = ICEPhonesTbl.ICEID) INNER JOIN PhonesTbl ON (ICEPhonesTbl.PhoneID = PhonesTbl.PhoneID) AND (ICEPhonesTbl.PhoneID = PhonesTbl.PhoneID)) INNER JOIN PhoneTypesTbl ON ICEPhonesTbl.PhoneTypeID = PhoneTypesTbl.PhoneTypeID) ON [MembersTbl].MemberID=[oldICEMembersJTbl].MemberID) INNER JOIN oldICENullsTbl ON [MembersTbl].MemberID=[oldICENullsTbl].MemberID;

[dave1947]

This is a club membership database, want to keep track of current members, officers, committees, events. Have tables for member names, addresses, phones (members have have multiples of various types), emergency contact names (possibility of many to each member) and contact phones (again, possibility of one to many), dues for coming year, locker assignments and fees, and some external organizations (sponsors, vendors, etc.) and their addresses and phones. The phone table has a primary key and phone number, a join table links members (by primary key of members table) and phone numbers (by primary key of phone table) and phonetype_fk link to the phone type table. I'm trying to store all phone numbers in a single table, rather than have a separate member phone table, emergency contact phone table, and external organization phone table, and trying to do the same with addresses. You're right that I don't expect to do many searches by phone number rather than by name. I want to use the query to generate a member phone list with all relevant numbers and the emergency contact information (name(s) and number(s)). It has been the club practice to create a registration renewal form for each member that shows their contact and address information so they can confirm or correct it, with dues and locker fees (based on number of lockers leased), and I want to use a similar query to generate the renewal form. This has been done as a Word mail merge from an Excel spreadsheet. I also need to records dues receipts and produce lists of unpaid dues to send reminder notices.

[June7]

Sorry, can't see anything wrong with the query. What was the exact error message? What was the conflict?

[dave1947]

Sorry, can't see anything wrong with the query. What was the exact error message? What was the conflict?

Afraid I didn't write it down. When I attempt to open in design view I get an error message that it can't represent the join expression ICENamesTblICEID = ICEPhonesTblICEID in design view.
I cleaned out the sensitive date and attached the database current state as an attachment. I have a bunch of files called oldxxxxxxx because I intended to redesign the tables with lookup fields, but I'm not going to do that now and will be eliminating lookup fields from the tables where they currently exist. I want to design queries that retrieve information and not codes, and use the queries to drive forms for locating, updating and adding new records. I eliminated all the relationships in order to scrub the sensitive data.

[June7]

Query is [qryMember and ICE Name and Phone]? That's a lot of tables in one query.

Why are these fields joined twice: (ICEPhonesTbl.PhoneID = PhonesTbl.PhoneID) AND (ICEPhonesTbl.PhoneID = PhonesTbl.PhoneID)? (I deleted one of the pairs.)

Field in MembersTbl [MemLast Name] has a space. That space is not in the field name in the query. (I changed the field name in table.)

ICEPhonesTbl does not have PhoneID field, it is ICEPhoneID. (I edited the query.) It also does not have ICEPhoneTypeID field.

Fix those errors and query opens in Design view:
SELECT [MemFirstName] & " " & [MemLastName] AS Member, oldICENullsTbl.ICENullYN, ICENamesTbl.ContactName, PhoneTypesTbl.PhoneType AS Expr1, [PhoneAreaCode] & "-" & [PhoneExchange] & "-" & [PhoneNumber] AS [Phone Number], PhonesTbl.PhoneExtension AS Expr2
FROM (MembersTbl INNER JOIN (((oldICEMembersJTbl INNER JOIN ICENamesTbl ON oldICEMembersJTbl.ICEID = ICENamesTbl.ICEID) INNER JOIN ICEPhonesTbl ON ICENamesTbl.ICEID = ICEPhonesTbl.ICEPhoneID) INNER JOIN PhonesTbl ON ICEPhonesTbl.ICEPhoneID = PhonesTbl.PhoneID) ON MembersTbl.MemberID = oldICEMembersJTbl.MemberID) INNER JOIN oldICENullsTbl ON MembersTbl.MemberID = oldICENullsTbl.MemberID;

But still some issues.
There is no PhoneExtension field in PhoneTbl. There is ICEPhoneExtension in ICEPhonesTbl.
What table has [PhoneAreaCode] and [PhoneExchange] fields?
Will have to add in the PhoneTypesTbl.

How did you manage to build such a messed up query?

[dave1947]

Query is [qryMember and ICE Name and Phone]? That's a lot of tables in one query.

But still some issues.
There is no PhoneExtension field in PhoneTbl. There is ICEPhoneExtension in ICEPhonesTbl.
What table has [PhoneAreaCode] and [PhoneExchange] fields?
Will have to add in the PhoneTypesTbl.

How did you manage to build such a messed up query?

That is the query I'm trying to debug. It's a messed up query because I'm only beginning and have a better feel for table normalization than query construction. I've got Access 2010 the missing manual and have watched a bunch of online tutorials but most of what is illustrated runs a query against one or two fields, not the same field with multiple criteria simultaneously. I built the query when the phone table broke the phone number into three parts - area code, exchange, number - then revised the table to store the number as a single string, and didn't update the query. I also put a field in the members table to hard key concatenate the name rather than do it by query, and that field is the one I will pull in queries, forms and reports. There is a dbtable called calllist that includes names and phone numbers with fields for home phone, cell phone, work phone, and emergency contact (ICE) phone, but some members have multiple contacts and some contacts have multiple phones, and some members have multiple cell phones, and some numbers have extensions. Building a table structure for that many options would mean a lot of empty fields for some records and I thought a separate phone number table would be a better design, though it results in my query challenge - how to pull multiple phone numbers relating to a person and array them in headed columns, and with headed columns that list each contact with the contact's phone numbers. I have it set up in an Excel spreadsheet now with numbers stacked in the same cell when there are multiples of a type, and would like to get the same result in Access. I could conceivably just run an export to Excel, but I think the query for that would be the same thing I'm trying to build. I intend to use the single phone number table to store numbers for members, ICE contacts, vendors, sponsors, and peer organizations with join tables for each. I will probably have 125-150 active members in any year, 20 or so peer organizations, 5-10 sponsors, and a bunch of vendors with 600-800 phone numbers.

In essence the query result would be: (not using table and field names)
member name, home phone(s), work phone(s), cell phone(s), contact name(s), contact number(s) with any multiples stacked in the same column

I currently have the phone extension field in the join table, since multiple people may share a base phone number and each have individual extensions. If you think that's bad design, I'll gladly move it to the phone table.

I'll have a similar issue with addresses, since some members have multiple addresses, and so do some vendors. Currently I have an address table for member home addresses in this gated community, and a separate address table for any address outside the gated community, with relevant join tables. I structured the address tables to include a zipcode_fk that links to a zip code table which contains a field for each zip code and a city_fk to a city table that has city names with a state_fk to a state table - want to save data entry time on addresses by entering a zip code and have it get the linked city and state. The address table for the gated community includes a street_fk to a street name table to ensure uniformity - I'm surprised at how often I see the names misspelled or strangely abbreviated or leaving off the street/court/terrace/drive differentiator that can get mail delivered to the wrong place. I've seen address normalizers used on websites but doubt I can afford it and don't want to take more space than I need on the club computer.

In addition to the poor query design, when I open the query, it successively prompts me to enter a member name, phone area code, exchange, number, contact id, contact phone id before it pulls the data. I want to avoid that and have it either not prompt for anything and pull the data for every member, or have it prompt only for the member name and then pull the data for that name. I haven't gotten all the way through the query section of the book yet so I hope I learn how to control that.

Thanks for pointing out the numerous errors in my tables and queries. I appreciate your help.

[June7]

The query is prompting because it cannot find the fields referenced in the query.

So have you gotten the query to work now?