Really confused anyone want a challenge?????? (1 Viewer)

[N1ck9141]

Ok the latest task, i have to create a query, that works in a similar way to the find duplicates query...

however is has to look through "query1" and count common phrases for example..

if the word "sand" appears in query1 "row 7 colum 4" and "row 8 colum 9", it would produce a table which stated "sand" "2"....because there are two "sand"s throughout the whole query not jst in particualar rows or colums

any help???

 

[ajetrumpet]

is that english?

 

[N1ck9141]

no it wasnt it was very bad lol, corrected anyway so anyhelp???

 

[ajetrumpet]

read up on recordsets. you may have to use one here to loop through every row, and subsequently every field while you're on a row to count the occurances. alternatively though, you can use:

DCOUNT("field", "table", "[field] = 'SEARCHED TEXT'")

for each field in the table to get the occurances, if there are not many fields.

a recordset here would be difficult to write because you will have to nest the field looping inside the record looping.

 

[Atomic Shrimp]

Are you trying to tabulate a word count of words appearing in a collection of sentences, or just tabulate a count words appearing one word per field?

 

[N1ck9141]

I have no idea what you just said lol...
I need the query to finish with a table that in colum1 lists the text in each Cell for example down colum 1 i would have
"sam"
"John"
"james"
etc.

in "coloum 2" how many times they appear..so the query continues reading through if it comes across "sam" again it will then put a 2 next to sam
e.g.

"sam" "2"
"john"
"jame"

if it finds sam again that 2 becomes a 3...

however if it comes across a new word in a cell e.g. "nick"
it will then add that to the list in colum1 e.g.

"sam" "3"
"john" "1"
"james" "9"
"nick" <----New text that is currently in no other cell

then the query will keep looking so on till it covers every cell in the quiery its searching through

 

[DCrake]

This is an expansion on an earlier thread. Have you solved that one yet?

Anyway what is the purpose of the exercise? What are you going to do with the information once you have it. Ok it says "Sand 3" so what good is that to anyone? It's a bit like calling a car salesman and asking "How many blue cars have you got?" If he rings back and says "Three" whats that going to tell you?

David

 

[N1ck9141]

What the data actually is, is "softwarenames" and "no. of pcs that contain it"....

so it will say "Access" "9" and this way, when we update our contract at work we no we require "9" licence keys for "access" so on, but sadly i dnt get to choose the format at which the data comes to me i have it with computer along top "headings" and below is the software on them so i need it to read the software and figure out how many times the item of software appears in the WHOLE query (hence why duplicate doesnt work) so that i no how many licences are required

does this help at all?

 

[ajetrumpet]

It's a bit like calling a car salesman and asking "How many blue cars have you got?" If he rings back and says "Three" whats that going to tell you?

nothing. but the original call might indicate that it was the Blue Man Group calling the dealer for some new rides to their next concert performance. haaaa!

 

[N1ck9141]

the only other way i can see of doing it, is someone finding a way create a query which will take in vertical colum and stack them ontop of each other in one long colum so i can then run duplicate on this, however that itself isnt easy

however this then raises the issue of not showing when there is simply 1 copy which is also needed

 

[DCrake]

This again can be done with union queries

Select Field1 As Tempfield From TblStock Where Field1 Is Not Null

Union

Select Field2 As TempField From TblStock Where Field2 Is Not Null

Union

Select Field3 As TempField From TblStock Where Field3 Is Not Null

etc

You can do this for as many fields as you wish and as many tables as you wish.

This will give you one long list.

You then use this union query as the underlying query of your search based query. Note the use of Is Not Null. This reduces the amount of data being returned by yhe union query. Remember you should only need to use this on text type fields.

David

 

[N1ck9141]

so would this be correct if the fields and in tbl "test" and are called "P (1) and PC (2)"

SELECT [PC (1).Version] As Tempfield From test Where [PC (1).Version] Is Not Null
UNION
SELECT [PC (2).Version] As Tempfield From test Where [PC (2).Version] Is Not Null;

this doesnt actually stack them just give me one single list with files that both contain where as it shld give doubles of the ones u both lol...

i hav worked out its only giving me one list cause it puts the first one dwn then covers over with second one the way the code is writen is there a way to write to add onto the data that is already present

 

[Atomic Shrimp]

I have no idea what you just said lol...

What I mean is this: does your source data consist of a bunch of single words, for example, a column containing:
Sand
Water
Salt
Sand

Or are you wanting to count instances of the words occurring within whole sentences, for example, a column containing:
A bucket of sand
three buckets of sand and a bucket of water
twelve tons of sharp sand and six hundredweight of fine builders sand

??

The answer to this question makes a big difference to the approach - just counting up instances of single words in a column is pretty simple.

 

[N1ck9141]

oh i see well sometimes it is single words sometimes it will be multiple because its software names it could be "adobe" or "windows live messenger" so i need it to count up the contence of cells single word or sentence does this help

and the UNION method is working because the second UNION or SELECT statement covers over the first so i still end up with a like of only one pc rather than the lot of them all in one big colum

 

[Atomic Shrimp]

This again can be done with union queries

Select Field1 As Tempfield From TblStock Where Field1 Is Not Null

Union

Select Field2 As TempField From TblStock Where Field2 Is Not Null

Union

Select Field3 As TempField From TblStock Where Field3 Is Not Null

etc

You can do this for as many fields as you wish and as many tables as you wish.

This will give you one long list.

You then use this union query as the underlying query of your search based query. Note the use of Is Not Null. This reduces the amount of data being returned by yhe union query. Remember you should only need to use this on text type fields.

David

I was going to recommend exactly this solution, but when I ran my union query, rather than returning a long list with repeats, it only returned one instance of each word.

I started off with table1:

[B]field1	field2	field3[/B]
sand	sugar	salt
water	sand	salt
water	water	sugar

My union query:

SELECT ALL Table1.field1 as tempresults
FROM Table1
UNION
SELECT ALL Table1.field2 as tempresults
FROM Table1
UNION
SELECT ALL Table1.field3 as tempresults
FROM Table1;

Returns:

[b]tempresults[/b]
salt
sand
sugar
water

I was expecting:

[b]tempresults[/b]
sand
water
water
sugar
sand
water
salt
salt
sugar

 

[N1ck9141]

Yea this is just what i got im glad it wasnt just my code going wrong...but it does beg the question is there not a statement other than SELECT which will add to whats all ready there or a statement other than UNION which will add to whats already there?

 

[Atomic Shrimp]

oh i see well sometimes it is single words sometimes it will be multiple because its software names it could be "adobe" or "windows live messenger" so i need it to count up the contence of cells single word or sentence does this help

Shouldn't be a big problem, as long as you're happy to treat "Windows Live Messenger" as a single entity (i.e. you count the number of instances of that phrase, not the individual instances of "windows" and instances of "live", etc

 

[N1ck9141]

yes that is just what i was hoping to achieve to have it like describe do u no how i would go about this then, because the few ideas i have tried e.g. UNION one isnt working or having the affect the we wld have predicted

 

[DCrake]

Here is a sample of selecting mor thanone field in a table and more than one table to create one large union table.

David