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
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.
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.


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 38

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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

628 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