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:
alt_acc_comp_score.MATERIAL_NO
alt_acc_comp_score.ALT_ACC_MATERIAL_NO

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



Thanks
fordraiders
LVL 3
FordraidersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

SELECT ...
FROM...
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FordraidersAuthor Commented:
thanks
0
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...

;-)

Jeff
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.