Query to produce results filted by current record on form

[kelann]

Hi, all!



I have a form (YoungProfMatchF) wherein there is a combo box linked to a table (YoungProfT) and field (YPName). The user selects a person (YPName) from the combo box, then presses a button that runs the below query (YPMatchQ) created in SQL view. The query shows the results of ALL YoungProfs and Orgs that have either (or both) of two fields in common (I added the // lines only in this forum post):

// DISPLAY THE ORGANIZATION NAMES AND CORRESONDING YOUNG PROFESSIONAL NAMES
SELECT OrganizationsT.OrganizationName, YoungProfT.YPFirstName

// WHERE BOTH THE ORG & YP HAVE CHOSEN EITHER 'MUSIC' OR 'ENTREPRENEURSHIP' OR BOTH
FROM OrganizationsT INNER JOIN YoungProfT ON (OrganizationsT.OrgMusic = True AND YoungProfT.YPMusic = True) OR (OrganizationsT.OrgEntrepreneurship = True AND YoungProfT.YPEntrepreneurship = True);

I would, however, like it to only show the ORGs corresponding to the YoungProf selected in the combo box.

Is there any way to add this filtering to my query?

With thanks!!

--ak

[June7]

Reference the combobox in query criteria under the appropriate field.

Forms!YoungProfMatchF!comboboxname

[kelann]

Reference the combobox in query criteria under the appropriate field.

Forms!YoungProfMatchF!comboboxname

Thank you very much -- I'm not at work so I can't test it this moment, so I hope you don't mind me asking -- since the query is in the SQL view, can I still put that statement in the query criteria or does it have to be inserted somehow in the SQL statement?

--ak

[June7]

You can type it into the SQL View window but probably easier in the query design grid. Let Access do the work of making sure SQL syntax is correct. Access will offer help with popup tips in the design grid.

[kelann]

Hi, June!

I have finally gotten back to my database, but I am still having some trouble.

I have a form (MatchF), where the user selects a young professional from a combobox (YPMatchSearch) that looks up from the young professional table (YPsT). They then press the MatchQ button. Currently, the MatchQ gives me a table that lists all possible matches of YPs and organizations, with organizations on the left and matching YPs on the right. An organization is listed once for each YP it matches with, so it can be listed several times on the query result. It is written thus (note the item in red):

SELECT OrganizationsT.OrgName, [YPsT].YPLookupName
FROM OrganizationsT INNER JOIN YPsT ON (OrganizationsT.[OrgArtsCulture]=True And [YPsT].[YPArtsCulture]=True)
Or (OrganizationsT.[OrgEnvironmental]=True And [YPsT].[YPEnvironmental]=True)
Or (OrganizationsT.[OrgHealthcare]=True And [YPsT].[YPHealthcare]=True)
Or (OrganizationsT.[OrgYouthMentorship]=True And [YPsT].[YPYouthMentorship]=True)
Or (OrganizationsT.[OrgPovertyBasicNeeds]=True And [YPsT].[YPPovertyBasicNeeds]=True)
Or (OrganizationsT.[OrgSportsAthletics]=True And [YPsT].[YPSportsAthletics]=True)
Or (OrganizationsT.[OrgIntlDiversity]=True And [YPsT].[YPIntlDiversity]=True)
Or (OrganizationsT.[OrgBusiness]=True And [YPsT].[YPBusiness]=True);

What I would like for it to do is to only give me results for the YP selected in the YPMatchSearch combo box.

I tried adding a reference to the combo box on the form, but my result is a table that lists the organizations on the left -- in the same way as above, but on the right is my forms referene (in red below) as a column header with no data in it:

SELECT OrganizationsT.OrgName, Forms!MatchF!YPMatchSearch
FROM OrganizationsT INNER JOIN YPsT ON (OrganizationsT.[OrgArtsCulture]=True And [YPsT].[YPArtsCulture]=True)
Or (OrganizationsT.[OrgEnvironmental]=True And [YPsT].[YPEnvironmental]=True)
Or (OrganizationsT.[OrgHealthcare]=True And [YPsT].[YPHealthcare]=True)
Or (OrganizationsT.[OrgYouthMentorship]=True And [YPsT].[YPYouthMentorship]=True)
Or (OrganizationsT.[OrgPovertyBasicNeeds]=True And [YPsT].[YPPovertyBasicNeeds]=True)
Or (OrganizationsT.[OrgSportsAthletics]=True And [YPsT].[YPSportsAthletics]=True)
Or (OrganizationsT.[OrgIntlDiversity]=True And [YPsT].[YPIntlDiversity]=True)
Or (OrganizationsT.[OrgBusiness]=True And [YPsT].[YPBusiness]=True);

Can you suggest how best to modify the SQL? I can't do it in the query table, because I get a message that says my join can't be represented. Then it shows what it can and wipes out the rest from my SQL statement.

Many, many thanks for your thoughts!!!

--ak

[June7]

Your JOIN clause is incomplete.

A JOIN clause is where primary key/foreign key fields are linked, like:

FROM OrganizationsT INNER JOIN YPst ON [OrganizationsT].[ID]=[YPsT].[OrgID]

The selection in combobox should be filter criteria.

WHERE YPLookupName = Forms!MatchF!YPMatchSearch

Use the query designer to build query. Drag tables into the design window, set joins if designer doesn't (if you have established table relationships, the joins will automatically set, if not, you must set them), drag fields to the grid, set criteria under appropriate fields.

[kelann]

Thanks, June! Are you saying the code should look like this? I don't understand the italics underlined part in the 2nd line. I will try to use the query builder, but I find it more confusing than SQL -- not that I understand SQL very much, either. I am sort of bumping my way through, but I am keeping copious notes!!

SELECT OrganizationsT.OrgName, [YPsT].YPLookupName
FROM OrganizationsT INNER JOIN YPst ON [OrganizationsT].[ID]=[YPsT].[OrgID]
WHERE YPLookupName = Forms!MatchF!YPMatchSearch
ON (OrganizationsT.[OrgArtsCulture]=True And [YPsT].[YPArtsCulture]=True)
Or (OrganizationsT.[OrgEnvironmental]=True And [YPsT].[YPEnvironmental]=True)
Or (OrganizationsT.[OrgHealthcare]=True And [YPsT].[YPHealthcare]=True)
Or (OrganizationsT.[OrgYouthMentorship]=True And [YPsT].[YPYouthMentorship]=True)
Or (OrganizationsT.[OrgPovertyBasicNeeds]=True And [YPsT].[YPPovertyBasicNeeds]=True)
Or (OrganizationsT.[OrgSportsAthletics]=True And [YPsT].[YPSportsAthletics]=True)
Or (OrganizationsT.[OrgIntlDiversity]=True And [YPsT].[YPIntlDiversity]=True)
Or (OrganizationsT.[OrgBusiness]=True And [YPsT].[YPBusiness]=True);

[June7]

I don't know your names for the pk/fk fields so the example I offered is generic. You have two tables in this query, how are they related? What are the pk/fk fields?

Review http://www.opengatesw.net/ms-access-...cess-Query.htm

[kelann]

In actuality, the tables aren't related at all.

Each of the YPs and the Orgs have check boxes for interests. YPs interests begin with "YP" (i.e., YPEnvironmental) and Orgs beginning with "Org" (i.e., OrgEnvironmental). What the query is supposed to do is look at the interests of the YP currently selected on the form and produce a list of organizations that have at least one interest in common.

The name of the combo box on the MatchF where the user selects a young professional is YPMatchSearch.

The "ON" phrase below begins where it compares interests.

The below works great, but it works for ALL records, and I only want it to work on the record selected in Forms!YPMachF!YPMatchSearch.


SELECT OrganizationsT.OrgName
FROM OrganizationsT INNER JOIN YPsT
ON (OrganizationsT.[OrgArtsCulture]=True And [YPsT].[YPArtsCulture]=True)
Or (OrganizationsT.[OrgEnvironmental]=True And [YPsT].[YPEnvironmental]=True)
Or (OrganizationsT.[OrgHealthcare]=True And [YPsT].[YPHealthcare]=True)
Or (OrganizationsT.[OrgYouthMentorship]=True And [YPsT].[YPYouthMentorship]=True)
Or (OrganizationsT.[OrgPovertyBasicNeeds]=True And [YPsT].[YPPovertyBasicNeeds]=True)
Or (OrganizationsT.[OrgSportsAthletics]=True And [YPsT].[YPSportsAthletics]=True)
Or (OrganizationsT.[OrgIntlDiversity]=True And [YPsT].[YPIntlDiversity]=True)
Or (OrganizationsT.[OrgBusiness]=True And [YPsT].[YPBusiness]=True);

I can't figure out what to insert or where to make it work only on the current record, and I can't get this into a query table. I'm too new, and I just can't make it work in one no matter how hard I try, and the SQL is working (generallY, so I can't justify burning time on trying to squeeze it into the table.

Thank you so much for any assistance!!

--ak

[June7]

I don't understand how that query produces anything. Seems the join clause should error. Here is what I think should work:

SELECT DISTINCT OrganizationsT.OrgName FROM OrganizationsT, YPsT
WHERE YPsT.YPLookupName = Me.YPMatchSearch AND
((OrganizationsT.[OrgArtsCulture]=True And [YPsT].[YPArtsCulture]=True)
Or (OrganizationsT.[OrgEnvironmental]=True And [YPsT].[YPEnvironmental]=True)
Or (OrganizationsT.[OrgHealthcare]=True And [YPsT].[YPHealthcare]=True)
Or (OrganizationsT.[OrgYouthMentorship]=True And [YPsT].[YPYouthMentorship]=True)
Or (OrganizationsT.[OrgPovertyBasicNeeds]=True And [YPsT].[YPPovertyBasicNeeds]=True)
Or (OrganizationsT.[OrgSportsAthletics]=True And [YPsT].[YPSportsAthletics]=True)
Or (OrganizationsT.[OrgIntlDiversity]=True And [YPsT].[YPIntlDiversity]=True)
Or (OrganizationsT.[OrgBusiness]=True And [YPsT].[YPBusiness]=True));

Without a JOIN clause, the query will pair up every YP with every Org.

[kelann]

Thanks, June!

Originally, it did produce every permutation, as you suggested in the last line of your post above. Then I put in that INNER JOIN command, and it now connects them correctly, but it does it for everyone.

I put in your code, and I get an "Enter Parameter Value" for Me.YPMatchSearch.

I tried replacing the text in red (YPsT.YPLookupName = Me.YPMatchSearch) it with Forms!MarchSelectedYPF!YPMatchSearch (YPMatchSearch is on a form, then they press a button that runs our query), and I don't get an error, but I get a blank result.

Any thoughts? I really appreciate it!!!

--ak

[June7]

Sorry, I was mixing VBA code and query designer syntax when I wrote the sql statement. So instead of Me, the form reference is correct.

Must be something about the WHERE clause. Build the query in steps. Try without the WHERE and see if the pairings are happening:
SELECT OrgName, YPLookupName FROM OrganizationsT, YPsT;

If that works, try:
SELECT OrgName, YPLookupName FROM OrganizationsT, YPsT
WHERE YPsT.YPLookupName = Me.YPMatchSearch;

If that works, try:
SELECT OrgName, YPLookupName FROM OrganizationsT, YPsT
WHERE YPsT.YPLookupName = Me.YPMatchSearch AND
((OrganizationsT.[OrgArtsCulture]=True And [YPsT].[YPArtsCulture]=True));

If that works, etc.

To help further, I would need to work with data. If you want to provide db for analysis, follow instructions at bottom of my post.

[kelann]

SELECT OrgName, YPLookupName FROM OrganizationsT, YPsT;

This produces every organization with every young professional.

SELECT OrgName, YPLookupName FROM OrganizationsT, YPsT
WHERE YPsT.YPLookupName = Me.YPMatchSearch;

This produces an Enter Parameter Value error for Me.YPMatchSearch

I'm attaching my database -- WOW! Compact and repair took it from 8.3 meg to 1.3 meg!

The form in question with the button is MatchSelectedYPF. The only relevant fields are the header (which is YPMatchSearch) and the button. The button runs the query YPMatchQSelected.

Thank you, thank you, thank you for any assistance you can provide!

And have a great weekend!

--ak

[June7]

Sorry, I did it again, use form reference instead of the Me alias.

Yes, C&R should be run periodically, especially after design edits. Can set this to run automatically when file closes.

[kelann]

Thank you -- I will try this again on Monday. Have a great weekend!