Word count with like search????? (1 Viewer)

[N1ck9141]

I have a data base with multiple tables, example t1, t2, and t3

i want to create a query that will search all data in "t1, t2 and t3" and find a word i specifie... for example i run query and get a popup box i type sam...and it produces a result that says i have 6 sams all together...

any help or idea???

(for more informaiton i actually have 30 tables just using 3 as example )

 

[DCrake]

Can "sam" appear in any text type field of will it be in a common field in many tables?

 

[N1ck9141]

"sam" will appearing in the same field in many different tables, even the field name will be the same which is in this case "name" so same field in each table but many tables yes

 

[DCrake]

Ok then the best method would be to create a union query of all the tables with the coressponding field name and perform a Like "*Sam*" on the query.

Tip: Add an extra column in you union query to denote the source table.

Select Field As MyField, "Table1" As Source From Table1
Union Select All Field As MyField, "Table2" As Source From Table2

Etc

David

 

[N1ck9141]

ok how do i create the union ive never heard of this method before???

 

[Atomic Shrimp]

Are they 30 tables all with similar structure and containing similar kinds of data? What's the reason for them being separate entities?

 

[N1ck9141]

they are basically name tables like example class1 and class2 at school, i need a query that will search both together and go" u have 4 Sams or even better u hav 4sams 3 in class1 and 1 in class2 "if u follow?

 

[DCrake]

I gave you a brief description of the syntax in my earlier thread

Select Field As MyField, "Table1" As Source From Table1
Union Select All Field As MyField, "Table2" As Source From Table2

Create a new query but don't select a table or query. Then click on the SQL view

and type in the sql statement as outlined above.

Where Field is the common field name, "Table1" is an alias of the table that holds the field. for example if it is coming from a contacts table it would read something like this

"Contacts" As SourceTable From tblContacts

Repeat this for one or two test tables and save the query. Then select the datasheet view and it should list the columns you have defined.

Once ok fill in rest of tables, save query.

Then create another query and use the union query as the source, bring down all fields and place your search crtieria in the condition line in the new query.

David

 

[N1ck9141]

thank you ill try this now

 

[N1ck9141]

No im still getting the syntex wrong it doesnt seem to be picking up my data at all thanks anyway i guess ill have to figure something else out

 

[DCrake]

Morning Nick.

Here are two examples of Union queries.

SELECT fldNESector AS Sector, fldPCTCode As PCT, "Discharges" AS Source,  fldHRGCode AS HRG,ConvOPCS4(fldOPCS4code) AS OPCS4, FinYear([fldDischargeDate]) AS [Financial Year], FinMonth([fldDischargeDate]) As Period, Year([fldDischargeDate]) AS [Year], Month([fldDischargeDate]) AS [Month], Format(Format([fldDischargeDate],"ww"),"00") AS Wk, 1 AS [Count],[fldGPCodeNetcare] AS GMC,
[fldPracticeCodeNetcare] as Practice
FROM TbdDischarges
WHERE (((fldTreated)=-1));


UNION ALL SELECT TbdDischarges.fldNESector AS Sector, QryAllAuthorsations.fldPCT AS PCT, "Additionals" AS Source, QryAllAuthorsations.fldHRGCode AS HRG, QryAllAuthorsations.OPCS4, FinYear([fldDischargeDate]) AS [Financial Year], FinMonth([fldDischargeDate]) AS Period, Year([fldDischargeDate]) AS [Year], Month([fldDischargeDate]) AS [Month], Format(Format([fldDischargeDate],"ww"),"00") AS Wk, 1 AS [Count], TbdDischarges.fldGPCodeNetcare AS GMC, TbdDischarges.fldPracticeCodeNetcare AS Practice
FROM QryAllAuthorsations INNER JOIN TbdDischarges ON QryAllAuthorsations.WorkFlowId = TbdDischarges.fldWorkflowID
WHERE (((QryAllAuthorsations.fldChargeable)=-1));

UNION ALL SELECT tbdPreOpData.fldNESector AS Sector, tbdPreOpData.fldPCTCode AS PCT, "Pre-Ops" AS Source, tbdPreOpData.fldHRGCode AS HRG, ConvOPCS4(fldOPCS4Code) AS OPCS4, FinYear([fldClinicAppointmentDate]) AS [Financial Year], FinMonth([fldClinicAppointmentDate]) AS Period, Year([fldClinicAppointmentDate]) AS [Year], Month([fldClinicAppointmentDate]) AS [Month], Format(Format([fldClinicAppointmentDate],"ww"),"00") AS Wk, 1 AS [Count], tbdPreOpData.fldGPCodeNetcare AS GMC, tbdPreOpData.fldPracticeCodeNetcare AS Practice
FROM tbdPreOpData
WHERE (((tbdPreOpData.fldPatientAttended)=True));

SELECT HRG, F2 AS MT, 'F2' AS Fld FROM PreOpMT;
UNION ALL SELECT HRG, F3 AS MT, 'F3' AS Fld FROM PreOpMT;
UNION ALL SELECT HRG, F4 AS MT, 'F4' AS Fld FROM PreOpMT;
UNION ALL SELECT HRG, F5 AS MT, 'F5' AS Fld FROM PreOpMT;
UNION ALL SELECT HRG, F6 AS MT, 'F6' AS Fld FROM PreOpMT;
UNION ALL SELECT HRG, F7 AS MT, 'F7' AS Fld FROM PreOpMT;

 

[Atomic Shrimp]

I tend to use one of two easy methods for building union queries - start by creating individual (partial) queries, then either:

Save them as individual query objects in the database, then unite them with a union query that doesn't know or care about their contents, i.e.:

SELECT Query12.*
FROM Query12

UNION

SELECT Query13.*
FROM Query13;

Or:

Copy and paste the SQL out of the individual queries into a single query (in SQL view), removing all but the last semicolon, then putting 'UNION' in between each block of original SQL, i.e.:

SELECT [banana].Field1, [banana].incID, [banana].postcode
FROM [banana]

UNION

SELECT [onion].Field1, [onion].incID, [onion].postcode
FROM [onion];

 

[N1ck9141]

Would this not then give me 30 popup boxes that require the same peice of information over and over again?

e.g. one popup box for every quiery i created

 

[N1ck9141]

I tend to use one of two easy methods for building union queries - start by creating individual (partial) queries, then either:

Save them as individual query objects in the database, then unite them with a union query that doesn't know or care about their contents, i.e.:

SELECT Query12.*
FROM Query12
 
UNION
 
SELECT Query13.*
FROM Query13;

Or:

Copy and paste the SQL out of the individual queries into a single query (in SQL view), removing all but the last semicolon, then putting 'UNION' in between each block of original SQL, i.e.:

SELECT [banana].Field1, [banana].incID, [banana].postcode
FROM [banana]
 
UNION
 
SELECT [onion].Field1, [onion].incID, [onion].postcode
FROM [onion];

I tried this method and it just seems to produce one table with only the common data in nothing else, am i doing it right ??? because that wasnt what i was aiming for

 

[DCrake]

The object of the exercise is to look in the same field in different tables to look for the occurance of a phrase. Instread of doing 30 lookups for example we are stacking each table on top of each other to make one big table and looking in there.

However if we find it in the union query it would be handy to know which table it actually resided in. That is why we added a table identifier as an extra column in the query.

We do not need to parameterise each part of the union query we will do that via another query

If you can send say three tables with a few records in each and I will put together what you need.

David