Re: Subform and Form

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: bdehning (bdehning_at_discussions.microsoft.com)
Date: 12/27/04 

Date: Sun, 26 Dec 2004 16:57:02 -0800

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?

"Steve Schapel" wrote:

> Brian,
>
> Ok, thanks for the further explanation.
>
> It seems to me that Account Information and Policy Number should *not*
> be fields in each table. Clearly, Policy Number should be a field in
> both the Account Information and Location tables, as this is the field
> which is the basis of the relationship between them. And, if I
> understand you correctly, LocationID should be a field in both the
> Location and Service Calls tables, as this is the field which is the
> basis of the relationship between them. But it is not correct for
> Policy Number to be in the Service Calls table. I am not sure what the
> Account Information field is all about, but I imagine this only belongs
> in one table, definitely not all three. You are right in your approach
> to using queries to retrieve the information you need when it is spread
> over more than one related table.
>
> --
> Steve Schapel, Microsoft Access MVP
>
> bdehning wrote:
> > I have 3 tables Account Information, Location and Service Calls.
> > "Account Information" is 1-many to " Location" by PK Policy Number
> > "Location" is 1-many to "Service Calls" by PK Location ID. Location and
> > Service Calls tables have appropriate foreign keys.
> >
> > Account Information and Policy Number are fields in each table.
> > Unfortunately when I create and run certain queries I do not always capture
> > the Policy Number and Account Name in each of the 3 tables which causes some
> > difficulty with other queries later. This happens in the Service Calls table.
> >
> >
> > Am I missing some link somwhere. Based on my relationships, is my query
> > structured wrong as is why I may not be able to get the fields Account
> > Information or Policy Number or do I need to create another query to do that?
> >
> >
>

Relevant Pages

  • Re: Subform and Form
    ... The second query is already based on all 3 tables, ... Policy Number in the Account Information table, ... > 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, ...
    (microsoft.public.access.forms)
  • Re: Subform and Form
    ... It seems to me that Account Information and Policy Number should *not* ... > Unfortunately when I create and run certain queries I do not always capture ... > Information or Policy Number or do I need to create another query to do that? ...
    (microsoft.public.access.forms)
  • Subform and Form
    ... The Problem is that 2 fields that are in the Account Information Table are ... not in the Service Call Table after the subform is completed for an account. ... so the Account Inforamation and Policy Number will ...
    (microsoft.public.access.forms)
  • Re: GPO causing client security logs to fill?
    ... a virus in play. ... settings to be applied on your client workstations. ... Group Policy is a complex and often misunderstood beast. ... I modified the account ...
    (microsoft.public.windows.server.sbs)
  • Re: The local policy of this system does not permit you to logon i
    ... Security policies were propagated with warning. ... Error 0x534 occurs when a user account in one or more Group Policy objects ... I have checked the security policies & the administrator profile is not ...
    (microsoft.public.windows.server.sbs)