Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 341
  • Last Modified:

MS Access: Have Query return second column

Hello,

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?

Thanks
0
dabug80
Asked:
dabug80
  • 3
  • 2
  • 2
  • +2
2 Solutions
 
Mike EghtebasDatabase and Application DeveloperCommented:
Can you post your query here?
0
 
dabug80Author Commented:
Sure:

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
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
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:

http://office.microsoft.com/en-001/access-help/using-multivalued-fields-in-queries-HA010149297.aspx#BM3

-------
Also try-->   you possibly could handle this by switching the columns in table tbl_Treatments field tprPractitioner lookup query.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
dabug80Author Commented:
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.
0
 
ReneD100Commented:
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'
0
 
PatHartmanCommented:
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.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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:

http://access.mvps.org/access/lookupfields.htm
0
 
ReneD100Commented:
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.
0
 
dabug80Author Commented:
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?

----

Edit:

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:

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

Best practice is:

tblMovie
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.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now