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 33

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 33

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.

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'
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

LVL 34

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now