• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 308
  • Last Modified:

distinct query on 2 fields but keep field order in tact

Access 2010

I have a query that i need to have 2 distinct fields:

but i need the fields in this order

SELECT DISTINCT alt_acc_comp_score.MATERIAL_NO, main_xref_t1_2.WWGSlsStatus, alt_acc_comp_score.ALT_ACC_MATERIAL_NO, main_xref_t1_2.WWGShortDesc, main_xref_t1_2.WWGMfrNum, main_xref_t1_2.WWGVdrBrandName, main_xref_t1_2.WWGSlsStatus, main_xref_t1_2.WWGCatalogPgNo, main_xref_t1_2.WWGUOM, main_xref_t1_2.WWGUOMQty, main_xref_t1_2.WWGGreen, main_xref_t1_2.WWGExclBrand, main_xref_t1_2.WWGPrice, main_xref_t1_2.WWGCountry, main_xref_t1_2.XRefType, main_xref_t1_2.XRef_Comment, main_xref_t1_2.WWGLongDesc, alt_acc_comp_score.comp_score
FROM alt_acc_comp_score INNER JOIN main_xref_t1_2 ON alt_acc_comp_score.ALT_ACC_MATERIAL_NO = main_xref_t1_2.WWGSKU
WHERE (((alt_acc_comp_score.MATERIAL_NO)="2a234"))
ORDER BY alt_acc_comp_score.MATERIAL_NO, alt_acc_comp_score.comp_score DESC;

Open in new window

  • 2
1 Solution
Jeffrey CoachmanMIS LiasonCommented:
In order to have "2 distinct fields', you typically need a "Group By" statement for those two fields (Totals Query)

but then you may probably lose sum of the detail records because of this.

GROUP BY alt_acc_comp_score.MATERIAL_NO, alt_acc_comp_score.ALT_ACC_MATERIAL_NO

Do this first and see what  you get.

If you use a Group By, then typically you need to use HAVING for the criteria, instead of "WHERE",,

But lets see if an expert more versed in SQL can see what you might be lacking in your syntax.
FordraidersAuthor Commented:
Jeffrey CoachmanMIS LiasonCommented:
ok, but there was no rush, ...you could have waited to see if another expert could have provided a more complete solution...


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Turn Raw Data into a Real Career

There’s a growing demand for qualified analysts who can make sense of Big Data. With an MS in Data Analytics, you can become the data mining, management, mapping, and munging expert that today’s leading corporations desperately need.

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