MS Access: Have Query return second column

Posted on 2014-11-02
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
  • 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.
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.


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 300 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 35

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 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 200 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 84
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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

856 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