Subform and Form

[Guest]

I have a subform which has 3 fields from the last of my 3, 1-Many table
relationships. The main form shows data in the fields from the Main table
which contains the Account Information.

Users use the subreport to enter infromation about Accounts they select up
in the main form area.

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.

If I can see the 2 fields with data in the main form, how can I place those
two fields in the subform, so the Account Inforamation and Policy Number will
also be shown in the Service Call table when the subform is completed?

Does this make sense?

 

[Steve Schapel]

Brian,

Presumably there is a field in the Service Call table which is the basis
of its relationship to the Account Information table via the Account
Information table's primary key field? The general concept here should
be that this is the only field which is represented in both tables. The
entry of the data into this field in the Service Call via the subform,
will be automatic if you set the subform's Link Master Fields and Link
Child Fields properties to show the linking field from both tables. Any
other data which is already in Account Information should not also be in
Service Call, and any such fields should be deleted from Service Call
table. Any time you need data from both tables, for your purposes on
form or report, it can easily be obtained via a query which includes
both tables.

 

[Guest]

Steve, I am getting closer to what I want. I was able to place two fields on
the Subform and they now appear in the table with the subform fields after
subform is completed.

The problem now is that when I use the query to produce the results I want
the two fields Account Information and Policy number are pulled as expected
from the Service Call Table and the other records show these fields from the
Account Information Table.

How can I use a query or what can I do to get the results from Service Call
into the fields in the query so that all are in the same Account Information
and Policy Number fields so my report will work correctly later. I am sure I
can use a union query but do to use of memo fields I need another strategy.
THe report can sort the records to the correct Account or Policy Number
because I can only use one field on the report.

Seems like this should be fairly straight forward.

 

[Steve Schapel]

Brian,

What are the Account Information and Policy Number fields? Which
table(s) are these fields in? What is the basis of the relationship
between the two tables? Maybe you could post back with a list of the
fields that are in each table.

 

[Guest]

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?

 

[Steve Schapel]

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.

 

[Guest]

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]

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

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?

 

[Guest]

Steve,

Thanks for setting me straight again on my structrure.

What I had done was base my form and subform on the tables instead of
queries which worked much better to bring fields from all 3 tables as you
noted. I had the child and master links but was doing what you noted as bad
structuring of a relational database and was trying to get field results in
tables that was not needed based on the database structure. The right join
was a result of query I needed to join until I redid my database as you so
nicely pointed out.

This should also help me with any other bad designs that I may have done as
well which may cause some queries to be modified.

Thanks again.

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

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]

Brian,

I gather we have made progress here, which is good to know. Please post
back if you need any more help with this.

 

[Guest]

I have a subform which has 3 fields from the last of my 3, 1-Many table
relationships. The main form shows data in the fields from the Main table
which contains the Account Information.

Users use the subreport to enter infromation about Accounts they select up
in the main form area.

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.

If I can see the 2 fields with data in the main form, how can I place those
two fields in the subform, so the Account Inforamation and Policy Number will
also be shown in the Service Call table when the subform is completed?

Does this make sense?