ACCESS FEED: Re: [MS_AccessPros] Query by form on a union query

Selasa, 12 Agustus 2014

Re: [MS_AccessPros] Query by form on a union query

I took out the OrgID_PK field but I'm still not getting any results. Could it be because I'm using a select query as a data source and the other 3 are tables?

Karen

On Tuesday, August 12, 2014 10:12 AM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Karen-

I would expect to work on all the fields except maybe OrgID_PK that returns some null values. How are you executing the search on the form? If adding a filter to the UNION query worked, then you should also be able to define a filter on the form.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Aug 12, 2014, at 5:00 PM, K J k2j1203@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

My apologies. Here is the sql for the union query and the select query.

Select query
SELECT tblOrganization.Dept, tblOrganization.OrgID_PK, tblActivities.ActivityID_PK, tblActivities.[Input Date], tblActivities.ActivityName, tblOrganization.OrgName, tblActivities.Address, tblActivities.City, tblActivities.Zip, tblActivities.ActivityContact, tblActivities.ContactPhone, tblActivities.[Activity Originator], tblActivities.ActivityDate
FROM tblOrganization LEFT JOIN tblActivities ON tblOrganization.OrgID_PK = tblActivities.OrgID_FK;

Union query
SELECT [Dept], [ActivityID_PK], [OrgId_PK], [Input Date], [ActivityName], [OrgName], [Address], [City], [Zip], [ActivityContact], [Activity Originator], [ActivityDate] from [qryFHN_Activities]
UNION
SELECT [Dept], [Pending Activity ID], NULL AS [OrgID], [Input Date], [Event Name], [Host Organization Name], [Address], [City], [Zip], [Org Contact Name], [FHN or CCAI Coordinator], [Event Date] from [Pending Activities]
UNION
SELECT [Dept], [Program ID], NULL AS [OrgID], [Input Date], [Event Name], [Organization Name], [Address], [City], [Zip], [Main Contact], [Originator], [Start Date] from [Outreach Activities]
UNION SELECT [Dept], [Program ID], NULL AS [OrgID], NULL AS [InputDate], [Event Name], [Organization Name], [Address], [City], [Zip], [Main Contact], [Originator], [Start Date] from [Medicare Activities];

Karen

On Tuesday, August 12, 2014 9:40 AM, "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Karen,
It would help if you provided the SQL views of your queries.
Are any of your fields "Lookup Fields" defined in your table designs?

Duane Hookom MVP
MS Access

Subject: [MS_AccessPros] Query by form on a union query
Hello everyone.

Situtation: I created a union query selecting data from 4 different tables. I set up a form to display the information and run a search for data using query by form. This works just fine. However, 3 of the tables have a field for organization name and one doesn't. The one that doesn't has and OrgID field that links to another table Activities by OrgID. So, in order to get the name of the organization added to the table I used in my union query, I created a separate select query to add the orgranization name. I tried replacing the 4th table in the union query with the select query. If I just run the union query, not using the form, everything looks great. Then I added search criteria to the union query in the query design mode and the query works fine. However, when I try to search by form, I don't get any results.

I hope I explained everything well.

Is there a way to get the results that I need using query by form or is that just not possible?

Karen