Re: Subform and Form

From: Steve Schapel (schapel_at_mvps.org.ns)
Date: 12/27/04 

Date: Mon, 27 Dec 2004 21:38:09 +1300

Brian,

The first query contains a [Service Calls].[Policy Number] field which I
have already advised you to remove from this table.

The second query is already based on all 3 tables, so you should be able
to include any fields you like from these tables. However, you have a
RIGHT JOIN between the Account Information and Location tables, which
doesn't seem to make sense. What was your purpose in doing this? It
means to return all Location records, even if there is no matching
Policy Number in the Account Information table, which should never
happen (as I understand it).

Also, the idea of a form based on a query with all 3 tables would be
very unusual in this type of design. The whole concept of a
form/subform construct is that the main form and subform are based on
tables or queries which are in a one-to-many relationship to each other,
and which "work" by way of the link between them as defined by the
subform's Link Master Fields and Link Child Fields property settings. 

-- 
Steve Schapel, Microsoft Access MVP
bdehning wrote:
> Ok, now that you helped straighten me out on that here is my current issue 
> then!
> 
> I have two forms which I use to enter fields.  One form has a subform and 
> the other is a form with many fields showing existing data and users fill in 
> the remaining fields.  Both of these forms use fields from all 3 tables.  
> Each query for the form on its own will show the Account Name and Policy 
> Number for each record.   But when I try and join these 2 queries together, 
> some of the records leave the Account Name and Policy Numbers blank.  Here 
> are the 2 queries.  
> 
> SELECT [Service Calls].[Policy Number], [Service Calls].[Service Call 
> Number], [Service Calls].[Account Name], [Service Calls].[Location ID], 
> [Service Calls].[Date Call Completed], [Service Calls].[Date Written Report 
> Sent], [Service Calls].Comments, [Service Calls].[Assigned Consultant]
> FROM [Service Calls];
> 
> 
> SELECT Location.[Assigned Consultant], [Service Calls].[Service Call 
> Number], [Service Calls].[Scheduled Service Month], Location.[Location 
> Address], Location.[Location City], Location.[Location State], 
> Location.[Location Zip Code], [Service Calls].[Date Call Completed], [Service 
> Calls].[Date Written Report Sent], [Service Calls].[Cancel Service Call], 
> [Service Calls].[Waive Service Call], [Service Calls].Comments, [Service 
> Calls].[Rescheduled Service Month], [Service Calls].[INITIAL EVALUATION], 
> [Service Calls].[PROGRAM/MGMT EVALUATION], [Service Calls].[JOBSITE SURVEY], 
> [Service Calls].[OPERATIONS SURVEY], [Service Calls].[LOSS ANALYSIS], 
> [Service Calls].[ACCIDENT INVESTIGATION], [Service Calls].TRAINING, [Service 
> Calls].[SAFETY MEETING], [Service Calls].[ACTION PLANNING], [Service 
> Calls].[PROGRESS REPORT], [Service Calls].[INDUSTRIAL HYGIENE/ERGONOMICS], 
> [Service Calls].[PHONE SURVEY/OTHER], [Service Calls].Evaluation, [Service 
> Calls].Recommendations, [Service Calls].[Call Type], [Account 
> Information].[Account Name], [Service Calls].[Type of Service], [Service 
> Calls].[Service Call Type], [Service Calls].[Assigned Consultant], 
> Location.[Policy Number]
> FROM ([Account Information] RIGHT JOIN Location ON [Account 
> Information].[Policy Number] = Location.[Policy Number]) INNER JOIN [Service 
> Calls] ON Location.[Location ID] = [Service Calls].[Location ID];
> 
> Can you help me so that all records will show Policy Number and Account Name 
> when I join these 2 queries in one query?  Does this make sense?
>

Relevant Pages

  • Re: Subform and Form
    ... Each query for the form on its own will show the Account Name and Policy ... But when I try and join these 2 queries together, ... FROM ([Account Information] RIGHT JOIN Location ON [Account ...
    (microsoft.public.access.forms)
  • Re: joining queries!! how!
    ... account heads & budget amounts. ... column for the tables related to the two queries. ... >with a query that for sure gets ... >> One query is for cash expenses. ...
    (microsoft.public.access.queries)
  • Re: Help with Cartesian or cross joins
    ... Showing how these Queries produce results different from what you want would go a long way toward helping ... And we have the Table of existing, and thus unavailable, account numbers at each Branch. ... Now we create a Query to list all ... This Query lists account numbers up to 998 for Branch "01" and up to 997 for Branch "02", ...
    (microsoft.public.access.queries)
  • RE: set record source of subreport
    ... I was looking for the most efficient method, but unfortunately, my queries ... the filtering by account from the query and do it in the OpenReport method. ... It will filter the report by ...
    (microsoft.public.access.reports)
  • Union Query Problems
    ... I have 2 queries which work fine by themselves but having trouble getting ... Here is first Query: ... FROM ([Account Information] RIGHT JOIN Location ON [Account ... The Union query works fine until I try to add the nonblankcomments field ...
    (microsoft.public.access.queries)