Select Distinct Sort

[Eljefegeneo]

Getting a little crazy on this. I have a select query as follows:

  SELECT Distinct tblPrescriptions.Medicine
  FROM tblPrescriptions
  WHERE (((tblPrescriptions.ForWhom) Like "*M*") AND ((tblPrescriptions.DatePurchased) Between Date() And Date()-365))
  ORDER BY tblPrescriptions.Medicine;

I cannot get the list to sort ascending.

I even tried to use the above to make a new query, and select Ascending rode, but the results are the same. My understanding that if you use Distinct it automatically sorts Ascending.

 

[plog]

Can you demonstrate how it is not sorting ascending? Also, is the Medicine field a text data type or numeric data table in the table?

 

[Eljefegeneo]

tblPrescriptions has the following fields (plus a few others):
PrescriptionID
Date Purchased
Medicine
ForWhom

The Medicine field is a lookup (Just realized this may be the problem)

  SELECT PrescriptionLookup.IDPresc, PrescriptionLookup.Prescription, PrescriptionLookup.PrescriptionNumber
  FROM PrescriptionLookup
  ORDER BY PrescriptionLookup.Prescription;

And looking at it the field characteristics, the bound column is 1.

 

[plog]

Not only did you just move the goalpost, but you changed the game as well.

What does the query you just posted have to do with your first post? Why are you talking about new fields? Why did you use the term 'bound column'? A query has no bound columns.

Perhaps you can tell us what it is you are actually trying to accomplish, because your second post has absolutely nothing to do with your first.

 

[Eljefegeneo]

Sorry for the confusion. I posted the code for the select distinct query first. Then I thought that I would show where the data came from. I.e. the table. When I was looking at the table property for the Medicine field, that is when I realized that it was a lookup filed based on another table.

I am merely trying to sort a Select Distinct query alphabetically. When I realized that it (the Medicine field) was a lookup field, I thought it prudent to show where the data for the field comes from in case that was the source of the problem.

Medicine is a text field, but the bound field on the table from which the data comes is a numeric field. I hope this doesn't add to the confusion.

If it would help, I can upload a watered down version of the DB.

 

[Pat Hartman]

You've just run into one of the many reasons to NOT use table level lookups. This "feature" is for novices who don't know how to write queries or code because once you do either, you keep running into stuff like this. You think you are working with a text value but you are NOT.

Lookups on tables mask the actual value of a field. You "see" a string and can't understand why you can't sort it. However, Access "sees" the underlying number because that is what is actually stored in that field and so that is what it sorts by.

I would simply get rid of the lookups. They are not actually helping you since users should NEVER, EVER be presented with raw tables or queries. Users interact with forms. Using combo boxes on forms is good practice because the user sees something that makes sense. Behind the scenes, your queries will join to the lookup table and select the numeric value from the "main" table and the text value from the "lookup" table and then the query can sort on the number or the text, which ever makes sense.

 

[Eljefegeneo]

Yes, lesson learned the hard way. Fortunately the outcome wasn't really that important, but it would have been nice to solve. .

 

[Pat Hartman]

You can solve it. Although, it is always better to actually fix the problem than to patch it, you can patch by simply adding the lookup table to the join so you can actually select the text column from that table and sort on the text value.

 

[Eljefegeneo]

Thanks for the suggestions. I'll try this tomorrow. Too late to do it tonight, my first few attempts got nowhere. That happens when I should be getting ready for sleep.

 

[arnelgp]

SELECT Distinct tblPrescriptions.Medicine, PrescriptionLookup.Prescription
FROM tblPrescriptions INNER JOIN PrescriptionLookup ON tblPrescriptions.Medicine=PrescriptionLookup.IDPresc
WHERE (((tblPrescriptions.ForWhom) Like "*M*") AND ((tblPrescriptions.DatePurchased) Between Date() And Date()-365))
ORDER BY PrescriptionLookup.Prescription;