Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


MS Access: Have Query return second column

Posted on 2014-11-02
Medium Priority
Last Modified: 2014-11-05

I am working on a MS Acess 2007 query. One field is based on a two column table, with the values:

1      Fred
2      Sam
3      John
4      Ed

I have configured this table to display the second column (the name) and this shows up fine. However when I change the Total to group by 'Last', the value shown is the ID (column 1) rather than column 2.

In a query, is it possible to return the second column when you group by last?

Question by:dabug80
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40418817
Can you post your query here?

Author Comment

ID: 40418821

SELECT tbl_Patients.tpPatient, tbl_Patients.tpFirstname, tbl_Patients.tpSurname, tbl_Patients.tpEmail, tbl_Patients.tpMobile, tbl_Patients.tpOktocontact, Last(tbl_Treatments.tprPractitioner) AS LastOftprPractitioner, Last(tbl_Treatments.ttTreatmentdate) AS LastOfttTreatmentdate
FROM tbl_Patients INNER JOIN tbl_Treatments ON tbl_Patients.tpPatient = tbl_Treatments.tpPatientlookup
GROUP BY tbl_Patients.tpPatient, tbl_Patients.tpFirstname, tbl_Patients.tpSurname, tbl_Patients.tpEmail, tbl_Patients.tpMobile, tbl_Patients.tpOktocontact, tbl_Patients.tpAddress, tbl_Patients.tpSuburb, tbl_Patients.tpState, tbl_Patients.tpPostcode
HAVING (((tbl_Patients.tpOktocontact)=Yes) AND ((Sum([ttpaid]+[ttHICAPS]))>150));

The field in question is: tprPractitioner
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40418835
Is this table (blow) what is supplying data to tprPractitioner in table tbl_Treatments?

1      Fred
2      Sam
3      John
4      Ed

if so you need to add .value for what you want to display. I do not have Access 2007 to test it.

Please check to see if this page could help out:

Also try-->   you possibly could handle this by switching the columns in table tbl_Treatments field tprPractitioner lookup query.
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


Author Comment

ID: 40418841
Thanks, but that info doesn't seem to help.

The records are shown correctly (as column 2) if I just want the field visible, but it's when I group by last, that the records are shown as column 1. So I think the 'group by' is a complicating factor.

Accepted Solution

ReneD100 earned 1200 total points
ID: 40418925
I assume that this is the field you mean Last(tbl_Treatments.tprPractitioner) AS LastOftprPractitioner. This is (I hope) an foreign key, linking to the first column from your {1,Fred;2,Sam;etc} table. Since this a number, the number will show rather than the Name.
Without the 'Last', Access really also returns the ID instead of the Name, but because you made this a 'lookup'  field, Access will insert a combobox with rowsource over the value and show the name instead of the ID. Once you use functions like Last this does not work anymore - image what would happen if you would use count() or sum()...

By looking at your query I assume you build it with the Query Builder in Access, so I am not sure how far your knowledges stretches. The easiest for you is probably to save this query, and build a new one with this query as input table/query and your {Fred/Sam/etc} table as second and link the fields LastOftprPractitioner and the ID column  from the {Fred/Sam}  table - I'm guessing it's called 'Practitioner'
LVL 39

Expert Comment

ID: 40419554
As Rene alluded to, the problem is caused entirely because the field is defined as a table level lookup.  Experts warn against these regularly.  There is no reason to use a lookup at the table level.  Doing so causes various types of problems that you need to work around such as this one.  You can always have a combo box on a form to provide the "lookup" feature.

To solve the problem, you need to add the lookup table to the query and join to it.  You can then select the "text" description rather than the ID field.
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 800 total points
ID: 40421261
I have configured this table to display the second column (the name) and this shows up fine.
Agree 100% with Pat - you should never use a lookup defined at the table level. Lookups are Form/UI conventions, not table properties, and users should never directly view tables, so there is no need to have those sorts of properties at the table level. I personally believe this was a result of the Access team trying to make Access more "excel-like".

Do away with this at the table level, and let your tables store DATA. Join your tables properly to produce the right query results, and you'll be well ahead of the curve.

For a humorous look at the issue, see this:

Expert Comment

ID: 40421270
Few years ago I had to run a query over some sales issues in 2007 so I put a "=2007" in the query field. No results. Remove the criterium and it shows tons of data for 2007. Turns out  it was a lookup field and the year field should really have been 23 (or so). Lookup fields in tables can make things really unlogical and create quite a bit over extra work to create queries and look at results the right away.

Author Comment

ID: 40423222
Thanks for all your comments. Just a best practice question then - If I would like to maintain a table with employee IDs and employee names,  what's the best way of structuring the database so that a query could return both values?



Ok. I've watched this helpful video on setting up tables and have better understood the situation.

Rather than create one table that takes its lookup values from another table, I understand that best practices dictate you should create another 'middleman' table to define this join relationship.

So bad practice is:

Movie Title
Genre ---- link to tbleGenre (one to many
Year of publication

Best practice is:

Movie Title
Genre ---- link to tblGenreMovie (one to many) --- link to tbleGenre (many to one)
Year of publication

So in this way the 'middleman' tbleGenremovie table establishes the relationship and allows the field data to be easily returned and sorted robustly.
LVL 85
ID: 40423470
Just to be clear: The "middleman" (or JOIN) table is used to create a Many-To-Many relationship. So in your example above, if a Movie could belong to more than one Genre, then you'd use a Join table between those two.

If a Movie could belong to one and only one Genre, then you don't need a Join table.

In your specific case, where you asked:

If I would like to maintain a table with employee IDs and employee names
It would seem that is the proper setup for an Employee table. It's when you use that table in relationship with other tables that you have to be concerned about the various storage type and relationships available. For example, if you have a Project table, and you could have multiple Employees on a Project, then you'd need a Join table between Projects and Employees. If a Project can be assigned to one (and only one) Employee, then you don't need the Join.

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question