How do I find a record within two tables

[cfreeh]

I'm trying to create a macro that will look in two tables to find records. I have a form that has a sub-form and need to search for records within both tables. The problem is that I need to leave the "search for" blank until I have a particular word that I am looking for. The tables are joined by a same field (Project Number) and different fields within each table. Several people will use this database and each will have different words within the tables to Find Records. I have tried using the "FindRecord" command in a Macro and using [] (brackets) in the "Find What" Action Arguments but that will not work.

I have tried to create a Find Record for both tables but once these are put on the Main Form the command button only looks at fields witin the main table.

Any help will be greatly appreciated.

 

[jzwp22]

The tables are joined by a same field (Project Number) and different fields within each table.

I'm not sure what you mean by the above statement. Can you provide your table structure with the key fields and the fields that you want to search?

 

[cfreeh]

I have a SPA table and SPA Task table - attached are screen shots of both tables..... (SPA = Safe Plan of Action)

I'm wanting to search all fields in both tables which are used on one form and find a record that would have a particular word within any field. does that make sense?

 

[jzwp22]

There were no attachments. You could represent the tables as follows

tblSAP
-pkSAPID primary key,autonumber
-txtFieldName1
others

tblSAPTasks
-pkSAPTaskID primary key, autonumber
-fkSAPID foreign key to tblSAP
-txtFieldName1
-txtFieldName2

Where the txt fields in both tables are the ones you want to search.

 

[cfreeh]

SPA Table
ID primary key, Autonumber
PROJECT NUMBER, text
DATE, Date/Time
JOB / TASK, text
WORK AREA, text
CLASSIFICATION, text

SPA Task Table
ID primary key, Autonumber
PROJECT NUMBER, text
STEPS OF TASK, text
HAZARD/REACTION, text
SAFE PLAN, text
RESOURCES, text
TASK ENTRY DATE, date/time

The link between the two tables are PROJECT NUMBER. I have a form based on the SPA Table with a sub-form on the SPA Task table. In order for personnel to find a record using a particular word, (ie: they know the word Lubrication is somewhere in the task table) I would like to have a button that they click on, type in a word and it goes to the first record with that word in it, then the next record and so on. Ideally I would a record set to open with all the records containing that word, so they can select the record and possibly duplicate the record with a new project number.

One set at a time though, just being able to find a record with a particular word will be a great start!

Thanks

 

[jzwp22]

First, I have a few suggestions. It is generally recommended to make joins between tables via numeric fields since relational database are more efficient with numeric rather than text fields. Also, it is best to not have spaces or special characters in your table or field names. There are also reserved words in Access that should not be used as table or field names. The words name, date and time are just 3 of them. So with that, this is how I would rework your tables:

SPATable
-pkProjectID primary key, Autonumber
-PROJECTNUMBER, text
-ProjectDate, Date/Time
-JOBTASK, text
-WORKAREA, text
-CLASSIFICATION, text

SPATaskTable
-pkSPATaskID primary key, Autonumber
-fkProjectID foreign key relating back to SPATable
-STEPSOFTASK, text
-HAZARDREACTION, text
-SAFEPLAN, text
-RESOURCES, text
-TASKENTRYDATE, date/time


If I were to assume that you want to search all text fields except the project number, the query would look something like this.

SELECT DISTINCT SPA.pkProjectID
FROM SPA INNER JOIN SPATasks ON SPA.pkProjectID=SPATasks.fkProjectID
WHERE SPA.JobTask Like "*" & [keyword] & "*" Or SPA.WorkArea Like "*" & [keyword] & "*" Or SPA.Classification Like "*" & [keyword] & "*" Or SPATasks.StepsOfTask Like "*" & [keyword] & "*" Or SPATasks.HazardReaction Like "*" & [keyword] & "*" Or SPATasks.SafePlan Like "*" & [keyword] & "*" Or SPATasks.Resources Like "*" & [keyword] & "*";

Is this what you are basically looking for? If so, then we just have to be able to filter your form with the above query.

 

[cfreeh]

Thank you for the help.....

I have changed my tables accordingly to your suggestions (field naming conventions) and have inserted the query into my database. I am not able to find all of the records with the "keyword". It is only giving me 8 out of 27 records with the keyword. Any further suggestions as to how I can correct the query?

Thanks
Cindy

 

[jzwp22]

Since the query is working for the most part, it is probably something subtle that is responsible. It will take a systematic approach to see which part of the WHERE clause is at fault.

In the records that were not returned, is there a specific field in which the keyword exists?

Another approach is to remove all conditions from the WHERE clause and add 1 back at a time and checking to make sure the appropriate records are returned with each successive addition.

Can you post the SQL text of your query? Perhaps there is a typo that is affecting the returned results.

 

[cfreeh]

This is the SQL text:
SELECT DISTINCT SPA.pkProjectID, SPA.PROJECTNUMBER
FROM SPA INNER JOIN SPATASKS ON SPA.PROJECTNUMBER = SPATASKS.fkProjectID
WHERE (((SPA.JOBTASK) Like "*" & [keyword] & "*")) OR (((SPA.WORKAREA) Like "*" & [keyword] & "*")) OR (((SPATASKS.STEPSOFTASK) Like "*" & [keyword] & "*")) OR (((SPATASKS.HAZARDREACTION) Like "*" & [keyword] & "*")) OR (((SPATASKS.SAFEPLAN) Like "*" & [keyword] & "*")) OR (((SPATASKS.RESOURCES) Like "*" & [keyword] & "*"));

 

[jzwp22]

I do not see anything wrong with the query, so you'll have to try the other approaches I mentioned.

If you would like me to take a look, can you post your database with any sensitive data removed?

 

[cfreeh]

Is there a way I can send you the database without going through this forum?

 

[jzwp22]

I've sent you a friend request. Once you accept that, I think that you should be able to see my e-mail address in my profile.

 

[cfreeh]

I'm trying to accept your friend request but it has an error on the page

 

[jzwp22]

I changed the privacy setting on my contact info so you should be able to see my e-mail address. I tried to send you an IM but that failed too. You might have to have a miniumum # of posts before some features kick in but I could not find anything that said that for sure.

 

[MSAccessRookie]

Question:

In the Table SPA Only, are the fields SPA.pkProjectID, and SPA.PROJECTNUMBER distinct?

Your Query Selects Distinct entries. I assume that this is because a given keyword could result in the same record for more than one of the Criteria. If it can also result in more than one record for the same Criteria, then this could account for some (if not all) of the missing records that you referred to.

You might want to try using a Union Query that does not use the Distinct qualifier and has an additional Field to show what matched. By doing this you might be able to get the missing records back. Once you have done this, you can work on modifying the Query to bring back only the results that you desire. Something like this would be a good starting point:

SELECT SPA.pkProjectID, SPA.PROJECTNUMBER, "JOBTAS" AS WhereFound
FROM SPA INNER JOIN SPATASKS
ON SPA.PROJECTNUMBER = SPATASKS.fkProjectID
WHERE SPA.JOBTAS Like "*" & [keyword] & "*" UNION
SELECT SPA.pkProjectID, SPA.PROJECTNUMBER, "WORKAREA" AS WhereFound
FROM SPA INNER JOIN SPATASKS
ON SPA.PROJECTNUMBER = SPATASKS.fkProjectID
WHERE SPA.WORKAREA Like "*" & [keyword] & "*" UNION
SELECT SPA.pkProjectID, SPA.PROJECTNUMBER, "STEPSOFTASK" AS WhereFound
FROM SPA INNER JOIN SPATASKS
ON SPA.PROJECTNUMBER = SPATASKS.fkProjectID
WHERE SPATASKS.STEPSOFTASK Like "*" & [keyword] & "*" UNION
SELECT SPA.pkProjectID, SPA.PROJECTNUMBER, "HAZARDREACTION" AS WhereFound
FROM SPA INNER JOIN SPATASKS
ON SPA.PROJECTNUMBER = SPATASKS.fkProjectID
WHERE SPATASKS.HAZARDREACTION Like "*" & [keyword] & "*" UNION
SELECT SPA.pkProjectID, SPA.PROJECTNUMBER, "SAFEPLAN" AS WhereFound
FROM SPA INNER JOIN SPATASKS
ON SPA.PROJECTNUMBER = SPATASKS.fkProjectID
WHERE SPATASKS.SAFEPLAN Like "*" & [keyword] & "*" UNION
SELECT SPA.pkProjectID, SPA.PROJECTNUMBER, "RESOURCES" AS WhereFound
FROM SPA INNER JOIN SPATASKS
ON SPA.PROJECTNUMBER = SPATASKS.fkProjectID
WHERE SPATASKS.RESOURCES Like "*" & [keyword] & "*";
​

Note that this code is untested, but should provide an appropriate framework to start from.

 

[jzwp22]

I should have mentioned that I used DISTINCT in the query because the ultimate goal was to use this query to get the filtered results into a form. The query was just a way of isolating the relevant project ID's.

 

[cfreeh]

I'm still not able to accept you as a friend.... I don't know if I can attach for everyone to view on here or not. Any other suggestions as to how I can send you the database?

Thanks
Cindy

 

[cfreeh]

MSAccessRookie: I tried your SQL Statement and am getting the same results.... thank you for your suggestion, any other help will be appreciated.

Cindy

 

[jzwp22]

You should be able to attach a database to your post. Check the FAQ section for more details. I thought you wanted to e-mail it to me because it contained sensitive data. If not, then posting it here would be best.

 

[jzwp22]

BTW, what keyword did you use that only returned 8 of the 27 project records?

I noticed that your project number field was not a number but text. That field was not included in the query. Could that have been the source of the missing records?


Here is the amended query.

SELECT DISTINCT SPA.pkProjectID
FROM SPA INNER JOIN SPATASKS ON SPA.PROJECTNUMBER=SPATASKS.fkProjectID
WHERE (((SPA.JOBTASK) Like "*" & [keyword] & "*")) OR (((SPA.WORKAREA) Like "*" & [keyword] & "*")) OR (((SPATASKS.STEPSOFTASK) Like "*" & [keyword] & "*")) OR (((SPATASKS.HAZARDREACTION) Like "*" & [keyword] & "*")) OR (((SPATASKS.SAFEPLAN) Like "*" & [keyword] & "*")) OR (((SPATASKS.RESOURCES) Like "*" & [keyword] & "*")) OR  (((SPA.PROJECTNUMBER) Like "*" & [keyword] & "*"))