ms access - SQL grouping by words LIKE (with wildcards), how to achieve correct sum -


i working access query in excel vba. table contains mix of more general , specialised products, column strings e.g:

    mountain bike x2321, 10     enduro mountain bike special y3233, 20      mountain bike a2a321, 10     skateboard z23233, 10     skateboard y2dsf3, 10     skadeboard pro advanced special 20, etc

i sum total sales values , aggregate sums categories specified in separate tableb, dictionary substrings: (

mountain bike special, mountain bike skateboard pro skateboard.

i sorted table in descending order longer names show before shorter ones. sorting expected join result in scateboard pro tested existence against record before scateboard tested.

my expected result be

mountain bike special   total 20 mountain bike           total  20 skateboard pro total  20 skateboard      total 20

however query totals mountain bikes, special ones , skateboards, pro ones incorrect. them summed in separate categories. sorting descending tableb, dictionary did not hepl. :

mountain bike special   total 20 mountain bike                 total  40 skateboard pro total  20 skateboard      total 40

my attempted query includes searching , wildcards, looks follows:

select "" kod, sum(b) wartosc, tableb.descr opis_grupy

from tablea left join tableb on tablea.descr (""+tableb.descr+"")

where tablea.a not null

group tableb.descr

order tableb.descr desc

unfortunately, such query not sum correctly. contemplating use of first np. http://www.mrexcel.com/forum/excel-questions/417807-sql-join-question-excel-access-only-return-first-join-instance.html (użycie funkcji first) have no idea how implement query should sum expected. hepling me out.

you try use (sub-)query first find longest matching category, like:

select tablea.descr adescr, tableb.descr bdescr tablea left join tableb on tablea.descr "*" + tableb.descr + "*" group tablea.descr, tableb.descr having len( tableb.descr ) = max( len( tableb.descr ) )

notice having clause limit results longest match tableb. once have dataset can aggregate bdescr in next step.


Comments

Post a Comment

Popular posts from this blog

php - failed to open stream: HTTP request failed! HTTP/1.0 400 Bad Request -

this code i'm using find latitude , longitude of place: function coords($address){ echo $url="http://maps.googleapis.com/maps/api/geocode/json?address=".$address; $json = file_get_contents(urlencode($url)); $data = json_decode($json, true); print_r($data['results'][0]['geometry']['location']['lat']); print_r($data['results'][0]['geometry']['location']['lng']); } however returns warning: failed open stream: http request failed! http/1.0 400 bad request if use above code in simple procedure works fine not in function. note: have tried curl_init()... method produced same result? instead of encoding whole url encode address part. following code work fine $add='jamshoro phase 2'; $url="http://maps.googleapis.com/maps/api/geocode/json?address=".urlencode($add); $json = file_get_contents($url); $data = json_decode($json, true); print_r($data['r
Read more

java - How to filter a backspace keyboard input -

i have code gives message when key number pressed: txtvalueofclause.addeventfilter(keyevent.key_typed, new eventhandler<keyevent>() { @override public void handle(keyevent t) { char ar[] = t.getcharacter().tochararray(); char ch = ar[t.getcharacter().tochararray().length - 1]; if (!(ch >= '0' && ch <= '9')) { system.out.println("the char entered not number"); t.consume(); } } }); now if press on wrong button , press backspace remove it, error message. how can add backspace input if statement? i have found answer: txtvalueofclause.addeventfilter(keyevent.key_typed, new eventhandler<keyevent>() { @override public void handle(keyevent t) { char ar[] = t.getcharacter().tochararray(); char ch = ar[t.getcharacter().tochar
Read more

java - Show Soft Keyboard when EditText Appears -

i have code in android app, alert dialog , keyboard, , when dialog shown want keyboard appear. not , not sure why? here code: final alertdialog.builder alert = new alertdialog.builder(this); alert.settitle("title"); alert.setmessage("message"); final edittext input = new edittext(this); input.setinputtype(inputtype.type_class_number); alert.setview(input); input.setonfocuschangelistener(new view.onfocuschangelistener() { @override public void onfocuschange(view v, boolean hasfocus) { if (hasfocus) { log.i(tag, "in focus"); inputmethodmanager inputmethodmanager = (inputmethodmanager) getsystemservice(context.input_method_service); inputmethodmanager.showsoftinput(input, inputmethodmanager.show_implicit); } } }); but keyboard not appear though edit text in focus, , tell appear why, doesn't come up? thanks in advance.
Read more