Re: Subform and Form
From: bdehning (bdehning_at_discussions.microsoft.com)
Date: 12/27/04
- Next message: Rich J: "Go To Main Form Record from selected subform record"
- Previous message: Dirk Goldgar: "Re: RecodSetClone vs RecordSet"
- In reply to: Steve Schapel: "Re: Subform and Form"
- Next in thread: Steve Schapel: "Re: Subform and Form"
- Reply: Steve Schapel: "Re: Subform and Form"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 27 Dec 2004 15:25:01 -0800
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.
"Steve Schapel" wrote:
> 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?
> >
>
- Next message: Rich J: "Go To Main Form Record from selected subform record"
- Previous message: Dirk Goldgar: "Re: RecodSetClone vs RecordSet"
- In reply to: Steve Schapel: "Re: Subform and Form"
- Next in thread: Steve Schapel: "Re: Subform and Form"
- Reply: Steve Schapel: "Re: Subform and Form"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
